Posted by Seamus on Tuesday, June 12, 2012.

Writing XLSX from Ruby

Our xlsx_writer library for Ruby lets you create spreadsheets compatible with Microsoft Office 2007 Excel and above.


  1. Essential cell types: general, currency, date, integer, float (decimal)
  2. Standardized formatting: Arial 10pt, left-aligned text and dates, right-aligned numbers and currency
  3. Auto-fit to contents: always enabled
  4. Autofilters: just give it a range of cells
  5. Header and footer print styles: margins, arbitrary text, page numbers, and vector logos (.emf)

Basic example

require 'xlsx_writer'
doc =
sheet1 = doc.add_sheet 'Sheet1'
sheet1.add_row ['header1', 'header2', 'header3']
sheet1.add_row ['a', 'b', 'c']
sheet1.add_row [1, 2, 3]
require 'fileutils' doc.path, "/path/to/desired/location"

Advanced example

require 'xlsx_writer'
doc =
sheet1 = doc.add_sheet("People")

# First add data...

  "Average citations per paper"
  Date.parse("July 31, 1912"), 
  "Milton Friedman",
  "Economist / Statistician",
  {:type => :Currency, :value => 10_000},

# Then add autofilters and page styles...

sheet1.add_autofilter 'A1:E1'

# (figure out your croptop and cropleft by mocking it up in Excel and then unzipping the xlsx file. Get the .emf files, "cropleft" (if necessary), etc. from there)

left_header_image = doc.add_image('image1.emf', 118, 107)
left_header_image.croptop = '11025f'
left_header_image.cropleft = '9997f'
center_footer_image = doc.add_image('image2.emf', 116, 36) = 1.5
doc.page_setup.header = 0
doc.page_setup.footer = 0
doc.header.right.contents = 'Corporate Reporting'
doc.footer.left.contents = 'Confidential'
doc.footer.right.contents = :page_x_of_y
doc.header.left.contents = left_header_image = [ 'Powered by ', center_footer_image ]

# Finally you can generate the file.

require 'fileutils' doc.path, 'myfile.xlsx'

# don't forget

Debugging utilities

The library comes with two scripts:

  1. unpack.rb: Takes an XLSX file, unzips it, and reformats the XML it contains to be more readable.
  2. repack.rb: Takes a directory, converts the XML files to DOS line endings, and zips it into out.xlsx.

They have been useful in the past for debugging Excel crashes. You create a file in xlsx_writer and also in Excel, then unpack both of them and do a line-by-line comparison of the XML files within using diff -r.

What blog is this?

Safety in Numbers is Brighter Planet's blog about climate science, Ruby, Rails, data, transparency, and, well, us.

Who's behind this?

We're Brighter Planet, the world's leading computational sustainability platform.

Who's blogging here?

  1. Patti Prairie CEO