You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Markus Kirsten <mk...@gmail.com> on 2017/11/30 13:33:44 UTC
Problem using INDEX within MAX: WARNING: Incomplete code - cannot
handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
Hi,
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)
Let me know if helpful with anything additional. Also happy to rewrite the Excel formula, if I can get it to work. Running Apache POI 3.17 on macOS.
Many thanks in advance,
Markus
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot
handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
Posted by Markus Kirsten <mk...@gmail.com>.
Done, thank you for the identification of the problem. I’ve submitted the bug with the old description + a more brief description based on your email to https://bz.apache.org/bugzilla/show_bug.cgi?id=61859
> On 1 Dec 2017, at 15:30, Yegor Kozlov <ye...@dinom.ru> wrote:
>
> The problem is not in the INDEX function, but in how the arguments are
> evaluated. POI does not fully support array operands and collapses the
> expression ($B$2:$B$11=F2)*$A$2:$A$11 into a number while INDEX expects an
> array, i.e. the INDEX arguments are wrong. Instead of evaluating
>
> INDEX({1,2,2,3,0,0,0,4,0,0}, 0)
>
> POI evaluates it as
>
> INDEX(1, 0)
>
> Can you create a bug report and attach the file? It is certainly an area to
> improve.
>
> On Thu, Nov 30, 2017 at 4:33 PM, Markus Kirsten <mk...@gmail.com> wrote:
>
>> Hi,
>> 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)
>>
>> Let me know if helpful with anything additional. Also happy to rewrite the
>> Excel formula, if I can get it to work. Running Apache POI 3.17 on macOS.
>>
>>
>> Many thanks in advance,
>> Markus
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem using INDEX within MAX: WARNING: Incomplete code - cannot
handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
Posted by Yegor Kozlov <ye...@dinom.ru>.
The problem is not in the INDEX function, but in how the arguments are
evaluated. POI does not fully support array operands and collapses the
expression ($B$2:$B$11=F2)*$A$2:$A$11 into a number while INDEX expects an
array, i.e. the INDEX arguments are wrong. Instead of evaluating
INDEX({1,2,2,3,0,0,0,4,0,0}, 0)
POI evaluates it as
INDEX(1, 0)
Can you create a bug report and attach the file? It is certainly an area to
improve.
On Thu, Nov 30, 2017 at 4:33 PM, Markus Kirsten <mk...@gmail.com> wrote:
> Hi,
> 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)
>
> Let me know if helpful with anything additional. Also happy to rewrite the
> Excel formula, if I can get it to work. Running Apache POI 3.17 on macOS.
>
>
> Many thanks in advance,
> Markus
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Problem using INDEX within MAX: WARNING: Incomplete code -
cannot handle first arg of type (org.apache.poi.ss.formula.eval.NumberEval)
Posted by "pj.fanning" <fa...@yahoo.com>.
Thanks for the detailed error report.
Can you create an issue so that we can track this?
https://bz.apache.org/bugzilla/
--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org