<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=706355809571598&amp;ev=PageView&amp;noscript=1">
Free Assessment
Estimate Project
Menu
Estimate Project

Make xlsx files with AXLSX : basics

by Pierre M., on 29 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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)

1
2
# 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.

1
2
3
4
# 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.

New Call-to-action

 
Topics:Under the hood

Comments