[Bug 61859] New: Limited support for array operands

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[Bug 61859] New: Limited support for array operands

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=61859

            Bug ID: 61859
           Summary: Limited support for array operands
           Product: POI
           Version: 3.17-FINAL
          Hardware: Macintosh
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

Created attachment 35587
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=35587&action=edit
Simple file to reproduce the bug

POI does not correctly support array operands and collapses e.g., the
expression inside the INDEX function in INDEX(($B$2:$B$11=F2)*$A$2:$A$11) into
a number. The formula INDEX expects an array, and instead of evaluating

INDEX({1,2,2,3,0,0,0,4,0,0}, 0)

POI evaluates it as

INDEX(1, 0)

STEPS TO REPRODUCE:
1. Evaluate all cells in the attached file (or even just G4) with Apache POI
2. The following exception is thrown WARNING: Incomplete code - cannot handle
first arg of type (org.apache.poi.ss.formula.eval.NumberEval) for cell G4

See original description of the problem below. Identification that this had to
do with how POI handles arguments that are arrays was kindly provided by Yegor
Kozlov.


======
For a spreadsheet I am trying to update and evaluate with POI I am having some
difficulties. The problem I am trying to solve in its simplest form is the
following; From a data sheet with two columns - one with a numeric value, and
one with a group, I would like to find the maximum value for each group. So
from the table below, I’d like to calculate that max for A is 4, max for B is
10 and max for C is 20.

Value   Group
1       A
2       A
2       A
3       A
5       B
3       B
10      B
4       A
10      C
20      C

One way to do this would be to create the following table, where the column max
value is to;

=MAX(INDEX(($B$2:$B$11=F2)*$A$2:$A$11;0))

The logic is basically; find where column B (the group) matches the specified
group. This is an array with 0/1s. Multiply that array with the value array.
Now we have an array with either 0s or the values from the specified group.
Take max of that.

Group   Max value
A       4
B       10
C       20

However, when evaluating the formula above, I get the following error. I have
uploaded a minimal test file on https://ufile.io/z6qg4 The 3 red cells are the
only ones with formulas and it is when I evaluate them that I get this error.

WARNING: Incomplete code - cannot handle first arg of type
(org.apache.poi.ss.formula.eval.NumberEval) for cell G4
java.lang.RuntimeException: Incomplete code - cannot handle first arg of type
(org.apache.poi.ss.formula.eval.NumberEval)
        at
org.apache.poi.ss.formula.functions.Index.convertFirstArg(Index.java:106)
        at org.apache.poi.ss.formula.functions.Index.evaluate(Index.java:50)
        at org.apache.poi.ss.formula.functions.Index.evaluate(Index.java:114)
        at
org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:523)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:290)
        at
org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:232)
        at
org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:65)
        at
org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCellEnum(BaseFormulaEvaluator.java:192)
        at
org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:887)

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]