From 6d5e89298648e480c29747ba878cc0ea35f263a3 Mon Sep 17 00:00:00 2001
From: Dominik Stadler <centic@apache.org>
Date: Thu, 1 Oct 2015 14:05:28 +0000
Subject: [PATCH] Bug 55032: Fix handling missing option values in financial
 functions PV, FV, NPER and PMT

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1706254 13f79535-47bb-0310-9956-ffa450edef68
---
 .../ss/formula/functions/FinanceFunction.java | 23 ++++++++++---
 .../poi/ss/formula/eval/TestFormulaBugs.java  | 33 +++++++++++++++++++
 2 files changed, 52 insertions(+), 4 deletions(-)

diff --git a/src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java b/src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java
index def0acc344..939d143a85 100644
--- a/src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java
+++ b/src/java/org/apache/poi/ss/formula/functions/FinanceFunction.java
@@ -20,6 +20,7 @@ package org.apache.poi.ss.formula.functions;
 import org.apache.poi.ss.formula.eval.BoolEval;
 import org.apache.poi.ss.formula.eval.ErrorEval;
 import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.MissingArgEval;
 import org.apache.poi.ss.formula.eval.NumberEval;
 import org.apache.poi.ss.formula.eval.ValueEval;
 
@@ -63,10 +64,24 @@ public abstract class FinanceFunction implements Function3Arg, Function4Arg {
 		switch (args.length) {
 			case 3:
 				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], DEFAULT_ARG3, DEFAULT_ARG4);
-			case 4:
-				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3], DEFAULT_ARG4);
-			case 5:
-				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], args[3], args[4]);
+			case 4: {
+			    ValueEval arg3 = args[3];
+			    if(arg3 == MissingArgEval.instance) {
+			        arg3 = DEFAULT_ARG3;
+			    }
+				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], arg3, DEFAULT_ARG4);
+			}
+			case 5: {
+                ValueEval arg3 = args[3];
+                if(arg3 == MissingArgEval.instance) {
+                    arg3 = DEFAULT_ARG3;
+                }
+                ValueEval arg4 = args[4];
+                if(arg4 == MissingArgEval.instance) {
+                    arg4 = DEFAULT_ARG4;
+                }
+				return evaluate(srcRowIndex, srcColumnIndex, args[0], args[1], args[2], arg3, arg4);
+			}
 		}
 		return ErrorEval.VALUE_INVALID;
 	}
diff --git a/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java b/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java
index f66cd06595..21a694dfe7 100644
--- a/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java
+++ b/src/testcases/org/apache/poi/ss/formula/eval/TestFormulaBugs.java
@@ -29,7 +29,12 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
 import org.apache.poi.hssf.usermodel.HSSFRow;
 import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellValue;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
 import org.junit.Test;
 
 /**
@@ -176,4 +181,32 @@ public final class TestFormulaBugs {
 			double value) {
 		sheet.createRow(rowIx).createCell(colIx).setCellValue(value);
 	}
+
+	@Test
+	public void test55032() throws IOException {
+        Workbook wb = new HSSFWorkbook();
+        Sheet sheet = wb.createSheet("input");
+
+        Row row = sheet.createRow(0);
+        Cell cell = row.createCell(1);
+
+        checkFormulaValue(wb, cell, "PV(0.08/12, 20*12, 500, ,0)", -59777.14585);
+        checkFormulaValue(wb, cell, "PV(0.08/12, 20*12, 500, ,)", -59777.14585);
+        checkFormulaValue(wb, cell, "PV(0.08/12, 20*12, 500, 500,)", -59878.6315455);
+        
+        checkFormulaValue(wb, cell, "FV(0.08/12, 20*12, 500, ,)", -294510.2078107270);
+        checkFormulaValue(wb, cell, "PMT(0.08/12, 20*12, 500, ,)", -4.1822003450);
+        checkFormulaValue(wb, cell, "NPER(0.08/12, 20*12, 500, ,)", -2.0758873434);
+
+        wb.close();
+	}
+
+    private void checkFormulaValue(Workbook wb, Cell cell, String formula, double expectedValue) {
+        cell.setCellFormula(formula);
+	    
+        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
+        CellValue value = evaluator.evaluate(cell);
+        
+        assertEquals(expectedValue, value.getNumberValue(), 0.0001);
+    }
 }
-- 
2.39.5