Export data to Excel (xls) in Ruby on Rails

class UserController < ApplicationController
  def export
    headers['Content-Type'] = "application/vnd.ms-excel"
    headers['Content-Disposition'] = 'attachment; filename="report.xls"'
    headers['Cache-Control'] = ''
    @users = User.find(:all)
  end

<%= link_to "Export as Excel", export_person_url %>

<table border="1">
  <tr>
    <th>Name</th>
  </tr>
  <% @users.each do |u| %>
  <tr>
   <td><%= u.name %></td>
  <% end %>
 </tr>
</table>

11 comments

• joselo

  September 11, 2009 at 9:01 pm

  Hi, this is a cool way to export to excel, But is posible to format column?? like Text columnn or Number Column?

  thanks
• Dhaval Parikh

  September 15, 2009 at 1:27 pm

  Yes ofcourse u can do it…let me know what exactly ur looking for and I can help u out
• Nirav

  September 16, 2009 at 4:24 pm

  This is the most simplest way opening an html in excel. This method applies to all the scripting languages(asp, php, asp.net etc). You just need to add Content Type in the head section of html.

  Also, all formatting applied to html applies to the excel file.

  HEADING will appear same in excel as in IE.
• Doel

  March 15, 2010 at 8:18 pm

  Hi, can i create a new worksheet on the same .xls file using this?
• Mark S

  April 8, 2010 at 11:55 pm

  Dhaval, I am a Rails Newbie and do not understand the implementation of the “views”. The controller I think I got (I just cut and paste and changed where you had “user(s)” to “classmate(s)” to match my model. But I don’t get how you call the “export.html.erb” or where the partial “_report.html.erb” gets called.

  Can you provide more details for implementing this solution??? TIA!
• Mark S

  April 9, 2010 at 12:09 am

  Dhaval, sorry I don’t know if my first post went thru or not as I don’t yet see it above. But I am trying to implement this solution but don’t understand how to incorporate the “views” into my existing project. I have cut-and-pasted the controller into mine, but I don’t get how to reference the “export.html.erb” and the partial “_report.html.erb” into my app. Can you provide greater details??? My app uses “classmate” in place of “user”. TIA.
• Lori M Olson

  June 17, 2010 at 2:12 am

  Note that this form of export to Excel will not work with versions of Excel including 2007 and newer. Excel now verifies that the content of the file matches with the file extension. It will throw up a scary security exception to your users if you do it this way.
• Jnanesh

  September 1, 2011 at 10:08 pm

  I am new to rails. Is this code generic or required some tweaking based on the applications. Because directly cut-paste is not working in case of my application.

  Thanks,
  Jnanesh
• Dhaval Parikh

  September 12, 2011 at 4:24 pm

  Hi Jnanesh

  Sorry for a late reply. I guess this method no longer works you may use the spreadsheet gem available on

  http://spreadsheet.rubyforge.org/

  Thanks
• Sunny

  May 8, 2012 at 11:22 am

  Hey do you know how you can export images in excel using above code.
  Also is there is anything for exporting data in PPT using rails 3.
• Dhaval Parikh

  May 8, 2012 at 11:26 am

  Hi

  I have heard about win32ole gem which can do this by storing the image as an ole object. https://github.com/bpmcd/win32ole

  I am not sure about exporting data to PPT. I will try to find out and get back to you.

  thanks