java.lang.OutOfMemoryError : GC overhead limit exceeded excel reader 错误

Question

当我尝试运行下面的程序时，出现 java.lang.OutOfMemoryError: GC overhead limit exceeded 异常。该程序的主要方法访问指定目录并遍历所有包含 .xlsx 的文件。这工作正常，因为我在任何其他逻辑之前对其进行了测试。它调用 xlsx 的方法基本上将 xlsx 文件转换为 csv 并将其附加到现有文件中，也可以正常工作。但是当我把它放在 for 循环中时，这就是我得到这个异常的时候。我猜它在打开 xlsx 并将其转换为 csv 和打开第二个的时间后会发生冲突，也许我必须以某种方式关闭此行:

File inputFile = new File("C:\\Users\\edennis.AD\\Desktop\\test\\"+nameOfFile);

这是我现在唯一的猜测，当循环的第二次迭代到来时，这个文件正在干扰。我正在使用 Apache POI 库来操作 excel 文件。提前致谢!

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelMan {

    public static void main(String[] args) throws FileNotFoundException {



        int i =0;


            File dir = new File("C:\\Users\\edennis.AD\\Desktop\\test\\");
            for (File child : dir.listFiles()) {

            //initializing whether the sheet sent to method is first or not, and //counting iterations for each time the for loop as run

            boolean firstSheet = true;  
            i++;

           String nameOfFile = child.getName();

           if (nameOfFile.contains(".xlsx")){   

            System.out.println(nameOfFile);

                if (i != 0)
                firstSheet = false;


                File inputFile = new File("C:\\Users\\edennis.AD\\Desktop\\test\\"+nameOfFile);

                //  writing excel data to csv 
              File outputFile = new File("C:\\Users\\edennis.AD\\Desktop\\test\\memb.csv");
              xlsx(inputFile, outputFile, firstSheet);


            }


          //  }

        }


    }




        static void xlsx(File inputFile, File outputFile, boolean firstSheet) {
            // For storing data into CSV files
            StringBuffer data = new StringBuffer();


            try {
                FileOutputStream fos = new FileOutputStream(outputFile, true);
                // Get the workbook object for XLSX file
                XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
                // Get first sheet from the workbook


                XSSFSheet sheet = wBook.getSheetAt(7);
                Row row;
                Cell cell;
                // Iterate through each rows from first sheet
                java.util.Iterator<Row> rowIterator = sheet.iterator();

                while (rowIterator.hasNext()) {

                    if (firstSheet != true)
                        rowIterator.next();

                    row = rowIterator.next();

                    // For each row, iterate through each columns
                    java.util.Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {


                        cell = cellIterator.next();

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BOOLEAN:
                                data.append(cell.getBooleanCellValue() + "^");

                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                data.append(cell.getNumericCellValue() + "^");

                                break;
                            case Cell.CELL_TYPE_STRING:
                                data.append(cell.getStringCellValue() + "^");
                                break;                            
                            case Cell.CELL_TYPE_BLANK:
                                data.append("" + "^");
                                break;
                            default:
                                data.append(cell + "^");

                        }


                    }
                    data.append("\r\n");

                }

                fos.write(data.toString().getBytes());
                fos.close();


            } catch (Exception ioe) {
                ioe.printStackTrace();
            }
        }



}

附加信息:

下面是堆栈跟踪

   MR.xlsx
    Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
        at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3039)
        at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3060)
        at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3250)
        at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
        at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1802)
        at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
        at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
        at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
        at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
        at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4808)
        at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
        at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
        at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
        at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3439)
        at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1270)
        at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1257)
        at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
        at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
        at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:138)
        at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:130)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:286)
        at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:159)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:207)
        at ExcelMan.xlsx(ExcelMan.java:71)
        at ExcelMan.main(ExcelMan.java:47)

excel 文件很大，目录里大约有 30 个左右，最大的大约有 170 MB，这样的文件大小我应该从 POI 更改吗？

Answer 1

您的 excel 文件有多大？我曾经遇到过类似的问题，从 xls 中创建 csv。就我而言，我不得不切换到 event driven model ，看看 XSSF 和 SAX(事件 API)。我也用完了内存(-Xmx8g)

来自链接网站的引述:

Further effort on HSSF is going to focus on the following major areas:

• Performance: POI currently uses a lot of memory for large sheets.