Bug 47083

Summary: AND Formula not working in HSSF
Product: POI Reporter: Kiran Sireesh <kiransireesh>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: critical    
Priority: P1    
Version: 3.2-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   

Description Kiran Sireesh 2009-04-23 06:02:26 UTC
I've got the following formula in a cell:  =AND(B13="ACCEPT",B58="Inforce")
When the above cell is evaluated using formula evaluator, using the following snippet
HSSFCell cell =  workbook.getSheet("Calculation").getRow(63).getCell(4);
CellValue cellValue = formulaEvaluator.evaluate(cell);			
System.out.println(cellValue.formatAsString());

I'm getting #VALUE! on the screen. It is not getting evaluated. This is very much needed.

Regards,
Kiran
Comment 1 Josh Micich 2009-04-23 09:26:27 UTC
Without an example spreadsheet it's hard to tell what is going wrong.  POI has quite a few junit test cases that show AND() to be working correctly.  Perhaps the problem is with one of the sub expressions.  If either evaluates to #VALUE!, that error will propagate to AND()'s return result.

You can drill down to the source of the #VALUE! error by trying things like this:
HSSFCell cell =  workbook.getSheet("Calculation").getRow(63).getCell(4);
cell.setCellFormula("B13=\"ACCEPT\"");
CellValue cellValue = formulaEvaluator.evaluate(cell);            
System.out.println(cellValue.formatAsString());

If this doesn't help you solve your problem, please upload an example spreadsheet which demonstrates the error.
Comment 2 Nick Burch 2010-06-03 07:16:29 UTC
No response received in over a year, so assuming it's working with a newer version of POI