[java] Writing a large resultset to an Excel file using POI

This is sort of inline w/ Writing a large ResultSet to a File but the file in question is an Excel file.

I'm using the Apache POI library to write an Excel file with a large data set retrieved from a ResultSet object. The data could range from a few thousand records to about 1 million; not sure how this translates into file system bytes in Excel format.

The following is a test code I wrote to check out the time taken to write such a large result set and also the performance implication w.r.t CPU & Memory.

protected void writeResultsetToExcelFile(ResultSet rs, int numSheets, String fileNameAndPath) throws Exception {

    BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(fileNameAndPath));
    int numColumns = rs.getMetaData().getColumnCount();

    Workbook wb = ExcelFileUtil.createExcelWorkBook(true, numSheets);
    Row heading = wb.getSheetAt(0).createRow(1);

    ResultSetMetaData rsmd = rs.getMetaData();

    for(int x = 0; x < numColumns; x++) {
        Cell cell = heading.createCell(x+1);
        cell.setCellValue(rsmd.getColumnLabel(x+1));
    }

    int rowNumber = 2;
    int sheetNumber = 0;

    while(rs.next()) {

        if(rowNumber == 65001) {
            log("Sheet " + sheetNumber + "written; moving onto to sheet " + (sheetNumber + 1));
            sheetNumber++;
            rowNumber = 2;
        }

        Row row = wb.getSheetAt(sheetNumber).createRow(rowNumber);
        for(int y = 0; y < numColumns; y++) {
            row.createCell(y+1).setCellValue(rs.getString(y+1));
            wb.write(bos);
        }

        rowNumber++;
    }

    //wb.write(bos);

    bos.close();
}

Not much luck with the above code. The file which is created seems to grow rapidly (~70Mb per sec). So I stopped the execution after about 10 minutes (killed the JVM when the file reaches 7Gb) and tried to open the file in Excel 2007. The moment I open it, the file size becomes 8k(!) and only the header and the first row are created. Not sure what I'm missing here.

Any ideas?

For now I took @Gian's advice & limited the number of records per Workbook to 500k and rolled over the rest to the next Workbook. Seems to be working decent. For the above configuration, it took me about 10 mins per workbook.

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

Useful link

I updated BigGridDemo to support multiple sheets.

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;
}
}

You can using SXSSFWorkbook implementation of Workbook, if you use style in your excel ,You can caching style by Flyweight Pattern to improve your performance.

You can increase the performance of excel export by following these steps:

1) When you fetch data from database, avoid casting the result set to the list of entity classes. Instead assign it directly to List

List<Object[]> resultList =session.createSQLQuery("SELECT t1.employee_name, t1.employee_id ... from t_employee t1 ").list();

instead of

List<Employee> employeeList =session.createSQLQuery("SELECT t1.employee_name, t1.employee_id ... from t_employee t1 ").list();

2) Create excel workbook object using SXSSFWorkbook instead of XSSFWorkbook and create new row using SXSSFRow when the data is not empty.

3) Use java.util.Iterator to iterate the data list.

Iterator itr = resultList.iterator();

4) Write data into excel using column++.

int rowCount = 0;
int column = 0;
while(itr.hasNext()){
 SXSSFRow row = xssfSheet.createRow(rowCount++);

 Object[] object = (Object[]) itr.next();
 //column 1     
 row.setCellValue(object[column++]); // write logic to create cell with required style in setCellValue method
 //column 2
 row.setCellValue(object[column++]);
 itr.remove();
}

5) While iterating the list, write the data into excel sheet and remove the row from list using remove method. This is to avoid holding unwanted data from the list and clear the java heap size.

itr.remove();

Unless you have to write formulas or formatting you should consider writing out a .csv file. Infinitely simpler, infinitely faster, and Excel will do the conversion to .xls or .xlsx automatically and correctly by definition.