Java POIでSXSSFWorkbookを使ってOutOfMemoryを回避する方法

2021.02.23 2021.02.23

JavaのPOIでSXSSFWorkbookを使ってOutOfMemoryを回避する方法です。

Java POIでSXSSFWorkbookを使ってOutOfMemoryを回避する方法

JavaのPOIを使ってExcelファイル出力をするのはよくある仕様ですね。今回は30列で15000行を出力しようとしたところ、11000行を出力した辺りでOutOfMemoryとなり、処理が停止しました。

そもそもJavaで大量データのExcel出力なんかするなって声が聞こえてきそうですが、まあシステム要求がそうならなんとかしてみたくなるのもエンジニアって生きモノなんでしょうね。

調べたところXSSFWorkbookではなくSXSSFWorkbookを使うと処理が軽くなり、メモリ使用量が抑えられるとのこと。

ここでは JavaのPOIでSXSSFWorkbookを使ってOutOfMemoryを回避する方法 を紹介します。

Sponsored Links

1 検証したPOIのバージョン
2 SXSSFWorkbookの注意点
3 SXSSFWorkbookでExcel出力する
4 大量データをExcel出力する
- 4.1 データ作成用クラスを作る
- 4.2 30列15000行を出力する
5 まとめ

検証したPOIのバージョン

POI 3.14
POI 4.12

POI 4系とはFontやStyleの設定方法が違いますが、今回のサンプルソースはどちらでも動きます。

Sponsored Links

SXSSFWorkbookの注意点

XSSFとSXSSFの大きな違いは、セルの読み込み(getRow)ができないってこと。テンプレートファイルを用意しておいて、そのセルに値を書き込むってのは通例だと思うのですが、これができない。

つまり、先に行に値や書式が入っている場合にはエラー(IllegalArgumentException)となってしまうんです。

SXSSFでは先に行に値や書式が入っている場合にはエラーになる

これは自由度がかなり低いと言わざるを得ないですね。

また、SXSSFWorkbookはメモリを使わない代わりにテンポラリーファイルを作成します。処理が終わったらテンポラリーファイルを削除するメソッド(dispose)を呼び出す必要があります。

SXSSFWorkbookでExcel出力する

SXSSFWorkbookではセルの読み込みができませんが、SXSSFはXSSFベースで構築されています。そこで一旦XSSFで読み込んで、それをベースとしてセルコピーしながらExcelファイルに出力していく方法をとります。

テンプレートファイルの読み込み

XSSFとSXSSFでファイルを読み込みます。XSSFWorkbookで読み込んだものをSXSSFWorkbookのコンストラクタに渡してやればテンプレートファイルを開けます。

XSSFWorkbook original = new XSSFWorkbook(new FileInputStream(テンプレートファイルパス)); // XSSF
XSSFSheet tmpSheet = original.getSheet("Temp"); // XSSF
Row baseRow = tmpSheet.getRow(tmpSheet.getLastRowNum()); // XSSF
SXSSFWorkbook workbook = new SXSSFWorkbook(original); // SXSSF
SXSSFSheet listSheet = workbook.getSheet("List"); // SXSSF

ここでは「Temp」というシートを設けて、そこにセル書式を設定しておきます。データを貼り付けるのは「List」というシートとしています。

Tempシートにセル書式を設定してListシートに貼り付ける

行を追加してセルをコピー

次に「List」シートに行を追加して、「Temp」シートの列分セルをコピーします。

// 行を追加
SXSSFRow newRow = listSheet.createRow(startRowNum++);
// 列分処理
for ( int i = 0; i < baseRow.getLastCellNum(); i++ ) {
  // ベースとなるセルを取得
  Cell originCell = baseRow.getCell(i);
  // 貼り付け先のセルを作成
  SXSSFCell newCell = newRow.createCell(i);
  //セルの値をコピー
  switch (originCell.getCellType()) {
  case XSSFCell.CELL_TYPE_BLANK:
    newCell.setCellValue(originCell.getStringCellValue());
    break;
  case XSSFCell.CELL_TYPE_BOOLEAN:
    newCell.setCellValue(originCell.getBooleanCellValue());
    break;
  case XSSFCell.CELL_TYPE_ERROR:
    newCell.setCellErrorValue(originCell.getErrorCellValue());
    break;
  case XSSFCell.CELL_TYPE_FORMULA:
    newCell.setCellFormula(originCell.getCellFormula());
    break;
  case XSSFCell.CELL_TYPE_NUMERIC:
    newCell.setCellValue(originCell.getNumericCellValue());
    break;
  case XSSFCell.CELL_TYPE_STRING:
    newCell.setCellValue(originCell.getRichStringCellValue());
    break;
  }
  // セルスタイルのコピー
  newCell.setCellStyle(originCell.getCellStyle());
}

ワークブックを保存する

最後にワークブックを保存します。保存したらクローズして、ワーク用のテンポラリーファイル削除しましょう。

// ワークブックを保存
workbook.write(new FileOutputStream(new File(保存ファイルパス)));
// クローズ
workbook.close();
original.close();
// ワーク用のテンポラリーファイル削除
workbook.dispose();

テストする

適当な件数をループさせてテストしてみると、、

SXSSFで適当な件数をループさせてテストする

おおおー、コピーされているー！！

Sponsored Links

大量データをExcel出力する

では、早速30列で15000行を出力してみようと思います。下図のように各シートを列30まで増やして準備します。

各シートを列30まで増やす

データ作成用クラスを作る

下記のようなクラスを作ってください。

@Setter
@Getter
public class ColItem implements Serializable {
  private String col1;
  private String col2;
  private String col3;
・・・省略・・・
  private String col29;
  private String col30;
}

Lombokを使ってgetter・setterを省略しています。

Lombokを使ってJavaBeansコードをシンプルに書く方法

2020-05-25

Lombokとは、JavaBeansコードを書く際に、getter・setterをコード上に直接書かかず、アノテーションを付与してgetter,setter,toString,equalsなどの繰り返しコードを自動生成してくれるプロダクト。コードが宣言的になり、可読性が高まるのが特徴。サンプルソースで紹介。

www.saka-en.com

30列15000行を出力する

今回のテストソース全文です。

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import dto.ColItem;

public class PoiSXSSFTest {

  @Test
  public void makeEcelFileForSXSSFTest() throws Exception {

    // データ作成
    List<ColItem> datas = new ArrayList<ColItem>();
    for (int i = 0; i < 15000; i++) {
      ColItem colItem = new ColItem();
      for (int j = 0; j < 30; j++) {
        try {
          PropertyDescriptor prop = new PropertyDescriptor("col" + String.valueOf(j + 1), colItem.getClass());
          Method setter = prop.getWriteMethod();
          setter.invoke(colItem, "ColData" + String.valueOf(j + 1));
        } catch (IntrospectionException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
          e.printStackTrace();
          break;
        }
      }
      datas.add(colItem);
    }

    // ファイルパス設定
    String dir = Thread.currentThread().getContextClassLoader().getResource("").getPath();
    System.out.println(dir);
    String templatePath = dir + "Book1.xlsx";
    String resultPath = dir + "result_Book1.xlsx";

    // テンプレートファイル取得
    XSSFWorkbook original = new XSSFWorkbook(new FileInputStream(templatePath)); // XSSF
    XSSFSheet tmpSheet = original.getSheet("Temp"); // XSSF
    Row baseRow = tmpSheet.getRow(tmpSheet.getLastRowNum()); // XSSF
    SXSSFWorkbook workbook = new SXSSFWorkbook(original); // SXSSF
    SXSSFSheet listSheet = workbook.getSheet("List"); // SXSSF

    int startRowNum = 1;
    // データ処理
    for ( ColItem data : datas ) {
      // 行を追加
      SXSSFRow newRow = listSheet.createRow(startRowNum++);
      // 列分処理
      for ( int i = 0; i < baseRow.getLastCellNum(); i++ ) {
        // ベースとなるセルを取得
        Cell originCell = baseRow.getCell(i);
        // 貼り付け先のセルを作成
        SXSSFCell newCell = newRow.createCell(i);
        //セルの値をコピー
        switch (originCell.getCellType()) {
        case XSSFCell.CELL_TYPE_BLANK:
          newCell.setCellValue(originCell.getStringCellValue());
          break;
        case XSSFCell.CELL_TYPE_BOOLEAN:
          newCell.setCellValue(originCell.getBooleanCellValue());
          break;
        case XSSFCell.CELL_TYPE_ERROR:
          newCell.setCellErrorValue(originCell.getErrorCellValue());
          break;
        case XSSFCell.CELL_TYPE_FORMULA:
          newCell.setCellFormula(originCell.getCellFormula());
          break;
        case XSSFCell.CELL_TYPE_NUMERIC:
          newCell.setCellValue(originCell.getNumericCellValue());
          break;
        case XSSFCell.CELL_TYPE_STRING:
          newCell.setCellValue(originCell.getRichStringCellValue());
          break;
        }
        // セルスタイルのコピー
        newCell.setCellStyle(originCell.getCellStyle());
      }
      // データ貼り付け
      int colNum = 0;
      newRow.getCell(colNum++).setCellValue(data.getCol1());
      newRow.getCell(colNum++).setCellValue(data.getCol2());
      newRow.getCell(colNum++).setCellValue(data.getCol3());
      newRow.getCell(colNum++).setCellValue(data.getCol4());
      newRow.getCell(colNum++).setCellValue(data.getCol5());
      newRow.getCell(colNum++).setCellValue(data.getCol6());
      newRow.getCell(colNum++).setCellValue(data.getCol7());
      newRow.getCell(colNum++).setCellValue(data.getCol8());
      newRow.getCell(colNum++).setCellValue(data.getCol9());
      newRow.getCell(colNum++).setCellValue(data.getCol10());
      newRow.getCell(colNum++).setCellValue(data.getCol11());
      newRow.getCell(colNum++).setCellValue(data.getCol12());
      newRow.getCell(colNum++).setCellValue(data.getCol13());
      newRow.getCell(colNum++).setCellValue(data.getCol14());
      newRow.getCell(colNum++).setCellValue(data.getCol15());
      newRow.getCell(colNum++).setCellValue(data.getCol16());
      newRow.getCell(colNum++).setCellValue(data.getCol17());
      newRow.getCell(colNum++).setCellValue(data.getCol18());
      newRow.getCell(colNum++).setCellValue(data.getCol19());
      newRow.getCell(colNum++).setCellValue(data.getCol20());
      newRow.getCell(colNum++).setCellValue(data.getCol21());
      newRow.getCell(colNum++).setCellValue(data.getCol22());
      newRow.getCell(colNum++).setCellValue(data.getCol23());
      newRow.getCell(colNum++).setCellValue(data.getCol24());
      newRow.getCell(colNum++).setCellValue(data.getCol25());
      newRow.getCell(colNum++).setCellValue(data.getCol26());
      newRow.getCell(colNum++).setCellValue(data.getCol27());
      newRow.getCell(colNum++).setCellValue(data.getCol28());
      newRow.getCell(colNum++).setCellValue(data.getCol29());
      newRow.getCell(colNum++).setCellValue(data.getCol30());
    }
    // ワークブックを保存
    workbook.write(new FileOutputStream(new File(resultPath)));
    // クローズ
    workbook.close();
    original.close();
    // ワーク用のテンポラリーファイル削除
    workbook.dispose();
  }
}

実行すると、、、

30列15000行が出力された