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 <[email protected]> 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: [email protected] For additional commands, e-mail: [email protected]
