大量のデータに基づくExcelファイル生成スキーム
詳細
大量のデータに基づくExcel生成スキーム
大量のデータに基づくExcel生成スキーム
従来,POIに基づいてExcelファイルを生成する際には,公式に提供されているHSSFやXSSF対応の一連のAPIを利用しており,それらは操作が簡便で,使い勝手が比較的速い.しかし、大きなデータ量のExcelファイルの生成には時間がかかることが多く、これは標準的なAPIを利用して開発された痛点です.パフォーマンスの高いAPIについて、POIはSXSSFシリーズのAPIを使用することを公式に提案します.HSSFやXSSFよりもパフォーマンスが大幅に向上しますが、大量のデータに直面すると遅くなります.そのため、公式にはXMLベースのスキームも提供されています.
実は1つのExcelファイルにとって、最も核心的なのはそのデータです.Excelファイルのデータとスタイルファイルは別々に保存されており、それらはすべて独自のシステムのXMLファイルに対応しています.興味のある方は、Excelファイルの接尾辞名を「.zip
」に変更し、圧縮ファイルで解凍することができます.その中の構造はXMLファイルの山からなっていることがわかります.解凍されたファイルを圧縮ファイルに圧縮し、その接尾辞名をExcelファイルに対応する接尾辞名「.xlsx
」または「.xls
」に変更し、Excelプログラムで開きます.この時、それも開くことができることに気づきます.筆者が本稿で述べる大量のデータに基づいてExcelを生成するシナリオは,既存のExcelファイル(このExcelファイルは実行時に生成できる)に依存し,我々のデータを対応するXML表示に生成し,我々のXMLを元のXMLファイルに置き換え,パッケージ化してExcelファイルにするシナリオである.このような方式に基づいて、筆者はテストを行い、3500万行、5列のExcelファイルを生成した.このファイルのサイズは1 GBで、412秒かかる.この効率は従来のAPIを適用するよりも指数倍である.
実装の詳細については、Apache公式からダウンロードしたサンプルコードを参照してください.元のアドレスはhttps://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java.生成されたXMLに適用するスタイルは事前に生成する必要があります.関数を適用したり、セルをマージしたりするロジックが必要な場合は、まずExcelファイルを持って対応する関数やマージロジックを適用し、それを解凍して中のXMLファイルの展示形式を見て、自分でつづるときも対応する形式につづることができ、自分で生成したExcelファイルにも対応する効果があります.public class BigDataTest {
private static final String XML_ENCODING = "UTF-8";
public static void main(String[] args) throws Exception {
long start = System.currentTimeMillis();
// Step 1. Create a template file. Setup sheets and workbook-level objects such as
// cell styles, number formats, etc.
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Big Grid");
Map<String, XSSFCellStyle> styles = createStyles(wb);
//name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
String sheetRef = sheet.getPackagePart().getPartName().getName();
//save the template
FileOutputStream os = new FileOutputStream("template.xlsx");
wb.write(os);
os.close();
//Step 2. Generate XML file.
File tmp = File.createTempFile("sheet", ".xml");
Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);
generate(fw, styles);
fw.close();
//Step 3. Substitute the template entry with the generated data
FileOutputStream out = new FileOutputStream("D:/big-grid2.xlsx");
// XML Excel XML , Excel 。
substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
out.close();
wb.close();
long end = System.currentTimeMillis();
System.out.println(" : " + (end - start));
}
/**
* Create a library of cell styles.
*/
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
XSSFDataFormat fmt = wb.createDataFormat();
XSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(HorizontalAlignment.RIGHT);
style1.setDataFormat(fmt.getFormat("0.0%"));
styles.put("percent", style1);
XSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setDataFormat(fmt.getFormat("0.0X"));
styles.put("coeff", style2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HorizontalAlignment.RIGHT);
style3.setDataFormat(fmt.getFormat("$#,##0.00"));
styles.put("currency", style3);
XSSFCellStyle style4 = wb.createCellStyle();
style4.setAlignment(HorizontalAlignment.RIGHT);
style4.setDataFormat(fmt.getFormat("mmm dd"));
styles.put("date", style4);
XSSFCellStyle style5 = wb.createCellStyle();
XSSFFont headerFont = wb.createFont();
headerFont.setBold(true);
style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style5.setFont(headerFont);
styles.put("header", style5);
return styles;
}
private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {
Random rnd = new Random();
Calendar calendar = Calendar.getInstance();
SpreadsheetWriter sw = new SpreadsheetWriter(out);
sw.beginSheet();
//insert header row
sw.insertRow(0);
int styleIndex = styles.get("header").getIndex();
sw.createCell(0, "Title", styleIndex);
sw.createCell(1, "% Change", styleIndex);
sw.createCell(2, "Ratio", styleIndex);
sw.createCell(3, "Expenses", styleIndex);
sw.createCell(4, "Date", styleIndex);
sw.endRow();
//write data rows
for (int rownum = 1; rownum < 100; rownum++) {
sw.insertRow(rownum);
sw.createCell(0, "Hello, " + rownum + "!");
sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
sw.createCell(4, calendar, styles.get("date").getIndex());
sw.endRow();
calendar.roll(Calendar.DAY_OF_YEAR, 1);
}
sw.endSheet();
}
/**
*
* @param zipfile the template file
* @param tmpfile the XML file with the sheet data
* @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
* @param out the stream to write the result to
*/
private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
ZipFile zip = ZipHelper.openZipFile(zipfile);
try {
ZipOutputStream zos = new ZipOutputStream(out);
Enumeration extends ZipEntry> en = zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if(!ze.getName().equals(entry)){
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
}
}
zos.putNextEntry(new ZipEntry(entry));
InputStream is = new FileInputStream(tmpfile);
copyStream(is, zos);
is.close();
zos.close();
} finally {
zip.close();
}
}
private static void copyStream(InputStream in, OutputStream out) throws IOException {
byte[] chunk = new byte[1024];
int count;
while ((count = in.read(chunk)) >=0 ) {
out.write(chunk,0,count);
}
}
/**
* Writes spreadsheet data in a Writer.
* (YK: in future it may evolve in a full-featured API for streaming data in Excel)
*/
public static class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
public SpreadsheetWriter(Writer out){
_out = out;
}
public void beginSheet() throws IOException {
_out.write("\"1.0\" encoding=\""+XML_ENCODING+"\"?>" +
"\" http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
_out.write("
" );
}
public void endSheet() throws IOException {
_out.write("");
_out.write("");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
public void insertRow(int rownum) throws IOException {
_out.write("\"
"+(rownum+1)+"\">
");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow() throws IOException {
_out.write("
");
}
public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("\" "+ref+"\" t=\"inlineStr\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("" +value+"");
_out.write("");
}
public void createCell(int columnIndex, String value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("\" "+ref+"\" t=\"n\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("" +value+"");
_out.write("");
}
public void createCell(int columnIndex, double value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
}
}
}
(注:本文はElimが2017年6月5日に書いた)
public class BigDataTest {
private static final String XML_ENCODING = "UTF-8";
public static void main(String[] args) throws Exception {
long start = System.currentTimeMillis();
// Step 1. Create a template file. Setup sheets and workbook-level objects such as
// cell styles, number formats, etc.
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Big Grid");
Map<String, XSSFCellStyle> styles = createStyles(wb);
//name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
String sheetRef = sheet.getPackagePart().getPartName().getName();
//save the template
FileOutputStream os = new FileOutputStream("template.xlsx");
wb.write(os);
os.close();
//Step 2. Generate XML file.
File tmp = File.createTempFile("sheet", ".xml");
Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);
generate(fw, styles);
fw.close();
//Step 3. Substitute the template entry with the generated data
FileOutputStream out = new FileOutputStream("D:/big-grid2.xlsx");
// XML Excel XML , Excel 。
substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
out.close();
wb.close();
long end = System.currentTimeMillis();
System.out.println(" : " + (end - start));
}
/**
* Create a library of cell styles.
*/
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
XSSFDataFormat fmt = wb.createDataFormat();
XSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(HorizontalAlignment.RIGHT);
style1.setDataFormat(fmt.getFormat("0.0%"));
styles.put("percent", style1);
XSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setDataFormat(fmt.getFormat("0.0X"));
styles.put("coeff", style2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HorizontalAlignment.RIGHT);
style3.setDataFormat(fmt.getFormat("$#,##0.00"));
styles.put("currency", style3);
XSSFCellStyle style4 = wb.createCellStyle();
style4.setAlignment(HorizontalAlignment.RIGHT);
style4.setDataFormat(fmt.getFormat("mmm dd"));
styles.put("date", style4);
XSSFCellStyle style5 = wb.createCellStyle();
XSSFFont headerFont = wb.createFont();
headerFont.setBold(true);
style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style5.setFont(headerFont);
styles.put("header", style5);
return styles;
}
private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {
Random rnd = new Random();
Calendar calendar = Calendar.getInstance();
SpreadsheetWriter sw = new SpreadsheetWriter(out);
sw.beginSheet();
//insert header row
sw.insertRow(0);
int styleIndex = styles.get("header").getIndex();
sw.createCell(0, "Title", styleIndex);
sw.createCell(1, "% Change", styleIndex);
sw.createCell(2, "Ratio", styleIndex);
sw.createCell(3, "Expenses", styleIndex);
sw.createCell(4, "Date", styleIndex);
sw.endRow();
//write data rows
for (int rownum = 1; rownum < 100; rownum++) {
sw.insertRow(rownum);
sw.createCell(0, "Hello, " + rownum + "!");
sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
sw.createCell(4, calendar, styles.get("date").getIndex());
sw.endRow();
calendar.roll(Calendar.DAY_OF_YEAR, 1);
}
sw.endSheet();
}
/**
*
* @param zipfile the template file
* @param tmpfile the XML file with the sheet data
* @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
* @param out the stream to write the result to
*/
private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
ZipFile zip = ZipHelper.openZipFile(zipfile);
try {
ZipOutputStream zos = new ZipOutputStream(out);
Enumeration extends ZipEntry> en = zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if(!ze.getName().equals(entry)){
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
}
}
zos.putNextEntry(new ZipEntry(entry));
InputStream is = new FileInputStream(tmpfile);
copyStream(is, zos);
is.close();
zos.close();
} finally {
zip.close();
}
}
private static void copyStream(InputStream in, OutputStream out) throws IOException {
byte[] chunk = new byte[1024];
int count;
while ((count = in.read(chunk)) >=0 ) {
out.write(chunk,0,count);
}
}
/**
* Writes spreadsheet data in a Writer.
* (YK: in future it may evolve in a full-featured API for streaming data in Excel)
*/
public static class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
public SpreadsheetWriter(Writer out){
_out = out;
}
public void beginSheet() throws IOException {
_out.write("\"1.0\" encoding=\""+XML_ENCODING+"\"?>" +
"\" http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
_out.write("
" );
}
public void endSheet() throws IOException {
_out.write("");
_out.write("");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
public void insertRow(int rownum) throws IOException {
_out.write("\"
"+(rownum+1)+"\">
");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow() throws IOException {
_out.write("
");
}
public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("\" "+ref+"\" t=\"inlineStr\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("" +value+"");
_out.write("");
}
public void createCell(int columnIndex, String value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("\" "+ref+"\" t=\"n\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("" +value+"");
_out.write("");
}
public void createCell(int columnIndex, double value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
}
}
}