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();
}
}
}
c:/Users/