Win32OLE and Ruby: working with worksheets

This post is part of a series. If you can’t understand what is detailed below, or can’t find the information you’re looking for, take a look at the index page.

First things first

Naturally, before you can use Win32OLE, you’ll have to include it in your Ruby script:

require 'win32ole'

Getting a hold of Excel

Once that is done, you have to get an Excel application object to be able to do any magic. You can do this in several ways:

1. Excel isn’t running, so you will create a new instance:

   excel = WIN32OLE::new('excel.Application')

2. Excel is already running, so you will simply connect to that instance instance:

   excel = WIN32OLE::connect('excel.Application')

Excel’s data structure

Now that you have a means to manipulate Excel with Ruby, how do you get to the worksheet with your data? First, let me explain a little about how data is structured within Excel:

1. The Excel application can have zero or more workbooks
2. Each workbook can have zero or more worksheets
3. Each worksheets contains rows, columns, and cells. This allows for different ways of addressing zones. (This probably makes no sense now, just keep it in mind for later on.)

On to worksheets

If you created a new instance of Excel earlier on, getting to a worksheet is fairly straightforward. The only trap is that this application will basically be empty, so you’ll have to create both a workbook, then one or more worksheets with the work book:

excel = WIN32OLE::new('excel.Application')
workbook = excel.Workbooks.Add # create a new workbook
worksheet = workbook.Worksheets.Add # create a new worksheet within the workbook we just created

If you’re connecting to an existing Excel instance, you’ll have to deal with the case where the user has other worksheets open that have nothing to do with whatever you’re attempting to accomplish. For simplicity’s sake, we’ll assume the worksheet you’re interested in is called “data”:

excel = WIN32OLE::connect('excel.Application')
worksheet = nil
excel.Workbooks.each{|wb # loop through all excel workbooks (i.e. open documents)
wb.Worksheets.each{|ws| # loop through each workbook's worksheets
if ws.name == "data"
worksheet = ws
break
end
}
break unless worksheet.nil?
}

Working with several worksheets

Naturally, a simple method to work with several distinct worksheets is simply to assign each of the to a separate variable. Another means is refer to them within their Workbook object. As you may have noticed in th code above, worksheets have a “name” attribute that corresponds to the text displayed on the worksheet’s tab. You can therefore access a specific worksheets form a workbook by calling its name:

worksheet = workbook.Worksheets("2009 report") # get the worksheet called '2009 report'

Alternatively, if you know where the worksheet is within the workbook, you can refer to it by this position number:

worksheet = workbook.Worksheets(2)# get the second worksheet in the workbook

Renaming, moving, and deleting worksheets

Worksheets can be renamed by assigning a value to the “name” attribute:

worksheet.name = "Summary"

Moving worksheets within the workbook is accomplished by using the move method:

worksheet_a = workbook.Worksheets(1)

worksheet_b = workbook.Worksheets(2)

worksheet_c = workbook.Worksheets(3)

# the worksheet order is currently a, b, c

worksheet_c.Move(worksheet_a, nil) # move the C worksheet before the A work sheet

# the worksheet order is now c, a, b

worksheet_a.Move(nil, worksheet_b) # move the A worksheet after the B work sheet

# the worksheet order is now c, b, a

Note how whether the worksheet gets moved before or after the argument worksheet depends on the argument position: use the first position to move before, and the second argument to move after.

Deleting worksheets is straightforward:

worksheet.Delete

Now that you understand how to work with workbooks and worksheets, let’s move on to doing something useful like working with cells and data.