| Summary: | Formula evaluation ROW() results in two different results. | ||
|---|---|---|---|
| Product: | POI | Reporter: | Detlef Brendle <detlef.brendle> |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | major | CC: | guarale |
| Priority: | P2 | ||
| Version: | 3.9-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Linux | ||
| Attachments: | junit test case | ||
This problem occurs also with POI 3.10. Generally speaking, I've noticed that none of ROW() or COLUMN() functions work fine if used as THIRD argument of an IF() function.
Examples are:
IF(FALSE,0,ROW()) returns FALSE instead of <row number>
IF(FALSE,0,-ROW()) returns 0.0 instead of -<row number>
Same behavior with COLUMN() Function.
If other functions (SUM(), NOW(), etc..) are used, it works as expected
If these fumctions are used as SECOND argument (TRUE branch) it works as expected.
Debugging POI code I've noticed the following "strange" if statement, which, in my opinion is the cause of the wrong behavior:
WorkbookEvaluator line 479 (POI 3.10)
if (ptgs[i] instanceof AttrPtg && nextPtg instanceof FuncVarPtg) {
// this is an if statement without a false param [...]
i++;
stack.push(BoolEval.FALSE);
}
When processing IF function, nextPtg IS the ROW() function and this code makes it always return FALSE (or 0.0).
|
Created attachment 31090 [details] junit test case Consider the attached test case: - If I evaluate the formula: IF(B1=0,"",((ROW()-ROW(A$1))*12)) the result is: org.apache.poi.ss.usermodel.CellValue [-12.0] which in my eyes is wrong. When negating the condition and the formula results in the correct value: IF(NOT(B1=0),((ROW()-ROW(A$1))*12),"")