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

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.

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:

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:

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:

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:

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:

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.

: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:

More from this author

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!

Ilya Bodrov is a senior engineer working at Campaigner LLC, teaching assistant at Learnable and lecturer at Russian State Technological University (Internet Technology department). His primary programming languages are Ruby (with Rails) and JavaScript (AngularJS). He enjoys coding, teaching people and learning new things. Ilya also has some Cisco and Microsoft certificates and was working as a tutor in an educational center for a couple of years. In his free time he writes posts for his website , participates in OpenSource projects, goes in for sports and plays music.