Re: Excel formula oddness

[Yegor Kozlov <ye...@dinom.ru>]

Can you please try with the latest build from trunk?
The problem should be fixed in r1294127 and
https://issues.apache.org/bugzilla/show_bug.cgi?id=51710

Regards,
Yegor


On Tue, Aug 9, 2011 at 7:51 AM, Chris McCann <te...@gmail.com> wrote:
> I'm using POI 3.7 with a *.xlsx spreadsheet.  While evaluating a particular
> formula value in the spreadsheet I get the following error stacktrace:
>
> NativeException: java.lang.NullPointerException: null
>    from org/apache/poi/hssf/record/formula/functions/Sumif.java:92:in
> `accumulate'
>    from org/apache/poi/hssf/record/formula/functions/Sumif.java:83:in
> `sumMatchingCells'
>    from org/apache/poi/hssf/record/formula/functions/Sumif.java:72:in
> `eval'
>    from org/apache/poi/hssf/record/formula/functions/Sumif.java:65:in
> `evaluate'
>    from
> org/apache/poi/hssf/record/formula/functions/Var2or3ArgFunction.java:36:in
> `evaluate'
>    from org/apache/poi/ss/formula/OperationEvaluatorFactory.java:132:in
> `evaluate'
>    from org/apache/poi/ss/formula/WorkbookEvaluator.java:456:in
> `evaluateFormula'
>    from org/apache/poi/ss/formula/WorkbookEvaluator.java:279:in
> `evaluateAny'
>    from org/apache/poi/ss/formula/WorkbookEvaluator.java:618:in
> `evaluateReference'
>    from org/apache/poi/ss/formula/SheetRefEvaluator.java:47:in
> `getEvalForCell'
>    from org/apache/poi/ss/formula/LazyAreaEval.java:51:in
> `getRelativeValue'
>    from org/apache/poi/hssf/record/formula/eval/AreaEvalBase.java:109:in
> `getValue'
>    from
> org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java:143:in
> `collectValues'
>    from
> org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java:127:in
> `getNumberArray'
>    from
> org/apache/poi/hssf/record/formula/functions/MultiOperandNumericFunction.java:89:in
> `evaluate'
>    from org/apache/poi/ss/formula/OperationEvaluatorFactory.java:132:in
> `evaluate'
> ... 24 levels...
>    from org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java:150:in
> `evaluateFormulaCell'
>
> Tracing into the formula that's causing the problem I found an odd
> situation.  Looking at the formula in Excel (using Excel Mac version 12.2.6)
> I see the formula for cell U46 is listed as:
>
> =SUMIF($AI$3:$JN$3,U$36,$AI46:$JN46)
>
> When I call cell.get_cell_formula using POI, though, I get:
>
> U46 = SUMIF($AI$3:$JN$3,AB$36,$AI46:$JN46)
>
> The NPE is being raised because there's no value in AB36.
>
> This formula was created in the original worksheet by copying the formula
> from C46 all the way through V46.  If I look at the formulas from C46 to T46
> the correctly reference the respective $36 in the SUMIF.  This same problem
> exists in numerous other places in this spreadsheet -- the POI-displayed
> formula has a cell reference that's shifted over by a few columns,
> ultimately leading to a bad cell reference and an NPE.
>
> So, why is Excel showing one thing (the correct cell reference) and
> calculating the formula correctly while the formula is evaluated by HSSF (or
> is it XSSF for this part?) incorrectly, resulting in a NullPointerException?
>
> I found that if I manually edited the bogus "AB$36" in Excel to say "U$36",
> saved the worksheet and reloaded it in POI the formula is correctly
> evaluated.
>
> So far I haven't found an easy way to dig into the uncompressed XML
> worksheet to see what it says for these mangled formula values.
>
> Any ideas here what might be causing this?
>
> Cheers,
>
> Chris

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org