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
There are no comments for this entry yet.
Leave a comment
