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.