From time to time I like to actually post a bit of code on this programming blog, so here's
a stream-of-conscious (as in "not a lot of thought went into design quality") example that shows how to:
- Open Excel, making it invisible (or visible) to the user.
- Create a workbook and access individual worksheets
- Add data to a cell, or retrieve data from a cell
- Add a chart to a worksheet, with constants for various chart types
- Save as Excel 97-2003 format and close Excel
If you know where I can find the constants for file type numbers, that would be appreciated. Calling SaveAs
without the type seems to use whatever version of Excel you are running, but I'd like to find how to save as
CSV or other formats.
Needless to say, this requires Excel be on the computer that's running the code.
require 'win32ole'
xl = WIN32OLE.new("Excel.Application")
puts "Excel failed to start" unless xl
xl.Visible = false
workbook = xl.Workbooks.Add
sheet = workbook.Worksheets(1)
#create some fake data
data_a = []
(1..10).each{|i| data_a.push i }
data_b = []
(1..10).each{|i| data_b.push((rand * 100).to_i) }
#fill the worksheet with the fake data
#showing 3 ways to populate cells with values
(1..10).each do |i|
sheet.Range("A#{i}").Select
xl.ActiveCell.Formula = data_a[i-1]
sheet.Range("B#{i}").Formula = data_b[i-1]
cell = sheet.Range("C#{i}")
cell.Formula = "=A#{i} - B#{i}"
end
#chart type constants (via http://support.microsoft.com/kb/147803)
xlArea = 1
xlBar = 2
xlColumn = 3
xlLine = 4
xlPie = 5
xlRadar = -4151
xlXYScatter = -4169
xlCombination = -4111
xl3DArea = -4098
xl3DBar = -4099
xl3DColumn = -4100
xl3DLine = -4101
xl3DPie = -4102
xl3DSurface = -4103
xlDoughnut = -4120
#creating a chart
chart_object = sheet.ChartObjects.Add(10, 80, 500, 250)
chart = chart_object.Chart
chart_range = sheet.Range("A1", "B10")
chart.SetSourceData(chart_range, nil)
chart.ChartType = xlXYScatter
#get the value from a cell
val = sheet.Range("C1").Value
puts val
#saving as pre-2007 format
excel97_2003_format = -4143
pwd = Dir.pwd.gsub('/','\\') << '\\'
#otherwise, it sticks it in default save directory- C:\Users\Sam\Documents on my system
workbook.SaveAs("#{pwd}whatever.xls", excel97_2003_format)
xl.Quit
It's also posted
in my Miscellany project at GitHub
Hey! Why don't you make your life easier and subscribe to the full post
or short blurb RSS feed? I'm so confident you'll love my smelly pasta plate
wisdom that I'm offering a no-strings-attached, lifetime money back guarantee!
Leave a comment
Can you send me an email about how to get started with Ruby. I first need to use it to open Excel, go to a certain sheet, and enter some data (that I will read from a text file using Ruby.)I have been programming since 1970. I like new stuff. THANKS. Charlie
Posted by Charlie Koch
on May 03, 2010 at 07:03 PM UTC - 5 hrs
Hi Charlie,
The first few lines of code open Excel and go to the first sheet:
require 'win32ole'
xl = WIN32OLE.new("Excel.Application")
puts "Excel failed to start" unless xl
xl.Visible = false
workbook = xl.Workbooks.Add
sheet = workbook.Worksheets(1)
....
To enter a value in a cell use:
sheet.Range("A1").Formula = whateverData
Where "A1" is the cell.
To read from a file in Ruby, you can use:
File.open(file_path) do |file|
file.each_line do |line|
process the line here ... maybe use line.split(delimiter) to get it to an array
end
end
Hope that helps!
Posted by
Sammy Larbi
on May 04, 2010 at 07:17 AM UTC - 5 hrs
Hi
I have to read and fetch data from the ruby file(eg: properties.rb). The pattern of file is as:
##LOGIN PAGE##
xpath = "//input[@value='Login']"
I need to put all required xpath in one file and then fetch them as required in each test case. please let me know if this can be done and the code example. thanks
Posted by Harry
on Jun 22, 2010 at 07:00 AM UTC - 5 hrs
Harry,
It's not quite related to Excel as far as I can tell, and I'm not sure I fully understand the problem.
If you'd like, drop me a line via the contact page (
http://www.codeodor.com/Contact.cfm ) and I'll try to help you through email.
Thanks,
Sam
Posted by
Sammy Larbi
on Jun 22, 2010 at 07:31 AM UTC - 5 hrs
