以前、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.
