Axlsx: 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:

Friday, April 13, 2012

A Deeper Look: Excel reporting in Ruby with Axlsx

Step one: Create the basic data