Exporting Records in CSV and Excel Formats in Rails 5

Create a new Rails 5 project and create a product model.

rails g model product name released_on:date price:decimal

Specify the precision and scale for the price column in the migration file.

t.decimal :price, :precision => 2, :scale => 8

Migrate the database.

rails db:migrate

Copy the seed data to seeds.rb. Populate the database.

rails db:seed

Create a products controller.

rails g controller products

Retrieve products in the index action.

def index
  @products = Product.order(:name)
end

Define the resources in the routes.rb.

Rails.application.routes.draw do
  resources :products

  root to: 'products#index'
end

Create layout.css with css shown in the github code download link. Add:

table td, table th {
  padding-right: 20px;
  padding-bottom: 5px;
  text-align: left;
  &:last-child {
    text-align: right;
  }
}

to products.scss. Create products/index.html.erb:

<h1>Products</h1>
<table id="products">
  <tr>
    <th>Product Name</th>
    <th>Release Date</th>
    <th>Price</th>
  </tr>
<%= render @products %>
</table>

Create product partial, _product.html.erb:

<tr>
  <td><%= product.name %></td>
  <td><%= product.released_on.strftime("%B %e, %Y") %></td>
  <td><%= number_to_currency(product.price) %></td>
</tr>

Replace the body section of the layout file:

<body>
  <div id="container">
    <% flash.each do |name, msg| %>
      <%= content_tag :div, msg, id: "flash_#{name}" %>
    <% end %>
    <%= yield %>
  </div>
</body>

You will now be able to view list of products in the home page.

Export Data

Add download link to CSV and Excel in products index page.

<p>
  Download:
  <%= link_to "CSV", products_path(format: "csv") %> |
  <%= link_to "Excel", products_path(format: "xls") %>
</p>

Change the index action.

respond_to do |format|
  format.html
  format.csv { send_data @products.to_csv }
  format.xls 
end

Add the converter method to product model.

def self.to_csv(options = {})
  CSV.generate(options) do |csv|
    csv << column_names
    all.each do |product|
      csv << product.attributes
    end
  end
end

Click on the download link. You will get the error:

To respond to a custom format, register it as a MIME type first: http://guides.rubyonrails.org/action_controller_overview.html#restful-downloads. If you meant to respond to a variant like :tablet or :phone, not a custom format, be sure to nest your variant response within a format response: format.html { |html| html.tablet { ... } }

In config/initializers/mime_types.rb, add:

Mime::Type.register "application/xls", :xls

Restart the server. Click on download link again. You will get the error:

uninitialized constant Product::CSV

Add the require statement:

require 'csv'

in product model. Let's experiment in the rails console. We can get the list of column names for a model like this:

Product.column_names
 => ["id", "name", "released_on", "price", "created_at", "updated_at"]

We can retrieve the values of a given record like this:

p = Product.first
  Product Load (0.1ms)  SELECT  "products".* FROM "products" ORDER BY "products"."id" ASC LIMIT ?  [["LIMIT", 1]]
 => #<Product id: 1, name: "Settlers of Catan", released_on: "2016-04-15", price: #<BigDecimal:7fada51e1018,'0.35E2',9(18)>, created_at: "2016-06-09 20:16:29", updated_at: "2016-06-09 20:16:29">
> p.attributes
 => {"id"=>1, "name"=>"Settlers of Catan", "released_on"=>Fri, 15 Apr 2016, "price"=>#<BigDecimal:7fada51e1018,'0.35E2',9(18)>, "created_at"=>Thu, 09 Jun 2016 20:16:29 UTC +00:00, "updated_at"=>Thu, 09 Jun 2016 20:16:29 UTC +00:00}
> p.attributes.values
 => [1, "Settlers of Catan", Fri, 15 Apr 2016, #<BigDecimal:7fada51e1018,'0.35E2',9(18)>, Thu, 09 Jun 2016 20:16:29 UTC +00:00, Thu, 09 Jun 2016 20:16:29 UTC +00:00]

We can now implement the to_csv method in product model.

def self.to_csv(options = {})
  CSV.generate(options) do |csv|
    csv << column_names
    all.each do |product|
      csv << product.attributes.values
    end
  end
end

Since we are getting all the columns for this product, we retrieve the values for all the attributes of a given product. You will now be able to download the csv file. In a real project, I would create a new ProductCsvGenerator class for this method, the signature of the to_csv would not dependent on ActiveRecord specific details such as accessing product attribute values. It would look something like this:

def self.to_csv(column_names, products, options = {})
  CSV.generate(options) do |csv|
    csv << column_names
    products.each do |product|
      csv << product.column_values
    end
  end
end

In this case, the products collection would be a product wrapper that encapsulates the knowledge about accessing the values of the product attributes.

Exporting Specified Columns

Let's say you want to filter out created_at and updated_at fields, we can do it like this:

def self.to_csv(options = {})
  desired_columns = ["id", "name", "released_on", "price"]
  CSV.generate(options) do |csv|
    csv << desired_columns
    all.each do |product|
      csv << product.attributes.values_at(*desired_columns)
    end
  end
end

Create index.xls.erb with:

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell><Data ss:Type="String">ID</Data></Cell>
        <Cell><Data ss:Type="String">Name</Data></Cell>
        <Cell><Data ss:Type="String">Release Date</Data></Cell>
        <Cell><Data ss:Type="String">Price</Data></Cell>
      </Row>
    <% @products.each do |product| %>
      <Row>
        <Cell><Data ss:Type="Number"><%= product.id %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.name %></Data></Cell>
        <Cell><Data ss:Type="String"><%= product.released_on %></Data></Cell>
        <Cell><Data ss:Type="Number"><%= product.price %></Data></Cell>
      </Row>
    <% end %>
    </Table>
  </Worksheet>
</Workbook>

Click on Excel download link. You will now be able to download and view the excel spreadsheet. You can download the code for this article from exp.


Related Articles

Watch this Article as Screencast

You can watch this as a screencast Exporting Records in CSV and Excel Formats in Rails 5


Software Compatibility Best Practices

I spoke to some of the most talented and experienced software developers. I have created a guide that is filled with valuable insights and actionable ideas to boost developer productivity.

You will gain a better understanding of what's working well for other developers and how they address the software compatibility problems.

Get the Guide Now