GC Upper Limit with Apache POI

Question

I have 13 .xlsx files with approximately 1000 lines in each of them. Now I want to merge it into a single .xlsx file with one sheet. I use the code here http://blog.sodhanalibrary.com/2014/11/merge-excel-files-using-java.html#.Vi9ns36rSUk .

Here is my code (a few changes, the addSheet method has not changed)

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 uploaded and merged, but after "write" sysout I get

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, , , Excel.

, SXSSFWorkbook XSSFWorkbook (. poi-documentation org.apache.poi.xssf.streaming -). , , .

Answer 2

, .

java -Xmx8192m

xlsx , .

for:

excellFile1.close();

, .

Answer 3

-

java.lang.OutOfMemoryError: GC - JVMs , . JVM , , 98% GC, , GC 2% .

, vm:

-XX:-UseGCOverheadLimit

. GC.

, . , .

-Xms128m -Xmx512m(these switches sets the initial heap memory size to 128mb and Max memory to 512mb)

Answer 4

, API , , .

, : XSSFReader.SheetIterator .

, , API: XSSFSheetXMLHandler. .

: https://github.com/jeevatkm/excelReader/blob/master/src/main/java/com/myjeeva/poi/ExcelReader.java

, , : SheetContentsHandler....

SAX, .

 private void readSheet(StylesTable styles, ReadOnlySharedStringsTable sharedStringsTable,
      InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {

    SAXParserFactory saxFactory = SAXParserFactory.newInstance();
    XMLReader sheetParser = saxFactory.newSAXParser().getXMLReader();

    ContentHandler handler =
        new XSSFSheetXMLHandler(styles, sharedStringsTable, sheetContentsHandler, true);

    sheetParser.setContentHandler(handler);
    sheetParser.parse(new InputSource(sheetInputStream));
  }