Apache POI超出了GC开销限制 [英] GC overhead limit exceeded with Apache POI

问题描述

我有13个.xlsx文件，每个文件大约有1000行。现在我想将它合并到一个.xlsx文件一张。我正在使用这里的代码
http://blog.sodhanalibrary.com/2014/11/merge-excel-files-using-java.html#.Vi9ns36rSUk 。

这里是我的代码（几个更改，addSheet方法不变）

  try {
 FileInputStream excellFile1 = new FileInputStream（new File tmp_testOut1000.xlsx））; 
 XSSFWorkbook工作簿1 =新的XSSFWorkbook（excellFile1）; 
 XSSFSheet sheet1 = workbook1.getSheetAt（0）; （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（）; 
 
 //保存合并文件
 System.out.println（merging）; 
文件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（文件合并成功）; 
} catch（Exception e）{
 e.printStackTrace（）; 
} 
  

所有文件正在加载和合并，但在写入sysout后，我得到

 线程main中的异常java.lang.OutOfMemoryError：GC开销限制超过
在org.apache.xmlbeans .impl.store.Xobj.new_cursor（Xobj.java:1829）
在org.apache.xmlbeans.impl.values.XmlObjectBase.newCursor（XmlObjectBase.java:293）
在org.apache.xmlbeans .impl.values.XmlComplexContentImpl.arraySetterHelper（XmlComplexContentImpl.java:1151）
在org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTFontsImpl.setFontArray（未知源）
在org.apache。 poi.xssf.model.StylesTable.writeTo（StylesTable.java:424）
在org.apache.poi.xssf.model.StylesTable.commit（StylesTable.java:496）
在org.apache。 poi.POIXMLDocumentPart.onSave（POIXMLDocumentPart.java:341）
在org.apache.poi.POIXMLDocumentPart.onSave（POIXMLDocumentPart.java:345）
在org.apache.poi.POIXMLDocument.write（POIXMLDocument。 java：206）
在Start.main（ Start.java:275）
  

我该怎么办？为什么会发生这种情况呢？如何防止这种情况？

解决方案

POI是臭名昭着的记忆饥饿，所以内存不足并不罕见当处理大型Excel文件时。

当您能够加载所有原始文件，只有在编写合并文件时遇到问题，您可以尝试使用 SXSSFWorkbook 而不是 XSSFWorkbook ，并在添加一定量的内容后进行定期刷新（请参阅 org的poi文档.apache.poi.xssf.streaming -package）。这样，您不必将整个生成的文件保存在内存中，而只能保留小部分。

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 http://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?

解决方案

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.

这篇关于Apache POI超出了GC开销限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！