Some additional information: If I enumerate the values in the area in the formula, the evaluation works (=NPV(B5,B1,B2,B3,B4) ). However the result doesn't match what Excel has.
Thanks, Jon ----- Original Message ---- From: Jon Svede <[email protected]> To: POI Users List <[email protected]> Sent: Tue, December 7, 2010 9:44:40 AM Subject: NPV bug? 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] --------------------------------------------------------------------- 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]
