Generate Excel Spreadsheets with Rails and the Axlsx Gem

You have probably generated CSV files in your Rails applications based on some data. Those files can then be converted to Excel format in order to apply some styling, formatting, add graphs, etc. However, wouldn’t it be more convenient to automate all these tasks and generate .xlsx files instead? With the axlsx gem created by Randy Morgan that it is totally possible!

This gem provides a huge list of features allowing you to do nearly anything from adding simple formatting rules to generating graphs and setting options for printing. Many different examples can be found on GitHub. What’s more, there are other additional gems available:

• axlsx_rails – provides Rails templates for axlsx. A really nice solution that we will be using in this article
• acts_as_axlsx – special ActiveRecord mixin equipping your models with the to_xlsx method
• activeadmin-xlsx – plugin for ActiveAdmin, which was mentioned in a SitePoint article on ActiveAdmin.

Today we are going to see how to generate and customize Excel files in Rails apps with the help of axlsx gem.

The source code is available on GitHub.

Creating an App

For this demo I will be using Rails 5 (it is still in beta though), but axlsx_rails works with both Rails 3 and 4. axlsx itself works with Ruby 1.9.2 and higher.

Let’s call our demo app “ExcelNinja”:

$ rails new ExcelNinja -T

We will store some sample data in the database and then generate Excel files based on it, so add a new Product model:

$ rails g model Product title:string price:decimal
$ rake db:migrate

I’m going to take advantage of seeds.rb to populate this new table with sample data. However, I also want my products to look more or less realistic, so let’s include the faker gem. It can generate nearly anything from product prices to book titles and pseudo-IT phrases.

Gemfile

[...]
gem 'faker'
[...]

Run

$ bundle install

and tweak the seeds.rb file:

db/seeds.rb

30.times do
  Product.create({title: Faker::Commerce.product_name, price: Faker::Commerce.price})
end

Now populate the table:

$ rake db:seed

Okay, the last thing to do is to set up some routes and create a controller and view.

config/routes.rb

[...]
resources :products, only: [:index]

root 'products#index'
[...]

products_controller.rb

class ProductsController < ApplicationController
  def index
    @products = Product.order('created_at DESC')
  end
end

views/products/index.html.erb

<h1>List of products</h1>

I won’t really list anything in this view – instead we will present users with a link to download a formatted Excel file, so proceed to the next section.

Generating Excel File

Add a new gem into your Gemfile:

Gemfile

[...]
gem 'axlsx_rails'
[...]

and run

$ bundle install

Having this gem in place, you can introduce a new response format like this:

products_controller.rb

[...]
def index
  @products = Product.order('created_at DESC')
  respond_to do |format|
    format.html
    format.xlsx
  end
end
[...]

Present a new link with the corresponding format:

views/products/index.html.erb

<h1>List of products</h1>
<%= link_to 'Download as .xlsx', products_path(format: :xlsx) %>

Now create a new template called index.xlsx.axlsx (yeah, the extension’s name is complex so make sure you type it correctly). This will be a plain Ruby file with instructions to generate the .xlsx. Inside the view, a xlsx_package local variable is available:

views/products/index.xlsx.axlsx

wb = xlsx_package.workbook

This way we simply instantiate a new Excel workbook. Each workbook consists of sheets, so let’s add one:

views/products/index.xlsx.axlsx

wb = xlsx_package.workbook
wb.add_worksheet(name: "Products") do |sheet|
end

Inside this block you define the contents of this sheet:

views/products/index.xlsx.axlsx

wb = xlsx_package.workbook
wb.add_worksheet(name: "Products") do |sheet|
  @products.each do |product|
    sheet.add_row [product.title, product.price]
  end
end

So with add_row we create a new row that contains product’s title and price. name: "Products" sets the sheet’s title.

Boot up the server, navigate to the root page, and click the “Download as .xlsx” link. That was simple, wasn’t it?

To rename the generated file, use the following code:

products_controller.rb

[...]
respond_to do |format|
  format.html
  format.xlsx {
    response.headers['Content-Disposition'] = 'attachment; filename="all_products.xlsx"'
  }
end
[...]

Alternatively, you may use a render method with various options:

render xlsx: 'products', template: 'my/template', filename: "my_products.xlsx", disposition: 'inline',
  xlsx_created_at: 3.days.ago, xlsx_author: "Elmer Fudd"

What’s more, Excel files can be generated inside mailers – read more here.

Styling

Currently our sheet looks a bit boring, so let’s add some background color for the prices. To do this, initialize a new variable with styles inside a block:

index.xlsx.axlsx

wb = xlsx_package.workbook
wb.styles do |style|
  highlight_cell = style.add_style(bg_color: "EFC376")

  wb.add_worksheet(name: "Products") do |sheet|
    @products.each do |product|
     sheet.add_row [product.title, product.price], style: [nil, highlight_cell]
    end
  end
end

Using the add_style method, we introduce styling rules that can later be applied to one or more cells. When calling the add_row we simply pass the :style option that accept styles for every cell. As long as I do not want to style the first column (that contains product’s title), I simply specify nil as the first array’s element. The second element contains our custom style that sets orange background for the product’s price.

There are much more styles that can be set (refer to the examples for more information). For instance, let’s add a thin border for price and center the contents horizontally:

index.xlsx.axlsx

[...]
highlight_cell = style.add_style(bg_color: "EFC376",
                                   border: Axlsx::STYLE_THIN_BORDER,
                                   alignment: { horizontal: :center })
[...]

You can easily override styles for any cell:

sheet.rows.last.cells.first.u = :single
# Or
sheet.rows.last.cells[0].u = :single

Basically, rows and cells are organized as array’s elements. The u method makes the cell’s contents underlined (for some reason this does not work in LibreOffice though – find other known issues here).

Formatting

Okay, now I also want to display the creation date for every product. Modify the add_row method call:

index.xlsx.axlsx

sheet.add_row [product.title, product.price, product.created_at], style: [nil, highlight_cell]

However, as you remember, the created_at column stores information in a pretty ugly and non-friendly format. How about adding a formatting rule for this cell? It also can be done via the styles:

date_cell = style.add_style(format_code: "yyyy-mm-dd", border: Axlsx::STYLE_THIN_BORDER)

I’ve also added a thin border just like we did for prices. Styles and formatting rules can be mixed together without any problems.

Now apply the new styling rules:

index.xlsx.axlsx

sheet.add_row [product.title, product.price, product.created_at], style: [nil, highlight_cell, date_cell]

Let’s also add formatting rules for the prices:

index.xlsx.axlsx

highlight_cell = style.add_style(bg_color: "EFC376",
                                 border: Axlsx::STYLE_THIN_BORDER,
                                 alignment: { horizontal: :center },
                                 :format_code => '#.##')

Instead you may use one of the pre-defined formats:

highlight_cell = style.add_style(bg_color: "EFC376",
                                 border: Axlsx::STYLE_THIN_BORDER,
                                 alignment: { horizontal: :center },
                                 num_fmt: 8)

Adding Hyperlinks

You can add a hyperlink to any cell by using the add_hyperlink method and providing a reference to the desired cell. Suppose we want product titles to lead to the show action of the ProductsController. Let’s quickly add the new method, view, and route:

config/routes.rb

[...]
resources :products, only: [:index, :show]
[...]

products_controller.rb

[...]
def show
  @product = Product.find(params[:id])
end
[...]

views/products/show.html.erb

<h1><%= @product.title %></h1>
<p>Price: <%= @product.price %></p>

Now add the hyperlink:

index.xlsx.axlsx

[...]
@products.each do |product|
  sheet.add_row [product.title, product.price, product.created_at],
                style: [nil, highlight_cell, date_cell]

  sheet.add_hyperlink location: url_for(action: "show", id: product.id, only_path: false),
                      ref: sheet.rows.last.cells.first
  [...]
end
[...]

There are couple things to note here. The url_for method generates the proper link. However, we must set the :only_path option to false, otherwise this method will generate a relative path that’s obviously incorrect. The :ref option specifies the address of the hyperlink. We already learned that cells can be accessed just like array’s elements. As long as we call add_hyperlink inside the cycle, the sheet.rows.last will always point to the newly added row and sheet.rows.last.cells.first, in turn, points to the cell with the product’s title.

Hyperlinks can also point to other cells and sheets. Suppose you add some other sheet to your workbook:

wb.add_worksheet(name: 'Another Sheet') do |sheet|
  sheet.add_row ['a cell']
end

You want links to point to the B2 cell of this new sheet:

sheet.add_hyperlink location: "'Another Sheet'!B2", ref: sheet.rows.last.cells.first, target: :sheet

Just provide the sheet’s name, the cell’s number, and set the :target option to :sheet.

Adding Formulas

You can easily add Excel formulas with axlsx. Suppose we want to calculate the total price of all our products. Of course, this can be done directly inside the index.xlsx.axlsx file but that’s not really optimal. Instead, let’s employ the SUM function.

Add a new line of code just after the cycle:

index.xlsx.axlsx

[...]
@products.each do |product|
  [...]
end
sheet.add_row ['Total', "=SUM(B1:B#{@products.length})"]
[...]

We don’t know how many products will be stored inside the database, so use interpolation to generate the proper row number.

Adding Charts

axlsx supports all major chart types: bar, line, pie, and scatter. For this demo, let’s create a 3D bar chart illustrating product price. Use the add_chart method:

index.xlsx.axlsx

[...]
sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N40") do |chart|
end
[...]

:start_at specifies the top-left corner of the chart whereas :end_at – the bottom-right one. If you have many products in your database, these values will have to be tweaked accordingly – otherwise the chart will be too small. You can also introduce a simple formula like this one:

index.xlsx.axlsx

sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N#{(@products.length * 1.5).floor}") do |chart|
end

Inside the block simply describe the chart by adding data series, providing labels, and styling rules:

index.xlsx.axlsx

[...]
sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N#{(@products.length * 1.5).floor}") do |chart|
  chart.add_series data: sheet["B1:B#{@products.length}"],
                   labels: sheet["A1:A#{@products.length}"],
                   title: "Products by price",
                   colors: @products.map { "00FF00" }
end
[...]

• :data says where to get the chart’s data.
• :labels specifies where to load labels for each series.
• :title specifies the heading for the chart itself.
• :colors specifies which color to use for each series.

I found out that for LibreOffice you have to provide colors for all series, otherwise they won’t be displayed at all.

You can also hide grid lines easily:

index.xlsx.axlsx

sheet.add_chart(Axlsx::Bar3DChart, start_at: "D1", end_at: "N#{(@products.length * 1.5).floor}") do |chart|
  chart.add_series data: sheet["B1:B#{@products.length}"],
                   labels: sheet["A1:A#{@products.length}"],
                   title: "Products by price",
                   colors: @products.map { "00FF00" }

  chart.valAxis.gridlines = false
  chart.catAxis.gridlines = false
end

Apparently, these settings do not have any effect in LibreOffice, as it does not display grid lines in any case.

Conclusion

In this article, we’ve discussed the axlsx gem that allows you to easily generate Excel files. We’ve also taken advantage of axlsx_rails to integrate with Rails. As I already said, there is much more you can do with with axlsx: merge columns, add conditional formatting, add headers and footers, hide or protect sheets, etc. Many use-cases can be found in the examples file. The gem is also documented with YARD, so you can follow instructions here to be able to read it.

Unfortunately, axlsx is not ideal and does have some known interoperability issues with LibreOffice and Google Docs (we’ve actually stumbled upon some of them in this demo). The list of these issues can be found here.

Still, those issues are pretty minor and all in all axlsx is a really great solution and I do recommend trying it out. Feel free to post your questions in the comments if something is not working for you. Thanks for staying with me and see you soon!

Frequently Asked Questions (FAQs) about Generating Excel Spreadsheets with Rails Axlsx Gem