Win32OLE and Ruby: formatting worksheets

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:

  
  
    # 2 is the numerical value of xlLandscape
ws.PageSetup.Orientation = 2
 
# the following commands apply to the selected worksheet
ws.Select
# print the first row on each page
excel.ActiveSheet.PageSetup.PrintTitleRows = "$1:$1"
# center worksheet on page when printing
excel.ActiveSheet.PageSetup.CenterHorizontally = true
# print gridlines
excel.ActiveSheet.PageSetup.PrintGridlines = true
 
# remove margins
excel.ActiveSheet.PageSetup.LeftMargin = 0
excel.ActiveSheet.PageSetup.RightMargin = 0
 
# adjust column size to contents
ws.Columns.AutoFit

  
  

Pagebreaks

You can easily add pagebreaks using Win32OLE:

  
  
    # remove all pagebreaks to start with a clean slate
excel.ActiveSheet.ResetAllPageBreaks
 
ws.Select
 
# insert a horizontal pagebreak before the 20th row
excel.ActiveSheet.HPageBreaks.Add('Before' => ws.Rows(20))
 
# insert a vertical pagebreak before the 5th column
excel.ActiveSheet.VPageBreaks.Add('Before' => ws.Columns(8))
  
  

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.

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.