30 November 2011

Export to Excel in Rails 3 without a Gem

Export to Excel in Rails 3 without a Gem


Creating Demo project

 rails new xcel_demo –d mysql

Creating a User model

rails g scaffold User name:string email:string content:string

Add a MIME type in \config\initializers\mime_type.rb

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

Add a xls format output to the method in the Controller

  def index
    @users = User.all

    respond_to do |format|
      format.html # index.html.erb
      format.xls
      format.xml  { render :xml => @users }
    end
  end

Creating the view for the XLS


Now create a view to render the xls file. Here as we have modified the index method we will create a view index.xls.erb for the same in the views for Users
<h1>Listing users</h1>

<table>
  <tr>
    <th>Name</th>
    <th>City</th>
    <th>Gender</th>
    <th>Phone</th>
    <th>Address</th>
    <th></th>
    <th></th>
    <th></th>
  </tr>

<% @users.each do |user| %>
  <tr>
    <td><%= user.name %></td>
    <td><%= user.city %></td>
    <td><%= user.gender %></td>
    <td><%= user.phone %></td>
    <td><%= user.address %></td>
  </tr>
<% end %>
</table>

<br />
The text in the <th> tags are the headers and <td> tags are the data for the specified columns

Adding the link to export excel on the Index page


 <%= link_to 'Export XLS', url_for(:format => 'xls') %>
This will allow the data on index page to be exported in Excel Format.

20 comments:

  1. How to export special symbols, like "€"?

    ReplyDelete
  2. ITS SHOWING SOME ERROR LIKE THIS WHAT TO DO HELP ME


    Missing template users/index, application/index with {:locale=>[:en], :formats=>[:xls], :handlers=>[:erb, :builder, :coffee]}. Searched in: * "C:/Users/Ranjith/Desktop/aa/app/views"

    ReplyDelete
  3. Hi have you created the index.xls.erb in the views folder ??

    ReplyDelete
  4. do you have any idea why my html is not parsed in the xls file? I see the html-tags line by line!

    ReplyDelete
  5. have you followed all the steps ?? MIME::TYPE and all ?? please follow all the steps , if the issue occurs again , paste me the sample code or mail it to me :) ashish.upadhyaye@gmail.com

    ReplyDelete
    Replies
    1. I checked, I'm using the exact same code. I'm running rails 3.1 on opensuse. Could this be a problem?

      Thanks!

      Delete
    2. I have not tried out this on Rails 3.1 though, will update the post if I do it in a while, try out any of the Gems available they will give you more authority over excel generation .
      You may use this https://www.ruby-toolbox.com/projects/excel_rails

      Delete
  6. Thanks, you are good) Any other articles didn't help me!

    ReplyDelete
  7. How can I force Excel export to show All Borders?

    ReplyDelete
  8. Its really awesome. Thanks a lot

    ReplyDelete
  9. Hi Ashish it is really awesome and helpful but could you please tell me how can we make it uneditable.

    ReplyDelete
  10. How to change the filename of this Excel file?
    Thanks

    ReplyDelete
  11. How can you use special characters or umlauts? Chars like "§" are not working for me.

    ReplyDelete
  12. Excelent, realy simple!

    ReplyDelete
  13. Hi, i have tried generating excel file, but can you help me to render image to excel field.
    Thanks in advance.

    ReplyDelete
  14. great post - is there a gemless way to get an excel import to work?

    ReplyDelete

Any inputs are welcomed : ashish.cse2010@gmail.com