Win32OLE and Ruby: working with worksheets

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:

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

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

On the other hand if 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')
# create a new workbook
workbook = excel.Workbooks.Add
# create a new worksheet within the workbook we just created
worksheet = workbook.Worksheets.Add
  
  

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
# loop through all excel workbooks (i.e. open documents)
excel.Workbooks.each{|wb|
  # loop through each workbook's worksheets
  wb.Worksheets.each{|ws|
    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:

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

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

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

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
 
# move the C worksheet before the A work sheet
worksheet_c.Move(worksheet_a, nil)
 
# the worksheet order is now c, a, b
 
# move the A worksheet after the B work sheet
worksheet_a.Move(nil, worksheet_b)
 
# 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.

Would you like to see more Elixir content like this? Sign up to my mailing list so I can gauge how much interest there is in this type of content.