H2 DatabaseとApache POIでExcelブックのシートにクエリー結果を保存する関数を作成する

H2 DatabaseとApache POIでExcelブックのシートにクエリー結果を保存する関数を作成するには、以下のスクリプトを実行します。

create alias if not exists poi_update_set_queryresult as $$ 
import java.io.*;
import java.sql.*;
import java.text.*;
import org.h2.tools.*;
import org.apache.poi.ss.usermodel.*;
@CODE
int poi_update_set_queryresult(java.sql.Connection conn,
  String inFile, String outFile, String sheetName,
  int oy, int ox, String query, Boolean header)
  throws Exception
{
  if( inFile == null ){
    return -1;
  }
  if( outFile == null ){
    return -1;
  }
  if( query == null ){
    return -1;
  }
  Workbook workbook = WorkbookFactory.create(new FileInputStream(inFile));
  Sheet sheet = workbook.getSheet(sheetName);
  if( sheet == null ){
    return -1;
  }

  int columns = 0;
  PreparedStatement stmt = conn.prepareStatement(query);
  ResultSet rset = null;
  Row rowobj = null;
  Cell cell = null;
  try
  {
    rset = stmt.executeQuery();
    ResultSetMetaData rsmd = rset.getMetaData();
    columns = rsmd.getColumnCount();
    if( header ){
      rowobj = sheet.getRow(oy);
      if( rowobj == null ){
        rowobj = sheet.createRow(oy);
      }
      for(int lx=0;lx<columns;lx++){
        cell = rowobj.getCell(ox+lx);
        if( cell == null ){
          cell = rowobj.createCell(ox+lx);
        }
        cell.setCellValue(rsmd.getColumnName(lx+1));
      }
      oy++;
    }
    while(rset.next()){
      rowobj = sheet.getRow(oy);
      if( rowobj == null ){
        rowobj = sheet.createRow(oy);
      }
      for(int lx=0;lx<columns;lx++){
        cell = rowobj.getCell(ox+lx);
        if( cell == null ){
          cell = rowobj.createCell(ox+lx);
        }
        if( rsmd.getColumnType(lx+1) == Types.DATE ){
          DataFormat df = workbook.getCreationHelper().createDataFormat();
          short dfn = df.getFormat("yyyy/mm/dd");
          CellStyle cs = workbook.createCellStyle();
          cs.setDataFormat(dfn);
          cell.setCellValue(new java.util.Date(
            rset.getDate(lx+1).getTime()));
          cell.setCellStyle(cs);
        } else {
          cell.setCellValue(rset.getString(lx+1));
        }
      }
      oy++;
    }
  }
  finally
  {
    if( rset != null ){ rset.close(); }
    stmt.close();
  }

  workbook.write(new FileOutputStream(outFile));
  return 0;
}
$$

実行例

select poi_update_set_queryresult('c:\share\test1.xlsx', 
  'c:\share\test1g.xlsx', 'シート1', 3, 1, 
  'SELECT * FROM INFORMATION_SCHEMA.TABLES', TRUE);

入力Excel(test1.xlsx)


出力Excel(test1g.xlsx)


※システム環境変数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に関する他の記事はこちらを参照してください。