Export Data into Excel in Rails
Hi friends,
In my previous blog Import From Excel in Rails. I showed you how can we read excel records from excel sheet and store it into DB in rails. In this we will see how we can download the list in excel format. For that we will be extending our previous application provided here also https://github.com/raghvendra1501/sample_import_csv.git.
For exporting the excel, we will add a gem in Gemfile:

  # conversion into excel
  # @included gem
  # @link https://github.com/liangwenke/to_xls-rails
  gem 'to_xls-rails'

After installing the bundle we will create a route in the config/routes.rb. For giving a download link.

  get "download_xl" => "users#download_xl"

Now we will give this link wherever we want to use the download. In our case we are giving where the User's listing has been done. (app/views/users/index.html.erb):

    <p id="notice"><%= notice %></p>
    <h1>Import Form Excel</h1>
     <%= form_tag import_from_excel_path, multipart: true do %>
      <%= file_field_tag :file %>
      <%= submit_tag "Import", :class=> "btn"%>
    <% end %>

    <h1>Listing Users</h1>
    <%= link_to "Export Users", download_xl_path %>
    <table>
      <thead>
        <tr>
          <th>First name</th>
          <th>Last name</th>
          <th colspan="3"></th>
        </tr>
      </thead>

      <tbody>
        <% @users.each do |user| %>
          <tr>
            <td><%= user.first_name %></td>
            <td><%= user.last_name %></td>
            <td><%= link_to 'Show', user %></td>
            <td><%= link_to 'Edit', edit_user_path(user) %></td>
            <td><%= link_to 'Destroy', user, method: :delete, data: { confirm: 'Are you sure?' } %></td>
          </tr>
        <% end %>
      </tbody>
    </table>
    <%= link_to 'New User', new_user_path %>

Now for this link, we will create a controller inside app/controllers/users_controller

  def download_xl
    @users = User.all.to_xls
    respond_to do |format|
      format.html
      format.xls {
        send_data @users, :filename => 'users.xls'    
        return # prevet Rails to seek for index.xls.erb
      }
    end
  end

Now to finally achiveing downloading into excel you need to register the xls format in MIME type. For that copy change the config/initializers/mime_type.rb to

  # Be sure to restart your server when you modify this file.

  # Add new mime types for use in respond_to blocks:
  Mime::Type.register "application/xls", :xls
  # Mime::Type.register "text/richtext", :rtf

Now restart the server and try to download the excel by clicking on the link. And it will work as you will get the excel file. By default it will take all columns of the table, and make the headers in the HUMANIZED_ATTRIBUTE forms, but you can have several customization of how you can get the excel

  ## For retrieving all columns with their default humanized attribute names as header 
  @users = User.all.to_xls

  ## For retrieving selected columns with their default humanized attribute names as header 
  @users = User.all.to_xls(:only => [:first_name, :last_name])

  ## For retrieving selected columns with custom header column names
  @users = User.all.to_xls(:only => [:first_name, :last_name], :header_columns => ['First Name', 'Last Name'])

  ## For retrieving selected columns with custom header column names and given width for column
  @users = User.all.to_xls(:only => [:first_name, :last_name], :header_columns => ['First Name', 'Last Name'], :column_width=> [30, 30])

The full application can be found here:

 https://github.com/raghvendra1501/sample_import_csv.git
 

Hope you liked reading this blog. For more like this Click here.