Daniel Doubrovkine bio photo

Daniel Doubrovkine

aka dB., @awscloud, former CTO @artsy, +@vestris, NYC

Email Twitter LinkedIn Github Strava
Suggest a Topic
RSS
Creative Commons License

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

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.

gem "spreadsheet", "0.6.5.8"
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.

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.

def as_xls(options = {})
  {
      "Id" => id.to_s,
      "Name" => name,
      "E-Mail" => email,
      "Joined" => created_at,
      "Last Signed In" => last_sign_in_at,
      "Sign In Count" => sign_in_count
  }
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.

def index
  @users = User.all
  respond_to do |format|
    format.html
    format.xls { send_data @users.to_xls, content_type: 'application/vnd.ms-excel', filename: 'users.xls' }
  end
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.

= 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.

You get real XLS documents, book & al.

We can write a spec for this controller too.

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