Results 1 to 4 of 4
  1. #1
    mglowacki is offline Member
    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0

    Default ClassCastException when evaluating formula

    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?

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: ClassCastException when evaluating formula

    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?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    mglowacki is offline Member
    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0

    Default Re: ClassCastException when evaluating formula

    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.


    Quote Originally Posted by Tolls View Post
    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?

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: ClassCastException when evaluating formula

    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.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. ClassCastException
    By MetalR0 in forum New To Java
    Replies: 5
    Last Post: 08-03-2011, 03:22 AM
  2. ClassCastException
    By Umi in forum New To Java
    Replies: 2
    Last Post: 01-13-2010, 11:51 PM
  3. Replies: 2
    Last Post: 08-04-2009, 12:35 AM
  4. [SOLVED] Evaluating an Airthmetic Expression
    By learning_javaNow in forum New To Java
    Replies: 6
    Last Post: 03-01-2009, 04:54 AM
  5. ClassCastException
    By Felissa in forum New To Java
    Replies: 2
    Last Post: 07-04-2007, 05:06 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •