GC overhead limit exceeded with Apache POI

Question

I have 13 .xlsx files with about 1000 rows in each of them. Now I want to merge it to one .xlsx file with one sheet. I'm using code from here https://blog.sodhanalibrary.com/2014/11/merge-excel-files-using-java.html#.Vi9ns36rSUk.

Here's my code (few changes, addSheet method unchanged)

try {
        FileInputStream excellFile1 = new FileInputStream(new File("tmp_testOut1000.xlsx"));
        XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
        XSSFSheet sheet1 = workbook1.getSheetAt(0);

        for(int i = 2; i < 14; i++){
            FileInputStream excellFile2 = new FileInputStream(new File("tmp_testOut" + i + "000.xlsx"));
            XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);
            XSSFSheet sheet2 = workbook2.getSheetAt(0);
            System.out.println("add " + i);
            addSheet(sheet1, sheet2);
        }
        
        excellFile1.close();

        // save merged file
        System.out.println("merging");
        File mergedFile = new File("merged.xlsx");
        if (!mergedFile.exists()) {
            mergedFile.createNewFile();
        }
        FileOutputStream out = new FileOutputStream(mergedFile);
        System.out.println("write");
        workbook1.write(out);
        out.close();
        System.out.println("Files were merged succussfully");
    } catch (Exception e) {
        e.printStackTrace();
    }

All files are loading and merging but after "write" sysout I'm getting

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
at org.apache.xmlbeans.impl.store.Xobj.new_cursor(Xobj.java:1829)
at org.apache.xmlbeans.impl.values.XmlObjectBase.newCursor(XmlObjectBase.java:293)
at org.apache.xmlbeans.impl.values.XmlComplexContentImpl.arraySetterHelper(XmlComplexContentImpl.java:1151)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTFontsImpl.setFontArray(Unknown Source)
at org.apache.poi.xssf.model.StylesTable.writeTo(StylesTable.java:424)
at org.apache.poi.xssf.model.StylesTable.commit(StylesTable.java:496)
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:341)
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:345)
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:206)
at Start.main(Start.java:275)

What can I do? Why is this happening and how to prevent it?

Answer 1

POI is notoriously memory-hungry, so running out of memory is not uncommon when handling large Excel-files.

When you are able to load all original files and only get trouble writing the merged file you could try using an SXSSFWorkbook instead of an XSSFWorkbook and do regular flushes after adding a certain amount of content (see poi-documentation of the org.apache.poi.xssf.streaming-package). This way you will not have to keep the whole generated file in memory but only small portions.

Answer 2

Try allocating more memory eg.

java -Xmx8192m

Also what you can try is to merge in one xlsx file at a time instead of loading them all at once.

You can also move this line into your for loop:

excellFile1.close();

So you close it right away.