H2 DatabaseとApache POIでExcelブックのセル範囲の値を結果セットとして返す関数を作成する

H2 DatabaseとApache POIでExcelブックのセル範囲の値を結果セットとして返す関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_get_data as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
SimpleResultSet poi_get_data(
  String file, String sheetName,
  int row, int column, int dy, int dx, 
  String dateFormat)
  throws Exception
{
  if( file == null ){
    return null;
  }
  if( dateFormat == null ){
    return null;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return null;
  }

  SimpleResultSet rs = new SimpleResultSet();
  for(int lx=0;lx<dx;lx++){
    rs.addColumn("C" + lx, Types.VARCHAR, 2048, 0);
  }

  for(int ly=0;ly<dy;ly++){
    Object values[] = new Object[dx];
    for(int lx=0;lx<dx;lx++){
      Row rowobj = sheet.getRow(row+ly);
      if( rowobj == null ){
        return null;
      }
      Cell cell = rowobj.getCell(column+lx);
      if( cell == null ){
        return null;
      }
      String value = null;
      switch(cell.getCellType()){
      case Cell.CELL_TYPE_NUMERIC:
        CellStyle cs = cell.getCellStyle();
        if( cs != null ){
          String df = cs.getDataFormatString();
          if( df.indexOf('y') != -1 
            || df.indexOf('m') != -1
            || df.indexOf('d') != -1){
            java.util.Date dt = cell.getDateCellValue();
            SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
          value = sdf.format(dt);
          } else {
            value = "" + cell.getNumericCellValue();
          }
        }
        break;
      case Cell.CELL_TYPE_STRING:
        value = cell.getStringCellValue();
        break;
      case Cell.CELL_TYPE_ERROR:
        value = "" + (int)cell.getErrorCellValue();
        break;
      case Cell.CELL_TYPE_FORMULA:
        value = cell.getCellFormula();
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        value = "" + cell.getBooleanCellValue();
      }
      values[lx] = value;
    }
    rs.addRow(values);
  }
  return rs;
}
$$

実行例

select * from poi_get_data('c:\share\test2.xlsx', 'Sheet1', 3, 1, 3, 3, 'yyyy/mm/dd');

実行例結果画面


※システム環境変数CLASSPATHにtools.jarと以下のPOI関連のjarを追加しておくこと。
・poi-3.7-20101029.jar
・poi-ooxml-3.7-20101029.jar
・poi-ooxml-schemas-3.7-20101029.jar
・commons-logging-1.1.jar
・log4j-1.2.13.jar;
・geronimo-stax-api_1.0_spec-1.0.jar
・xmlbeans-2.3.0.jar
・dom4j-1.6.1.jar

○動作環境
JDK6 Update 22, H2 Database 1.2.147 (2010-11-21), Apache POI 3.7
○関連情報
・H2 Databaseに関する他の記事はこちらを参照してください。