さらに, Excel 2007 のOpenXML についてもちょっとだけ書きました。
Apache POI の問題は, 容量の大きいファイルを扱うと, あっという間にOut of Memory になることです。
この解決策として JVM のメモリ容量を増やすことがありますが, Out of Memory になりにくくなるだけで, パフォーマンスは変わりません。
そこで, 大量にデータを書き込むために, Excel で読み込んでもらうための形式をOpen XML 形式を使って作ってしまうという方法があります。
前のブログで書いたように, Excel 2007 形式は, .zip です。
この zip を作ってしまおうという話です。
ですが, .xlsx をいちから作るのは, 面倒なので いくつか楽をします。
(大量データを書き込むための方法を書いていたWebページがあったのですが, URLを忘れてしまいました。ごめんなさい)
とあるWeb ページを参考にさせていただきましてこのような方針でプログラムを作りました。
まず, POI を使って, テンプレートを作成します。
1. Excel のオブジェクトを POI を使って作成。
2. 必要な分だけ Sheet をつくる
3. 必要な CellStyle を作って, Workbook に追加(セルのボーダーや, 色など)
4. 必要な文字列(日本語など) を最初のシートにすべて書き込む.
5. これを , template.xlsx に書き込みます。(この時点でデータは文字のみです)
これで, POIのファイルができました。 なぜこれをつくるかといいますと,
2. でシートをつくることで, シートのエントリ用のデータを Excel に書き込みます。
(これで, いくつシートを利用するかがわかる)
そして, CellStyle をWorkbook に入れることで, CellStyle のエントリをつくります。
書き込まれたCellStyle は, XMLファイル内の数値で管理されます。
この数値を使ってのちに利用します。
文字列もCellStyle と同様に, エントリを作ることで, XMLファイルの string.xml に
書き込まれます。書き込んだ順番に, 0 , 1, 2, 3 という番号で管理されます。
次に, シートを作ります。 シートは, sheet.xml, sheet2.xml と順にExcel のアーカイブ内に
保存してあります。ですので, これを自作します。
形式は OpenXML形式で, Web を探せば見つかるはずです。
この形式通りに sheet.xml を書けば, 各シートの完成です。
最後に, template.xlsx を バイト化してコピーをつくります。その上に対して, sheet.xml, sheet2.xml
をアーカイブのエントリに追加していきます。
(これで, template.xlsx の文字列を書き込んだシートは消えます)
最後にこのバイト列をどこか別のファイルに書き込んで任意のファイル名(.xlsx)で保存します。
ではそのサンプルです。(セルのマージのコードも追加しときました。)
--------------------------------------------------------------------------------------------------------
●本体 エクセルファイルをつくります。
package com.atmarkplant.util.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelExportWeb
{
private static final String XML_ENCODING = "UTF-8";
public static void main(String[] args)
{
XSSFWorkbook wb = new XSSFWorkbook();
// Create sheet.
List<XSSFSheet> sheets = new ArrayList<XSSFSheet>();
// Excel File name.
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMddHHmmss");
String file_name = sdf1.format(new Date());
// 1 ----------------------------------------------------------
// Add sheets you want.
for ( int i=0; i < 10; i++ )
{
XSSFSheet tmp_sheet = wb.createSheet(String.valueOf(i));
sheets.add(tmp_sheet);
}
// Create string table.
Map<Integer, String> string_map = new TreeMap<Integer, String>();
int index = 0;
string_map.put(index++, "桜の花びらたち");
string_map.put(index++, "スカートひらり");
string_map.put(index++,"会いたかった");
string_map.put(index++,"制服が邪魔をする");
string_map.put(index++,"BINGO");
string_map.put(index++,"僕の太陽");
string_map.put(index++,"夕陽をみているか");
string_map.put(index++,"ロマンス、イラネ");
string_map.put(index++,"Baby Baby Baby");
string_map.put(index++,"大声ダイヤモンド");
string_map.put(index++,"10年桜");
string_map.put(index++,"涙サプライズ");
string_map.put(index++,"言い訳Maybe");
string_map.put(index++,"桜の栞");
string_map.put(index++,"River");
string_map.put(index++,"ポニーテールとシュシュ");
string_map.put(index++,"ヘビーローテーション");
string_map.put(index++,"Beginner");
string_map.put(index++,"チャンスの順番");
string_map.put(index++,"桜の木になろう");
string_map.put(index++,"Everyday、カチューシャ");
createStringTable ( sheets.get( 0 ), string_map );
// Create cell style.
Map<String, XSSFCellStyle> styles = createStyles(wb);
try
{
// Save the template.
File template = new File("template.xlsx");
FileOutputStream os = new FileOutputStream(template);
wb.write(os);
//------------------------------------------------------------------
// 2----------------------------------------------------------------
List<File> files = new ArrayList<File>();
List<String> entries = new ArrayList<String>();
int counter = 0;
for (int i=0;i < 10; i++ )
{
String sheetRef = sheets.get(counter).getPackagePart().getPartName().getName();
File temp_xml = File.createTempFile( "sheet" + (counter + 1), ".xml");
counter++;
Writer fw = new OutputStreamWriter(new FileOutputStream(temp_xml), XML_ENCODING);
SpreadSheetWriter sw = new SpreadSheetWriter(fw);
sw.beginSheet();
sw.insertRow( 0 );
// Insert data.
for ( int j=0; j < 500; j++ )
{
sw.createCell( j, string_map.get( j % 20 ) );
}
sw.endRow();
sw.insertRow( 1 );
sw.createBlankCell( 0, styles.get("border").getIndex() );
sw.addMergeList( 2, 4, 2, 4 );
sw.endRow();
sw.endSheet();
entries.add(sheetRef.substring(1));
files.add(temp_xml);
fw.close();
}
FileOutputStream excel = new FileOutputStream(new File(file_name + ".xlsx"));
substitute(template, files, entries, excel);
excel.close();
}
catch ( Exception oops )
{
oops.printStackTrace();
}
}
public static void createStringTable ( XSSFSheet sheet, Map<Integer, String> string_map )
{
XSSFRow row1 = sheet.createRow(0);
int index = 0;
for ( String value : string_map.values() )
{
Cell cell = row1.createCell( index++ );
cell.setCellValue( value );
}
}
public static Map<String, XSSFCellStyle> createStyles ( XSSFWorkbook wb )
{
Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
// Alignment center.
XSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(XSSFCellStyle.ALIGN_CENTER);
styles.put("center", style1);
// Border
XSSFCellStyle style2 = wb.createCellStyle();
style2.setBorderBottom(CellStyle.BORDER_THIN);
style2.setBorderLeft(CellStyle.BORDER_THIN);
style2.setBorderRight(CellStyle.BORDER_THIN);
style2.setBorderTop(CellStyle.BORDER_THIN);
styles.put("border", style2);
// Border and align center.
XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style3.setBorderBottom(CellStyle.BORDER_THIN);
style3.setBorderLeft(CellStyle.BORDER_THIN);
style3.setBorderRight(CellStyle.BORDER_THIN);
style3.setBorderTop(CellStyle.BORDER_THIN);
styles.put("border-center", style3);
return styles;
}
public static void substitute ( File zipfile, List<File> files, List<String> entries, OutputStream out ) throws IOException
{
ZipFile zip = new ZipFile(zipfile);
ZipOutputStream zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
while (en.hasMoreElements())
{
boolean flag = true;
ZipEntry ze = en.nextElement();
for (String entry : entries)
{
if (ze.getName().equals(entry))
{
flag = false;
}
}
if (flag)
{
//System.out.println("Entry:" + ze.getName());
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
IOUtils.copy(is, zos);
is.close();
}
}
for (int i = 0; i < entries.size(); i++)
{
zos.putNextEntry(new ZipEntry(entries.get(i)));
InputStream is = new FileInputStream(files.get(i));
IOUtils.copy(is, zos);
is.close();
zos.closeEntry();
}
zos.close();
zip.close();
}
}
●sheet.xml 作成用
package com.atmarkplant.util.poi;
import java.io.IOException;
import java.io.Writer;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellReference;
public class SpreadSheetWriter
{
private static final String XML_ENCODING = "UTF-8";
private final Writer _out;
private int _rownum;
private List<String> _mergeList = new ArrayList<String>();
public SpreadSheetWriter ( Writer out )
{
_out = out;
}
public void beginSheet ( ) throws IOException
{
_out.write("<?xml version=\"1.0\" encoding=\"" + XML_ENCODING + "\"?>" + "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
_out.write("<sheetData>\n");
}
public void beginSheet2 ( ) throws IOException
{
_out.write("<worksheet>");
_out.write("<sheetData>\n");
}
public void endSheet ( ) throws IOException
{
_out.write("</sheetData>");
mergeCellInit();
_out.write("</worksheet>");
}
public String cellName( int rowIndex, int columnIndex )
{
return new CellReference(rowIndex, columnIndex).formatAsString();
}
private String mergeCell( int startRow, int endRow, int startColumn, int endColumn )
{
StringBuilder builder = new StringBuilder();
builder.append("<mergeCell ref=\"");
builder.append(cellName( startRow, startColumn ));
builder.append(":");
builder.append(cellName( endRow, endColumn ));
builder.append("\"/>");
return builder.toString();
}
public void addMergeList( int startRow, int endRow, int startColumn, int endColumn )
{
_mergeList.add(mergeCell( startRow, endRow, startColumn, endColumn ));
}
private String _toMergeList()
{
StringBuilder builder = new StringBuilder();
for ( String merge : _mergeList )
{
builder.append(merge);
}
return builder.toString();
}
public void mergeCellInit() throws IOException
{
_out.write("<mergeCells>");
_out.write(_toMergeList());
_out.write("</mergeCells>");
}
/**
* Insert a new row
*
* @param rownum
* 0-based row number
*/
public void insertRow ( int rownum ) throws IOException
{
_out.write("<row r=\"" + (rownum + 1) + "\">\n");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow ( ) throws IOException
{
_out.write("</row>\n");
}
public void createCell ( int columnIndex, String value, int styleIndex ) throws IOException
{
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
if (styleIndex != -1)
_out.write(" s=\"" + styleIndex + "\"");
_out.write(">");
_out.write("<is><t>" + value + "</t></is>");
_out.write("</c>");
}
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("<c r=\"" + ref + "\" t=\"n\"");
if (styleIndex != -1)
_out.write(" s=\"" + styleIndex + "\"");
_out.write(">");
_out.write("<v>" + value + "</v>");
_out.write("</c>");
}
public void createCell ( int columnIndex, int value, int styleIndex ) throws IOException
{
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\"" + ref + "\" t=\"n\"");
if (styleIndex != -1)
_out.write(" s=\"" + styleIndex + "\"");
_out.write(">");
_out.write("<v>" + value + "</v>");
_out.write("</c>");
}
public void createStrCell ( int columnIndex, String value, int styleIndex ) throws IOException
{
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\"" + ref + "\" t=\"str\"");
if (styleIndex != -1)
_out.write(" s=\"" + styleIndex + "\"");
_out.write(">");
_out.write("<v>" + value + "</v>");
_out.write("</c>");
}
public void createStrCell ( int columnIndex, String value) throws IOException
{
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\"" + ref + "\" t=\"str\"");
_out.write(">");
_out.write("<v>" + value + "</v>");
_out.write("</c>");
}
public void createSCell ( int columnIndex, int value, int styleIndex ) throws IOException
{
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\"" + ref + "\" t=\"s\"");
if (styleIndex != -1)
_out.write(" s=\"" + styleIndex + "\"");
_out.write(">");
_out.write("<v>" + value + "</v>");
_out.write("</c>");
}
public void createSCell ( int columnIndex, int value) throws IOException
{
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\"" + ref + "\" t=\"s\"");
_out.write(">");
_out.write("<v>" + value + "</v>");
_out.write("</c>");
}
public void createBlankCell ( int columnIndex, int styleIndex) throws IOException
{
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\"" + ref + "\"");
if (styleIndex != -1)
_out.write(" s=\"" + styleIndex + "\"");
_out.write(">");
_out.write("</c>");
}
public void createCell ( int columnIndex, int value ) throws IOException
{
createCell(columnIndex, value, -1);
}
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);
}
}
10枚のシートに横に向かって, データを書き込みました。
2行目に, ボーダーセルを入れて あと, セルのマージもしてみました。
プログラムの詳しい解説は省きます。
これにより, 大幅にパフォーマンスが改善されました。
Windows XP で, 600×50 のデータ9 シート分で,
POIのまともなプログラムだと 2分以上かかっていたものが, 2秒くらいになりました。
上のプログラムは, Windows 7 で実行しましたが, 速攻でおわりました。
パフォーマンス改善にも役にたつはずです。
OpenXML の詳しい形式は, 他で参照してください。
それでは。
