0

I am using "switch(evaluator.evaluateInCell(cellIn).getCellType())" in my java program to evaluate formula in Excel. I have arount 15,000 rows in my excel. Here is my code:

Cell cellDB_AcctNumber = row1.createCell(lastCell1);
            cellDB_AcctNumber.setCellType(Cell.CELL_TYPE_FORMULA);
            cellDB_AcctNumber.setCellFormula("VLOOKUP($E"+k+",'SQL_AMS_DATA'!$C$2:$F$"+lastrowDB+",2,FALSE)");

            Cell cellDB_RoutNumber = (HSSFCell) row1.createCell(lastCell1+1);
            cellDB_RoutNumber.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cellDB_RoutNumber.setCellFormula("VLOOKUP($E"+k+",'SQL_AMS_DATA'!$C$2:$F$"+lastrowDB+",3,FALSE)");

            Cell cellDB_AcctType = (HSSFCell) row1.createCell(lastCell1+2);
            cellDB_AcctType.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cellDB_AcctType.setCellFormula("VLOOKUP($E"+k+",'SQL_AMS_DATA'!$C$2:$F$"+lastrowDB+",4,FALSE)");

When I run in Eclipse i got the below error. Could you please help me out of this error ?

Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
    at org.apache.poi.ss.formula.FormulaCellCacheEntrySet.add(FormulaCellCacheEntrySet.java:63)
    at org.apache.poi.ss.formula.CellCacheEntry.addConsumingCell(CellCacheEntry.java:85)
    at org.apache.poi.ss.formula.FormulaCellCacheEntry.changeConsumingCells(FormulaCellCacheEntry.java:80)
    at org.apache.poi.ss.formula.FormulaCellCacheEntry.setSensitiveInputCells(FormulaCellCacheEntry.java:60)
    at org.apache.poi.ss.formula.FormulaCellCacheEntry.updateFormulaResult(FormulaCellCacheEntry.java:109)
    at org.apache.poi.ss.formula.CellEvaluationFrame.updateFormulaResult(CellEvaluationFrame.java:75)
    at org.apache.poi.ss.formula.EvaluationTracker.updateCacheResult(EvaluationTracker.java:93)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:294)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:229)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:354)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:243)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateInCell(HSSFFormulaEvaluator.java:46)
    at com.sentry.comparison.convert.xls2xlsx.xls2xlsxConvert(xls2xlsx.java:74)
    at com.sentry.comparison.main.ComparisonMain.main(ComparisonMain.java:41)
wahwahwah
  • 2,966
  • 1
  • 17
  • 35
Vivekanandan Arunachalam
  • 3
  • 2
  • It means that you are out of memory. [Here](http://stackoverflow.com/questions/37335/how-to-deal-with-java-lang-outofmemoryerror-java-heap-space-error-64mb-heap) are some previously suggested answers to a similar problem. Ultimately, you should try to show what you've done to figure out why this error is happening to get the best help on SO. – wahwahwah Oct 02 '15 at 21:13

1 Answers1

1

You can increase heap memory on eclipse.ini file, adding this lines in the bottom of file:

-XX:PermSize=256M -XX:MaxPermSize=512M

But if you are running an Web Application into a web container or a web server, you could increase memory of your server.

If you are running into a tomcat, create a sh ou bat file (depending of you O.S.), put this file inside your bin directory and write this instructions inside this file:

set CATALINA_OPTS=-server -Xms256m -Xmx2048m -XX:PermSize=512m -XX:MaxPermSize=512

user1531914
  • 83
  • 5