Exporting to Excel file using Ruby on Rails 3

Posted on April 13, 2014 by sumanranjanpanda

In this blog I’ll show you how to export web application data to Excel file from Rails application
microsoft-excel

Let’s take an example to export data of the employees of an organization.

So following are the steps to download data in Excel format

Step#I (Add gems to the Gemfile)

/Gemfile

gem 'acts_as_xlsx'

It is an Active Record plugin that lets you convert any Active Record based model or finder method result into a fully valid xlsx, also knows as Office Open XML spreadsheet

gem 'axlsx_rails'

Axlsx-Rails provides a renderer and a template handler. It adds the :xlsx format and parses .xlsx.axlsx templates. This lets you take all the Axlsx code out of your controller or model and place it inside the template, where view code belongs!

And install it from the command line as follows:

bundle install

Step#II (Make the application respond to xlsx format)

To respond the request in xlsx format just include the format.xlsx in controller
/controllers/employees_controller.rb

 
class EmployeesController < ApplicationController
  def index
    @employees = Employee.order(:name).scoped
    respond_to do |format|
      format.html
      format.xlsx
    end
  end
end

Step#III (Update model)

Adding acts_as_xlsx to your ActiveRecord::Base inheriting models will add a class method to the model called to_xlsx.

/models/employee.rb


class Employee < ActiveRecord::Base
  acts_as_xlsx
  ....
  ...
end

Step#IV (Create template with the .xlsx.axlsx extension)

Create the template with the .xlsx.axlsx extension and in the template, use xlsx_package variable to create your spreadsheet.

In our case it will be as follows

/views/employees/index.xlsx.axlsx


wb = xlsx_package.workbook
wb.add_worksheet(name: "Employees") do |sheet|
  sheet.add_row ["Employee ID", "Name", "DOB", "Designation"] ## Header values of Excel
  @employees.each do |emp|
    sheet.add_row [emp.emp_id, emp.name, emp.dob, emp.designation]
  end
end

Step#V (Add link to download)

Add link to download Excel file in the view page

/views/employees/index.html.erb


<%= link_to "Download as Excel", employees_path(format: "xlsx") %>

Now when we reload the page we’ll see the download link and on clicking it will download the employees.xlsx file.

Posted in jQuery | Tagged , , , , , , | 1 Comment

Exporting to CSV file using Ruby on Rails 3

Posted on April 12, 2014 by sumanranjanpanda

In this blog I’ll show you how to export web application data to CSV file from Rails application by using only 4 simple steps.

csv

 

Here we’ll use CSV library, which comes with Ruby 1.9. And it is formerly know as Faster CSV, that was used with Ruby 1.8.

 

Let’s take an example to export data of the employees of an organization.

So following are the steps to download data in CSV format

Step#I (Require the CSV library)

It is a part of standard library, so we need to require it.

If we need to export data into CSV file from different sections, we’ll do this in our application’s config file.

/config/application.rb

require 'csv'

OR

just require it inside the controller

/controllers/employees_controller.rb

require 'csv'

Step#II (Make the application respond to csv format)

To respond the request in csv format just include the format.csv in controller

/controllers/employees_controller.rb

class EmployeesController < ApplicationController
  def index
    @employees = Employee.order(:name).scoped
    respond_to do |format|
      format.html
      format.csv { send_data @employees.to_csv}
      ##we can also change the downloaded CSV file name by setting filename attribute of send_data method
      #format.csv { send_data @employees.to_csv, :filename => '<file_name>.csv' }
    end
  end
end

Step#III (Generate the array of data for CSV)

We will do the CSV data generation work inside the Employee model to keep the controller skinny.

/models/employee.rb


class Employee < ActiveRecord::Base
  attr_accessible :emp_id, :name, :dob, :designation, :joining_date
  def self.to_csv
    CSV.generate do |csv|
      csv << column_names ## Header values of CSV
      all.each do |emp|
        csv << emp.attributes.values_at(*c olumn_names) ##Row values of CSV
      end
    end
  end
end

It will return an array of all of the model’s attributes and export as CSV

To return specific columns, we have to list them out as below


class Employee < ActiveRecord::Base
  attr_accessible :emp_id, :name, :dob, :designation, :joining_date
  
  def self.to_csv
    CSV.generate do |csv|
      csv << ["Employee ID", "Name", "DOB", "Designation"] ## Header values of CSV
      all.each do |emp|
        csv << [emp.emp_id, emp.name, emp.dob, emp.designation] ##Row values of CSV
      end
    end
  end
end

Step#IV (Add link to download)

Add link to download CSV file in the view page

/views/index.html.erb

 <%= link_to "Download CSV", employees_path(format: "csv") %>

Now when we reload the page we’ll see the download link and on clicking it will download the employees.csv file.