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 2022/02/21 10:19:20 UTC

[Bug 65907] New: implementation of SUMPRODUCT function cannot handle case where operators appear between ranges

https://bz.apache.org/bugzilla/show_bug.cgi?id=65907

            Bug ID: 65907
           Summary: implementation of SUMPRODUCT function cannot handle
                    case where operators appear between ranges
           Product: POI
           Version: 5.2.0-FINAL
          Hardware: All
                OS: All
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: dev@poi.apache.org
          Reporter: fanningpj@yahoo.com
  Target Milestone: ---

https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e
-- current code does not support example 3 (it does support example 1 though)


SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9)

the current function evaluator tries to apply the multiplies before the
SUMPRODUCT function implementation is called

Leads to an exception like:

java.lang.RuntimeException: Invalid arg type for SUMPRODUCT:
(org.apache.poi.ss.formula.eval.ErrorEval)

        at
org.apache.poi.ss.formula.functions.Sumproduct.evaluate(Sumproduct.java:85)

-- 
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 65907] implementation of SUMPRODUCT function cannot handle case where operators appear between ranges

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65907

--- Comment #2 from PJ Fanning <fa...@yahoo.com> ---
Even the B2:B9=B12 bit seems to case issues in POI - this is evaluated before
Sumproduct function code is called and POI does not seem to know how to
interpret this and returns an ErrorEval.

Supporting this type of formula eval could require serious rewriting of how POI
evals formulas.

-- 
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 65907] implementation of SUMPRODUCT function cannot handle case where operators appear between ranges

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65907

--- Comment #3 from RouSi <60...@qq.com> ---
我在E28有一个公式=SUMPRODUCT(($N$6:$N$26="镀锌板")*($M$6:$M$26)),代码在OperandResolver.chooseSingleElementFromAreaInternal(AreaEval
ae,
            int srcCellRow, int
srcCellCol)方法执行中有一个这个判断if(!ae.containsRow(srcCellRow)) {
                throw EvaluationException.invalidValue();
            }导致我公式无法执行报错

-- 
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 65907] implementation of SUMPRODUCT function cannot handle case where operators appear between ranges

Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=65907

--- Comment #1 from PJ Fanning <fa...@yahoo.com> ---
I added a disabled test to demo the issue - r1898269

-- 
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