Ruby on Rails — Exporting Data to Excel

Often my applications will require data from my app’s database to be exported to Microsoft Excel. This may be required for a number of different reasons — client reporting tools, data analysis that occurs outside the scope of our app, or even because some clients are more comfortable digesting data in tools their familiar with (have you met an engineer who didn’t love Excel?).

Whatever the reason, data export to excel can be accomplished fairly easily using standard Rails tooling and some help from the axlsx_rails gem. In this walk-through, we’ll build an inventory app that allows users to export the contents of the store’s inventory to Excel.

Let’s get started. We’ll be using Rails 5.1 and Ruby 2.5 throughout.

Setting it up

First, let’s create the app:

rails new -T -d postgresql
cd inventory

In the above command I’ve include a couple flags:

• -T asks rails to skip installing the default test suite. While we won’t be using tests in this tutorial, I typically use rspec, which I would normally install after initializing the app
• -d postgresql lets rails know that I want to use post postgresql as my database. I often deploy using Heroku, and Heroku uses postgresql by default. Using the same database during development reduces the risk of potential conflicts during deployment.

Now that we’ve created our app, let’s start up the server and make sure everything’s working as expected:

rails s

Yay!

Okay, let’s start building our application. To help us get up to speed quickly, we’ll scaffold together an items resource:

rails g scaffold item name:string quantity:integer

Next let’s create our database and run the migration generated by rails:

rails db:create
rails db:migrate

Now, let’s point our root_path towards the index action of the items controller:

#config/routes.rb
Rails.application.routes.draw do
  resources :items
  root to: 'items#index'
end

Now when we visit localhost:3000, we see:

The newly created items index

To get some items inside our app, let’s create a seed file. We’ll use the Faker gem to create a bunch of different coffees in our inventory. First, let’s add Faker to our gemfile. You can always find the latest version of the gem at RubyGems.org.

#Gemfile
...
gem 'faker', '~> 1.6', '>= 1.6.6'
...

Then run bundle to update our app:

bundle

Next, we’ll update our seeds.rb:

Then we seed the database:

rails db:seed

And now if we refresh our app, we see 100 different blends of coffee:

That’ll wake you up!

Okay, we’ve got our data, now let’s get into exporting to Excel.

Exporting to Excel

In order to get our data exported to Excel, we’re going to use the axlsx gem. Axlsx is an incredible gem, which allows you to “create excel worksheets with charts, images (with links), automated and fixed column widths, customized styles…”, all using ruby.

To access this gem in rails, we’ll be making use of the axlsx_rails gem, which provides great integration into rails.

Let’s get started.

In our gemfile, let’s add the following gems (as instructed in the readme):

#Gemfile
...
gem 'rubyzip', '>= 1.2.1'
gem 'axlsx', git: 'https://github.com/randym/axlsx.git', ref: 'c8ac844'
gem 'axlsx_rails'
...

and install the new gems via bundler:

bundle

Finally, we need to restart our server so that it’s initialized properly with these changes:

rails s

Now we’re ready to start creating our spreadsheet. Let’s add a link to download the spreadsheet to our index page:

Pay attention to the format: :xlsx included with the path. This tells our controller which format to provide back to the user.

This won’t work yet, though, because we haven’t told our controller what to do with a request for the xlsx format. Let’s fix that now. In our items controller, update the index method as follows:

We still have one final piece missing: the template for our new spreadsheet. When we call a format of xlsx, the axlsx_rails gem will instruct our app to look for a file with the same name as the controller action (‘index’) with a file extension of ‘.xlsx.axlsx’.

Let’s create that file now:

touch app/views/items/index.xlsx.axlsx

We can now edit this file. I’ve shown the final file here — we’ll unpack it below.

Ok, let’s take that apart a bit:

wb = xlsx_package.workbook

This line creates a variable (wb) that’s associated with a new workbook.

wb.add_worksheet(name: "Items") do |sheet|

This line adds a new worksheet to our newly created workbook, then sends it a block of code for execution. We also name the new worksheet (creatively, “Items”).

sheet.add_row ["Item Name", "Quantity"]

Here, we add the first row of data to our new worksheet. In this case, we’re adding the two attributes of our model, Item Name and Quanity. add_row accepts an array, with each item in the array representing the data of a single cell.

@items.each do |item|
  sheet.add_row [item.name, item.quantity]
end

Finally, we add all of our data. Note that helpfully all instance variables created in our controller action are available to us in this template. It’s simply a matter of iterating over each item in @items and showing the corresponding name and quantity.

Now, when we click our link, we download a new spreadsheet with all of our data!

Pretty neat!

Of course, it’s not pretty yet. Fortunately, the API provided by axlsx is incredible, and allows for a huge amount of customization. We’ll play with a little bit of styling to give you a taste of what’s possible, but I’d encourage you to glance through the docs to how wide open this really is.

Let’s add a title, a date, and add some styling to the header row:

Here we’ve added some styling options. You’ll see that for the project heading, we’ve added the options of b:true (bold) and sz: 14 (size 14). We’ve also added the date that the spreadsheet was downloaded (if you don’t recognize the strftime syntax, read up on it here. Finally, we bolded the column headings. The final result looks something like this:

Looking prettier!

And there you go! You can now export your data to excel!

You can see the whole project on GitHub:

jasondoggart/inventory

Do you have a better way of doing something? Have some ideas for improvement? Or do you want some clarification? Hit me up in the comments, I’d love to hear from you!

Up next — how to edit this spreadsheet, then import the changes (and new items) back into our database.