A Deeper Look: Excel reporting in Ruby with Axlsx

It has taken me far longer to get around to it than I wanted, but here it is - a more realistic example of how to take advantage of some of the features in the axlsx library. The example we are going to look at in this post is an attempt to recreate the skydrive template shown below.

That's the goal, now lets see how we close we can come.

Step one: Create the basic data 

If you have followed any of the other posts on this blog, you already know this bit. We need to make a package, and begin a worksheet block for adding rows to the worksheet.  The key at this point is to leave styling, formatting and layout aside and get the basic sheet up and working first. Unlike data driven sheets, we are working against a static content set. However the core concepts are the same.

require 'axlsx'

Axlsx::Package.new do | p |
  p.workbook do | wb | 
    wb.add_worksheet do | sheet |
      sheet.add_row
      sheet.add_row [nil, "College Budget"]
      sheet.add_row
      sheet.add_row [nil, "What's coming in this month.", nil, nil, "How am I doing"]
      sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"]
      sheet.add_row [nil, "Estimated monthly net income", 500, nil, "Monthly income", "=C9"]
      sheet.add_row [nil, "Financial aid", 100, nil, "Monthly expenses", "=C27"]
      sheet.add_row [nil, "Allowance from mom & dad", 20000, nil, "Semester expenses", "=F19"]
      sheet.add_row [nil, "Total", "=SUM(C6:C8)", nil, "Difference", "=F6 - SUM(F7:F8)"]
      sheet.add_row

      sheet.add_row [nil, "What's going out this month.", nil, nil, "Semester Costs"]
      sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"]
      sheet.add_row [nil, "Rent", 650, nil, "Tuition", 200]
      sheet.add_row [nil, "Utilities", 120, nil, "Lab fees", 50]
      sheet.add_row [nil, "Cell phone", 100, nil, "Other fees", 10]
      sheet.add_row [nil, "Groceries", 75, nil, "Books", 150]
      sheet.add_row [nil, "Auto expenses", 0, nil, "Deposits", 0]
      sheet.add_row [nil, "Student loans", 0, nil, "Transportation", 30]
      sheet.add_row [nil, "Other loans", 350, nil, "Total", "=SUM(F13:F18)"]
      sheet.add_row [nil, "Credit cards", 450]
      sheet.add_row [nil, "Insurance", 0]
      sheet.add_row [nil, "Laundry", 10]
      sheet.add_row [nil, "Haircuts", 0]
      sheet.add_row [nil, "Medical expenses", 0]
      sheet.add_row [nil, "Entertainment", 500]
      sheet.add_row [nil, "Miscellaneous", 0]
      sheet.add_row [nil, "Total", "=SUM(C13:C26)"]
 
      sheet.add_chart(Axlsx::Pie3DChart) do | chart |
        chart.title = sheet["B11"]
        chart.add_series :data => sheet["C13:C26"], :labels => sheet["B13:B26"]
        chart.start_at 7, 2
        chart.end_at 12, 15
      end
      sheet.add_chart(Axlsx::Bar3DChart, :barDir => :col) do | chart |
        chart.title = sheet["E11"]
        chart.add_series :labels => sheet["E13:E18"], :data => sheet["F13:F18"]
        chart.start_at 7, 16
        chart.end_at 12, 31
      end
    end 
  end
  p.serialize 'axlsx.xlsx'
end

Some things worth noting in that code are:
1. Many of the object creation methods in axlsx will yield themselves up if you pass a block. Take advantage of that to help keep your code clean and readable.

2. If you need some data calculated based on the values in your sheet it is better to add a string formula to excel than to do your calculations in Ruby. for example "=SUM(C13:C26)" as it makes the worksheet self updating, and saves you the trouble of maintaining the calculation.

3. When you are dealing with a fixed layout, like this report - use Excel style column/row references like "A1" and "C13:C26" - Axlsx will work out what actual cells that refers to.

4. Charts are good. Charts make people happy. Charts are what make your sales people, and your clients like you.

Run that code and we get something like this:


Not a bad start - but we need to get some style in there and fix the empty column widths to something a bit slimmer.

Step two: Style and Formatting

Excel styling can be a bit difficult to handle at first as it is a bit different from anything you may have done with CSS. The first thing to understand is that each cell stores a reference to one, and only one style definition in the master style records. This means that you cannot apply two styles and expect them to merge/inherit happily. Assigning a new style to an existing cell will override any previous settings so thinking about all the possible style combinations before writing the code is productive.

For this exercise, we add in styles in the sheet block as follows:

 styles = wb.styles

 header = styles.add_style :bg_color => "DD", :sz => 16, :b => true, :alignment => {:horizontal => :center}
 tbl_header = styles.add_style :b => true, :alignment => {:horizontal => :center}
 ind_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:indent => 1}
 col_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:horizontal => :center}
 label = styles.add_style :alignment => { :indent => 1 }
 money = styles.add_style :num_fmt => 5
 t_label = styles.add_style :b => true, :bg_color => "FFDFDEDF"
 t_money = styles.add_style :b => true, :num_fmt => 5, :bg_color => "FFDFDEDF"

a

a

That code defines every style we will use in our workbook. I think most of it is self explanatory with the exception of 'num_fmt => 5'. Excel defines a number of formats that can be directly accessed by index, so you don't have to write out the format code manually. As always, a reference is listed in the docs

But we are not done there, we still need to apply these styles to our rows. 'add_row' accepts an options hash that can contain style and type information. For this exercise let's focus on :style.

When specifying the style option, it can be either an integer, or a an array of integer or nil values.

  sheet.add_row [1,2,3], :style = 1

Would apply the same style to each of the three cells

  sheet.add_row [1,2,3], :style = [1]

Would apply the style only to the first cell.

  sheet.add_row [1,2,3], :style = [nil, 1]

Would apply the style only to the second cell in the row.

Have a look at the gist to see how each individual row is styled.

  The last thing we need to do is merge some of the titles and column headers and make the empty columns a bit more narrow.

 sheet.merged_cells.concat ["B4:C4","E4:F4","B11:C11","E11:F11","B2:F4"]

 sheet.column_widths 2, nil, nil, 2, nil, nil, 2

 Axlsx does it's best to automatically determine the proper width for you columns, but often you will want to make adjustments with the column_widths method. Each parameter is applied in order to the columns in the worksheet. Nil values are ignored (and use the default auto_width behavior).


 Our end result looks like this:

Not bad for around 60 lines of code.

If you are looking for something to hack with, the source code for this exercise is available here.
If you are looking to get involved and implement part of the ECMA-376 spec give me shout on github (randym) or grab me on #axlsx on freenode.

Happy reporting.