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.