Sorry this page looks weird. It was automatically migrated from my old blog, which had a different layout and different CSS.

Export to Excel in Rails 3

Recently I added the ability to export data to Excel from a Rails 3 webapp. There were pleasingly few lines of code.

The Code

In config/initializers/mime_types.rb:

Mime::Type.register 'application/vnd.ms-excel', :xls

In config/initializers/xls_renderer.rb:

ActionController::Renderers.add :xls do |xls, options|
  send_data(xls.respond_to?(:to_xls) ? xls.to_xls(options) : xls, :type => :xls)
end

In Gemfile:

gem 'to_xls', '~> 1.0.0'

In the view:

link_to 'Export to Excel', url_for(:format => 'xls')

In the controller:

def index
  @widgets = Widget.all
  respond_to do |format|
    format.html
    format.xls do
      render :xls => @widgets,
                     :columns => [ :name, :ref ],
                     :headers => %w[ Name Reference ]
    end
  end
end

The Tests

In test/unit/xlsrenderertest.rb:

require 'test_helper'
class XlsRendererTest < ActiveSupport::TestCase
  test 'xls mime type' do
    assert_equal :xls, Mime::XLS.to_sym
    assert_equal 'application/vnd.ms-excel', Mime::XLS.to_s
  end
end

In features/stepdefinitions/excelsteps.rb:

Then /^I should download an Excel document$/ do
  assert_equal 'binary',                   page.response_headers['Content-Transfer-Encoding']
  assert_equal 'application/vnd.ms-excel', page.response_headers['Content-Type']
  assert_equal 'attachment',               page.response_headers['Content-Disposition']
  assert page.source.length > 0  # not ideal but better than nothing
end

In features/whatever.feature:

Scenario: export to Excel
  When I go to the whatever page
  And I follow "Export to Excel"
  Then I should download an Excel document

References

The best source of information I found was José Valim’s Crafting Rails Applications. I like this book because it’s non-trivial and it explains everything test first.

Comments

Thanks for posting this clean, straightforward way to export data from a Rails 3 app to an excel spreadsheet. You laid it out so nicely!

David Baynes • 22 March 2012

Andrew Stewart • 9 November 2011 • Rails
You can reach me by email or on Twitter.