java - 在线程 "main"java.lang.OutOfMemoryError : Java heap space 中出现异常

Question

如果我在一个文件夹中放入超过 5 个 excel 文件，我会得到这个

java.lang.OutOfMemoryError: Java heap space.

我总共有 40 个 Excel 文件，每个文件可能包含 5 到 10 张。我的代码将找出 Excel 文件中的错误并将其记录在 Error.txt 文件中。

public class Hvd {

    public static int getExcelColumnNumber(String column) {
        int result = 0;
        for (int i = 0; i < column.length(); i++) {
            result *= 26;
            result += column.charAt(i) - 'A' + 1;
        }
        return result;
    }

    public static String getExcelColumnName(int number) {
        final StringBuilder sb = new StringBuilder();

        int num = number - 1;
        while (num >=  0) {
            int numChar = (num % 26)  + 65;
            sb.append((char)numChar);
            num = (num  / 26) - 1;
        }
        return sb.reverse().toString();
    }


    public static void main(String[] args) {
        try {

            //File directory = new File("C://Users//zkuwscr//Documents//new practice tests//Regression_Dev_Dec12th");
            File directory = new File("C://Users//kondeti.venkatarao//Documents//Regresion_sheets");
            File[] files = directory.listFiles();

            //File errorBW = new File("C://Users//zkuwscr//Documents//new practice tests//Regression_Dev_Dec12th//ErrorBW.txt");
            File errors = new File("C://Users//kondeti.venkatarao//Documents//Regresion_sheets//Error.txt");
            FileOutputStream errorsFileOutputStream = new FileOutputStream(errors);
            BufferedWriter errorsBufferedWriter = new BufferedWriter(new OutputStreamWriter(errorsFileOutputStream));

            File mismatch = new File("C://Users//kondeti.venkatarao//Documents//Regresion_sheets//Mismatch.txt");
            FileOutputStream mismatchFileOutputStream = new FileOutputStream(mismatch);
            BufferedWriter mismatchBufferedWriter = new BufferedWriter(new OutputStreamWriter(mismatchFileOutputStream));


            for (File file : files) {
                if (file.getName().endsWith(".xlsx")) {
                    //FileInputStream fis = new FileInputStream(file);
                    // Create Workbook instance holding reference to .xlsx file

                    OPCPackage pkg = OPCPackage.open(file);
                    XSSFWorkbook workbook = new XSSFWorkbook(pkg);
                    int i = 1;
                    while (i < workbook.getNumberOfSheets()) {

                        // System.out.println(workbook.getNumberOfSheets());
                        // Get first/desired sheet from the workbook
                        XSSFSheet sheet = workbook.getSheetAt(i);

                        // Iterate through each rows one by one
                        Iterator<Row> rowIterator = sheet.iterator();
                        while (rowIterator.hasNext()) {
                            Row row = rowIterator.next();
                            // For each row, iterate through all the columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            while (cellIterator.hasNext()) {
                                Cell cell = cellIterator.next();
                                // Check the cell type and format accordingly
                                switch (cell.getCellType()) {
                                /*
                                 * case Cell.CELL_TYPE_NUMERIC:
                                 * System.out.print(cell.getNumericCellValue());
                                 * break; case Cell.CELL_TYPE_STRING:
                                 * System.out.print(cell.getStringCellValue());
                                 * break;
                                 */
                                // case Cell.CELL_TYPE_FORMULA:
                                case Cell.CELL_TYPE_FORMULA:

                                    if (cell.getCellFormula().startsWith("IF("))
                                        if (sheet.getRow(row.getRowNum())
                                                .getCell(cell.getColumnIndex())
                                                .getRawValue().equals("1")) {

                                            HashSet<Integer> number= new HashSet<Integer>();
                                            ArrayList<String> alphas=new ArrayList<String>();

                                            String formula=sheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).toString();
                                            Matcher digitMatcher = Pattern.compile("\\d+").matcher(formula);
                                            Matcher alphabetMatcher = Pattern.compile("[a-zA-Z]+").matcher(formula);

                                            while(alphabetMatcher.find()) {
                                                if(!alphabetMatcher.group().equals("TYPE"))
                                                    alphas.add(alphabetMatcher.group());
                                            }

                                            int countIF = Collections.frequency(alphas, "IF");
                                            int countABS = Collections.frequency(alphas, "ABS");
                                            HashSet<String> alphaSet=new HashSet<String>(alphas);
                                            if(countIF!=5 && countIF!=6)
                                                alphaSet.remove("IF");
                                            if(countABS != 3 && countABS!=4)
                                                alphaSet.remove("ABS");

                                            while(digitMatcher.find()) {
                                              if(!digitMatcher.group().equals("0") && !digitMatcher.group().equals("1") && !digitMatcher.group().equals("01"))
                                              number.add(Integer.parseInt(digitMatcher.group()));
                                            }


                                            ArrayList<Integer> numberList = new ArrayList<Integer>(number);
                                            ArrayList<String> alphaList = new ArrayList<String>(alphaSet);
                                            System.out.println("alphaSet"+alphaSet);
                                            System.out.println("numberList"+numberList);

                                            int rowIndex=numberList.get(0)-1;
                                            int originalColumnIndex = getExcelColumnNumber(alphaList.get(0))-1;
                                            int referenceColumnIndex = getExcelColumnNumber(alphaList.get(1))-1;
                                            if(originalColumnIndex > referenceColumnIndex){
                                                int temp = referenceColumnIndex;
                                                referenceColumnIndex =originalColumnIndex;
                                                originalColumnIndex=temp;
                                            }


                                            //System.out.println(sheet.getRow(row.getRowNum()));
                                            System.out.println("File Name: "+ file.getName());
                                            System.out.println("Sheet Name: "+ sheet.getSheetName());
                                            System.out.println(sheet.getRow(row.getRowNum()).getCell(cell.getColumnIndex()).toString());
                                            if(sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula().equals(""))
                                                System.out.println("please help me out");
                                            System.out.println("Function Name: "+ sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula());
                                            System.out.println("row indext"+rowIndex);
                                            System.out.println("original column index"+originalColumnIndex);
                                            System.out.println("ref column index"+referenceColumnIndex);
                                            /*System.out.println("File Name: "
                                                    + file.getName());
                                            System.out.println("Sheet Name: "
                                                    + sheet.getSheetName());
                                            System.out.println(cell
                                                    .getCellFormula());*/
                                            if(sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula().contains("qCRA_") && sheet.getRow(rowIndex).getCell(originalColumnIndex).getRawValue().contains("Error:")){
                                                errorsBufferedWriter.newLine();
                                                errorsBufferedWriter.write("File Name: "+ file.getName());
                                                errorsBufferedWriter.newLine();
                                                errorsBufferedWriter.write("Sheet Name: "+ sheet.getSheetName());
                                                errorsBufferedWriter.newLine();
                                                errorsBufferedWriter.write("Function Name: "+ sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula());
                                                errorsBufferedWriter.newLine();
                                                errorsBufferedWriter.write("Cell Number: "+getExcelColumnName(originalColumnIndex+1)+numberList.get(0));
                                                errorsBufferedWriter.newLine();
                                                errorsBufferedWriter.write("Orginal Value : "+sheet.getRow(rowIndex).getCell(originalColumnIndex).getRawValue());
                                                errorsBufferedWriter.newLine();
                                                errorsBufferedWriter.write("Reference Value : "+sheet.getRow(rowIndex).getCell(referenceColumnIndex));
                                                errorsBufferedWriter.newLine();
                                            } else {
                                                mismatchBufferedWriter.newLine();
                                                mismatchBufferedWriter.write("File Name: "+ file.getName());
                                                mismatchBufferedWriter.newLine();
                                                mismatchBufferedWriter.write("Sheet Name: "+ sheet.getSheetName());
                                                mismatchBufferedWriter.newLine();
                                                mismatchBufferedWriter.write("Function Name: "+ sheet.getRow(rowIndex).getCell(originalColumnIndex).getCellFormula());
                                                mismatchBufferedWriter.newLine();
                                                mismatchBufferedWriter.write("Cell Number: "+getExcelColumnName(originalColumnIndex+1)+numberList.get(0));
                                                mismatchBufferedWriter.newLine();
                                                mismatchBufferedWriter.write("Orginal Value : "+sheet.getRow(rowIndex).getCell(originalColumnIndex).getRawValue());
                                                mismatchBufferedWriter.newLine();
                                                mismatchBufferedWriter.write("Reference Value : "+sheet.getRow(rowIndex).getCell(referenceColumnIndex));
                                                mismatchBufferedWriter.newLine();
                                            }
                                        }
                                    break;

                                }
                                cell=null;
                            }
                            row=null;
                        }
                        i++;
                        //fis.close();
                        pkg.close();
                        sheet=null;
                    }
                    workbook=null;
                }
            }
            errorsBufferedWriter.close();
            errorsFileOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Answer 1

1. 如果出现 OOM，请使用 -Xmx... 增加堆大小。
2. 扔掉 StringBuffer 并直接写入文件(BufferedWriter 甚至 PrintStream)。无需将所有输出保存在内存中。仅此一项就可以解决您的问题。