I've attached a simple example of my issue but here is the description. I have a spreadsheet with values (not formulas) in B1:B4. In B5 there is the value 0.10.
In B6 I have this formula:
=NPV(B5,B1:B4)
This seems to work fine in Excel.
When I try to evaluate this B6 cell, I get the errorCode 15 that I've
previously
e-mailed about. This error isn't thrown overtly, the CellValue from the
evaluator returns 0 and the errorCode is now set to a non-zero value.
When I debug this, it's falling in the NPV.evaluate(ValueEval[] args, int
srcRowIndex, int srcColumnIndex) method. This will eventually end up in the
OperandResolver.chooseSingleElementFromAreaInternal(AreaEval ae, int
srcCellRow,
int srcCellCol) which has this if statement in it:
if (ae.isColumn()) {
if(ae.isRow()) {
return ae.getRelativeValue(0, 0);
}
if(!ae.containsRow(srcCellRow)) {
throw EvaluationException.invalidValue();
}
return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn());
}
My error is coming from the throw clause. Interestingly, if I comment that
out,
I get a better error message:
java.lang.IllegalArgumentException: Specified row index (5) is outside the
allowed range (0..3)
So my question is, is this a bug or is this user error? Separating that out: is
my usage of NPV user error on my part and is that throw statement a bug?
This example of the NPV function is used heavily in the spreadsheet I am trying
to use POI with (and I don't know Excel nearly well enough to have known about
or how to use the NPV function). I've tried changing it so that the range of
cells includes B5 but this doesn't change the behavior (meaning that guessing
about what the proper usage of NPV isn't helping me).
Thanks in advance for any advice or suggestions.
Jon
----- Original Message ----
From: Jon Svede <[email protected]>
To: POI Users List <[email protected]>
Sent: Tue, December 7, 2010 7:17:17 AM
Subject: Re: Using FinanceLib
Is this function, the FinanceLib.npv() method, the one used internally by POI
when a cell in a spreadsheet specifies the NPV function?
Thanks,
Jon
----- Original Message ----
From: Edward Ivanovic <[email protected]>
To: POI Users List <[email protected]>
Sent: Mon, December 6, 2010 4:24:58 PM
Subject: Re: Using FinanceLib
That's great. Thanks Yegor!
On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov <[email protected]> wrote:
> OK, I got the request. The visibility of the FinanceLib class will be
> raised from default to public.
>
> Thanks for raising this topic.
>
> Regards,
> Yegor
>
>
> That's a shame. Why hide a perfectly good Java financial library?
>>
>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<[email protected]> wrote:
>>
>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>
>>> That's interesting - it no longer comes up in the classes list on
>>>> http://poi.apache.org/apidocs/index.html
>>>> I wonder if the direct page link that you can find via a Google search
>>>> is
>>>> an
>>>> orphaned page.
>>>>
>>>>
>>>> I updated the online apidocs to reflect current POI trunk.
>>>
>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>
>>>> defined as public:
>>>> final class FinanceLib {...
>>>>
>>>>
>>>>
>>>>http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>
>>>>a
>>>>
>>>> I think this is why I can't access any methods out of it even though the
>>>> methods themselves are declared as public.
>>>>
>>>>
>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>> only. This class can be renamed / re-packaged in future versions of POI
>>> or
>>> access
>>> to its methods can be changed from 'public' to 'default' or less.
>>>
>>>
>>> Yegor
>>>
>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>
>>>> declared as a public class. Can it be reverted to public again?
>>>>
>>>> Thanks
>>>>
>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<[email protected]>
>>>> wrote:
>>>>
>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>> in
>>>>
>>>>> the
>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>> available
>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>
>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>
>>>>> fine:
>>>>>
>>>>>
>>>>>
>>>>>http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>
>>>>>l
>>>>>
>>>>> Note that in 3.8, the package will change to
>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>
>>>>> Nick
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: [email protected]
>>>>> For additional commands, e-mail: [email protected]
>>>>>
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [email protected]
>>> For additional commands, e-mail: [email protected]
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
NPVTestCase.xls
Description: MS-Excel spreadsheet
--------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
