Using SXSSF poi 3.8
package example;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class SXSSFexample {
public static void main(String[] args) throws Throwable {
FileInputStream inputStream = new FileInputStream("mytemplate.xlsx");
XSSFWorkbook wb_template = new XSSFWorkbook(inputStream);
inputStream.close();
SXSSFWorkbook wb = new SXSSFWorkbook(wb_template);
wb.setCompressTempFiles(true);
SXSSFSheet sh = (SXSSFSheet) wb.getSheetAt(0);
sh.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk
for(int rownum = 4; rownum < 100000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
FileOutputStream out = new FileOutputStream("tempsxssf.xlsx");
wb.write(out);
out.close();
}
}
It requires: poi-ooxml-3.8.jar, poi-3.8.jar, poi-ooxml-schemas-3.8.jar, stax-api-1.0.1.jar, xml-apis-1.0.b2.jar, xmlbeans-2.3.0.jar, commons-codec-1.5.jar, dom4j-1.6.1.jar
http://stackoverflow.com/questions/7274076/writing-a-large-resultset-to-an-excel-file-using-poi
BigExcelWriterImpl.java
package com.gdais.common.apache.poi.bigexcelwriter; import static com.google.common.base.Preconditions.*; 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.util.Enumeration; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.Map; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; import javax.annotation.Nonnull; import javax.annotation.Nullable; import org.apache.commons.io.FilenameUtils; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.google.common.base.Function; import com.google.common.collect.ImmutableList; import com.google.common.collect.Iterables; public class BigExcelWriterImpl implements BigExcelWriter { private static final String XML_ENCODING = "UTF-8"; @Nonnull private final File outputFile; @Nullable private final File tempFileOutputDir; @Nullable private File templateFile = null; @Nullable private XSSFWorkbook workbook = null; @Nonnull private LinkedHashMap<String, XSSFSheet> addedSheets = new LinkedHashMap<String, XSSFSheet>(); @Nonnull private Map<XSSFSheet, File> sheetTempFiles = new HashMap<XSSFSheet, File>(); BigExcelWriterImpl(@Nonnull File outputFile) { this.outputFile = outputFile; this.tempFileOutputDir = outputFile.getParentFile(); } @Override public BigExcelWriter createWorkbook() { workbook = new XSSFWorkbook(); return this; } @Override public BigExcelWriter addSheets(String... sheetNames) { checkState(workbook != null, "workbook must be created before adding sheets"); for (String sheetName : sheetNames) { XSSFSheet sheet = workbook.createSheet(sheetName); addedSheets.put(sheetName, sheet); } return this; } @Override public BigExcelWriter writeWorkbookTemplate() throws IOException { checkState(workbook != null, "workbook must be created before writing template"); checkState(templateFile == null, "template file already written"); templateFile = File.createTempFile(FilenameUtils.removeExtension(outputFile.getName()) + "-template", ".xlsx", tempFileOutputDir); System.out.println(templateFile); FileOutputStream os = new FileOutputStream(templateFile); workbook.write(os); os.close(); return this; } @Override public SpreadsheetWriter createSpreadsheetWriter(String sheetName) throws IOException { if (!addedSheets.containsKey(sheetName)) { addSheets(sheetName); } return createSpreadsheetWriter(addedSheets.get(sheetName)); } @Override public SpreadsheetWriter createSpreadsheetWriter(XSSFSheet sheet) throws IOException { checkState(!sheetTempFiles.containsKey(sheet), "writer already created for this sheet"); File tempSheetFile = File.createTempFile( FilenameUtils.removeExtension(outputFile.getName()) + "-sheet" + sheet.getSheetName(), ".xml", tempFileOutputDir); Writer out = null; try { out = new OutputStreamWriter(new FileOutputStream(tempSheetFile), XML_ENCODING); SpreadsheetWriter sw = new SpreadsheetWriterImpl(out); sheetTempFiles.put(sheet, tempSheetFile); return sw; } catch (RuntimeException e) { if (out != null) { out.close(); } throw e; } } private static Function<XSSFSheet, String> getSheetName = new Function<XSSFSheet, String>() { @Override public String apply(XSSFSheet sheet) { return sheet.getPackagePart().getPartName().getName().substring(1); } }; @Override public File completeWorkbook() throws IOException { FileOutputStream out = null; try { out = new FileOutputStream(outputFile); ZipOutputStream zos = new ZipOutputStream(out); Iterable<String> sheetEntries = Iterables.transform(sheetTempFiles.keySet(), getSheetName); System.out.println("Sheet Entries: " + sheetEntries); copyTemplateMinusEntries(templateFile, zos, sheetEntries); for (Map.Entry<XSSFSheet, File> entry : sheetTempFiles.entrySet()) { XSSFSheet sheet = entry.getKey(); substituteSheet(entry.getValue(), getSheetName.apply(sheet), zos); } zos.close(); out.close(); return outputFile; } finally { if (out != null) { out.close(); } } } private static void copyTemplateMinusEntries(File templateFile, ZipOutputStream zos, Iterable<String> entries) throws IOException { ZipFile templateZip = new ZipFile(templateFile); @SuppressWarnings("unchecked") Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) templateZip.entries(); while (en.hasMoreElements()) { ZipEntry ze = en.nextElement(); if (!Iterables.contains(entries, ze.getName())) { System.out.println("Adding template entry: " + ze.getName()); zos.putNextEntry(new ZipEntry(ze.getName())); InputStream is = templateZip.getInputStream(ze); copyStream(is, zos); is.close(); } } } private static void substituteSheet(File tmpfile, String entry, ZipOutputStream zos) throws IOException { System.out.println("Adding sheet entry: " + entry); zos.putNextEntry(new ZipEntry(entry)); InputStream is = new FileInputStream(tmpfile); copyStream(is, zos); is.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); } } @Override public Workbook getWorkbook() { return workbook; } @Override public ImmutableList<XSSFSheet> getSheets() { return ImmutableList.copyOf(addedSheets.values()); } }
SpreadsheetWriterImpl.java
package com.gdais.common.apache.poi.bigexcelwriter; import java.io.IOException; import java.io.Writer; import java.util.Calendar; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.util.CellReference; class SpreadsheetWriterImpl implements SpreadsheetWriter { private static final String XML_ENCODING = "UTF-8"; private final Writer _out; private int _rownum; SpreadsheetWriterImpl(Writer out) { _out = out; } @Override public SpreadsheetWriter closeFile() throws IOException { _out.close(); return this; } @Override public SpreadsheetWriter 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"); return this; } @Override public SpreadsheetWriter endSheet() throws IOException { _out.write("</sheetData>"); _out.write("</worksheet>"); closeFile(); return this; } /** * Insert a new row * * @param rownum * 0-based row number */ @Override public SpreadsheetWriter insertRow(int rownum) throws IOException { _out.write("<row r=\"" + (rownum + 1) + "\">\n"); this._rownum = rownum; return this; } /** * Insert row end marker */ @Override public SpreadsheetWriter endRow() throws IOException { _out.write("</row>\n"); return this; } @Override public SpreadsheetWriter 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>"); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, String value) throws IOException { createCell(columnIndex, value, -1); return this; } @Override public SpreadsheetWriter 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>"); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, double value) throws IOException { createCell(columnIndex, value, -1); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, Calendar value, int styleIndex) throws IOException { createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex); return this; } @Override public SpreadsheetWriter createCell(int columnIndex, Calendar value) throws IOException { createCell(columnIndex, value, -1); return this; } }