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.
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).
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.
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.
It’s worth digging in that gem distributed under MIT license. A last word : Randy Morgan make it full covered by test/unit.
Everything You Need to Know About Moving to a SaaS Model.
Get the guide now >