概要
JXLS を利用してExcelを出力しているアプリケーションで、セルに動的にコメントを出力したいという要件が出てきたときの奮闘備忘録。
環境
- Java 8
- poi 4.1.2
- jxls 2.8.1
- jxls-poi 2.8.1
前提
Excel出力時のメモリ消費を抑えるために、JXLSのSXSSF Transformerを利用。
JXLS公式で公開されているSxssfDemo.javaを参考に、下記実装をベースとして検証。
※単純にSXSSF Transformerを使ってExcel出力しているだけの実装
Workbook workbook = WorkbookFactory.create(is);
PoiTransformer transformer = PoiTransformer.createSxssfTransformer(workbook, 5, false);
AreaBuilder areaBuilder = new XlsCommentAreaBuilder(transformer);
List<Area> xlsAreaList = areaBuilder.build();
Area xlsArea = xlsAreaList.get(0);
Context context = new Context();
context.putVar("data", generateSampleDataList());
xlsArea.applyAt(new CellRef("Result!A1"), context);
context.getConfig().setIsFormulaProcessingRequired(false);
workbook.setForceFormulaRecalculation(true);
workbook.setActiveSheet(1);
transformer.getWorkbook().write(os);
参考：
https://github.com/jxlsteam/jxls/blob/master/jxls-poi/src/test/java/org/jxls/examples/stress/SxssfDemo.java
https://poi.apache.org/components/spreadsheet/
先に結論
JXLSの CellDataUpdater の仕組みを使って、セルの出力時に動的にセルコメントを付与する。
経緯
テンプレートへの直接コメント付与
そもそもJXLSのExcelテンプレートに静的にコメントを付与しているとどうなるか確認。
テンプレート
出力結果↓
jxlsコマンドと同居させたコメントが無視されるのは理解できるが、なぜか通常のセルコメントが１列隣にズレて出力される。
SXSSF Transformerを利用しない方式で実装した場合は、正しい列にコメントが出力されていたので、SXSSF Transformerのバグ？
いずれにせよ今回の目的は「動的な」セルコメントの付与であるため気を取り直す。
ボツ案その１
テンプレートへのデータマッピングが終わった後にPOIのAPIを使ってセルコメント付与する案。
POIでセルコメントを生成・付与する処理が煩雑なので、ユーティリティクラスを作って吸収。
public class SamplePoiUtils {
/**
* セルコメントを設定
*
* @param sheet コメント設定対象シート
* @param rowNo コメント設定対象セルの行番号（0始まり）
* @param colNo コメント設定対象セルの列番号（0始まり）
* @param commentString コメント文字列
* @param isVisible コメントの表示状態（true：表示、false：非表示）
*/
public static void setCellComment(Sheet sheet, int rowNo, int colNo, String commentString, boolean isVisible) {
// 省略
}
}
Excelに書き出す直前にセルコメント付与処理を挿入。
表のヘッダ行にセルコメントを付与したいためA1セルに付けてみる。
Sheet sheet = transformer.getWorkbook().getSheet("Result");
SamplePoiUtils.setCellComment(sheet, 0, 0, "Comment on Cell A1", true);
transformer.getWorkbook().write(os);
実行したところヌルポ発生！
rowがnullになっている模様・・
java.lang.NullPointerException:
Cannot invoke "org.apache.poi.ss.usermodel.Row.getCell(int)" because "row" is null
原因はSXSSF方式。
やりたいのはヘッダ行（先頭行）へのコメント付与だが、出力データのマッピングが終わったときはすでにSXSSFの処理ウィンドウ内にヘッダ行が残っていなかった。
処理ウィンドウのサイズを極端に大きくすればいけるかもしれないが、出力行数が予測できないし、OOME発生のリスクが高まるので断念・・
SXSSF achieves its low memory footprint by limiting access to the rows that are within a sliding window, while XSSF gives access to all rows in the document. Older rows that are no longer in the window become inaccessible, as they are written to the disk.
参考：https://poi.apache.org/components/spreadsheet/how-to.html#sxssf
ボツ案その２
JXLSでの出力が完全に終わった後のワークブックを再度POIで開いてセルコメント付与する案。
色々試したがSXSSF方式では、既存の行への追記はできない。
XSSF方式で開けば実現できることは確認できたが、ワークブック内の全データがメモリ展開されることによるOOMEのリスクを考えて断念・・（OOME恐怖症）
参考：https://qiita.com/neko_the_shadow/items/3a70dc7b8236e93221e7
採用案
JXLSのCellDataUpdaterを利用してセルへのデータマッピング時にセルコメントも付与する案。
CellDataUpdaterインタフェースをインプリすれば、JXLSがセルにデータをマッピングするタイミングでフックして処理を入れ込むことができる。
参考：http://jxls.sourceforge.net/reference/updatecell_command.html
public interface CellDataUpdater {
void updateCellData(CellData cellData, CellRef targetCell, Context context);
}
その１：CellData#setCellCommentを使う案（ボツ案）
updateCellDataの引数CellDataにsetCellCommentなるメソッドがある。
（ええもん持っとるやんけ。勝ったわ。）
public void setCellComment(String cellComment) {
this.cellComment = cellComment;
}
ということで実装。
private static class CommentCellUpdater1 implements CellDataUpdater {
@Override
public void updateCellData(CellData cellData, CellRef targetCell, Context context) {
cellData.setCellComment("Comment on cell by CommentCellUpdater1");
}
}
Context context = new Context();
context.putVar("commentCellUpdater1", new CommentCellUpdater1());
テンプレートにjx:updateCellコマンドを設定。
B1セルにセルコメントを付与するように設定。
※lastCellにはその行の最終セルのアドレスを指定しないと出力が途中で切れるため注意
出力結果が以下。
またもやコメントの付与先のセルが１列隣のC1セルにずれて出力されている。
この挙動は前述の静的にコメントを付与した場合の挙動と同じ。JXLSのバグなのか？
そもそもCellData#setCellCommentは引数がStringのみなので、コメント文字列しか指定できない模様。
コメントの表示サイズとか太字化とか要件出てきたら対応できないな・・ということで断念。
その２：自前でCommentを作って設定する案（採用案）
こうなったらCellDataUpdaterの中で自前でPOIのAPI使って自由自在にセルコメント付与してみる。
private static class CommentCellUpdater2 implements CellDataUpdater {
@Override
public void updateCellData(CellData cellData, CellRef targetCell, Context context) {
// Sheetオブジェクトを取得
PoiTransformer transformer = (PoiTransformer) cellData.getTransformer();
Workbook wb = transformer.getWorkbook();
Sheet sheet = wb.getSheet(targetCell.getSheetName());
// セルコメントを設定
SamplePoiUtils.setCellComment(sheet, targetCell.getRow(), targetCell.getCol(),
"Comment on cell by CommentCellUpdater2", true);
}
}
SamplePoiUtils.setCellCommentは、前述のものを流用。
Context context = new Context();
context.putVar("commentCellUpdater2", new CommentCellUpdater2());
テンプレートにjx:updateCellコマンドを設定。
今回はA1とB1のセルに設定。
実行すると・・
java.lang.NullPointerException:
Cannot invoke "org.apache.poi.ss.usermodel.Sheet.createDrawingPatriarch()" because "sheet" is null
java.lang.NullPointerException:
Cannot invoke "org.apache.poi.ss.usermodel.Row.getCell(int)" because "row" is null
java.lang.NullPointerException:
Cannot invoke "org.apache.poi.ss.usermodel.Cell.setCellComment(org.apache.poi.ss.usermodel.Comment)" because "cell" is null
sheet, row, cellそれぞれでヌルポ発生。
原因は以下。
- JXLSではシートに初めて何かを出力するときにSheetオブジェクトが生成される
- シート内の先頭セルへのコメント付与ケースではsheetがnullになる
- JXLSでは行に初めて何かを出力するときにRowオブジェクトが生成される
- 行の先頭セルへのコメント付与ケースではrowがnullになる
- JXLSではセルに初めて何かを出力するときにCellオブジェクトが生成される
- セルへのコメント付与ケースでは毎回cellがnullになる
ということで、sheet, row, cellそれぞれについて、null時の対応を入れる。
PoiTransformer transformer = (PoiTransformer) cellData.getTransformer();
Workbook wb = transformer.getWorkbook();
Sheet sheet = wb.getSheet(sheetName);
if (sheet == null) {
sheet = wb.createSheet(sheetName);
}
Row row = sheet.getRow(rowNo);
if (row == null) {
row = sheet.createRow(rowNo);
}
Cell cell = row.getCell(colNo, MissingCellPolicy.CREATE_NULL_AS_BLANK);
そしてようやく・・・
無事出力されました！
めでたしめでたし（？）
これが正しいやり方かは分かりません。
正規の方法や、もっと効率的な方法があるよっていう方は是非コメントください！