Go to Qiita Advent Calendar 2024 Top
LoginSignup
18
17

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@SE-studying-now(SE勉強中)

POIで大容量ファイルの出力でOutOfMemoryを回避したい

Last updated at Posted at 2017-12-18

POIを使用してエクセルファイルを出力する処理がある。
48列で最大10000行のデータを出力する仕様だが、10000行を出力しようとしたところ、OutOfMemoryエラーが発生した。
そんな場合の対応方法をメモ。
＃そもそもJavaでそんなに大量のデータをエクセルで扱うなというのは言わないで頂きたい。。

#問題の実装
問題発生したソースのイメージは以下の通り。

問題発生したソース
	File file = new File(tempPath);	//一時ファイルパス　事前にファイル作成済みこれをテンプレートとする
	XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(file);
	XSSFSheet sheet = workbook.getSheetAt(0);
	XSSFRow baseRow = sheet.getRow(sheet.getLastRowNum());	//基準行　各セルのスタイルをコピーする
	int rowCnt = 1;
	for (取得したデータ1件 : list) {	//listは事前に取得しているものとして、ソースは明記しない
		sheet.createRow(sheet.getLastRowNum() + 1);
		XSSFRow newRow = sheet.getRow(sheet.getLastRowNum());
		int cellCnt = 0;
		XSSFCell originCell = null;
		XSSFCell newCell = null;
		XSSFCellStyle style = workbook.createCellStyle();
		originCell = baseRow.getCell(cellCnt);
		newCell = newRow.createCell(cellCnt++);
		// セルのスタイルのコピー
		style.cloneStyleFrom(originCell.getCellStyle());
		newCell.setCellStyle(style);
		// セルタイプのコピー
		newCell.setCellType(originCell.getCellType());
		newCell.setCellValue(取得したデータ1件.値);
		// 14列分値設定を繰り返す
	}
	// 作成日を設定
	sheet.getRow(0).getCell(2).setCellValue(LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy/MM/dd")));
	// 基準行を削除
	sheet.shiftRows(baseRow.getRowNum() + 1, sheet.getLastRowNum(), -1);
	// 出力ストリームに書き出し、バイト配列を得る
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
	workbook.write(baos);
	excelData = baos.toByteArray();
	baos.close();
	workbook.close();
	// 一時ファイルを削除
	file.delete();
	//バイト配列をレスポンスに載せてダウンロードさせる

処理概要は以下の通り。

  • テンプレートファイルとして作成してあるファイルを読み込む
  • テンプレートの最下行（基準行）に設定されたスタイルをコピーして追加行に反映する。
  • 基準行は全行追加後に削除する。
  • 1行目には必ず作成した日付を設定する。

XSSFWorkbook.write()を呼び出したところでOutOfMemoryエラーが発生した。

#対策
対策方法をググったところ、XSSFWorkbookではなくSXSSFWorkbookを使うと処理が軽くなるとのこと。
##SXSSFWorkbook
以下のドキュメントを参照した。

https://poi.apache.org/spreadsheet/#SXSSF+%28Since+POI+3.8+beta3%29

XSSFの拡張であるとのこと。
XSSFは全ての行にアクセス可能でAPI機能も全て使用可能であるが全ての行情報をメモリに展開しているためメモリ占有量が増える、
一方、SXSSFはアクセス可能な行数を抑えることでメモリ占有量を抑えている、らしい。（認識違いがあればご指摘を）

修正を行う

修正1

SXSSFWorkbookを使った形で先のコードを置き換えてみる。

XSSFWorkbookをSXSSFWorkbookに置き換え
	File file = new File(tempPath);	//一時ファイルパス　事前にファイル作成済みこれをテンプレートとする
	SXSSFWorkbook workbook = (SXSSFWorkbook) WorkbookFactory.create(file);
	SXSSFSheet sheet = workbook.getSheetAt(0);
	SXSSFRow baseRow = sheet.getRow(sheet.getLastRowNum());	//基準行　各セルのスタイルをコピーする
	int rowCnt = 1;
	for (取得したデータ1件 : list) {	//listは事前に取得しているものとして、ソースは明記しない
		sheet.createRow(sheet.getLastRowNum() + 1);
		SXSSFRow newRow = sheet.getRow(sheet.getLastRowNum());
		int cellCnt = 0;
		SXSSFCell originCell = null;
		SXSSFCell newCell = null;
		CellStyle style = workbook.createCellStyle();
		originCell = baseRow.getCell(cellCnt);
		newCell = newRow.createCell(cellCnt++);
		// セルのスタイルのコピー
		style.cloneStyleFrom(originCell.getCellStyle());
		newCell.setCellStyle(style);
		// セルタイプのコピー
		newCell.setCellType(originCell.getCellType());
		newCell.setCellValue(取得したデータ1件.値);
		// 14列分値設定を繰り返す
	}
	// 作成日を設定
	sheet.getRow(0).getCell(2).setCellValue(LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy/MM/dd")));
	// 基準行を削除
	sheet.shiftRows(baseRow.getRowNum() + 1, sheet.getLastRowNum(), -1);
	// 出力ストリームに書き出し、バイト配列を得る
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
	workbook.write(baos);
	excelData = baos.toByteArray();
	baos.close();
	workbook.close();
	// 一時ファイルを削除
	file.delete();
	//バイト配列をレスポンスに載せてダウンロードさせる

これを実行すると、エラーとなる。。。

エラー内容
java.lang.ClassCastException: org.apache.poi.xssf.usermodel.XSSFWorkbook cannot be cast to org.apache.poi.xssf.streaming.SXSSFWorkbook

WorkbookFactory.create(file);はSXSSFWorkbookに対応していないとのこと。
調べてみると、SXSSFWorkbookのインスタンス生成時の引数としてXSSFWorkbookのインスタンスを指定可能とのこと。

修正2

SXSSFWorkbookのインスタンス生成
	File file = new File(tempPath);	//一時ファイルパス　事前にファイル作成済みこれをテンプレートとする
	XSSFWorkbook original = (XSSFWorkbook) WorkbookFactory.create(file);
	SXSSFWorkbook workbook = new SXSSFWorkbook(original);
	SXSSFSheet sheet = workbook.getSheetAt(0);
	SXSSFRow baseRow = sheet.getRow(sheet.getLastRowNum());	//基準行　各セルのスタイルをコピーする
	original.close();	//SXSSFWorkbookに読み込み済み(?)のためクローズする

インスタンス生成を上記に置き換えて再度実行、上記のSXSSFSheet.getLastRowNum()を実行した時点でヌルポとなる。

エラー内容２
java.lang.NullPointerException

XSSFWorkbookのインスタンス生成でテンプレートファイルを読み込んでいるため、テンプレートファイルの既存行の最終行にアクセス可能と想像していたが、エラーとなった。
そこで、既存行の最終行数「6」を指定して再度実行。
以下のエラーとなる。

エラー内容３
java.lang.IllegalArgumentException: Attempting to write a row[6] in the range [0,6] that is already written to disk.

どうやら、XSSFWorkbookで読み込んだファイルの既存行はSXSSFWorkbookのインスタンス生成時点でアクセス不可能となっているらしい。

修正3

ただ今回の実装仕様上、既存行へのアクセスを可能としたい。いろいろと試行錯誤した結果、以下の形で既存行へアクセス可能とわかった。
（正しい方法かは未確認だが、実行可能）

テンプレートファイルの既存行へアクセスする
	File file = new File(tempPath);	//一時ファイルパス　事前にファイル作成済みこれをテンプレートとする
	XSSFWorkbook original = (XSSFWorkbook) WorkbookFactory.create(file);
	XSSFSheet orgSheet = original.getSheetAt(0);
	XSSFRow baseRow = sheet.getRow(sheet.getLastRowNum());	//基準行　各セルのスタイルをコピーする
	SXSSFWorkbook workbook = new SXSSFWorkbook(original);
	SXSSFSheet sheet = workbook.getSheetAt(0);
	original.close();	//SXSSFWorkbookに読み込み済み(?)のためクローズする

上記実行で、SXSSFWorkbook.write()を呼び出したところでエラーとなった。

エラー内容４
java.io.IOException: Zip bomb detected! The file would exceed the max. ratio of compressed file size to the size of the expanded data. This may indicate that the file is used to inflate memory usage and thus could pose a security risk. You can adjust this limit via ZipSecureFile.setMinInflateRatio() if you need to work with files which exceed this limit. Counter: 820224, cis.counter: 8192, ratio: 0.009987515605493134Limits: MIN_INFLATE_RATIO: 0.01

調べてみたところ、ZipSecureFile.setMinInflateRatio()を呼び出すことで回避可能とのこと。

#完成版
ZipSecureFile.setMinInflateRatio()の呼び出しを追加した、最終的な完成版のソースを以下に示す。

完成版ソース
	File file = new File(tempPath);	//一時ファイルパス　事前にファイル作成済みこれをテンプレートとする
	XSSFWorkbook original = (XSSFWorkbook) WorkbookFactory.create(file);
	XSSFSheet orgSheet = original.getSheetAt(0);
	SXSSFWorkbook workbook = new SXSSFWorkbook(original);
	SXSSFSheet sheet = workbook.getSheetAt(0);
	Row baseRow = orgSheet.getRow(orgSheet.getLastRowNum());
	int rowCnt = 1;
	int rowNum = 6;
	boolean isFirst = true;
	for (取得したデータ1件 : list) {    //listは事前に取得しているものとして、ソースは明記しない
		SXSSFRow newRow = sheet.createRow(rowNum++);
		int cellCnt = 0;
		XSSFCell originCell = null;
		SXSSFCell newCell = null;
		CellStyle style = workbook.createCellStyle();
		if (isFirst) {    //最初の行のみ実行
			sheet.changeRowNum(newRow, 5);
			rowNum = 6;
			baseRow = newRow;
			isFirst = false;
		}
	}
	// 出力ストリームに書き出し、バイト配列を得る
	ByteArrayOutputStream baos = new ByteArrayOutputStream();
	ZipSecureFile.setMinInflateRatio(0.001);	// 圧縮率や１エントリーのサイズをチェックしているらしい...
	workbook.write(baos);
	excelData = baos.toByteArray();
	baos.close();
	workbook.close();
	original.close();
	// 一時ファイルを削除
	file.delete();
	//バイト配列をレスポンスに載せてダウンロードさせる

実行すると、OutOfMemoryエラーは発生しなくなった。

18
17
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
18
17

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?