book a call

estimate project

The belighted Blog

Make xlsx files with AXLSX : basics

by Pierre M., on 28 August 2012

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 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
 send_data, :filename => "export.xlsx"

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.

Topics:      Under the hood

What is SaaS?

Everything You Need to Know About Moving to a SaaS Model.

Get the guide now >

Subscribe to Updates