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)
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
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:
Step#II (Make the application respond to xlsx format)
To respond the request in xlsx format just include the format.xlsx in controller
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.
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
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
<%= 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.