You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Chris McCann <te...@gmail.com> on 2011/08/09 05:51:00 UTC
Excel formula oddness
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
Re: Excel formula oddness
Posted by 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
Re: Excel formula oddness
Posted by Chris McCann <te...@gmail.com>.
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 <ni...@alfresco.com> 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: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Excel formula oddness
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel formula oddness
Posted by Chris McCann <te...@gmail.com>.
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 <ni...@alfresco.com> 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: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Excel formula oddness
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel formula oddness
Posted by Chris McCann <te...@gmail.com>.
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 <te...@gmail.com>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 <ni...@alfresco.com>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: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: Excel formula oddness
Posted by Chris McCann <te...@gmail.com>.
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 <ni...@alfresco.com> 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: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Excel formula oddness
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel formula oddness
Posted by Chris McCann <te...@gmail.com>.
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 <te...@gmail.com> 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 <ni...@alfresco.com>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: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
Re: Excel formula oddness
Posted by Chris McCann <te...@gmail.com>.
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 <ni...@alfresco.com> 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: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Excel formula oddness
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Excel formula oddness
Posted by Chris McCann <te...@gmail.com>.
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 <ni...@alfresco.com> 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: user-unsubscribe@poi.apache.**org<us...@poi.apache.org>
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Excel formula oddness
Posted by Nick Burch <ni...@alfresco.com>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org