You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2015/03/25 08:36:31 UTC
[Bug 57755] New: POI Formula Evaluation Failed with condition
https://bz.apache.org/bugzilla/show_bug.cgi?id=57755
Bug ID: 57755
Summary: POI Formula Evaluation Failed with condition
Product: POI
Version: 3.11-FINAL
Hardware: Sun
OS: SunOS
Status: NEW
Severity: critical
Priority: P2
Component: POI Overall
Assignee: dev@poi.apache.org
Reporter: jp.iwasinta@gmail.com
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57755] POI Formula Evaluation Failed with condition
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57755
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO
--- Comment #1 from Nick Burch <ap...@gagravarr.org> ---
Can you try stepping into the formula evaluator in a debugger, and see why the
evaluation is coming out that way?
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57755] POI Formula Evaluation Failed with condition
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57755
--- Comment #3 from JP <jp...@gmail.com> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57755] POI Formula Evaluation Failed with condition
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57755
--- Comment #4 from JP <jp...@gmail.com> ---
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); ....
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57755] POI Formula Evaluation Failed with condition
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57755
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |INVALID
Status|NEEDINFO |RESOLVED
--- Comment #5 from Nick Burch <ap...@gagravarr.org> ---
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!
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 57755] POI Formula Evaluation Failed with condition
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57755
--- Comment #2 from JP <jp...@gmail.com> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org