POIで大量データをxlsxファイルに書き込む際のパフォーマンス問題

java

https://github.com/icchw/XLSX/


Javaプログラムで、POIを使って数万件のデータをxlsxファイルに書き込もうとすると、非現実的なメモリー消費量になって書き込めない。

この問題に対応するために、POIを介さずにDOMを使ってXMLを書き込んでいくライブラリを作成してみた（DBから読み込んだデータなどを行単位で書き込んでいくような用途を想定）。

ただし、xlsxファイルを一から作るのは煩雑なため、事前にExcelで作成した物理ファイル、または、POIで作成したWorkbookをテンプレートとして使用する。


xlsxファイルの拡張子をzipに変更して展開すると、xl/worksheetsフォルダ内に、シートごとにデータを保持したXMLファイルがある（ex: sheet1.xml）。

このXMLファイルのsheetData要素内にデータを書き込んでいき、元のXMLファイルを置き換える実装としている。

ただしテンプレートファイル上で事前にヘッダ行を書き込んでおくケースも想定されるため、全列が空または式の行を最初の空行と判断して、当該行から書き込みを開始する。

またテンプレートファイル上で、最初の空行に設定した書式・列全体に設定した書式・最初の空行に挿入したExcel式を、各行の同一列にコピーしている。


なおExcelで作成したxlsxファイルでは、文字列は独立したsharedStrings.xmlというファイルに保持される。ライブラリ作成時は実装の簡便化のため、sharedStringsは使用せず、各セルの要素にinlineStrとして直接文字列を書き込んでいる。


使用方法は以下の通り。Stringは文字列、Number, BigDecimal, Date, Calendarは数値、その他はtoString()した結果が文字列として書き込まれる。
①書き込むデータのDTOクラスを作成
②XlsxWritable interfaceをimplements
③getMapメソッドを実装、列番号（0始まり）をkey、書き込む値をvalueとするmapを返す
④書き込み対象のシートのシート名・当該DTOのListを引数として、writeSheetメソッドを呼び出す


参考： Apache POI(Java) でExcel その2(パフォーマンス編)

使用例

https://github.com/icchw/XLSX/releases/ からダウンロードできるzip内に、jarファイルと合わせてSample一式を置いている。


テンプレートファイル


DataDto.java

import icchw.xlsx.XlsxWritable;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;


public class DataDto implements XlsxWritable {

	public DataDto(String str, double d, BigDecimal bigDecimal, Date date, Calendar cal, String prefectureCode) {
		super();
		this.str = str;
		this.d = d;
		this.bigDecimal = bigDecimal;
		this.date = date;
		this.cal = cal;
		this.prefectureCode = prefectureCode;
	}

	private String str;
	private double d;
	private BigDecimal bigDecimal;
	private Date date;
	private Calendar cal;
	private String prefectureCode;

	public Map<Integer, Object> getMap() {
		Map<Integer, Object> map = new HashMap<Integer, Object>();
		map.put(0, str);
		map.put(1, d);
		map.put(2, bigDecimal);
		map.put(4, date);
		map.put(5, cal);
		map.put(6, prefectureCode);
		return map;
	}

}


Main.java

import icchw.xlsx.WorkbookWrapper;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class Main {

	public static void main(String[] args) {

		String sheetName = "sheet1";

		try {
			// 使用例１：物理ファイルをtemplateにする場合
			File template = new File("input/template.xlsx");
			WorkbookWrapper wr = new WorkbookWrapper(template);

			// 使用例２：メモリ上のWorkbookをtemplateにする場合
//			XSSFWorkbook wb = new XSSFWorkbook();
//			wb.createSheet(sheetName);
//			WorkbookWrapper wr = new WorkbookWrapper(wb);

			// Write xmls
			List<DataDto> dataDtos = prepareData();
			System.out.println("start");
			long startTime = (new Date()).getTime();
			wr.writeSheet(sheetName, dataDtos);
			long endTime = (new Date()).getTime();
			System.out.println("end:" + ((long)(endTime - startTime)/1000) + "s");

			// Generate zip
			FileOutputStream output = new FileOutputStream(new File("output/" + generateFileName() + ".xlsx"));
			wr.write(output);
			output.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * テストデータ作成
	 * @return
	 */
	private static List<DataDto> prepareData() {
		List<DataDto> dataDtos = new ArrayList<DataDto>();
		for (int i=0; i<50000; i++) {
			dataDtos.add(new DataDto("こんにちは", (double)i/100, new BigDecimal("-0.5"), new Date(), Calendar.getInstance(), String.format("%1$02d", i%47+1)));
			dataDtos.add(new DataDto("", (double)i/100, new BigDecimal("0.5"), null, Calendar.getInstance(), String.format("%1$02d", i%47+1)));
		}
		return dataDtos;
	}

	/**
	 * テスト用ファイル名生成
	 * @return
	 */
	private static String generateFileName() {
		SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd_HHmmss");
		return sdf1.format(new Date());
	}

}


実行結果