Bridging the geek-suit divide

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.

As explained previously, each Worksheet object Win32OLE uses contains many rows, columns, and cells. You can access these by calling them directly (assuming you already have a ‘ws’ variable referring to a worksheet as explained here):

second_row = ws.Rows(2) # the range containing the worksheet's second row

second_column = ws.Columns(2) # the range containing the worksheet's second columns

a_cell = ws.Cells(1,2) # the cell on the first row, second column

cell_b2 = ws.Range("B2") # cell B2 (more on ranges below)

Take the time to note a few things:

• the ‘Rows’, ‘Columns’, and ‘Cells’ attributes are all plural
• indexes start at 1: ws.Rows(1) will correspond to the topmost row in the worksheet
• the code above will return a reference to a row/column/cell, and not the actual data contained

Acessing the data

So how do we actually access the data? Use the ‘Value’ attribute:

cell_content = ws.Cells(1,2).Value # put the cell's data into a variable

new_content = "Data test"

ws.Cells(1,2).Value = new_content # set the cell's data to "Data test"

The same can be done for ranges. For example, let’s copy the header information contained in the first row of one worksheet to another:

header_data = ws.Rows(1).Value

# you could do some other computation here

a_different_ws.Rows(1).Value = header_data

The UsedRange attribute

Before we move onto other things we can do to our cells, let me quickly tell you about a useful attribute (especially for loops): UsedRange. This contains the area of the work sheet that is actually used (i.e. the biggest row and column number with data in it). So to iterate along each row, we would do the following:

for row in 1..ws.UsedRange.Rows.Count do # note that we are including the last value, due to the way Excel indexes rows

ws.Cells(row,1).Value = "This is row number #{row}."

end

Remember the ‘Count’ attribute we used earlier? You can use it here to count the rows that are used in the worksheet.

Manipulating cells and ranges

Let’s get back on track: what else can you do with cells and ranges? Plenty of useful things:

ws.Rows(1).Font.Bold = true # set all font in the topmost row to bold

ws.Columns(1).Interior.Color = 6 # make the first column yellow (see comment after code)

ws.Columns(3).NumberFormat = "#,##0" # set the 3rd column's format to display numbers with commas

ws.Columns(4).NumberFormat = "mm/dd/yyyy" # set the 4th column's format to display dates in a specific format

ws.Columns(5).NumberFormat = "0.0%" # set the 5th column's format to display percentages with 1 decimal

ws.Rows(5).Delete # will delete the 5th row (see comment after code)

Note that the background color number are documented here. Also, bear in mind that after deleting the 5th row, row number 5 is what was previously the 6th row. This detail is particularly important if you are deleting rows or columns in a loop: you might have to adjust the row pointer that is used to fetch the next range. For example, to delete all rows where the first cell contains ‘zz’:

correction = 0

for i in 1..ws.UsedRange.Rows.Count do

row = i - correction

if ws.Cells(row,1).Value =~ /zz/

ws.Rows(row).Delete

correction += 1 # correct the row pointer to account for the deleted row

end

end

Working with formulas

Win32OLE will also give you access to the formula a cell contains. Therefore, you could add a sum to each column in a worksheet this way:

last_row = ws.UsedRange.Rows.Count

for col in ws.UsedRange.Columns.Count do

ws.Cells(last_row+1, col)['Formula'] = "=SUM(#{ws.Cells(1,col).Address}:#{ws.Cells(last_row,col).Address})"

end

As you’ve probably guessed, the ‘Address’ attribute will return a given cell’s address: ws.Cells(2,2).Address will return “$B$2”.

Working with ranges

We touched upon ranges earlier, and they are handy when dealing with blocks of data. If you wish data from ‘first_row’ to ‘last_row’ from the ‘ws_data’ worksheet to another worksheet called ‘ws_special’, for example, you can simply use a range:

ws_special.Range(ws_special.Rows(1), ws_special.Rows(last_row - first_row)).Value = ws_data.Range(ws_data.Rows(first_row), ws_data.Rows(last_row)).Value # copy the values from one range to another

ws_data.Range(ws_data.Rows(first_row), ws_data.Rows(last_row)).Delete # delete the original range

Notice that to use the range object, you give to arguments referencing objects of the same type: two rows (as in our example), two columns, or two cells. If two cells are supplied as arguments, the range will be the rectangular area having the first cell as its upper left corner and the second cell as its lower right corner.

Ranges can also be sorted:

ws.UsedRange.Sort('Header'=>1, 'Key1'=>ws.Range("B1") , 'Order1'=>1)

In this example, we’re sorting the entire worksheet (as indicated by the range being UsedRange), according to the data in the second column (key1 argument). This column has a header and we are sorting it in ascending order. (Note: in Excel 2003, you can sort using up to 3 criteria. Simply add the appropriate ‘Key’ and ‘Order’ arguments to the method call: ‘Key2’, ‘Order2’, etc.)

Microsoft’s documentation indicates the argument for the order is ‘xlSortOrder’: not very helpful… To be able to use functions in your Win32OLE code, you’ll need to use the numerical equivalents to these constants. You have several options to get them:

• Semi-automatically loading Excel’s constants into a class
• Creating a macro and setting a cell’s value to the constant for which you want to know the numerical equivalent
• Searching the web

By now, you should be well on your way to doing cool stuff with Win32OLE and Ruby. We can now move on to formatting the worksheets for the screen and printing.

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.

Now that we’ve crunched numbers and otherwise manipulated the data to our satisfaction, it’s time to make it a little more presentable. These little details will ultimately be the difference between having a workflow that is mostly automated and having a workflow where the end result is only one click away.

Freezing panes

Let’s start by freezing the first column and first row in our document. Freezing panes keeps them always visible, even when a user scrolls around in the document. This makes frozen panes very useful for always displaying data headers. In the following example, ‘excel’ is a Win32OLE Excel application object (as explained here):

excel.ActiveWindow.SplitColumn = 1 # freeze first column
excel.ActiveWindow.SplitRow = 1 # freeze first row
excel.ActiveWindow.FreezePanes = true # apply

In the above example, if you don’t want the first column to be frozen, for example, simply replace 1 with 0. The same holds true for not freezing rows.

Headers and footers

Excel worksheets have 3 distinct areas in both the header and footer: left, right, and center. Entering data to display in these is fairly straightforward (‘ws’ is a worksheet):

today = Date.today

ws.PageSetup.CenterHeader = "Last updated on #{today.month}/#{today.day}/#{today.year}"
ws.PageSetup.LeftFooter = ws.name

Formatting the printed result

Let’s set the worksheet to print in landscape format, with visible gridlines, and centered:

ws.PageSetup.Orientation = 2 # 2 is the numerical value of xlLandscape

ws.Select # the following commands apply to the selected worksheet
excel.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" # print the first row on each page
excel.ActiveSheet.PageSetup.CenterHorizontally = true # center worksheet on page when printing
excel.ActiveSheet.PageSetup.PrintGridlines = true # print gridlines

# remove margins
excel.ActiveSheet.PageSetup.LeftMargin = 0
excel.ActiveSheet.PageSetup.RightMargin = 0

ws.Columns.AutoFit # adjust column size to contents

Pagebreaks

You can easily add pagebreaks using Win32OLE:

excel.ActiveSheet.ResetAllPageBreaks # remove all pagebreaks to start with a clean slate

ws.Select

excel.ActiveSheet.HPageBreaks.Add('Before' => ws.Rows(20)) # insert a horizontal pagebreak before the 20th row

excel.ActiveSheet.VPageBreaks.Add('Before' => ws.Columns(8)) # insert a vertical pagebreak before the 5th column

Note that you cannot use an “after” argument to insert a pagebreak after a given row or column.

By now, you should be able to generate Excel documents that look just the way you want them to when you print them.

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.

In today’s business world, most data is presented in Excel format. However, working with Excel by hand can quickly become mind numbingly boring when it is reduced to repetitive actions such as copy and pasting, formatting, etc. This time would be much better on tasks with higher added value.

The solution? Automate the boring and repetitive parts! Continue reading →