CODEWITHSUNDEEP
javagarbage-collectionapache-poi
Mohamed Ennahdi El Idrissi
Mohamed Ennahdi El Idrissi

Reputation: 2977

POI's Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded

Using XSSFWorkbook, I keep getting Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded, when trying to generate an XLSX file with more than 2.5 million rows.

Besides, I included JVM arguments -Xms1024M and -Xmx6144 with no success.

while (rs.next()) {
    row = spreadsheet.createRow(rowID);
    for (int column = 0; column < numberOfColumns; column++) {
        value = (String) rs.getString(column + 1);
        cell = row.createCell(column);
        cell.setCellStyle(style);
        spreadsheet.setColumnWidth(column, COLUMN_WIDTH);
        formatedValue = Engine.formatInput(colNames.get(column),value);
        if (formatedValue instanceof Calendar) {
            cell.setCellStyle(dateCellStyle);
            cell.setCellValue((Calendar) formatedValue);
        } else {
            if (formatedValue instanceof Double) {
                cell.setCellValue((Double) formatedValue);
            } else {
                if (formatedValue instanceof Integer) {
                    cell.setCellValue((Integer) formatedValue);
                } else {
                    if (formatedValue instanceof String) {
                        cell.setCellValue((String) formatedValue);
                    } else {
                        /*
                         * Unreachable.
                         */
                        cell.setCellValue(value);
                    }
                }
            }
        }
    }
    rowID++;
    if (this.isMaxSpreadsheetRowsReached(rowID)) {
        newSpreadSheet(rsmd, numberOfColumns, styleEntete);
        rowID = 1;
    }
}

The exception occurs within the inner-for-loop.

One workaround was to use SXSSFWorkbook, but, still, I want to work with XSSFWorkbook.

Is there a solution for this?

Upvotes: 1

Views: 534

Answers (1)

TreffnonX
TreffnonX

Reputation: 2930

You have answered your own question. Your source seems to be streamed (rs.next()), while the generated Workbook is stored in Memory. There are a lot of internal object references within the spreadsheet object, which apparrently leads to a complex garbage collector overhead.

To avoid this, use the streaming verison of XSSFWorkbook (SXSSFWorkbook).

But to return to the reason why you probably ask this question/don't want to stream it: You probably want to keep all registers in memory, so you can update header information (print a total number of registers, an average, or thelike). I am afraight that this kind of operation can't scale indefinetly, and you can either achieve this by running through your source twice (first accumulate all the secondary information, like averages, maxima or minima, then print the content), or by printing all this info on a secondary page, which you can then reference within the first page with an "="-formula, so it looks like the information is on top of the page.

Upvotes: 1

Related Questions