Many organizations need Excel’s Worksheets. Therefore, it’s not surprising that you can find a bunch of gems that help you doing the job. For our last project, we choose axlsx because of our need of integrating charts in ours Workbooks, and other advanced features like styling cells.

The client gave us a sample xlsx export file with graphs and styles generated by his former app. I have to make my own worksheet similar and supply it in the new app.

Structure of Excel’s objects

Here is how it works. I let the code in a single file for the example; As you should aim to skinny controllers, it as to be refactored, for instance with presenters.

require 'axlsx'
class ExportsController < ApplicationController
def index
format.xlsx do
Axlsx::Package.new do |my_axlsx_package|
my_axlsx_package.workbook do |wb|
wb.add_worksheet(name:'foo') do |sheet|
# filling cells, see below
# finding cells, see below
end
end
send_data my_axlsx_package.to_stream.read, :filename => "export.xlsx"
end
end
end
end

Axlsx provides us those business classes : Package, Workbook, Worksheet Row and Cell (… and even more).

Fill data in the worksheet

The best way to fill your sheet is to do it with arrays.

Notice that you can pass an optionnal array of styles. (see examples in the gem)

# filling data in the worksheet
sheet.add_row ["This text comes in my first cell", 33, true, "this text come in the fourth cell"]

If you’d rather apply your style after having build your sheet, finding right place of each style could be tricky.

Finding cells

Worksheet’s instances offer those key methods : rows , cells and []. All return Cell ‘s instances.

The more convenient is “[]” that is Excel like.

# finding in Excel style
sheet["B1"] # returns the Cell object which value is "33". Great!
# finding with indexes
sheet[Axlsx::cell_r(1,0)] # returns the same cell.

Now you can apply style style and format to your cell, and update his value.

Notes :

1. You can use Excel alphanumeric references or indexes. Beware that indexes start at 0. The use of both system in the same code may be confusing.
2. brackets method takes also cells range like “A1:B4”

It’s worth digging in that gem distributed under MIT license. A last word : Randy Morgan make it full covered by test/unit.