Excel at Ruby driven spreadsheets

Sometimes we need to format our views in an Excel format like XLSX. As this RailsCast demonstrates, this can be a relatively easy thing to do, until you come to creating your own views.
This is where the Axlsx and Axlsx-Rails gems come in to play, simplifying the process of creating your own complex spreadsheets.
Installation is really easy, in your Gemfile drop in the following:

....

gem 'axlsx_rails'

...

As Axslx-Rails provides a renderer and template handler, we don't need to add the :xlsx format. In fact all we need to do in our controllers is simply call format.xlsx in our format block. Alternatively if you need to render a single view, that may not take the standard action name file, you can simply call render xlsx: 'file_name' to accomplish the same.

If you are just looking to export your data straight out from a model, I suggest looking at the acts_as_xlsx gem which gives you a nice easy way to export models. However this is a bit outside the scope of this post.

Using the axlsx_rails gem to build out your spreadsheets could not be simpler if all you want to do is create a spreasheet:

#index.xlsx.asxlsx

wb = xlsx_package.workbook

wb.add_worksheet(name: 'Report') do |sheet|
  sheet.add_row [ 'Product Name', 'Qty Sold', 'Markup %', 
                 'Profit this Quarter']

  @products.each do |product|
    sheet.add_row [ product.name, product.units.qty, 
                    product.markup, product.profit_for_quarter]
  end
end

Where it starts getting really interesting is all the additional options you have available to you beyond just outputting data, for instance chart generation:

#index.xlsx.asxlsx

wb = xlsx_package.workbook

wb.add_worksheet(name: 'Report') do |sheet|
  ........
end

# This cycles through a list of products and bases the pie graph on the size of the products data
wb.add_worksheet(name: 'Report Chart') do |sheet|
  sheet.add_row [ 'Product Profits Pie Chart']
  @products.each do |product|
    sheet.add_row [ product.name, product.profit_for_quarter]
  end
  # give ourselves some random colours to work with.
  colors = @products.length.times.map { "%06x" % 
                                        (rand * 0xffffff) }
  sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,5], 
                  :end_at => [10, 20], :title => 
                  "example 3: Pie Chart") do |chart|
  chart.add_series :data => sheet["B2:B#{@products.length}"], 
  :labels => sheet["A2:A#{@products.length}"], 
  :colors => colors
  end
end

There are a treasure trove of different chart options available to you, which can be found in the examples file in the gem repository.

There are also many different formatting methods to help your document look almost as pretty as a picture:

  wb.styles.fonts.first.name = 'Calibri'

  header_style = wb.styles.add_style({bg_color: "fce9da", 
                                      b: true, 
                                      :alignment => 
                                      {:horizontal => :center, 
                                       :vertical => :bottom} })

  sheet.add_row ['foo', 'bar', 'baz'], :style => header_style

For those of you using Active Admin, I highly recommend checking out Active Admin Axlsx, this enables all resource index views will now have a download link for xlsx.

I was genuinely surprised at the wealth of options available to create comprehensive spreadsheets with Axlsx and would not hesitate to reach for it again.