ruby + poi で Excel データを生成

csv データを Excel シートにする練習を jruby でやってみた。

2 つの csv ファイルを用意し、それぞれを 別シートとして保持する。
セルの書式設定、計算式(合計)の設定も試した。

まず生成した Excel データを OpenOffice で表示した画面を示そう。

シート１

コメントの表示

シート２

次に、csv ファイル、生成スクリプトを示す。(いずれも utf-8)
$ cat 01.csv
"ID","name","sex","age","data"
"1","ichirow","male","20","123456"
"2","hanako","female","40","-1234"

$ cat 2.csv
"ID","名前","性別","年齢","値"
"1","一郎","男","20","1000"
"2","次郎","男","18","0"
"3","三郎","男","16","-2000"
"4","四郎","男","14","1000"
"5","五郎","男","12","1"
"6","花子","女","20","2"

$ cat write-excel.rb

# See http://d.hatena.ne.jp/koush/20080426/1209197281
#     http://www.javadrive.jp/poi/index.html
#     > Jakarta POIでExcelを操作

require 'rubygems'
require 'fastercsv'
require 'pp'

require 'poi-3.5-beta5-20090219.jar'
require 'commons-lang-2.4.jar'

include Java

module HssfUserModel
  include_package 'org.apache.poi.hssf.usermodel'
end

module HssfUtil
  include_package 'org.apache.poi.hssf.util'
end

def makeSheet(workbook, sheetname, data = [])
  sheet = workbook.createSheet(sheetname)

  data.each_with_index do |row_data, r_index|
    row = sheet.createRow(r_index)
    row_data.each_with_index do |cell_data, c_index|
      cell = row.createCell(c_index)
      cell.setCellValue(cell_data)
      cell.setCellValue(cell_data.to_i) if org.apache.commons.lang.math.NumberUtils.isNumber(cell_data)

      style = createStyle(workbook, r_index, c_index, cell_data)
      cell.setCellStyle(style)
    end
  end

  # 合計行
  style = workbook.createCellStyle()
  style.setBorderTop(HssfUserModel::HSSFCellStyle::BORDER_MEDIUM)

  totalRow = sheet.createRow(data.size)
  totalTitleCell = totalRow.createCell(3)
  totalTitleCell.setCellStyle(style)
  totalTitleCell.setCellValue('Total') # ラベル

  totalValCell = totalRow.createCell(4)
  totalValCell.setCellStyle(style)
  totalValCell.setCellFormula("SUM(E2:E#{data.size})")  # 計算式

  # 合計セルにコメントを付ける
  patriarch = sheet.createDrawingPatriarch()
  anchor = HssfUserModel::HSSFClientAnchor.new(0, 0, 6, 3, 4, 2, 6, 5)
  comment = patriarch.createComment(anchor)
  comment.setString(HssfUserModel::HSSFRichTextString.new("コメント\ncomment"))
  comment.setAuthor("Apache Software Foundation")
  totalValCell.setCellComment(comment)
end

def createStyle(workbook, r_index, c_index, cell_data)
  style = workbook.createCellStyle()

  fontBold = workbook.createFont()
  fontBold.setBoldweight(HssfUserModel::HSSFFont::BOLDWEIGHT_BOLD)

  formatDouble = workbook.createDataFormat()

  if r_index == 0
    # 罫線
    style.setBorderBottom(HssfUserModel::HSSFCellStyle::BORDER_MEDIUM)
    # 背景色
    style.setFillForegroundColor(HssfUtil::HSSFColor::YELLOW.index)
    style.setFillPattern(HssfUserModel::HSSFCellStyle::SOLID_FOREGROUND)
    # 右寄せ
    style.setAlignment(HssfUserModel::HSSFCellStyle::ALIGN_CENTER)
    # フォント
    style.setFont(fontBold)
  end

  if org.apache.commons.lang.math.NumberUtils.isNumber(cell_data)
    # 右寄せ
    style.setAlignment(HssfUserModel::HSSFCellStyle::ALIGN_RIGHT)
    # 書式
    f = '#,##0;[RED]-#,##0'
    style.setDataFormat(workbook.createDataFormat().getFormat(f))
  end

  if c_index == 2 and r_index != 0
    # 回転
    style.setRotation(-30)
  end

  if c_index == 1 and r_index != 0
    # インデント
    style.setAlignment(HssfUserModel::HSSFCellStyle::ALIGN_LEFT)
    style.setIndention((r_index - 1) % 3)
  end
  style
end

FILE_NAME = 'test.xls'

fileOut = java.io.FileOutputStream.new(FILE_NAME)
wb = HssfUserModel::HSSFWorkbook.new
makeSheet(wb, 'シート-01', FasterCSV.read('01.csv'))
makeSheet(wb, 'シート-02', FasterCSV.read('02.csv'))
wb.write(fileOut)
fileOut.close