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
