Bug 57755 - POI Formula Evaluation Failed with condition
Summary: POI Formula Evaluation Failed with condition
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.11-FINAL
Hardware: Sun SunOS
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2015-03-25 07:36 UTC by JP
Modified: 2015-03-27 13:34 UTC (History)
0 users


Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description JP 2015-03-25 07:36:31 UTC 
Hello

I'am using POI in order to read excels files from my users. In one of the cells, i've got this formula :

IF(H24>0,((D24*(Q24))/((3600*H24)/I24))+IF(AND(N24>0,L24>0),(D24*L24*(Q24)/N24),0),0)
The problem arrive when i've got a 0 value for N24. In this case, POI returns me an DIV/0 error, but i don't understand why .. because of the IF (N24>0) test.

Anyway, I tried changing the formula and keep only the latest part like this : IF (N24>0,D24*L24*(Q24)/N24,0) => still doesn't work.

To Evaluate the formula I use : FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

And then i do a switch.
Comment 1 Nick Burch 2015-03-25 11:05:59 UTC 
Can you try stepping into the formula evaluator in a debugger, and see why the evaluation is coming out that way?
Comment 2 JP 2015-03-25 12:38:05 UTC 
I made some test.
When changed :
IF (N24>0,D24*L24*(Q24)/N24,0) 
by 
IF ((N24*1)>0,D24*L24*(Q24)/N24,0) it works.

Then trying to explain why :
I checked the excel file : The type in Standard Number
Then
I checked the code and i made cell.getCellType() which return a String ... so because of the string return the test  N24 > 0 was always true ... so that's why i had a DIV/0 error.

The thing i don't understand is why the getCell returns a string ... in place of a number.
Comment 3 JP 2015-03-27 09:59:19 UTC 
We found a Bypass. When using the method evaluateFormulaCell, it was impossible to get the catchedValue.

By using the valuator.evaluate(cell), we still have the error but we can get the catchedValue.
Comment 4 JP 2015-03-27 11:05:44 UTC 
Thanks for your response and sorry ...

I found an error of programation.

To get a long, the developer used this method :
public static Object getCellValue(Row row, int index) {
		if (row == null || row.getCell(index) == null) {
			return null;
		}
		Object retour = null;
		Cell cell = row.getCell(index);
		cell.setCellType(Cell.CELL_TYPE_STRING);
		String val = cell.getStringCellValue().trim();
		if (val != null && !"".equals(val)) {
			retour = cell.getStringCellValue();
		}
		
		return retour;
	}

The gulty is : cell.setCellType(Cell.CELL_TYPE_STRING); ....
Comment 5 Nick Burch 2015-03-27 13:34:17 UTC 
The javadocs for cell.setCellType do warn you not to do that!

I'd suggest switching to using a missing cell policy on the cell fetch, and using DataFormatter to give the string

Glad to hear that the formula parser wasn't the issue after all!