Apache POI によるエクセルファイルの出力 その1 (Export to Excel file via Apache POI, Part 1)

以前、C#でエクセル形式のファイルへの出力を検証しました。今回はJava環境での検証を行います。（「エクセルファイルのバッチ出力 その1」を併せて参照してください）。

【概要】

全体の概要は以前の検証と変更ありません。参考まで、概要図を再掲します。

Figure 1: Overall image

【環境】

サンプルはJavaで実装します。そのほかの環境は以下の通りです。

• 使用ライブラリ： Apache POI 3.7 (XSSF)
• Excel： Excel2010
• データベース：Oracle Database 11gR2 (11.2.0.1)
• DB接続：Oracle JDBC Thinドライバ

【ソース（本体部）】

ソースの例を以下に示します。データの抽出、およびエクセルへの出力を定義します。

この例では、レコードセットをnextで移動する毎に、テンプレートの行をコピーおよび挿入し、セル毎にデータを挿入します。

List 1: Main logic

package util;

import java.io.*;
import java.util.Iterator;
import java.sql.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;


public class Ora2Excel {

  // db objects
  private Connection con;
  private Statement  stm;
  
  // poi objects
  private Workbook wbk;
  
  public Ora2Excel() throws SQLException {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  }

  public void openDb(String userId, String password, String connString) throws SQLException {
    con = DriverManager.getConnection(connString, userId, password);
    stm = con.createStatement();
  }
  
  public void closeDb() throws SQLException {
    stm.close();
    con.close();
  }
  
  public void openBook(String fileName) throws IOException {
    wbk = new XSSFWorkbook(fileName);
  }
  
  public void saveBook(String fileName) throws IOException {
    FileOutputStream out = new FileOutputStream(fileName);
    wbk.write(out);
    out.close();
  }
  
  public void closeBook() {
    wbk = null;
  }
  
  public void extract(String sheetName, String sql) throws SQLException {
    Sheet wsh = wbk.getSheet(sheetName);
    ResultSet rst = stm.executeQuery(sql);
    int colCount = rst.getMetaData().getColumnCount();
    
    // determine the start position: search "$start"
    int rowStart = 0;
    int colStart = 0;
    Iterator<Row> iRow = wsh.rowIterator();
    while (iRow.hasNext() && rowStart + colStart == 0) {
      Row currentRow = (Row) iRow.next();
      Iterator<Cell> iCol = currentRow.cellIterator();
      while (iCol.hasNext()) {
        Cell currentCell = (Cell) iCol.next();
        if (currentCell.getCellType() == Cell.CELL_TYPE_STRING  && currentCell.getStringCellValue().trim().equalsIgnoreCase("$start")) {
          rowStart = currentCell.getRowIndex();
          colStart = currentCell.getColumnIndex();
          break;
        }
      }
    }
    
    // get "template row"
    Row templateRow = wsh.getRow(rowStart);
    
    // set cell values
    int idxRow = rowStart;
    while (rst.next()) {
      wsh.shiftRows(idxRow, wsh.getLastRowNum()+1, 1);
      
      Row r = wsh.createRow(idxRow);
      for (int idxCol = templateRow.getFirstCellNum(); idxCol < templateRow.getLastCellNum(); idxCol++) {
        Cell c = r.createCell(idxCol);
        
        if (idxCol >= colStart && idxCol - colStart < colCount) {
          int idxDbCol = idxCol-colStart + 1;
          switch(rst.getMetaData().getColumnType(idxDbCol)){
          case Types.NUMERIC:
            c.setCellValue(rst.getDouble(idxDbCol));
            break;
          case Types.DATE:
              c.setCellValue(rst.getDate(idxDbCol));
            break;
          case Types.TIMESTAMP:
              c.setCellValue(rst.getDate(idxDbCol));
            break;
          default:
            c.setCellValue(rst.getString(idxDbCol));
          }
        } else if (templateRow.getCell(idxCol).getCellType() == Cell.CELL_TYPE_FORMULA){
          c.setCellFormula(templateRow.getCell(idxCol).getCellFormula());
        } else if (templateRow.getCell(idxCol).getCellType() == Cell.CELL_TYPE_NUMERIC){
          c.setCellValue(templateRow.getCell(idxCol).getNumericCellValue());
        } else if (templateRow.getCell(idxCol).getCellType() == Cell.CELL_TYPE_STRING){
          c.setCellValue(templateRow.getCell(idxCol).getStringCellValue());
        } 
        c.setCellStyle(templateRow.getCell(idxCol).getCellStyle());
        
      }
      idxRow++;
    }
    rst.close();
    
    // remove the template row.
    wsh.removeRow(templateRow);
    wsh.shiftRows(idxRow,  wsh.getLastRowNum()+1, -1);
    
    // calculate formula cells
    XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) wbk);
  }
}

【テスト用ソース（呼び出し部）】

上記クラスを呼び出す側の例を以下に示します。

List 2: Calling Main Logic

import util.Ora2Excel;

public class Test1 {

  public static void main(String[] args) throws Exception {
    Ora2Excel o2x = new Ora2Excel();
    
    o2x.openDb("scott", "tiger", "jdbc:oracle:thin:@windows2003srv:1521:orcl");
    
    o2x.openBook("c:\\template.xlsx");
    o2x.extract("Sheet1", "select deptno, empno, ename, job, mgr, hiredate, sal, comm from emp order by deptno, empno");
    o2x.saveBook("c:\\result.xlsx");
    o2x.closeBook();
    
    o2x.closeDb();
  }

}

【テンプレートファイル】

テンプレートファイルは、前回使用したファイルからスパークラインを除去したものを使用しています（詳細はダウンロードしたファイルの内容を参照して下さい）。

※現状ではApache POIがスパークラインに対応していないため。

データを出力する開始点に"$start"を入力します。

Figure 2: Template file

出力結果のイメージは以下の通りです。

Figure 3: Result

次回からは性能検証、および性能改善の方策を検討します。

エクセルのテンプレートファイル、結果ファイルはここからダウンロードできます。

※上記のソースはサンプルです。例外処理などは除外しています。

※上記のソースおよびファイルを使用したことによる一切の結果について、作成者は責任を負いません。

---

[Summary]
The sample above shows how to export data onto Excel sheet via Apache POI (former Jakarta POI).
cf. Excel file processing, Part 1


[Outline]
Overall image is shown in Figure 1 - same as the past post.


[Environment]
Source is in Java. Environment are as follows:

• External Library: Apache POI 3.7
• Excel: Excel2010
• Database: Oracle Database 11gR2 (11.2.0.1)
• DB connection: Oracle JDBC Thin Driver

[Source (main part)]
Please see List 1.  The sample class retrieves rows from database, and set the data onto workbook cell by cell.


[Test source]
Please see List 2.


[Template file]
Please see Figure 2 (Template file) and 3 (Output).  In the template, you set the report header, number format, formula and so on.  The start position, the top-left position, of data area is defined by "$start".  Please refer to the sample file for details.