https://bz.apache.org/bugzilla/show_bug.cgi?id=62904
Bug ID: 62904
Summary: Simple Excel Array Formula Fails in POI
Product: POI
Version: 4.0.0-FINAL
Hardware: PC
OS: Linux
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
Created attachment 36259
--> https://bz.apache.org/bugzilla/attachment.cgi?id=36259&action=edit
Test Excel File
In our project we use POI to "calculate" an Excel workbook that is managed by a
business analyst.
In the last revision we had very strange results and I tracked it down to POI
not really supporting array formulas.
I made a test excel and project where it fails for this simple formula
{ =MIN(IF(A1:A6>=C1;A1:A6)) }
The strange thing is that it works sometimes, but fails for other values.
If this is not supported I would be OK with a hard failure as soon as the
formula is evaluated.
But here there are no exceptions at all, but the formula just gives wrong
results for some values.
See the example project including an automated test at
https://github.com/eekboom/poi-minif
----
I also attached the test excel file to this issue.
Here's a groovy spock test that shows the problem:
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.FormulaEvaluator
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import spock.lang.Specification
import spock.lang.Unroll
class PoiSpec extends Specification {
@Unroll
def 'test array formula: #value -> #expectedValue'() {
given:
Workbook workbook =
WorkbookFactory.create(Thread.currentThread().getContextClassLoader().getResourceAsStream("workbook.xlsx"))
Sheet sheet = workbook.getSheet("Sheet1")
FormulaEvaluator formulaEvaluator =
workbook.getCreationHelper().createFormulaEvaluator();
Row inputRow = sheet.getRow(0)
Cell inputCell = inputRow.getCell(2)
Row outputRow = sheet.getRow(2)
Cell outputCell = outputRow.getCell(2)
when:
inputCell.setCellValue(value)
formulaEvaluator.notifyUpdateCell(inputCell)
then:
formulaEvaluator.evaluate(outputCell).getNumberValue() ==
expectedValue
where:
value || expectedValue
3 || 5
5 || 5
6 || 10
27 || 30
}
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
