Writing a large resultset to an Excel file using POI

Question

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  amp  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  lt  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  lt  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

User · Answer

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

User · Answer

Oh   I think you re writing the workbook out 944 000 times   Your wb write bos  call is in the inner loop   I m not sure this is quite consistent with the semantics of the Workbook class    From what I can tell in the Javadocs of that class  that method writes out the entire workbook to the output stream specified   And it s gonna write out every row you ve added so far once for every row as the thing grows   This explains why you re seeing exactly 1 row  too   The first workbook  with one row  to be written out to the file is all that is being displayed - and then 7GB of junk thereafter

User · Answer

For now I took  Gian s advice  amp  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

User · Answer

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

User · Answer

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 lt Object   gt  resultList  session createSQLQuery  SELECT t1 employee name  t1 employee id     from t employee t1    list      instead of  List lt Employee gt  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

User · Answer

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

User · Answer

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  lt  100000  rownum             Row row   sh createRow rownum           for int cellnum   0  cellnum  lt  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

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

Examples related to java

Examples related to jdbc

Examples related to apache-poi