Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Export Into Excel in Rails

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 1.08k
    Comment on it

    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.

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: