#362 Exporting CSV and Excel

It’s common to want to export data from a Web application in CSV or Excel format and in this episode we’ll show how to do that in a Rails application. Below is a page that shows a list of products and we’d like to add some links to allow this list to be downloaded as a CSV or Excel file.

There are many gems that can help us to do this but we won’t be using any of them here and this is quite easy to write from scratch.

Exporting to CSV

Ruby 1.9 comes with an excellent CSV library that was formerly known as Faster CSV in Ruby 1.8. We’ll use this library to generate our CSV data. As it’s part of the standard library all we have to do is require it and we’ll do this in our application’s config file.

require File.expand_path('../boot', __FILE__)

require 'csv'
require 'rails/all'

We can now use this library to generate comma-separated data when the user visits the /products.csv path. This currently won’t work as the action doesn’t respond to that format yet but this is easy to fix by adding a respond_to block to our ProductsController’s index action. We now have a choice: we can generate a view template for this format or we can render the data inline in the controller. We’ll take the latter option as it’s slightly simpler for our needs, although both approaches work just as well.

class ProductsController < ApplicationController
  def index
    @products = Product.order(:name)
    respond_to do |format|
      format.html
      format.csv { render text: @products.to_csv }
    end
  end
end

We’ll generate the CSV data in the Product model in a new to_csv method whose name fits in nicely with the existing to_json and to_xml methods.

class Product < ActiveRecord::Base
  attr_accessible :name, :price, :released_on
  
  def self.to_csv
    CSV.generate do |csv|
      csv << column_names
      all.each do |product|
        csv << product.attributes.values_at(*column_names)
      end
    end
  end
end

To create the data we append an array of values to the csv object that’s passed to the block. The first row is a header row and we’ve set it to column_names, which returns an array of all of the model’s attributes. If we wanted to return a subset of the columns we’d have to list them out here instead. We then loop through all the products and add a row to the CSV file for each one and add the values that match the column names. This ensures that the right fields are returned and in the correct order. Once we’ve restarted the server so that it picks up the csv library we should be able to view our CSV data.

If we want this data to be downloaded rather than rendered inline in the browser we can use send_data instead of render :text in the controller. When we reload the page now the CSV file should be downloaded.

Exporting to Excel

What about Excel? The easiest solution would be for the user to open a CSV file in Excel, but it’s better to provide an xls file that will open in Excel by default. To do this we’ll need to add a new MIME type for xls files and we do this in the mime_types initializer file.

Mime::Type.register "application/xls", :xls

We’ll need to modify our controller now so that it responds to this type. The data needs to be tab-separated to open up properly in Excel; fortunately the csv library supports a column separator option that we can pass a tab character to to do this.

class ProductsController < ApplicationController
  def index
    @products = Product.order(:name)
    respond_to do |format|
      format.html
      format.csv { send_data @products.to_csv }
      format.xls { send_data @products.to_csv(col_sep: "\t") }
    end
  end
end

We’ll need to change the Product model’s to_csv method to accept a hash of options that we can pass to CSV.generate.

class Product < ActiveRecord::Base
  attr_accessible :name, :price, :released_on
  
  def self.to_csv(options = {})
    CSV.generate(options) do |csv|
      csv << column_names
      all.each do |product|
        csv << product.attributes.values_at(*column_names)
      end
    end
  end
end

If we visit the /products.xls path now a file is downloaded that we can open in Excel. For the most part this works well; the file opens and the columns are separated correctly but there are some problems. For example the file isn’t encoded properly so that some characters such as the degree symbol appear incorrectly in the file. There are work-arounds for this issue but there are other problems with this approach as well, one of which is that cells can’t contain a newline character.

Instead of rendering the data in the controller we’ll create a template file for rendering the xls file. First we’ll remove the block from the xls’s format in the controller.

class ProductsController < ApplicationController
  def index
    @products = Product.order(:name)
    respond_to do |format|
      format.html
      format.csv { send_data @products.to_csv }
      format.xls
    end
  end
end

Next we’ll create our new template.

<table border="1">
  <tr>
    <th>ID</th>
    <th>Name</th>
    <th>Release Date</th>
    <th>Price</th>
  </tr>
  <% @products.each do |product| %>
  <tr>
    <td><%= product.id %></td>
    <td><%= product.name %></td>
    <td><%= product.released_on %></td>
    <td><%= product.price %></td>
  </tr>
  <% end %>
</table>

We can use an HTML table that contains the data we want to export as Excel has no problem displaying these. When we open this up in Excel we’ll see that the encoding problem has disappeared and data containing newlines is handled correctly, too. The spreadsheet looks a little different, though, as the HTML formatting adds a border to the cells and centre-aligns the header cells. We can customize this in the HTML document if we want any formatting we add will be shown in Excel. If we want to generate a file that feels more like a native Excel document we can change our template to look like this:

<?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">ID</Data></Cell>
        <Cell><Data ss:Type="String">Name</Data></Cell>
        <Cell><Data ss:Type="String">Release Date</Data></Cell>
        <Cell><Data ss:Type="String">Price</Data></Cell>
      </Row>
    <% @products.each do |product| %>
      <Row>
        <Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
        <Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
      </Row>
    <% end %>
    </Table>
  </Worksheet>
</Workbook>

Our data is now rendered as XML in a format that Excel understands and using this gives us much more flexibility over what we can do. For example we can generate multiple worksheets and specify the format of each column. When we reload this file now to generate another spreadsheet we get a warning when the file downloads (on OS X at least) as a file in this format could contain potentially dangerous things, such as macros.

The file we get this time is still encoded properly and feels more like a true Excel document. The best source of documentation for this format is the “XML Spreadsheet Reference” on the MSDN web site. This has a list of all the tags we can use in our Excel documents. If a spreadsheet has a feature that we want to use we can reverse engineer it by saving it as an XML file and looking at it in a text editor.

Adding Links

Now that we can generate both formats we’ll add the links to our products that will allow the user to download the relevant files. We’ll add these at the top of the page.

<h1>Products</h1>

<p>
  Download:
  <%= link_to "CSV", products_path(format: "csv") %> |
  <%= link_to "Excel", products_path(format: "xls") %>
</p>
<!-- Rest of file omitted -->

Now when we reload the page we’ll see the download links and clicking one of them will download the relevant file.