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