こんにちは、技術研究所(通称:技研)のブルマンです。

 

私は社内のJava開発プロジェクトで何か問題があると相談を受けること多いのですが、最近あった「ApachePOIで表計算処理が終わらない」件について書きたいと思います。

問題の始まり

あるシステム開発で担当者から困っていることがあると相談を受けました。

このシステムはJavaで開発、レポートをExcelで出力する設計となっていました。
でJavaでExcelを使うとなると、やはり Apache POI  を使うことになります。メモリを異常に消費するなど評判が芳しくない点もありますが、なんのかんの頼りになるヤツです。
使用している「Apache POI」のバージョンは「3.13」で最新。

設計としては、
1.レポートのデータ専用シートにPOIでデータを書き込む
2・ユーザが見るシートにはCELLに表計算処理を埋め込んであり、上記のデータ専用シートの値を参照、再計算して表示する
となっていましたが、そこの表計算処理が動かないとの事。

表計算処理が動かない

実はPOIで書き込むとCELLに設定された表計算処理は自動では動きません。

その場合の対応方法は2つあります。

1.サーバ側でPOIに再計算させる
workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

2.クライアント側でExcelを開いた際に再計算させるよう設定する
sheet.setForceFormulaRecalculation(true);

今回はレポートをダウンロードして、そのままブラウザのビューアで見る場合があるとのことで「1.サーバ側でPOIに再計算させる」の方式を担当者に伝えました。メデタシメデタシと思っていたのですが、今度は表計算処理が終わらないとの連絡が。

表計算処理が終わらない

最初はアベンドしていると聞いていたのですが、色々調べているとヒープを大量に使用してGCが走りまくっている事が判明。ヒープサイズをチューニングしたら処理が終わったのですが、ヒープを100MBぐらい使って処理も1分掛かっています。

このレポート作成処理は都度行わなければならず、システムを載せるサーバのスペックもよくありません。レポート作成処理にこんなにヒープ・時間を食うと非常にまずい、、、

原因究明

うーむ、困ってしまいました。
そもそも、なんでこんなにヒープを使用してしまうんだろ?

POIはファイルのデータが何万行のように大量だとヒープも大量に使用するのですが、もらったファイルは数シートで数十行ぐらいしかデータがなく、あきらかに小さいファイルです。

困ってCELL関数の定義を眺めていると、、、

「=IF(ISERROR(LOOKUP($F14,データシート!$C:$C,CSVデータ!F:F)),””,(LOOKUP($F14,データシート!$C:$C,CSVデータ!F:F)))」

と定義してあります。
…「LOOKUP」「$C:$C」?、あれ?
「$C:$C」って1行目から最大行までの意味となるので、それを「LOOKUP」?
ファイルはxlsx形式ですので、最大行数1,048,576行分のデータを読み込んでしまっている?

今回のデータは256行で十分だったので関数定義を以下のように変更。

「=IF(ISERROR(LOOKUP($F14,データシート!$C1:$C256,CSVデータ!F1:F256)),””,(LOOKUP($F14,データシート!$C1:$C256,CSVデータ!F1:F256)))」

そしてPOIの表計算処理を走らせてみると1秒も立たずに帰ってきました。
お、これで問題解決できそうです!
上記内容を担当者に連絡後、無事問題が解決したと感謝されました。

まとめ

関数式はPOIによってエミュレートされて実行されますが、
その際に読み込んだデータはCellオブジェクトとして一時的にキャッシュされます。

今回の場合は設定されている関数は29列ほど読み込んでおり
29列×1,048,576行 = 30,408,704
およそ3000万ほどのCellオブジェクトがキャッシュされ100MB使用、1分ほど処理がかかっていたと推測されます。

なので256行というように明示的に指定すれば、1/4096のサイズとなるので推定25KB程度の使用量となり問題なくなりました。

というわけで

「Apache POIを利用する場合はCELL関数の定義に気をつけて!」

最後まで読んで頂きありがとうございました。