Easy Excel exports from Padrino

At some point in the lifecycle of any useful application you write, there will be a request to get something out of your system in Excel format. Here’s an easy, transparent method to get downloadable Excel reports out of your Padrino app.

Spreadsheets are still king in most organizations and Excel is still the main tool business users and decision makers comfortably interact with, often for for end user reporting, data consolidation, or intermediary analysis purpose.

A little extra work beyond require 'csv' creates a nice, unicode-friendly (which csv is not), excel export for your business users that will add polish to using Padrino, particularly in corporate environments where the lighter-weight framework might be more of an internal struggle to use despite its other shining advantages (and ironically, Excel downloads will often make your users much happier than other features with much more business value.)

While there are Rails gems that can help with these exports, I find them overkill and their integration too much added complexity for Padrino (and not too pleased with them in Rails either) when there is a simpler, template-driven method which is much more robust across spreadsheet programs, rather than only the latest version of MS Office.

Assuming this will not be the only time you’re going to have to provide an excel report, let’s create a reports controller and a blank reports model and directory in views to get set up for this. Assume this is set up under the handy and excellent admin app that padrino provides natively via generator.

# admin/controllers/reports.rb
AppName::Admin.controllers :reports do

  get :index, :provides => :html do
    @title = "Downloadable Excel reports"

    render 'reports/index'
  end
end

# models/report.rb
class Report < ActiveRecord::Base

end

As an example, I’m going to use something close to a feature I implemented which is an export of the species data for an update on a pro bono project I’m working on for a global wildlife conservation organization.

The feature exports all the species in the system out to a handy Excel format.

So, first off, let’s create a view from which someone can download the report (note: I’m using haml here, but use your templating language of choice.).

# admin/views/reports/index.haml
.container
  =[:error, :warning, :success, :notice].map { |type| flash_tag(type, :class => "alert alert-#{type} fade in", :bootstrap => true) }.join.html_safe
  %h3
    %em
      Hi
      = current_account.name.split.first
  %h3
    = @title

  .bs-callout.bs-callout-info
    %h4
      Here you can find a range of reports, which query the live database and
      then are download to your local machine for use in spreadsheet software
      like Excel (or easily imported into alternatives like Numbers, GSheets,
      LibreOffice etc.)

  %ul
    %li
      %strong.download_name= link_to "Species flat file export", url(:reports, :species_report, {:format => :xls})
      %br.download_description Flat file export of species list currently in the Ex Situ Progress system.
      %br

In the controller for reports admin/reports/controller, add the following to the file:

  get :species_report, :provides => :xls do
    @species = Species.order(:name)

    render 'reports/species_report'
  end

What this does is simply make the species_report link we put in the index view above, respond to an xls format request. It grabs all the Species, orders them by name and then asks the view species_report to render the results.

Now, the rendering to excel file part. This is the only non-standard part of getting this feature out the door for your users. Basically, you need to render the excel file as xml and in the format specified by excel (which is byzantine). This can be a pain since Excel has a ridiculous file format, and I’d recommend doing this as an erb, rather than haml to try to keep your sanity.

Here’s the example of what the species report looks like (complete file included as Excel is a pain to deal with and stupidly difficult to debug if you don’t often look at Excel files.)

# species_report.xls.erb
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">AArk Ex Situ Programs' Progress Species Report for <%= Date.today %></Data></Cell>
      </Row>
      <Row>
        <Cell><Data ss:Type="String">There are <%= @species.size %> species currently in the system on <%= Date.today %>.</Data></Cell>
      </Row>
      <Row>
        <Cell><Data ss:Type="String"></Data></Cell>
      </Row>
      <Row>
        <Cell><Data ss:Type="String">Species Name</Data></Cell>
        <Cell><Data ss:Type="String">Analogue</Data></Cell>
        <Cell><Data ss:Type="String">Phylogenetic Study</Data></Cell>
        <Cell><Data ss:Type="String">Common Name</Data></Cell>
        <Cell><Data ss:Type="String">Ex Situ Research Needs Notes</Data></Cell>
        <Cell><Data ss:Type="String">Order</Data></Cell>
        <Cell><Data ss:Type="String">Family</Data></Cell>
        <Cell><Data ss:Type="String">Genus</Data></Cell>
        <Cell><Data ss:Type="String">Species</Data></Cell>
        <Cell><Data ss:Type="Number">Species ID</Data></Cell>
      </Row>
        <% @species.each do |species| %>
          <Row>
            <Cell><Data ss:Type="String"><%= species.name %></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.analog %></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.has_phylogenetic_study %></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.common_name %></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.ex_situ_research_needs_notes %></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.order %></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.family %></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.genus%></Data></Cell>
            <Cell><Data ss:Type="String"><%= species.species %></Data></Cell>
            <Cell><Data ss:Type="Number"><%= species.id %></Data></Cell>
          </Row>
        <% end %>
    </Table>
  </Worksheet>
</Workbook>

Load up the reports index, click on the link and it should download a nice copy of the xls file to you that you can open up. If you get some sort of strange table error opening the Excel file, you’ve made an error somewhere in the xml file (check between the call > data directives since I made this mistake myself a couple of times.). The other gotcha I ran into is if you accidentally specify a ss:Type="Number" for a string field. Excel is very persnickety.

While you don’t see it in this file, you can also use standard erb logic if you need conditionals in here or anything you would normally do for an erb file, which is very handy for reports with more complex logic (for example, in one other report, I need to determine whether there is a primary contact for a tracking record and if not, then use a progarm primary contact which is set by default.).

The nice thing about using this template-driven technique is that you can easily adapt this to just about any report you need to generate out of the database and get into Excel for your users. Hopefully, it should also be transparent to figure out how to create multisheet reports and take advantage of other Excel features.

I should point out here that I’ve intentionally avoided formatting for these as the main use case I’ve found these days is people doing data sorting/filtering or comparison (or inclusion into other reports).

This approach ends up being a quick, simple and transparent solution to the problem of needing to provide xls and additionally, the xml template approach provides a portable xls file across numerous different spreadsheet programs for import (for example, GSheets, Numbers, or LibreOffice).