Exporting Data to Excel in Ruby on Rails w/ Spreadsheet and to_xls

Back | excel, rails, ruby | 9/1/2011 |

image

We’re going to export data to Excel from our RoR application, in about five lines of code. There’re many options available out there and it’s all pretty confusing. People try to export CSV and XML formats.

Here’s how we do it, and so should you.

Just Do It!

Use spreadsheet 0.6.5.8 with this monkey-patch added as initializers/spreadsheet_encodings.rb and to_xls 1.0 from my to-xls-on-models branch.

  1. gem "spreadsheet", "0.6.5.8"
  2. gem "to_xls", :git => "https://github.com/dblock/to_xls.git", :branch => "to-xls-on-models"

Register the Excel MIME type in config/initializers/mime_types.rb.

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

Add a as_xls method to any model that you want to export that contains the fields of interest. Here’s what I added to app/models/user.rb.

  1. def as_xls(options = {})
  2.   {
  3.       "Id" => id.to_s,
  4.       "Name" => name,
  5.       "E-Mail" => email,
  6.       "Joined" => created_at,
  7.       "Last Signed In" => last_sign_in_at,
  8.       "Sign In Count" => sign_in_count
  9.   }
  10. end

You can also simply call as_json inside as_xls. Note that currently only top-level keys are exported.

Add support for the .XLS format in any controller out of which you want to export Excel files. Here’s what I added to app/controllers/users_controller.rb.

  1. def index
  2.   @users = User.all
  3.   respond_to do |format|
  4.     format.html
  5.     format.xls { send_data @users.to_xls, content_type: 'application/vnd.ms-excel', filename: 'users.xls' }
  6.   end
  7. end

Add a link on the users view. Here’s what I added to app/views/users.html.haml. The parameter merging lets you reuse whatever parameters were passed in the page.

  1. = link_to 'Export', users_path(request.parameters.merge({:format => :xls}))

We added some styles, so here’s what the export button looks like next to another one.

image

You get real XLS documents, book & al.

We can write a spec for this controller too.

  1. describe "GET index.xls" do
  2.   it "creates an Excel spreadsheet with all users" do      
  3.     user = Fabricate :user
  4.     get :index, :format => :xls
  5.     response.headers['Content-Type'].should == "application/vnd.ms-excel"
  6.     s = Spreadsheet.open(StringIO.new(response.body))
  7.     s.worksheets.count.should == 1
  8.     w = s.worksheet(0)
  9.     w.should_not be_nil
  10.     w.row(0)[0].should == "Id"
  11.     w.row(1)[0].should == user.id.to_s
  12.     w.row(0)[1].should == "Name"
  13.     w.row(1)[1].should == user.name
  14.   end
  15. end

Links