basic JRuby code to read/write a spreadsheet using the Apache poi libraries

excel_reader.rb

require 'java'
require 'pp'

import "org.apache.poi.hssf.usermodel.HSSFWorkbook"
import "org.apache.poi.hssf.usermodel.HSSFSheet"
import "org.apache.poi.hssf.usermodel.HSSFRow"
import "org.apache.poi.hssf.usermodel.HSSFCell"
import "java.io.FileInputStream"
import "java.io.FileOutputStream"

class ExcelReader
  include Enumerable
  attr_reader :worksheets
  
  def initialize(filename)
    raise "No such file #{filename}" unless File.exist? filename
    @workbook = HSSFWorkbook.new(FileInputStream.new(filename),false)  # second parameter is to preserve nodes we don't care about
    @worksheets = []
    0.upto(@workbook.getNumberOfSheets - 1) do |sheetNum|
      @worksheets << Worksheet.new(@workbook, @workbook.getSheetName(sheetNum), @workbook.getSheetAt(sheetNum))
    end
  end
  def each
    @worksheets.each do |sheet|
      yield sheet
    end
  end
  class Worksheet
    include Enumerable
    attr_accessor :name
    def initialize(workbook, name, worksheet)
      @workbook = workbook
      @name = name
      @worksheet = worksheet
    end
    def each
      it = @worksheet.rowIterator
      next_row = 0
      while it.hasNext
        row = Row.new(@workbook, it.next)
        while row.index > next_row  # fill gaps with empty rows
          yield EmptyRow.new(next_row)
          next_row += 1
        end
        yield row
        next_row += 1
      end
    end
  end
  class Row
    include Enumerable
    def initialize(workbook, row)
      @workbook = workbook
      @row = row
    end
    def each
      it = @row.cellIterator
      next_col = 0
      while it.hasNext
        cell = Cell.new(@workbook, it.next)
        while cell.index > next_col # fill gaps with empty cells
          yield EmptyCell.new(next_col)
          next_col += 1
        end
        yield cell
        next_col += 1
      end
    end
    def index
      @row.row_num
    end
  end
  class Cell
    CHAR_MAPPINGS={"\342\200\223" => "-"}
    def initialize(workbook, cell)
      @workbook = workbook
      @cell = cell
    end
    def clean_text(string)
      result = string
      CHAR_MAPPINGS.each do |oldtext, newtext|
        result = result.gsub(oldtext, newtext)
      end
      result
    end
    def value
      case @cell.cell_type
      when HSSFCell::CELL_TYPE_STRING
        return clean_text(@cell.rich_string_cell_value.string)
      when HSSFCell::CELL_TYPE_BLANK
        return ""
      when HSSFCell::CELL_TYPE_BOOLEAN
        return @cell.boolean_cell_value
      when HSSFCell::CELL_TYPE_ERROR
        return "Error: #{@cell.error_cell_value}"
      when HSSFCell::CELL_TYPE_FORMULA
        return @cell.cell_formula
      when HSSFCell::CELL_TYPE_NUMERIC
        return @cell.numeric_cell_value
      else
        raise "Unknown cell type #{@cell.cell_type.inspect}"
      end
    end
    def bold_weight
      fontix = @cell.cell_style.font_index
      font = @workbook.getFontAt(fontix)
      font.boldweight
    end
    def index
      @cell.column_index
    end
  end
  class EmptyRow
    attr_accessor :index
    include Enumerable
    def each
    end
    def initialize(index)
      @index = index
    end
  end
  class EmptyCell
    attr_accessor :index
    def initialize(index)
      @index = index
    end
    def value
      ""
    end
    def bold_weight
      0
    end
  end
end

excel_writer.rb

require 'java'

import "org.apache.poi.hssf.usermodel.HSSFWorkbook"
import "org.apache.poi.hssf.usermodel.HSSFSheet"
import "org.apache.poi.hssf.usermodel.HSSFRow"
import "org.apache.poi.hssf.usermodel.HSSFCell"
import "org.apache.poi.hssf.usermodel.HSSFRichTextString"
import "org.apache.poi.hssf.usermodel.HSSFFont" 
import "java.io.FileInputStream"
import "java.io.FileOutputStream"

class ExcelWriter
  MAX_ROW=65000
  attr_reader :worksheets
  def initialize(params = {})
    params = {:autobold => false}.merge(params)
    @workbook = HSSFWorkbook.new
    @worksheets = {}  # hash of name => {:sheet => sheet, :nextrow => row}
    @autobold = params[:autobold]
  end

  def add_worksheet(name)
    @worksheets[name] = ExcelWorksheet.new(@workbook, name, @workbook.create_sheet(name), @autobold)
  end
  def worksheet(name)
    @worksheets[name]
  end
  def write(filename)
    fileOut = FileOutputStream.new(filename)
    @workbook.write(fileOut)
    fileOut.close
  end

  class ExcelWorksheet
    def initialize(workbook, name, worksheet, autobold)
      @workbook = workbook
      @name = name
      @worksheet = worksheet
      @nextrow = 0
      @overlapcount = 0
      @autobold = autobold
      if @autobold
        @bold_style = @workbook.createCellStyle()
        @bold_font = @workbook.createFont()
        @bold_font.setBoldweight(HSSFFont::BOLDWEIGHT_BOLD);
        @bold_style.setFont(@bold_font);
      end
    end
    def add_row(*columns)
      row = @worksheet.createRow(@nextrow)
      @nextrow += 1
      columns.each_with_index do |column, colnum|
        if column.is_a? Numeric
          row.createCell(colnum).setCellValue(column)
        else
          if @autobold and column.to_s =~ /^\*(.*)\*$/
            # bold cell!
            cell_text = $1
            cell = row.createCell(colnum)
            cell.setCellValue(HSSFRichTextString.new(cell_text))
            cell.setCellStyle(@bold_style);
          else 
            row.createCell(colnum).setCellValue(HSSFRichTextString.new(column.to_s))
          end
        end
      end
      if @nextrow > MAX_ROW
        @overlapcount += 1
        @nextrow = 0
        @worksheet = @workbook.create_sheet("#{@name}_#{@overlapcount}") 
      end
    end
  end
 end