Saturday, March 17, 2007

Automating Excel with Ruby

EXAMPLE: Assuming that data represents a 2-dimensional array that you wish to write to a Microsoft Excel worksheet, here's a code snippet, with comments...


# Require the WIN32OLE library
require 'win32ole'
# Create an instance of the Excel application object
xl = WIN32OLE.new('Excel.Application')
# Make Excel visible
xl.Visible = 1
# Add a new Workbook object
wb = xl.Workbooks.Add
# Get the first Worksheet
ws = wb.Worksheets(1)
# Set the name of the worksheet tab
ws.Name = 'Sample Worksheet'
# For each row in the data set
data.each_with_index do |row, r|
# For each field in the row
row.each_with_index do |field, c|
# Write the data to the Worksheet
ws.Cells(r+1, c+1).Value = field.to_s
end
end
# Save the workbook
wb.SaveAs('c:\temp\workbook.xls')
# Close the workbook
wb.Close
# Quit Excel
xl.Quit

A key to automating Excel is understanding the Excel Object Model, which defines the objects, their properties and methods. Here's a few methods and resources that may help...

Record an Excel macro, then view the resulting VBA code to determine the necessary objects, properties, and methods.

Pick up a copy of 'Writing Excel Macros with VBA' by Steven Roman.

Google for 'Excel Object Model' to find info on the Excel Object Model.

15 comments:

Stephan said...

Hi,

personally, I rather use spreadsheet-excel and parseexcel (spreadsheet @ Rubyforge).
The interface seems simpler and more rubyesuqe to me. And you don't need to have Excel installed on the machine.
Apart from that you might consider wrapping the code in a begin ... ensure:

require 'win32ole'

begin
excel = WIN32OLE.new( "excel.application" )
# Use Excel here...
excel.workbooks.close
rescue => e
# Handle exception...
ensure
excel.Quit
end

That way you're pretty sure that the Excel application will in fact be quit when the script is done. Otherwise you'll have a dangling Excel instance, which might prevent a rerun of the program. At least that's what I experienced.

David Mullet said...

Good points, pitsula!

I'm very comfortable with COM automation, due to years of working with Microsoft VB and COM (we all have our dark sides), and the fact that Microsoft Office is always installed on all my users' PCs. As such, I haven't worked with the spreadsheet libraries you mentioned, but plan to in the near future.

For the sake of brevity, code snippets that I post here usually lack the error handling and such that should be included in a complete script/application. You provide a good example of using the 'ensure' statement to ensure that the Excel application exits, even in the event of an error.

Thanks!

Anonymous said...

Hmm, am I missing something?
Where does data come from?

C:/createExcel.rb:14: undefined local variable or method `data' for main:Object (NameError)

But anyway. A cool example!
Stefan E.

David Mullet said...

Stefan-

The example is somewhat incomplete, in that it assumes that data represents a 2-dimensional array that you have already created, perhaps from a database query, reading data from a file, etc..

Anonymous said...

Thanks for the hint!
(Stefan E)

Vasudev Ram said...

Hey, thanks for these posts !

Looks like quite useful info.
I plan to use some of this info in some upcoming open source projects and technical articles. Will mention that I got good info from your blog.

Vasudev Ram
Site: http://www.dancingbison.com
Blog: http://jugad.livejournal.com

Jim said...

Hi,

Great tutorial! However, when I try to run the example, I get the following error:

C:/Users/Jim/My Documents/Aptana Studio 3 Workspace/ExcelTest/ExcelTest.rb:3:in `initialize': failed to create WIN32OLE object from `Excel.Application' (WIN32OLERuntimeError)
HRESULT error code:0x80110474
The COM+ registry database detected a system error
from C:/Users/Jim/My Documents/Aptana Studio 3 Workspace/ExcelTest/ExcelTest.rb:3:in `new'
from C:/Users/Jim/My Documents/Aptana Studio 3 Workspace/ExcelTest/ExcelTest.rb:3:in `'

Any ideas what might be wrong?

Thanks!

Jim

David Mullet said...

@Jim: Assuming that you have no typos in your code, it appears there may be an issue with your installation of Excel. Note the line in the error "The COM+ registry database detected a system error". Reinstall Excel or repair your installation and send me an email if you are still having problems.

Anonymous said...

Hi...sorry but I've a bit problem..when I save the file..It's only for read and I can't write (I've change the permission manually)...can I do the file be save for read-write?..thanks.....

K.Funky said...

Hi,
this really is very interesting. The question I have concerning the Ruby Office interaction:
Is there a way to call ruby code from within Excel, similar as I would call a VBA macro?
Thanks for any hint on this issue

K.Funky said...

Hi,
this looks very interesting. I am wondering how the other direction would work. Is there a way to call Ruby code from within Excel, similar as you would call a VBA macro?
Thanks for any hints.

David Mullet said...

@K.Funky:

You can call the Ruby interpreter and run a Ruby script from within a VBA macro, via the Shell command:

Sub Macro1()
Shell "ruby.exe C:\my_script.rb", vbNormalFocus
End Sub

The first parameter is the shell command to execute. The second parameter is the WindowState, which can be one of the following Excel constants:

vbHide
vbMaximizedFocus
vbMinimizedFocus
vbMinimizedNoFocus
vbNormalFocus
vbNormalNoFocus

-David

Sagar said...

Excellent Post...Keep up the good work.

Anonymous said...

Can you please tell me how i can open the existing excel file and dd worksheet at the end with out over righting the already existing worksheets.......

David Mullet said...

@Anonymous:

Call the Worksheets.Add() method and pass it two arguments:

- nil
- an object that represents the last item in the Worksheets collection

Example, where wb is your open workbook:

last_worksheet = wb.Worksheets(wb.Worksheets.Count)
ws = wb.Worksheets.Add(nil, last_worksheet)