Win32OLE and Ruby: formatting 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.

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 =

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

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


You can easily add pagebreaks using Win32OLE:

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


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 entry was posted in Automation, Ruby and tagged , , . Bookmark the permalink.