ClassCastException when evaluating formula

[mglowacki]

Hi,

I am evaluating row by row in the loop. In one of the errors I got exception:

java.lang.ClassCastException: org.apache.poi.ss.formula.eval.NumberEval cannot be cast to org.apache.poi.ss.formula.eval.BoolEval
at org.apache.poi.ss.formula.functions.NumericFunctio n$36.evaluate(NumericFunction.java:462)
at org.apache.poi.ss.formula.functions.Fixed3ArgFunct ion.evaluate(Fixed3ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactor y.evaluate(OperationEvaluatorFactory.java:132)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teFormula(WorkbookEvaluator.java:491)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teAny(WorkbookEvaluator.java:287)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teReference(WorkbookEvaluator.java:653)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEva lForCell(SheetRefEvaluator.java:51)
at org.apache.poi.ss.formula.LazyAreaEval.getRelative Value(LazyAreaEval.java:51)
at org.apache.poi.ss.formula.eval.AreaEvalBase.getVal ue(AreaEvalBase.java:109)
at org.apache.poi.ss.formula.functions.LookupUtils$Co lumnVector.getItem(LookupUtils.java:99)
at org.apache.poi.ss.formula.functions.Vlookup.evalua te(Vlookup.java:61)
at org.apache.poi.ss.formula.functions.Var3or4ArgFunc tion.evaluate(Var3or4ArgFunction.java:36)
at org.apache.poi.ss.formula.OperationEvaluatorFactor y.evaluate(OperationEvaluatorFactory.java:132)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teFormula(WorkbookEvaluator.java:491)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teAny(WorkbookEvaluator.java:287)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teReference(WorkbookEvaluator.java:653)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEva lForCell(SheetRefEvaluator.java:51)
at org.apache.poi.ss.formula.LazyAreaEval.getRelative Value(LazyAreaEval.java:51)
at org.apache.poi.ss.formula.eval.AreaEvalBase.getVal ue(AreaEvalBase.java:109)
at org.apache.poi.ss.formula.functions.MultiOperandNu mericFunction.collectValues(MultiOperandNumericFun ction.java:150)
at org.apache.poi.ss.formula.functions.MultiOperandNu mericFunction.getNumberArray(MultiOperandNumericFu nction.java:127)
at org.apache.poi.ss.formula.functions.MultiOperandNu mericFunction.evaluate(MultiOperandNumericFunction .java:89)
at org.apache.poi.ss.formula.OperationEvaluatorFactor y.evaluate(OperationEvaluatorFactory.java:132)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teFormula(WorkbookEvaluator.java:491)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teAny(WorkbookEvaluator.java:287)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teReference(WorkbookEvaluator.java:653)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEva lForCell(SheetRefEvaluator.java:51)
at org.apache.poi.ss.formula.LazyRefEval.getInnerValu eEval(LazyRefEval.java:44)
at org.apache.poi.ss.formula.eval.OperandResolver.get SingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.eval.TwoOperandNumericOp eration.singleOperandEvaluate(TwoOperandNumericOpe ration.java:29)
at org.apache.poi.ss.formula.eval.TwoOperandNumericOp eration.evaluate(TwoOperandNumericOperation.java:3 6)
at org.apache.poi.ss.formula.functions.Fixed2ArgFunct ion.evaluate(Fixed2ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactor y.evaluate(OperationEvaluatorFactory.java:119)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teFormula(WorkbookEvaluator.java:491)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teAny(WorkbookEvaluator.java:287)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teReference(WorkbookEvaluator.java:653)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEva lForCell(SheetRefEvaluator.java:51)
at org.apache.poi.ss.formula.LazyRefEval.getInnerValu eEval(LazyRefEval.java:44)
at org.apache.poi.ss.formula.eval.OperandResolver.get SingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.eval.RelationalOperation Eval.evaluate(RelationalOperationEval.java:64)
at org.apache.poi.ss.formula.functions.Fixed2ArgFunct ion.evaluate(Fixed2ArgFunction.java:33)
at org.apache.poi.ss.formula.OperationEvaluatorFactor y.evaluate(OperationEvaluatorFactory.java:119)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teFormula(WorkbookEvaluator.java:491)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teAny(WorkbookEvaluator.java:287)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teReference(WorkbookEvaluator.java:653)
at org.apache.poi.ss.formula.SheetRefEvaluator.getEva lForCell(SheetRefEvaluator.java:51)
at org.apache.poi.ss.formula.LazyRefEval.getInnerValu eEval(LazyRefEval.java:44)
at org.apache.poi.ss.formula.eval.OperandResolver.get SingleValue(OperandResolver.java:62)
at org.apache.poi.ss.formula.WorkbookEvaluator.derefe renceResult(WorkbookEvaluator.java:543)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teFormula(WorkbookEvaluator.java:506)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua teAny(WorkbookEvaluator.java:287)
at org.apache.poi.ss.formula.WorkbookEvaluator.evalua te(WorkbookEvaluator.java:229)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator .evaluateFormulaCellValue(HSSFFormulaEvaluator.jav a:354)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator .evaluate(HSSFFormulaEvaluator.java:185)
at ExcelWorks.main(ExcelWorks.java:61)

The cell contains this formula: =IF($K$5>0,5;VLOOKUP($K$5;$N$7:$Q$24;3);VLOOKUP($K $6;$N$7:$Q$24;4))

looks like the problem is VLOOKUP.

Here is my code:

Workbook wb = null;
try {
wb = new HSSFWorkbook(inputStream);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Sheet sheet = wb.getSheetAt(0);
// You can get the Row one by one if you know which row of Excel file
// has the data.
Row row = sheet.getRow(4);
if (row != null) {
Cell cell = row.getCell(1);
if (cell != null) {
System.out.println(cell.toString());
cell.setCellValue(2.5);
}
}

Sheet mappingSheet = wb.getSheet("mapping");
int i=0;
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
while(true) {
Row row2 = mappingSheet.getRow(i);
if(row2 == null) {
break;
}
Cell cell2 = row2.getCell(0);


System.out.println(cell2);
int j=2;
while(true) {
try {
cell2 = row2.getCell(j++);
if(cell2 == null || evaluator.evaluate(cell2) == null) {
break;
}
System.out.print(evaluator.evaluate(cell2).getNumb erValue() + " ");
} catch (Exception e) {
e.printStackTrace();
break;
}
}
System.out.println();
i++;
}

Couldn't find any help, maybe I should modify my Excel somehow?

[Tolls]

It's quite possible that POI has a bug.
It's pretty good with formulae these days, but I wouldn't rely on it to handle everything you cold throw at it.
Try a version of your excel without the IF, just containing first one of the VLOOKUPs and then the other one, as it looks like it's VLOOKUP that's got the problem.
You might be able to spot exactly what the trouble is.

Also, this presumes this actually works in Excel itself?

[mglowacki]

Actually I tried it before and yes, it is VLOOKUP that does not work. And I thought it might be problem, but I found Apache POI supports VLOOKUP. Regarding the sheet, it was created as xlsx doc, then I saved it as xls in OpenOffice. It works fine in both Excel and Calc apps.

It's quite possible that POI has a bug.
It's pretty good with formulae these days, but I wouldn't rely on it to handle everything you cold throw at it.
Try a version of your excel without the IF, just containing first one of the VLOOKUPs and then the other one, as it looks like it's VLOOKUP that's got the problem.
You might be able to spot exactly what the trouble is.

Also, this presumes this actually works in Excel itself?

[Tolls]

I don't know if there's a bug list you could look through, or add to, for POI.
I do wonder if it's the contents of one of the cells in the VLOOKUP throwing it, causing it to get confused as to whether it's dealing with a number or boolean.