You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2008/03/27 22:54:23 UTC

svn commit: r641996 - in /poi/trunk/src/scratchpad: src/org/apache/poi/hssf/record/formula/functions/ testcases/org/apache/poi/hssf/record/formula/functions/ testcases/org/apache/poi/hssf/usermodel/

Author: josh
Date: Thu Mar 27 14:54:21 2008
New Revision: 641996

URL: http://svn.apache.org/viewvc?rev=641996&view=rev
Log:
(real) fix for  bug #44691. Modified Pmt.java to allow for 3 arguments.  Added TestPmt junit.

Added:
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java
Removed:
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44691.java
Modified:
    poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Pmt.java
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java

Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Pmt.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Pmt.java?rev=641996&r1=641995&r2=641996&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Pmt.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Pmt.java Thu Mar 27 14:54:21 2008
@@ -14,61 +14,78 @@
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
-/*
- * Created on May 15, 2005
- *
- */
 package org.apache.poi.hssf.record.formula.functions;
 
 import org.apache.poi.hssf.record.formula.eval.BoolEval;
 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
 import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.EvaluationException;
 import org.apache.poi.hssf.record.formula.eval.NumberEval;
 import org.apache.poi.hssf.record.formula.eval.NumericValueEval;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
 
-public class Pmt extends FinanceFunction {
+/**
+ * Implementation for the PMT() Excel function.<p/>
+ * 
+ * <b>Syntax:</b><br/>
+ * <b>PMT</b>(<b>rate</b>, <b>nper</b>, <b>pv</b>, fv, type)<p/>
+ * 
+ * Returns the constant repayment amount required for a loan assuming a constant interest rate.<p/>
+ * 
+ * <b>rate</b> the loan interest rate.<br/>
+ * <b>nper</b> the number of loan repayments.<br/>
+ * <b>pv</b> the present value of the future payments (or principle).<br/>
+ * <b>fv</b> the future value (default zero) surplus cash at the end of the loan lifetime.<br/>
+ * <b>type</b> whether payments are due at the beginning(1) or end(0 - default) of each payment period.<br/>
+ * 
+ */
+public final class Pmt extends FinanceFunction {
+
+	public Eval evaluate(Eval[] args, int srcRow, short srcCol) {
+		
+		if(args.length < 3 || args.length > 5) {
+			return ErrorEval.VALUE_INVALID;
+		}
+		
+ 		try {
+ 			// evaluate first three (always present) args
+			double rate = evalArg(args[0], srcRow, srcCol);
+			double nper = evalArg(args[1], srcRow, srcCol);
+			double pv  = evalArg(args[2], srcRow, srcCol); 
+			double fv = 0;
+			boolean arePaymentsAtPeriodBeginning = false;
+			
+			switch (args.length) {
+				case 5:
+					ValueEval ve = singleOperandNumericAsBoolean(args[4], srcRow, srcCol);
+					if (ve instanceof ErrorEval) { 
+						return ve;
+					}
+					arePaymentsAtPeriodBeginning = ((BoolEval) ve).getBooleanValue();
+				case 4:
+					fv = evalArg(args[3], srcRow, srcCol);
+			}
+			double d = FinanceLib.pmt(rate, nper, pv, fv, arePaymentsAtPeriodBeginning);
+			if (Double.isNaN(d)) {
+				return (ValueEval) ErrorEval.VALUE_INVALID;
+			}
+			if (Double.isInfinite(d)) {
+				return (ValueEval) ErrorEval.NUM_ERROR;
+			}
+			return new NumberEval(d);
+		} catch (EvaluationException e) {
+			return e.getErrorEval();
+		}
+	}
 
-    public Eval evaluate(Eval[] operands, int srcRow, short srcCol) {
-        double rate = 0, fv = 0, nper = 0, pv = 0, d = 0;
-        boolean type = false;
-        ValueEval retval = null;
-        ValueEval ve = null;
-        
-        switch (operands.length) {
-        default:
-            retval = ErrorEval.VALUE_INVALID;
-            break;
-        case 5:
-            ve = singleOperandNumericAsBoolean(operands[4], srcRow, srcCol);
-            if (ve instanceof ErrorEval) { retval = ErrorEval.VALUE_INVALID; break; }
-            type = ((BoolEval) ve).getBooleanValue();
-        case 4:
-            ve = singleOperandEvaluate(operands[0], srcRow, srcCol);
-            if (ve instanceof NumericValueEval) rate = ((NumericValueEval) ve).getNumberValue();
-            else { retval = ErrorEval.VALUE_INVALID; break; }
-            
-            ve = singleOperandEvaluate(operands[1], srcRow, srcCol);
-            if (ve instanceof NumericValueEval) nper  = ((NumericValueEval) ve).getNumberValue();
-            else { retval = ErrorEval.VALUE_INVALID; break; }
-            
-            ve = singleOperandEvaluate(operands[2], srcRow, srcCol);
-            if (ve instanceof NumericValueEval) pv   = ((NumericValueEval) ve).getNumberValue();
-            else { retval = ErrorEval.VALUE_INVALID; break; }
-            
-            ve = singleOperandEvaluate(operands[3], srcRow, srcCol);
-            if (ve instanceof NumericValueEval) fv = ((NumericValueEval) ve).getNumberValue();
-            else { retval = ErrorEval.VALUE_INVALID; break; }
-        }
-        
-        if (retval == null) {
-            d = FinanceLib.pmt(rate, nper, pv, fv, type);
-            retval = (Double.isNaN(d))
-                    ? (ValueEval) ErrorEval.VALUE_INVALID
-                    : (Double.isInfinite(d)) 
-                        ? (ValueEval) ErrorEval.NUM_ERROR 
-                        : new NumberEval(d);
-        }
-        return retval;
-    }
+	private double evalArg(Eval arg, int srcRow, short srcCol) throws EvaluationException {
+		ValueEval ve = singleOperandEvaluate(arg, srcRow, srcCol);
+		if(ve instanceof ErrorEval) {
+			throw new EvaluationException((ErrorEval) ve);
+		}
+		if (ve instanceof NumericValueEval) {
+			return ((NumericValueEval) ve).getNumberValue();
+		}
+		throw new EvaluationException(ErrorEval.VALUE_INVALID); 
+	}
 }

Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java?rev=641996&r1=641995&r2=641996&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java (original)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/AllIndividualFunctionEvaluationTests.java Thu Mar 27 14:54:21 2008
@@ -14,7 +14,6 @@
    See the License for the specific language governing permissions and
    limitations under the License.
 ==================================================================== */
-        
 
 package org.apache.poi.hssf.record.formula.functions;
 
@@ -41,6 +40,7 @@
 		result.addTestSuite(TestMid.class);
 		result.addTestSuite(TestMathX.class);
 		result.addTestSuite(TestMatch.class);
+		result.addTestSuite(TestPmt.class);
 		result.addTestSuite(TestOffset.class);
 		result.addTestSuite(TestRowCol.class);
 		result.addTestSuite(TestSumproduct.class);
@@ -50,5 +50,4 @@
 		result.addTestSuite(TestXYNumericFunction.class);
 		return result;
 	}
-
 }

Added: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java?rev=641996&view=auto
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java (added)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/record/formula/functions/TestPmt.java Thu Mar 27 14:54:21 2008
@@ -0,0 +1,87 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.hssf.record.formula.functions;
+
+import junit.framework.AssertionFailedError;
+import junit.framework.TestCase;
+
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.Eval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.usermodel.HSSFErrorConstants;
+
+/**
+ * 
+ * @author Josh Micich
+ */
+public final class TestPmt extends TestCase {
+	
+	private static void confirm(double expected, NumberEval ne) {
+		// only asserting accuracy to 4 fractional digits
+		assertEquals(expected, ne.getNumberValue(), 0.00005);
+	}
+	private static Eval invoke(Eval[] args) {
+		return new Pmt().evaluate(args, -1, (short)-1);
+	}
+	/**
+	 * Invocation when not expecting an error result
+	 */
+	private static NumberEval invokeNormal(Eval[] args) {
+		Eval ev = invoke(args);
+		if(ev instanceof ErrorEval) {
+			throw new AssertionFailedError("Normal evaluation failed with error code: "
+					+ ev.toString());
+		}
+		return (NumberEval) ev;
+	}
+
+	private static void confirm(double expected, double rate, double nper, double pv, double fv, boolean isBeginning) {
+		Eval[] args = { 
+				new NumberEval(rate),
+				new NumberEval(nper),
+				new NumberEval(pv),
+				new NumberEval(fv),
+				new NumberEval(isBeginning ? 1 : 0),
+		};
+		confirm(expected, invokeNormal(args));
+	}
+
+	
+	public void testBasic() {
+		confirm(-1037.0321, (0.08/12), 10, 10000, 0, false);
+		confirm(-1030.1643, (0.08/12), 10, 10000, 0, true);
+	}
+	
+	public void test3args() {
+		
+		Eval[] args = { 
+				new NumberEval(0.005),
+				new NumberEval(24),
+				new NumberEval(1000),
+		};
+		Eval ev = invoke(args);
+		if(ev instanceof ErrorEval) {
+			ErrorEval err = (ErrorEval) ev;
+			if(err.getErrorCode() == HSSFErrorConstants.ERROR_VALUE) {
+				throw new AssertionFailedError("Identified bug 44691");
+			}
+		}
+		
+		confirm(-44.3206, invokeNormal(args));
+	}
+}



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org