Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
554 views
in Technique[技术] by (71.8m points)

java - GC overhead limit exceeded with Apache POI

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...