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 |
On Mon, 8 Aug 2011, Chris McCann wrote:
> 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. The key question is what has been stored in the xlsx file? If you unzip the .xlsx and then look at the sheet xml file for the sheet with the problem formula in, you should be able to find the cell xml (there's a reference on it with the cell's location). What has excel stored there? Nick --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
Nick,
Exactly my thinking but for some reason trying to open the unzipped XML file using TextMate clobbered my machine. Not sure what's up there but will try to find a workable solution to opening it. I'll report what I find here. Cheers, Chris On Tue, Aug 9, 2011 at 4:32 AM, Nick Burch <[hidden email]> wrote: > On Mon, 8 Aug 2011, Chris McCann wrote: > >> 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. >> > > The key question is what has been stored in the xlsx file? If you unzip the > .xlsx and then look at the sheet xml file for the sheet with the problem > formula in, you should be able to find the cell xml (there's a reference on > it with the cell's location). What has excel stored there? > > Nick > > ------------------------------**------------------------------**--------- > To unsubscribe, e-mail: [hidden email].**org<[hidden email]> > For additional commands, e-mail: [hidden email] > > |
TextMate simply won't handle the file but I was able to open it in FireFox.
Looking at the XML for these two cells in the \xl\worksheets\sheet1.xml file: <c r="R40" s="52"> <f t="shared" si="58"/> <v>0</v> </c> <c r="S40" s="52"> <f t="shared" ref="M40:V42" si="59">SUMIF($AI$3:$JN$3,S$36,$AI40:$JN40)</f> <v>0</v> </c> My assumptions are that <f> is "formula", t = "type", si = "shared index" and <v> = value. The formula in R40 was copied from cell C40, and its entry shows: <c r="C40" s="52"> <f t="shared" ref="C40:R42" si="58">SUMIF($AI$3:$JN$3,C$36,$AI40:$JN40)</f> <v>642500</v> </c> Evaluating the cells using POI (in a JRuby app) shows the following two cell formulas: R40 = SUMIF($AI$3:$JN$3,R$36,$AI40:$JN40) S40 = SUMIF($AI$3:$JN$3,*Y$36*,$AI40:$JN40) Clearly something is amiss with the Y$36 reference in S40 -- it should be S$36. The SUMIF for T46 likewise shows what looks to be the correct formula in the XML file but POI shows the 2nd param to the formula to be AA$36 instead of T$36. I hope this info is helpful. Cheers, and thanks, Chris On Tue, Aug 9, 2011 at 8:57 AM, Chris McCann <[hidden email]> wrote: > Nick, > > Exactly my thinking but for some reason trying to open the unzipped XML > file using TextMate clobbered my machine. Not sure what's up there but will > try to find a workable solution to opening it. I'll report what I find > here. > > Cheers, > > Chris > > > On Tue, Aug 9, 2011 at 4:32 AM, Nick Burch <[hidden email]>wrote: > >> On Mon, 8 Aug 2011, Chris McCann wrote: >> >>> 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. >>> >> >> The key question is what has been stored in the xlsx file? If you unzip >> the .xlsx and then look at the sheet xml file for the sheet with the problem >> formula in, you should be able to find the cell xml (there's a reference on >> it with the cell's location). What has excel stored there? >> >> Nick >> >> ------------------------------**------------------------------**--------- >> To unsubscribe, e-mail: [hidden email].**org<[hidden email]> >> For additional commands, e-mail: [hidden email] >> >> > |
In reply to this post by Chris McCann
On Tue, 9 Aug 2011, Chris McCann wrote:
> Exactly my thinking but for some reason trying to open the unzipped XML file > using TextMate clobbered my machine. If the file was written by office, then the xml won't have linebreaks in it. That means it'll be one huge line, and that can break a lot of syntax highlighters! I'd suggest you either try opening it in a web browser (they normally do a fair job of displaying xml), or send the xml through "xmllint -format" to have the human readable whitespace added back in Nick --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
Nick,
Roget that - was able to open it with FireFox just fine. See my previous emails with details on what I found. Cheers, Chris On Tue, Aug 9, 2011 at 3:30 PM, Nick Burch <[hidden email]> wrote: > On Tue, 9 Aug 2011, Chris McCann wrote: > >> Exactly my thinking but for some reason trying to open the unzipped XML >> file >> using TextMate clobbered my machine. >> > > If the file was written by office, then the xml won't have linebreaks in > it. That means it'll be one huge line, and that can break a lot of syntax > highlighters! > > I'd suggest you either try opening it in a web browser (they normally do a > fair job of displaying xml), or send the xml through "xmllint -format" to > have the human readable whitespace added back in > > > Nick > > ------------------------------**------------------------------**--------- > To unsubscribe, e-mail: [hidden email].**org<[hidden email]> > For additional commands, e-mail: [hidden email] > > |
In reply to this post by Chris McCann
On Tue, 9 Aug 2011, Chris McCann wrote:
> <c r="R40" s="52"> > <f t="shared" si="58"/> > <v>0</v> > </c> > <c r="S40" s="52"> > <f t="shared" ref="M40:V42" > si="59">SUMIF($AI$3:$JN$3,S$36,$AI40:$JN40)</f> > <v>0</v> > </c> > > My assumptions are that <f> is "formula", t = "type", si = "shared index" > and <v> = value. Off the top of my head, I think that's correct. If you look in the file format documentation (linked from the poi website, but it's a bit big...) it'll confirm > Evaluating the cells using POI (in a JRuby app) shows the following two cell > formulas: > > R40 = SUMIF($AI$3:$JN$3,R$36,$AI40:$JN40) > S40 = SUMIF($AI$3:$JN$3,*Y$36*,$AI40:$JN40) > > Clearly something is amiss with the Y$36 reference in S40 -- it should be > S$36. The SUMIF for T46 likewise shows what looks to be the correct formula > in the XML file but POI shows the 2nd param to the formula to be AA$36 > instead of T$36. Firstly, any chance you could open a new bug in bugzilla, and upload the problem file? (Or if not that one, a similar one with dummy data that shows the same thing). If you can, then also do a short unit test using the file which shows POI getting the formula correct on the first cell, but incorrect on a later shared one If you look in XSSFCell, then I think it's the convertSharedFormula method that's used to look up the formula in the later cells. If you have time, I'd suggest you then dive into that with a debugger against your unit test, and see if you can spot the incorrect logic Cheers Nick --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
Nick,
I've uncovered the source of the problem. It is happening, as you suggested, in the SharedFormulaRecord.convertSharedFormulas() method in the call to fixupRelativeColumn(). The problem stems from the fact that it appears Excel is putting incorrect information in the shared formula data, and that's causing the relative column in the second parameter of the SUMIF function to be calculated incorrectly. It looks to me like Excel itself ignores or compensates for this error while the POI implementation doesn't. Here's an example of the source of a cell with a shared formula in which the relative column is miscalculated: <c r="S40" s="52"> <f t="shared" ref="M40:V42" si="59">SUMIF($AI$3:$JN$3,S$36,$AI40:$JN40)</f> <v>0</v> </c> The formula in cell S40 correctly references cell S$36 in the SUMIF() function, but it's resolved by convertSharedFormulas() as Y$36. The reason is because of the "ref" attribute. It references the cells across which this function is shared, and in this example ref="M40:V42". In fact, this formula was probably originally copied over the range C40:V42 but somehow the ref in S40 was changed to M40:V42. When fixupRelativeColumn() is called, it sees that the first column in the ref range is M (column 12) and the relative column is S (col 18). It computes the offset between those two columns (18 - 12 = 6), and adds that to the relative column (18 + 6 = 24 => Y$36). Since Y36 is empty it causes a NullPointerException. Two questions, then: 1) Why is this shared formula ref attribute getting mangled? 2) Since this mangled data exists in the *.xlsx file, how does Excel actually handle it correctly? I have no clue about 1), but I suspect that in the case of 2) that it simply ignores the ref attribute when <f[unction]> element isn't blank, in other words, if a function is actually provided. In the example case above since the SUMIF function is provided directly, rather than a pointer to a shared function, the function can be evaluated directly without calculating the relative column offsets. How does Excel handle the other columns that actually need to use the shared formula and the (bad) relative cell references? Again, no clue, though I'm guessing that when it sees a situation like this, in which a shared formula is given but the ref attribute is wrong, that it internally adjusts the ref to point to the correct starting cell. In this case instead of M40:V42 it would see the SUMIF function for S30 and internally correct the ref to be S40:V42, which would result in correct shared formula calculations since the relative offsets would then be correct. I'm sure there are numerous corner cases I'm not considering but the fact is that Excel correctly calculates the formula value despite the botched ref attribute. I'm loathe to try to write a patch in the SharedFormulaRecord class for this problem since it seems to be making up for an internal inconsistency that Excel is putting into a shared formula description. Let me know what you think of all this. Cheers, Chris On Wed, Aug 10, 2011 at 4:01 AM, Nick Burch <[hidden email]> wrote: > On Tue, 9 Aug 2011, Chris McCann wrote: > >> <c r="R40" s="52"> >> <f t="shared" si="58"/> >> <v>0</v> >> </c> >> <c r="S40" s="52"> >> <f t="shared" ref="M40:V42" >> si="59">SUMIF($AI$3:$JN$3,S$**36,$AI40:$JN40)</f> >> <v>0</v> >> </c> >> >> My assumptions are that <f> is "formula", t = "type", si = "shared index" >> and <v> = value. >> > > Off the top of my head, I think that's correct. If you look in the file > format documentation (linked from the poi website, but it's a bit big...) > it'll confirm > > > > Evaluating the cells using POI (in a JRuby app) shows the following two >> cell >> formulas: >> >> R40 = SUMIF($AI$3:$JN$3,R$36,$AI40:$**JN40) >> S40 = SUMIF($AI$3:$JN$3,*Y$36*,$**AI40:$JN40) >> >> Clearly something is amiss with the Y$36 reference in S40 -- it should be >> S$36. The SUMIF for T46 likewise shows what looks to be the correct >> formula >> in the XML file but POI shows the 2nd param to the formula to be AA$36 >> instead of T$36. >> > > Firstly, any chance you could open a new bug in bugzilla, and upload the > problem file? (Or if not that one, a similar one with dummy data that shows > the same thing). If you can, then also do a short unit test using the file > which shows POI getting the formula correct on the first cell, but incorrect > on a later shared one > > If you look in XSSFCell, then I think it's the convertSharedFormula method > that's used to look up the formula in the later cells. If you have time, I'd > suggest you then dive into that with a debugger against your unit test, and > see if you can spot the incorrect logic > > Cheers > > Nick > > ------------------------------**------------------------------**--------- > To unsubscribe, e-mail: [hidden email].**org<[hidden email]> > For additional commands, e-mail: [hidden email] > > |
Just curious if there's a response to this. Have been on vacation for a
week but diving back into real life now and would like to figure out the best way to handle this problem. Cheers, Chris On Sat, Aug 13, 2011 at 11:17 PM, Chris McCann <[hidden email]>wrote: > Nick, > > I've uncovered the source of the problem. It is happening, as you > suggested, in the SharedFormulaRecord.convertSharedFormulas() method in the > call to fixupRelativeColumn(). > > The problem stems from the fact that it appears Excel is putting incorrect > information in the shared formula data, and that's causing the relative > column in the second parameter of the SUMIF function to be calculated > incorrectly. It looks to me like Excel itself ignores or compensates for > this error while the POI implementation doesn't. > > Here's an example of the source of a cell with a shared formula in which > the relative column is miscalculated: > > > <c r="S40" s="52"> > <f t="shared" ref="M40:V42" > si="59">SUMIF($AI$3:$JN$3,S$36,$AI40:$JN40)</f> > <v>0</v> > </c> > > The formula in cell S40 correctly references cell S$36 in the SUMIF() > function, but it's resolved by convertSharedFormulas() as Y$36. The reason > is because of the "ref" attribute. It references the cells across which > this function is shared, and in this example ref="M40:V42". > > In fact, this formula was probably originally copied over the range C40:V42 > but somehow the ref in S40 was changed to M40:V42. When > fixupRelativeColumn() is called, it sees that the first column in the ref > range is M (column 12) and the relative column is S (col 18). It computes > the offset between those two columns (18 - 12 = 6), and adds that to the > relative column (18 + 6 = 24 => Y$36). Since Y36 is empty it causes a > NullPointerException. > > Two questions, then: 1) Why is this shared formula ref attribute getting > mangled? 2) Since this mangled data exists in the *.xlsx file, how does > Excel actually handle it correctly? > > I have no clue about 1), but I suspect that in the case of 2) that it > simply ignores the ref attribute when <f[unction]> element isn't blank, in > other words, if a function is actually provided. In the example case above > since the SUMIF function is provided directly, rather than a pointer to a > shared function, the function can be evaluated directly without calculating > the relative column offsets. > > How does Excel handle the other columns that actually need to use the > shared formula and the (bad) relative cell references? Again, no clue, > though I'm guessing that when it sees a situation like this, in which a > shared formula is given but the ref attribute is wrong, that it internally > adjusts the ref to point to the correct starting cell. > > In this case instead of M40:V42 it would see the SUMIF function for S30 and > internally correct the ref to be S40:V42, which would result in correct > shared formula calculations since the relative offsets would then be > correct. I'm sure there are numerous corner cases I'm not considering but > the fact is that Excel correctly calculates the formula value despite the > botched ref attribute. > > I'm loathe to try to write a patch in the SharedFormulaRecord class for > this problem since it seems to be making up for an internal inconsistency > that Excel is putting into a shared formula description. > > Let me know what you think of all this. > > Cheers, > > Chris > > > On Wed, Aug 10, 2011 at 4:01 AM, Nick Burch <[hidden email]>wrote: > >> On Tue, 9 Aug 2011, Chris McCann wrote: >> >>> <c r="R40" s="52"> >>> <f t="shared" si="58"/> >>> <v>0</v> >>> </c> >>> <c r="S40" s="52"> >>> <f t="shared" ref="M40:V42" >>> si="59">SUMIF($AI$3:$JN$3,S$**36,$AI40:$JN40)</f> >>> <v>0</v> >>> </c> >>> >>> My assumptions are that <f> is "formula", t = "type", si = "shared index" >>> and <v> = value. >>> >> >> Off the top of my head, I think that's correct. If you look in the file >> format documentation (linked from the poi website, but it's a bit big...) >> it'll confirm >> >> >> >> Evaluating the cells using POI (in a JRuby app) shows the following two >>> cell >>> formulas: >>> >>> R40 = SUMIF($AI$3:$JN$3,R$36,$AI40:$**JN40) >>> S40 = SUMIF($AI$3:$JN$3,*Y$36*,$**AI40:$JN40) >>> >>> Clearly something is amiss with the Y$36 reference in S40 -- it should be >>> S$36. The SUMIF for T46 likewise shows what looks to be the correct >>> formula >>> in the XML file but POI shows the 2nd param to the formula to be AA$36 >>> instead of T$36. >>> >> >> Firstly, any chance you could open a new bug in bugzilla, and upload the >> problem file? (Or if not that one, a similar one with dummy data that shows >> the same thing). If you can, then also do a short unit test using the file >> which shows POI getting the formula correct on the first cell, but incorrect >> on a later shared one >> >> If you look in XSSFCell, then I think it's the convertSharedFormula method >> that's used to look up the formula in the later cells. If you have time, I'd >> suggest you then dive into that with a debugger against your unit test, and >> see if you can spot the incorrect logic >> >> Cheers >> >> Nick >> >> ------------------------------**------------------------------**--------- >> To unsubscribe, e-mail: [hidden email].**org<[hidden email]> >> For additional commands, e-mail: [hidden email] >> >> > |
On Sat, 20 Aug 2011, Chris McCann wrote:
> Just curious if there's a response to this. Have been on vacation for a > week but diving back into real life now and would like to figure out the > best way to handle this problem. If you can, grab the specification for the .xlsx format and have a look at what it says for shared formulas. It ought to say how they should be handled, and armed with that we can figure out if it's an excel bug, or the format just being a bit odd. Nick --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
Do you know where I can find the spec? I looked at ISO/IEC 29500-1:2011,
which appears to be the current OOXML specification but didn't see anything related to shared formula references. Cheers, Chris On Tue, Aug 23, 2011 at 6:42 AM, Nick Burch <[hidden email]> wrote: > On Sat, 20 Aug 2011, Chris McCann wrote: > >> Just curious if there's a response to this. Have been on vacation for a >> week but diving back into real life now and would like to figure out the >> best way to handle this problem. >> > > If you can, grab the specification for the .xlsx format and have a look at > what it says for shared formulas. It ought to say how they should be > handled, and armed with that we can figure out if it's an excel bug, or the > format just being a bit odd. > > > Nick > > ------------------------------**------------------------------**--------- > To unsubscribe, e-mail: [hidden email].**org<[hidden email]> > For additional commands, e-mail: [hidden email] > > |
On Wed, 24 Aug 2011, Chris McCann wrote:
> Do you know where I can find the spec? I looked at ISO/IEC > 29500-1:2011, which appears to be the current OOXML specification but > didn't see anything related to shared formula references. That's the one. With any luck something like 18.17 (Formulas of SpreadhseetML) would cover it Nick --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
I scanned that section but didn't see anything that seemed related to this
problem. I'll take a closer look. Cheers, Chris On Thu, Aug 25, 2011 at 3:11 AM, Nick Burch <[hidden email]> wrote: > On Wed, 24 Aug 2011, Chris McCann wrote: > >> Do you know where I can find the spec? I looked at ISO/IEC 29500-1:2011, >> which appears to be the current OOXML specification but didn't see anything >> related to shared formula references. >> > > That's the one. With any luck something like 18.17 (Formulas of > SpreadhseetML) would cover it > > > Nick > > ------------------------------**------------------------------**--------- > To unsubscribe, e-mail: [hidden email].**org<[hidden email]> > For additional commands, e-mail: [hidden email] > > |
In reply to this post by Chris McCann
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 <[hidden email]> 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: [hidden email] For additional commands, e-mail: [hidden email] |
Free forum by Nabble | Edit this page |