Apache POI 3.7 OutOfMemoryError: Java heap space when writing to large no of rows to xlsx files

Question

I need to write a result set of more than 65000 rows in xlsx file. So, I am trying to use Apache POI 3.7. I get an OutOfMemoryError: Java heap space. How do I solve this problem besides increasing JVM memory which doesn't seem to solve the problem.

Simple sample code:

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");

    // Create a row and put some cells in it. Rows are 0 based.
    for (int i=0;i<65000;i++){
        Row row = sheet.createRow((int) i);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);
    cell.setCellValue(1);

    // Or do it on one line.
    row.createCell(1).setCellValue(1.2);
    row.createCell(2).setCellValue(
            createHelper.createRichTextString("This is a string"));
    row.createCell(3).setCellValue(true);
    }
   

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("test1.xls");
    fileOut.flush();
    wb.write(fileOut);
    fileOut.close();
}

Answer 1

You might want to check out what the POI project did recently with the SXSSF API. There is also a section in the How-To.
For the example code you provided this would be the perfect fit as you just create data and then forget about it after creation. With the SXSSF extension you can start writing the data to the file immediately. This keeps the memory footprint low.

Please note that this feature is not present in Apache POI 3.7 but was added in Apache POI 3.8 beta 3.
Final version 3.8 is currently being voted on. So if you have to use a final version it should not be too long before that is released.

Answer 2

I am having the same issue, this not a fix but it works, create different excel files and then merge them manually, i know this is not a good solution but since it was urgent for me had no choice.

You can do this in two different ways:

1.- Create a new file once yoi have reached the 3000 rows:

XSSFWorkbook wb = new XSSFWorkbook();
fileOut = new FileOutputStream("file1.xlsx");
fileOut = new FileOutputStream("file2.xlsx");...

you can add this into a for statement to create them dinamically, once you have reached the first 3000 records save the file and continue writting on the next one.

2.- Delimit the result set or source records to get less than 3000 records, and run again the entire program but with a different query range each time:

between recNum = 1 and recNum <=100

the next run will be:

between recNum > 100 and recNum <= 200

This is just an example, hope this can be useful to you.