POIで大量データをxlsxファイルに書き込む際のパフォーマンス問題
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 { // 使用例1:物理ファイルをtemplateにする場合 File template = new File("input/template.xlsx"); WorkbookWrapper wr = new WorkbookWrapper(template); // 使用例2:メモリ上の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()); } }