Apache POIを使って、ちょっと大きなExcelを作る

JavaでExcelの読み書きをする時には、Apache POIを使うことが多いと思います。まあ、POIの上にテンプレートを組んで…みたいなものもあるようですが。

Apache POI - the Java API for Microsoft Documents
http://poi.apache.org/

POI自体は、Excel以外も扱えますね。

で、最近ちょっと「大きなExcelをプログラムで出力したいんだけど」みたいなことを言われ、ちょっとPOIの事情を調べてみました。

POIはExcel出力可能ですが、かなりメモリを多く取るので大きなExcelを出力しようとするとちょっとつらいです。

ちなみに、ここで自分が言う「大きなExcel」というのは、数十万行のオーダーでしたね。そんなの、Excelで出力してどうするんだよ、とは思いますが。

調べてみたところ、POI 3.8 beta3以降であれば、SXSSF APIというものを使用することで低メモリでExcel生成が可能になっているようです。

POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files
http://poi.apache.org/spreadsheet/index.html

SXSSF (Streaming Usermodel API)
http://poi.apache.org/spreadsheet/how-to.html#sxssf

もちろん、トレードオフはあるようで、「
POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files」のページの表にもありますが、

• Sheet／Row／Cellの削除はできない
• Rowのシフトはできない
• Sheetのクローン不可
• 数式の使用は不可
• Cellへのコメントの使用は不可

というのがあります。あと、一時ファイルが作成されます。

あ、まあ話としてはExcel 2007以降のフォーマット（xlsx）ですからね。

使ってみる

では、早速使ってみましょう。Maven依存関係には、以下を加えます。

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.10.1</version>
    </dependency>

現在の最新安定版は、3.10.1です。
依存関係としてはartifactIdとして「poi」と「poi-ooxml-schemas」が必要ですが、上記を加えるとこれらも自動的に追加されます。

せっかくなので、XSSF APIとSXSSF APIを切り替えるサンプルで書いてみます。
src/main/java/ExcelGen.java

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.stream.IntStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelGen {
    public static void main(String[] args) {
        String type = args[0];
        int rows = Integer.parseInt(args[1]);
        String fileName = args[2];

        Workbook workbook;
        switch (type.toUpperCase()) {
        case "XSSF":
            workbook = new XSSFWorkbook();
            break;
        case "SXSSF":
            workbook = new SXSSFWorkbook();
            break;
        default:
            workbook = new XSSFWorkbook();
            break;
        }

        Sheet sheet = workbook.createSheet();

        IntStream
            .range(0, rows)
            .forEach(i -> {
                Row row = sheet.createRow(i);

                row.createCell(0).setCellValue("テスト-" + (i + 1));
                row.createCell(1).setCellValue("foo-" + (i + 1));
                row.createCell(2).setCellValue("bar-" + (i + 1));
            });

        try (FileOutputStream fos = new FileOutputStream(fileName)) {
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

起動引数で、XSSF APIかSXSSF APIかを選択するようにして、それ以降のコードはすべて同じです。

        case "XSSF":
            workbook = new XSSFWorkbook();
            break;
        case "SXSSF":
            workbook = new SXSSFWorkbook();
            break;

第2引数は出力するExcelの行数、第3引数は出力するファイル名です。

実行は、面倒なので「java -jar」で実行できるように、maven-shade-pluginを使用しました。

      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-shade-plugin</artifactId>
        <version>2.3</version>
        <configuration>
          <transformers>
            <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
              <mainClass>ExcelGen</mainClass>
            </transformer>
          </transformers>
        </configuration>
        <executions>
          <execution>
            <phase>package</phase>
            <goals>
              <goal>shade</goal>
            </goals>
          </execution>
        </executions>
      </plugin>

パッケージングして

$ mvn package

こんな感じで実行。

$ java -jar target/large-exel-0.0.1-SNAPSHOT.jar [フォーマット] [行数] [出力ファイル名]

まず、XSSF APIを使ってみます。メモリは256Mまでにして、10万行のExcelを作ってみましょう。
＊timeもつけてみました

$ time java -Xmx256M -jar target/large-exel-0.0.1-SNAPSHOT.jar XSSF 100000 xssf.xlsx

結果、そこそこ時間がかかった挙句、OutOfMemoryErrorで落ちます。

$ time java -Xmx256M -jar target/large-exel-0.0.1-SNAPSHOT.jar XSSF 100000 xssf.xlsx
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
	at org.apache.xmlbeans.impl.store.Saver$TextSaver.resize(Saver.java:1700)
	at org.apache.xmlbeans.impl.store.Saver$TextSaver.preEmit(Saver.java:1303)
	at org.apache.xmlbeans.impl.store.Saver$TextSaver.emit(Saver.java:1190)
	at org.apache.xmlbeans.impl.store.Saver$TextSaver.emitElement(Saver.java:962)
	at org.apache.xmlbeans.impl.store.Saver.processElement(Saver.java:476)
	at org.apache.xmlbeans.impl.store.Saver.process(Saver.java:307)
	at org.apache.xmlbeans.impl.store.Saver$TextSaver.saveToString(Saver.java:1864)
	at org.apache.xmlbeans.impl.store.Cursor._xmlText(Cursor.java:546)
	at org.apache.xmlbeans.impl.store.Cursor.xmlText(Cursor.java:2436)
	at org.apache.xmlbeans.impl.values.XmlObjectBase.xmlText(XmlObjectBase.java:1500)
	at org.apache.poi.xssf.model.SharedStringsTable.getKey(SharedStringsTable.java:134)
	at org.apache.poi.xssf.model.SharedStringsTable.addEntry(SharedStringsTable.java:180)
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:350)
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:320)
	at ExcelGen.lambda$main$0(ExcelGen.java:37)
	at ExcelGen$$Lambda$1/868737467.accept(Unknown Source)
	at java.util.stream.Streams$RangeIntSpliterator.forEachRemaining(Streams.java:110)
	at java.util.stream.IntPipeline$Head.forEach(IntPipeline.java:557)
	at ExcelGen.main(ExcelGen.java:34)

real	1m48.413s
user	2m48.404s
sys	0m3.948s

では、続いてSXSSF APIで実行。

$ time java -Xmx256M -jar target/large-exel-0.0.1-SNAPSHOT.jar SXSSF 100000 sxssf.xlsx

real	0m4.883s
user	0m5.312s
sys	0m1.276s

XSSF APIに比べて、めちゃくちゃ速いですね！

ちゃんとExcelファイルもできています。

$ ls -lh sxssf.xlsx
-rw-rw-r-- 1 xxxxx xxxxx 1.9M 10月 11 18:12 sxssf.xlsx

開いて確認することもできます。当然、重いですが…。

ところで、この時Linuxなら「/tmp」配下（用はJavaの一時ディレクトリ配下）に巨大なファイルが残ります。
※消し方は、後述

$ ls -lh /tmp/poi-sxssf-sheet7480297749844044258.xml 
-rw-rw-r-- 1 xxxxx xxxxx 21M 10月 11 18:12 /tmp/poi-sxssf-sheet7480297749844044258.xml

実に、出来上がったExcelファイルの10倍以上のサイズのファイルです。
ちなみにこれ、シートの中間ファイルです。

どうなってるんだろうと思って、ちょっとソースを覗いてみました。

まず、SXSSF APIって、XSSF APIの上に成り立ってるんですね。
https://github.com/apache/poi/blob/REL_3_10_1/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFWorkbook.java#L63

そもそも、SXSSFWorkbookクラスのインスタンスを作成する際に、コンストラクタ引数にXSSFWorkbookのインスタンスを渡すことができますし。

で、SheetであるSXSSSheetは、SheetDataWriterというWriterを持っています。
https://github.com/apache/poi/blob/REL_3_10_1/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java#L59

このSheetDataWriterが、この中間ファイルを書き出しているようです。
https://github.com/apache/poi/blob/REL_3_10_1/src/ooxml/java/org/apache/poi/xssf/streaming/SheetDataWriter.java#L65

フラッシュの制御や、中間ファイルの圧縮設定はSXSSFWorkbookで設定できるみたいですね。

で、SXSSFWorkbook#write(OutputStream)を呼び出した際に、先ほどのシートの中間ファイルから全体のファイルを一時ファイルとして作成し、そこからwriteメソッドの引数に与えたOutputStreamに最終形式のデータを書き込んでいくという形になっているようです。
https://github.com/apache/poi/blob/REL_3_10_1/src/ooxml/java/org/apache/poi/xssf/streaming/SheetDataWriter.java#L65

全体の一時ファイルは、削除されます。

シートの中間ファイルがいつ消えるのかというと、finalize依存だったり…。
https://github.com/apache/poi/blob/REL_3_10_1/src/ooxml/java/org/apache/poi/xssf/streaming/SheetDataWriter.java#L116

追記）
シートの中間ファイルは、Workbookをclose（POIのバージョンによってはdispose）すれば削除されます。

まあ、機能的に制限はあるところとかの注意点に気を付けていれば、使えそうですかね。

そもそも、そんなでかいExcelを作らなければいいのに…。