English
Français 
In my last post I introduced Apache POI for generating Microsoft Office files, as well as RJB, a Ruby to Java bridge. Today I would like to explain how to use these tools to generate Excel files.
Apache POI handles both the old Excel file format (from Excel 97 to 2007) and the new OOXML format. This article only deals with the first one.
For a full overview of Apache POI’s features you can browse the offical website and the related Java documentation.
In order to build your first script using Apache POI you have to download it and install RJB (gem install rjb). Further instructions for installing RJB are available on its website.
Please note that if you need sudo to install the gem, you must ensure that JAVA_HOME is also set for this environment:
$ sudo env JAVA_HOME=$JAVA_HOME gem install rjb
In your Ruby script you have to require RJB, load the Java Virtual Machine and then import all the Java classes you will need to create your spreadsheet:
require 'rubygems'
require 'rjb'
# JVM loading
apache_poi_path = File.join File.dirname(__FILE__), '/apache-poi-3.7/poi-3.7-beta2-20100630.jar'
apache_poi_contrib_path = File.join File.dirname(__FILE__), '/apache-poi-3.7/poi-contrib-3.7-beta2-20100630.jar'
Rjb::load("#{apache_poi_path}:#{apache_poi_contrib_path}", ['-Xms256M', '-Xmx512M'])
# Java classes import
file_class = Rjb::import('java.io.FileOutputStream')
workbook_class = Rjb::import('org.apache.poi.hssf.usermodel.HSSFWorkbook')
cell_style_class = Rjb::import('org.apache.poi.hssf.usermodel.HSSFCellStyle')
font_class = Rjb::import('org.apache.poi.hssf.usermodel.HSSFFont')
The following script shows how to perform basic actions like creating text cells with a basic style. First you must create the styles you need, and then apply them on a cell or a group of cells. Creating styles in loop (for example when styling a column) is not recommended, since their number is limited by Apache POI.
# Workbook, will contain worksheets
book = workbook_class.new
# First worksheet
sheet = book.createSheet('My worksheet')
# All columns will have the length of 20 characters
sheet.setDefaultColumnWidth(20)
# Default font style with Verdana
verdana_font = book.createFont
verdana_font.setFontName 'Verdana'
# Defaut style using Verdana
default_style = book.createCellStyle
default_style.setFont verdana_font
# Writing cells
row = sheet.createRow(0) # First line
cell = row.createCell(0) # First column
cell.setCellValue('Foo')
cell.setCellStyle(default_style) # Styles your cell
# Saves your file in the same directory
file_name = 'my_spreadsheet.xls'
out = file_class.new(File.join File.dirname(__FILE__), file_name)
book.write(out)
out.close
In this small overview of Apache POI we saw that it is a simple way to create Excel documents that are even readable on old versions of Microsoft Office. However its installation is not trivial when Ruby, not Java, is the language of your application, requiring the Java Virtual Machine and RJB to work with Ruby. This heavy framework will complicate your program maintenance but it is, unfortunately, the only reliable way to generate old Office files from Ruby.