getting Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

Question

if i put more than 5 excel files in a folder i getting this

java.lang.OutOfMemoryError: Java heap space.

Totally i have 40 excel files and each file may contain 5 to 10 sheets. my code will find out the errors in the excel files and write it down in the Error.txt file.

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. If you get OOM, use -Xmx... to increase heap size.
2. Throw out your StringBuffers and write directly to files (BufferedWriter or even PrintStream). No need to keep all the output in memory. This alone might solve your problem.