Generating Excel Spreadsheets in JRuby on Rails

February 4, 2008 · Filed under java, jruby · Tagged jruby java

I’ve been working on a rails app for a couple weeks now using JRuby and things have been going really well. At the start I didn’t really need to use the Java integration for anything and chose JRuby mostly so I could package as a war and deploy to tomcat. But then I ran into the need to export to excel. I first found this page and implemented their example:

http://wiki.rubyonrails.org/rails/pages/HowToExportToExcel

Unfortunately the Office 2003 xml format doesn’t work on anything except Microsoft Excel. I tried Open Office, NeoOffice, and iWork’s Numbers to no avail. So here’s how I used JRuby’s java integration along with apache poi to export my models as .xls files:

Get the poi jar (poi-3.0.2-beta2-20080112.jar) from http://poi.apache.org/ and put it in the lib dir of your project.

Register the excel mime type…

# config/environment.rb
# Add new mime types for use in respond_to blocks:
Mime::Type.register "application/vnd.ms-excel", :xls

Create a new Excel helper to do the export

# app/helpers/excel_helper.rb
module ExcelHelper
  require 'java'
  require '../../lib/poi-3.0.2-beta2-20080112.jar'
  include_class 'org.apache.poi.poifs.filesystem.POIFSFileSystem'
  include_class 'org.apache.poi.hssf.usermodel.HSSFCell'
  include_class 'org.apache.poi.hssf.usermodel.HSSFWorkbook'
  include_class 'org.apache.poi.hssf.usermodel.HSSFCellStyle'
  include_class 'org.apache.poi.hssf.usermodel.HSSFDataFormat'
  include_class 'java.io.ByteArrayOutputStream'
  include_class 'java.util.Date'

  def toXls(items=[])
    book = HSSFWorkbook.new()
    sheet = book.createSheet("values");
    dateStyle = book.createCellStyle();
    dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"))

    # header row
    if !items.empty?
      row = sheet.createRow(0);
      cell_index = 0
      items[0].class.columns.each do |column|
        row.createCell(cell_index).setCellValue(column.name);
        cell_index += 1
      end
    end

    # value rows
    row_index = 1
    items.each do |item|
      row = sheet.createRow(row_index);
      
      cell_index = 0
      item.class.columns.each do |column|
        cell = row.createCell(cell_index)
        if column.sql_type =~ /date/ then
          millis = item.send(column.name).to_f * 1000
          cell.setCellValue(Date.new(millis))
          cell.setCellStyle(dateStyle);
        elsif column.sql_type =~ /int/ then
          cell.setCellValue(item.send(column.name).to_i)
        else
          cell.setCellValue(item.send(column.name))
        end
        cell_index += 1
      end
      row_index += 1
    end

    outs = ByteArrayOutputStream.new
    book.write(outs);
    outs.close();
    String.from_java_bytes(outs.toByteArray)
  end
end

Use the Excel helper in your controller

# app/controllers/my_controller.rb
include ExcelHelper

class MyStuffController < ApplicationController
  def index
    respond_to do |format|
      format.html do
        @items = MyStuff.find :all
      end
      format.xls do
        @items = MyStuff.find :all
        filename = params[:controller] + ".xls"
        send_data toXls(@items), :filename => filename,
          :type => "application/vnd.ms-" + "excel"
      end
    end
  end
end

At this point all you need to do to export as excel is add a .xls to your normal route. So if you normally go to http://localhost:3000/my_stuff, just go to http://localhost:3000/my_stuff.xls to get the excel file.