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.

This entry was posted in Automation, Ruby and tagged , , . Bookmark the permalink.

4 Responses to Win32OLE and Ruby: working with worksheets

  1. Serguei Cambour says:

    What do you think about closing the Excel file/book like that:

    excel = WIN32OLE.new(“excel.application”)
    workbook = excel.Workbooks.Open(“path/to/some_book/file”)
    worksheet = workbook.Worksheets(“som_sheet_name”)
    …. reading cells values
    excel.ActiveWorkbook.Close(0) if excel.ActiveWorkbook
    excel.Quit()

    Any way, I found it Ruby std doc page for win32ole lib.
    But even with this code implemented, if I open a series of Excel files to read values, I still have a lot of Excel files left running in Windows Task manager.
    I tried it On Windows XP, SP3, Ruby 1.9.3.
    Any idea? Thank you.

  2. david says:

    I think your best option is to track the workbooks you open (e.g. in an array), then select each one and close it (for example within a Ruby block). Whether or not you want to call excel.quit will depend on whether your user want to use Excel without your script. In other words, if your user is running Excel, then executes your script and gets his Excel instance closed, he might be unhappy…

  3. Jacqueline says:

    What do you think about closing the Excel file/book like that:excel = WIN32OLE.new( excel.application )workbook = excel.Workbooks.Open( path/to/some_book/file )worksheet = woobkrok.Worksheets( som_sheet_name ) . reading cells valuesexcel.ActiveWorkbook.Close(0) if excel.ActiveWorkbookexcel.Quit()Any way, I found it Ruby std doc page for win32ole lib.But even with this code implemented, if I open a series of Excel files to read values, I still have a lot of Excel files left running in Windows Task manager.I tried it On Windows XP, SP3, Ruby 1.9.3.Any idea? Thank you.

  4. david says:

    I’ve found closing Workbooks to be somewhat tricky, especially since sometimes users already have Excel open (and are using other files). YOu could track all the workbooks you open, then select each one in turn before closing it. Of course, you’ll have to be careful about treating error cases properly. Otherwise, you’ll have unclosed Excel instances running after an exception.

    In the end, I’ve never really looked into closing the workbooks myself, because I usually presented the result to the user. The user can then close the workbooks, save as, etc…