poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r1451886 - in /poi/trunk: src/java/org/apache/poi/ss/formula/eval/ src/java/org/apache/poi/ss/formula/functions/ src/testcases/org/apache/poi/ss/formula/functions/ test-data/spreadsheet/
Date Sat, 02 Mar 2013 13:17:53 GMT
Author: yegor
Date: Sat Mar  2 13:17:53 2013
New Revision: 1451886

URL: http://svn.apache.org/r1451886
Log:
Bugzilla 54469 - Support for financial functions IPMT and PPMT

Added:
    poi/trunk/src/java/org/apache/poi/ss/formula/functions/Finance.java
    poi/trunk/src/java/org/apache/poi/ss/formula/functions/IPMT.java
    poi/trunk/src/java/org/apache/poi/ss/formula/functions/PPMT.java
    poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIPMT.java
      - copied, changed from r1451872, poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java
    poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestPPMT.java
    poi/trunk/test-data/spreadsheet/finance.xls   (with props)
Modified:
    poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
    poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls

Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1451886&r1=1451885&r2=1451886&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java Sat Mar  2 13:17:53
2013
@@ -161,6 +161,8 @@ public final class FunctionEval {
 
 		retval[ID.INDIRECT] = null; // Indirect.evaluate has different signature
         retval[162] = TextFunction.CLEAN;  //Aniket Banerjee    
+        retval[167] = new IPMT();
+        retval[168] = new PPMT();
 		retval[169] = new Counta();
 
 		retval[183] = AggregateFunction.PRODUCT;

Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Finance.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Finance.java?rev=1451886&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Finance.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Finance.java Sat Mar  2 13:17:53
2013
@@ -0,0 +1,155 @@
+package org.apache.poi.ss.formula.functions;
+
+
+/**
+  * Implementation of the financial functions pmt, fv, ppmt, ipmt.
+  * 
+  * @author Mike Argyriou micharg@gmail.com
+  */
+public class Finance {
+
+	/**
+     * Emulates Excel/Calc's PMT(interest_rate, number_payments, PV, FV, Type)
+     * function, which calculates the payments for a loan or the future value of an investment
+     * 
+     * @param r
+     *            - periodic interest rate represented as a decimal.
+     * @param nper
+     *            - number of total payments / periods.
+     * @param pv
+     *            - present value -- borrowed or invested principal.
+     * @param fv
+     *            - future value of loan or annuity.
+     * @param type
+     *            - when payment is made: beginning of period is 1; end, 0.
+     * @return <code>double</code> representing periodic payment amount.
+     */
+	// http://arachnoid.com/lutusp/finance.html
+	static public double pmt(double r, int nper, double pv, double fv, int type) {
+	    double pmt = -r * (pv * Math.pow(1 + r, nper) + fv) / ((1 + r*type) * (Math.pow(1 +
r, nper) - 1));
+	    return pmt;
+	}
+
+
+	/**
+     * Overloaded pmt() call omitting type, which defaults to 0.
+     * 
+     * @see #pmt(double, int, double, double, int)
+     */
+	static public double pmt(double r, int nper, double pv, double fv) {
+	    return pmt(r, nper, pv, fv, 0);
+	}
+	
+	/**
+     * Overloaded pmt() call omitting fv and type, which both default to 0.
+     * 
+     * @see #pmt(double, int, double, double, int)
+     */
+	static public double pmt(double r, int nper, double pv) {
+	    return pmt(r, nper, pv, 0);
+	}
+	
+	
+	/**
+     * Emulates Excel/Calc's IPMT(interest_rate, period, number_payments, PV,
+     * FV, Type) function, which calculates the portion of the payment at a
+     * given period that is the interest on previous balance.
+     * 
+     * @param r
+     *            - periodic interest rate represented as a decimal.
+     * @param per
+     *            - period (payment number) to check value at.
+     * @param nper
+     *            - number of total payments / periods.
+     * @param pv
+     *            - present value -- borrowed or invested principal.
+     * @param fv
+     *            - future value of loan or annuity.
+     * @param type
+     *            - when payment is made: beginning of period is 1; end, 0.
+     * @return <code>double</code> representing interest portion of payment.
+     * 
+     * @see #pmt(double, int, double, double, int)
+     * @see #fv(double, int, double, double, int)
+     */
+	// http://doc.optadata.com/en/dokumentation/application/expression/functions/financial.html
+	static public double ipmt(double r, int per, int nper, double pv, double fv, int type) {
+	    double ipmt = fv(r, per - 1, pmt(r, nper, pv, fv, type), pv, type) * r;
+	    if (type==1) ipmt /= (1 + r);
+	    return ipmt;
+	}
+	
+	static public double ipmt(double r, int per, int nper, double pv, double fv) {
+		return ipmt(r, per, nper, pv, fv, 0);
+	}
+	
+	static public double ipmt(double r, int per, int nper, double pv) {
+		return ipmt(r, per, nper, pv, 0);
+	}
+	 
+	/**
+     * Emulates Excel/Calc's PPMT(interest_rate, period, number_payments, PV,
+     * FV, Type) function, which calculates the portion of the payment at a
+     * given period that will apply to principal.
+     * 
+     * @param r
+     *            - periodic interest rate represented as a decimal.
+     * @param per
+     *            - period (payment number) to check value at.
+     * @param nper
+     *            - number of total payments / periods.
+     * @param pv
+     *            - present value -- borrowed or invested principal.
+     * @param fv
+     *            - future value of loan or annuity.
+     * @param type
+     *            - when payment is made: beginning of period is 1; end, 0.
+     * @return <code>double</code> representing principal portion of payment.
+     * 
+     * @see #pmt(double, int, double, double, int)
+     * @see #ipmt(double, int, int, double, double, boolean)
+     */
+	static public double ppmt(double r, int per, int nper, double pv, double fv, int type) {
+	    return pmt(r, nper, pv, fv, type) - ipmt(r, per, nper, pv, fv, type);
+	}
+	
+	static public double ppmt(double r, int per, int nper, double pv, double fv) {
+	    return pmt(r, nper, pv, fv) - ipmt(r, per, nper, pv, fv);
+	}
+	
+	static public double ppmt(double r, int per, int nper, double pv) {
+	    return pmt(r, nper, pv) - ipmt(r, per, nper, pv);
+	}
+	
+    /**
+     * Emulates Excel/Calc's FV(interest_rate, number_payments, payment, PV,
+     * Type) function, which calculates future value or principal at period N.
+     * 
+     * @param r
+     *            - periodic interest rate represented as a decimal.
+     * @param nper
+     *            - number of total payments / periods.
+     * @param pmt
+     *            - periodic payment amount.
+     * @param pv
+     *            - present value -- borrowed or invested principal.
+     * @param type
+     *            - when payment is made: beginning of period is 1; end, 0.
+     * @return <code>double</code> representing future principal value.
+     */
+	//http://en.wikipedia.org/wiki/Future_value
+	static public double fv(double r, int nper, double pmt, double pv, int type) {
+	    double fv = -(pv * Math.pow(1 + r, nper) + pmt * (1+r*type) * (Math.pow(1 + r, nper)
- 1) / r);
+	    return fv;
+	}
+	
+	/**
+     * Overloaded fv() call omitting type, which defaults to 0.
+     * 
+     * @see #fv(double, int, double, double, int)
+     */
+	static public double fv(double r, int nper, double c, double pv) {
+		return fv(r, nper, c, pv, 0);
+	}
+}
+

Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/IPMT.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/IPMT.java?rev=1451886&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/IPMT.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/IPMT.java Sat Mar  2 13:17:53 2013
@@ -0,0 +1,56 @@
+/*
+ *  ====================================================================
+ *    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.ss.formula.functions;
+
+import org.apache.poi.ss.formula.eval.ErrorEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.OperandResolver;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+public class IPMT extends NumericFunction {
+
+	@Override
+	public double eval(ValueEval[] args, int srcCellRow, int srcCellCol) throws EvaluationException
{
+   		
+		if(args.length != 4)
+        		throw new EvaluationException(ErrorEval.VALUE_INVALID);
+
+		double result;
+
+		ValueEval v1 = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol); 
+		ValueEval v2 = OperandResolver.getSingleValue(args[1], srcCellRow, srcCellCol); 
+		ValueEval v3 = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol); 
+		ValueEval v4 = OperandResolver.getSingleValue(args[3], srcCellRow, srcCellCol); 
+
+		double interestRate = OperandResolver.coerceValueToDouble(v1);
+		int period = OperandResolver.coerceValueToInt(v2);
+		int numberPayments = OperandResolver.coerceValueToInt(v3);
+		double PV = OperandResolver.coerceValueToDouble(v4);
+
+		result = Finance.ipmt(interestRate, period, numberPayments, PV) ;
+
+		checkValue(result);
+		
+		return result;
+	}
+
+	
+
+}

Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/PPMT.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/PPMT.java?rev=1451886&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/PPMT.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/PPMT.java Sat Mar  2 13:17:53 2013
@@ -0,0 +1,60 @@
+/*
+ *  ====================================================================
+ *    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.ss.formula.functions;
+
+import org.apache.poi.ss.formula.*;
+import org.apache.poi.ss.formula.functions.*;
+import org.apache.poi.ss.formula.eval.*;
+
+/**
+  * Compute the interest portion of a payment.
+  * 
+  * @author Mike Argyriou micharg@gmail.com
+  */
+public class PPMT extends NumericFunction {
+
+	@Override
+	public double eval(ValueEval[] args, int srcCellRow, int srcCellCol) throws EvaluationException
{
+
+		if(args.length < 4)
+        		throw new EvaluationException(ErrorEval.VALUE_INVALID);
+
+		double result;
+
+		ValueEval v1 = OperandResolver.getSingleValue(args[0], srcCellRow, srcCellCol);
+                ValueEval v2 = OperandResolver.getSingleValue(args[1], srcCellRow, srcCellCol);
+                ValueEval v3 = OperandResolver.getSingleValue(args[2], srcCellRow, srcCellCol);
+                ValueEval v4 = OperandResolver.getSingleValue(args[3], srcCellRow, srcCellCol);
+
+		double interestRate = OperandResolver.coerceValueToDouble(v1);
+                int period = OperandResolver.coerceValueToInt(v2);
+                int numberPayments = OperandResolver.coerceValueToInt(v3);
+                double PV = OperandResolver.coerceValueToDouble(v4);
+
+		result = Finance.ppmt(interestRate, period, numberPayments, PV) ;
+
+		checkValue(result);
+		
+		return result;
+	}
+
+	
+
+}

Copied: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIPMT.java (from r1451872,
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java)
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIPMT.java?p2=poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIPMT.java&p1=poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java&r1=1451872&r2=1451886&rev=1451886&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestSumifs.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestIPMT.java Sat Mar  2 13:17:53
2013
@@ -22,246 +22,36 @@ package org.apache.poi.ss.formula.functi
 import junit.framework.AssertionFailedError;
 import junit.framework.TestCase;
 import org.apache.poi.hssf.HSSFTestDataSamples;
-import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.formula.OperationEvaluationContext;
 import org.apache.poi.ss.formula.eval.*;
 
 /**
- * Test cases for SUMIFS()
+ * Test cases for IPMT()
  *
- * @author Yegor Kozlov
  */
-public final class TestSumifs extends TestCase {
+public final class TestIPMT extends TestCase {
 
-    private static final OperationEvaluationContext EC = new OperationEvaluationContext(null,
null, 0, 1, 0, null);
-
-	private static ValueEval invokeSumifs(ValueEval[] args, OperationEvaluationContext ec) {
-		return new Sumifs().evaluate(args, EC);
-	}
-	private static void confirmDouble(double expected, ValueEval actualEval) {
-		if(!(actualEval instanceof NumericValueEval)) {
-			throw new AssertionFailedError("Expected numeric result");
-		}
-		NumericValueEval nve = (NumericValueEval)actualEval;
-		assertEquals(expected, nve.getNumberValue(), 0);
-	}
-
-    private static void confirm(double expectedResult, ValueEval[] args) {
-        confirmDouble(expectedResult, invokeSumifs(args, EC));
-    }
 
     /**
-     *  Example 1 from
-     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
-     */
-	public void testExample1() {
-        // mimic test sample from http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
-        ValueEval[] a2a9 = new ValueEval[] {
-                new NumberEval(5),
-                new NumberEval(4),
-                new NumberEval(15),
-                new NumberEval(3),
-                new NumberEval(22),
-                new NumberEval(12),
-                new NumberEval(10),
-                new NumberEval(33)
-        };
-
-        ValueEval[] b2b9 = new ValueEval[] {
-                new StringEval("Apples"),
-                new StringEval("Apples"),
-                new StringEval("Artichokes"),
-                new StringEval("Artichokes"),
-                new StringEval("Bananas"),
-                new StringEval("Bananas"),
-                new StringEval("Carrots"),
-                new StringEval("Carrots"),
-        };
-
-        ValueEval[] c2c9 = new ValueEval[] {
-                new NumberEval(1),
-                new NumberEval(2),
-                new NumberEval(1),
-                new NumberEval(2),
-                new NumberEval(1),
-                new NumberEval(2),
-                new NumberEval(1),
-                new NumberEval(2)
-        };
-
-        ValueEval[] args;
-        // "=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1)"
-        args = new ValueEval[]{
-                EvalFactory.createAreaEval("A2:A9", a2a9),
-                EvalFactory.createAreaEval("B2:B9", b2b9),
-                new StringEval("A*"),
-                EvalFactory.createAreaEval("C2:C9", c2c9),
-                new NumberEval(1),
-        };
-        confirm(20.0, args);
-
-        // "=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1)"
-        args = new ValueEval[]{
-                EvalFactory.createAreaEval("A2:A9", a2a9),
-                EvalFactory.createAreaEval("B2:B9", b2b9),
-                new StringEval("<>Bananas"),
-                EvalFactory.createAreaEval("C2:C9", c2c9),
-                new NumberEval(1),
-        };
-        confirm(30.0, args);
-
-        // a test case that returns ErrorEval.VALUE_INVALID :
-        // the dimensions of the first and second criteria ranges are different
-        // "=SUMIFS(A2:A9, B2:B8, "<>Bananas", C2:C9, 1)"
-        args = new ValueEval[]{
-                EvalFactory.createAreaEval("A2:A9", a2a9),
-                EvalFactory.createAreaEval("B2:B8", new ValueEval[] {
-                        new StringEval("Apples"),
-                        new StringEval("Apples"),
-                        new StringEval("Artichokes"),
-                        new StringEval("Artichokes"),
-                        new StringEval("Bananas"),
-                        new StringEval("Bananas"),
-                        new StringEval("Carrots"),
-                }),
-                new StringEval("<>Bananas"),
-                EvalFactory.createAreaEval("C2:C9", c2c9),
-                new NumberEval(1),
-        };
-        assertEquals(ErrorEval.VALUE_INVALID, invokeSumifs(args, EC));
-
-	}
-
-    /**
-     *  Example 2 from
-     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
-     */
-    public void testExample2() {
-        ValueEval[] b2e2 = new ValueEval[] {
-                new NumberEval(100),
-                new NumberEval(390),
-                new NumberEval(8321),
-                new NumberEval(500)
-        };
-        // 1%	0.5%	3%	4%
-        ValueEval[] b3e3 = new ValueEval[] {
-                new NumberEval(0.01),
-                new NumberEval(0.005),
-                new NumberEval(0.03),
-                new NumberEval(0.04)
-        };
-
-        // 1%	1.3%	2.1%	2%
-        ValueEval[] b4e4 = new ValueEval[] {
-                new NumberEval(0.01),
-                new NumberEval(0.013),
-                new NumberEval(0.021),
-                new NumberEval(0.02)
-        };
-
-        // 0.5%	3%	1%	4%
-        ValueEval[] b5e5 = new ValueEval[] {
-                new NumberEval(0.005),
-                new NumberEval(0.03),
-                new NumberEval(0.01),
-                new NumberEval(0.04)
-        };
-
-        ValueEval[] args;
-
-        // "=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%")"
-        args = new ValueEval[]{
-                EvalFactory.createAreaEval("B2:E2", b2e2),
-                EvalFactory.createAreaEval("B3:E3", b3e3),
-                new StringEval(">0.03"), // 3% in the MSFT example
-                EvalFactory.createAreaEval("B4:E4", b4e4),
-                new StringEval(">=0.02"),   // 2% in the MSFT example
-        };
-        confirm(500.0, args);
-    }
-
-    /**
-     *  Example 3 from
-     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
-     */
-    public void testExample3() {
-        //3.3	0.8	5.5	5.5
-        ValueEval[] b2e2 = new ValueEval[] {
-                new NumberEval(3.3),
-                new NumberEval(0.8),
-                new NumberEval(5.5),
-                new NumberEval(5.5)
-        };
-        // 55	39	39	57.5
-        ValueEval[] b3e3 = new ValueEval[] {
-                new NumberEval(55),
-                new NumberEval(39),
-                new NumberEval(39),
-                new NumberEval(57.5)
-        };
-
-        // 6.5	19.5	6	6.5
-        ValueEval[] b4e4 = new ValueEval[] {
-                new NumberEval(6.5),
-                new NumberEval(19.5),
-                new NumberEval(6),
-                new NumberEval(6.5)
-        };
-
-        ValueEval[] args;
-
-        // "=SUMIFS(B2:E2, B3:E3, ">=40", B4:E4, "<10")"
-        args = new ValueEval[]{
-                EvalFactory.createAreaEval("B2:E2", b2e2),
-                EvalFactory.createAreaEval("B3:E3", b3e3),
-                new StringEval(">=40"),
-                EvalFactory.createAreaEval("B4:E4", b4e4),
-                new StringEval("<10"),
-        };
-        confirm(8.8, args);
-    }
-
-    /**
-     *  Example 5 from
-     *  http://office.microsoft.com/en-us/excel-help/sumifs-function-HA010047504.aspx
-     *
-     *  Criteria entered as reference and by using wildcard characters
+     *  from http://office.microsoft.com/en-001/excel-help/ipmt-HP005209145.aspx
      */
     public void testFromFile() {
 
-        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("sumifs.xls");
+        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("finance.xls");
         HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
 
-        HSSFSheet example1 = wb.getSheet("Example 1");
-        HSSFCell ex1cell1 = example1.getRow(10).getCell(2);
+        HSSFSheet example1 = wb.getSheet("IPMT");
+        HSSFCell ex1cell1 = example1.getRow(6).getCell(0);
         fe.evaluate(ex1cell1);
-        assertEquals(20.0, ex1cell1.getNumericCellValue());
-        HSSFCell ex1cell2 = example1.getRow(11).getCell(2);
-        fe.evaluate(ex1cell2);
-        assertEquals(30.0, ex1cell2.getNumericCellValue());
+        assertEquals(-22.41, ex1cell1.getNumericCellValue(), 0.1);
 
-        HSSFSheet example2 = wb.getSheet("Example 2");
-        HSSFCell ex2cell1 = example2.getRow(6).getCell(2);
-        fe.evaluate(ex2cell1);
-        assertEquals(500.0, ex2cell1.getNumericCellValue());
-        HSSFCell ex2cell2 = example2.getRow(7).getCell(2);
-        fe.evaluate(ex2cell2);
-        assertEquals(8711.0, ex2cell2.getNumericCellValue());
-
-        HSSFSheet example3 = wb.getSheet("Example 3");
-        HSSFCell ex3cell = example3.getRow(5).getCell(2);
-        fe.evaluate(ex3cell);
-        assertEquals(8,8, ex3cell.getNumericCellValue());
-
-        HSSFSheet example4 = wb.getSheet("Example 4");
-        HSSFCell ex4cell = example4.getRow(8).getCell(2);
-        fe.evaluate(ex4cell);
-        assertEquals(3.5, ex4cell.getNumericCellValue());
-
-        HSSFSheet example5 = wb.getSheet("Example 5");
-        HSSFCell ex5cell = example5.getRow(8).getCell(2);
-        fe.evaluate(ex5cell);
-        assertEquals(625000., ex5cell.getNumericCellValue());
+        HSSFCell ex1cell2 = example1.getRow(7).getCell(0);
+        fe.evaluate(ex1cell2);
+        assertEquals(-292.45, ex1cell2.getNumericCellValue(), 0.1);
 
     }
 }

Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestPPMT.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestPPMT.java?rev=1451886&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestPPMT.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestPPMT.java Sat Mar  2 13:17:53
2013
@@ -0,0 +1,54 @@
+/*
+ *  ====================================================================
+ *    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.ss.formula.functions;
+
+import junit.framework.TestCase;
+import org.apache.poi.hssf.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+
+/**
+ * Test cases for PPMT()
+ *
+ */
+public final class TestPPMT extends TestCase {
+
+
+    /**
+     *  http://office.microsoft.com/en-001/excel-help/ppmt-function-HP010342774.aspx
+     */
+    public void testFromFile() {
+
+        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("finance.xls");
+        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+
+        HSSFSheet example1 = wb.getSheet("PPMT");
+        HSSFCell ex1cell1 = example1.getRow(5).getCell(0);
+        fe.evaluate(ex1cell1);
+        assertEquals(-75.62, ex1cell1.getNumericCellValue(), 0.1);
+
+        HSSFCell ex1cell2 = example1.getRow(16).getCell(0);
+        fe.evaluate(ex1cell2);
+        assertEquals(-27598.05, ex1cell2.getNumericCellValue(), 0.1);
+
+    }
+}

Modified: poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/FormulaEvalTestData.xls?rev=1451886&r1=1451885&r2=1451886&view=diff
==============================================================================
Binary files - no diff available.

Added: poi/trunk/test-data/spreadsheet/finance.xls
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/finance.xls?rev=1451886&view=auto
==============================================================================
Binary file - no diff available.

Propchange: poi/trunk/test-data/spreadsheet/finance.xls
------------------------------------------------------------------------------
    svn:mime-type = application/vnd.ms-excel



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


Mime
View raw message