Index: src/java/org/apache/poi/ss/formula/functions/Npv.java =================================================================== --- src/java/org/apache/poi/ss/formula/functions/Npv.java (revision 1043675) +++ src/java/org/apache/poi/ss/formula/functions/Npv.java (working copy) @@ -17,6 +17,7 @@ package org.apache.poi.ss.formula.functions; +import org.apache.poi.ss.formula.TwoDEval; import org.apache.poi.ss.formula.eval.ErrorEval; import org.apache.poi.ss.formula.eval.EvaluationException; import org.apache.poi.ss.formula.eval.NumberEval; @@ -30,66 +31,30 @@ * income. * * @author SPetrakovsky + * @author Marcel May */ -public final class Npv implements Function2Arg, Function3Arg, Function4Arg { +public final class Npv implements Function { - - public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { - double result; - try { - double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); - double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); - result = evaluate(rate, d1); - NumericFunction.checkValue(result); - } catch (EvaluationException e) { - return e.getErrorEval(); - } - return new NumberEval(result); - } - public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, - ValueEval arg2) { - double result; - try { - double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); - double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); - double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex); - result = evaluate(rate, d1, d2); - NumericFunction.checkValue(result); - } catch (EvaluationException e) { - return e.getErrorEval(); - } - return new NumberEval(result); - } - public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1, - ValueEval arg2, ValueEval arg3) { - double result; - try { - double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex); - double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex); - double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex); - double d3 = NumericFunction.singleOperandEvaluate(arg3, srcRowIndex, srcColumnIndex); - result = evaluate(rate, d1, d2, d3); - NumericFunction.checkValue(result); - } catch (EvaluationException e) { - return e.getErrorEval(); - } - return new NumberEval(result); - } - public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { int nArgs = args.length; if (nArgs<2) { return ErrorEval.VALUE_INVALID; } - int np = nArgs-1; - double[] ds = new double[np]; - double result; - try { + double result; + try { + double[] ds; + if (2==nArgs && args[1] instanceof TwoDEval) { + // eg A4:A10 + ds = extractNumbers((TwoDEval) args[1]); + } else { + // eg A4,A5,... + ds = new double[args.length-1]; + for (int i = 0; i < ds.length; i++) { + ds[i] = NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex); + } + } double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex); - for (int i = 0; i < ds.length; i++) { - ds[i] = NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex); - } - result = evaluate(rate, ds); + result = FinanceLib.npv(rate, ds); NumericFunction.checkValue(result); } catch (EvaluationException e) { return e.getErrorEval(); @@ -97,11 +62,23 @@ return new NumberEval(result); } - private static double evaluate(double rate, double...ds) { - double sum = 0; - for (int i = 0; i < ds.length; i++) { - sum += ds[i] / Math.pow(rate + 1, i); - } - return sum; - } + private double[] extractNumbers(final TwoDEval pArg) { + // Validate + LookupUtils.ValueVector vector = LookupUtils.createVector(pArg); + if (null == vector) { + throw new RuntimeException("area" + pArg.getWidth() + "x" + pArg.getHeight() + " must be either row or column"); + } + double[] res = new double[vector.getSize()]; + for (int i = 0; i < res.length; i++) { + res[i] = extractDouble(vector.getItem(i)); + } + return res; + } + + private double extractDouble(final ValueEval pValue) { + if (pValue instanceof NumberEval) { + return ((NumberEval) pValue).getNumberValue(); + } + throw new RuntimeException("Can not convert to number: " + pValue); + } } Index: src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java =================================================================== --- src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java (revision 0) +++ src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java (revision 0) @@ -0,0 +1,64 @@ +/* ==================================================================== + 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.usermodel.*; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; + +/** + * Tests for {@link Npv} + * + * @author Marcel May + * @see Excel Help + */ +public final class TestNpv extends TestCase { + + public void testEvaluateInSheetExample2() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("Sheet1"); + HSSFRow row = sheet.createRow(0); + + sheet.createRow(1).createCell(0).setCellValue(0.08d); + sheet.createRow(2).createCell(0).setCellValue(-40000d); + sheet.createRow(3).createCell(0).setCellValue(8000d); + sheet.createRow(4).createCell(0).setCellValue(9200d); + sheet.createRow(5).createCell(0).setCellValue(10000d); + sheet.createRow(6).createCell(0).setCellValue(12000d); + sheet.createRow(7).createCell(0).setCellValue(14500d); + + HSSFCell cell = row.createCell(8); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + + // Enumeration + cell.setCellFormula("NPV(A2, A4,A5,A6,A7,A8)+A3"); + fe.clearAllCachedResultValues(); + fe.evaluateFormulaCell(cell); + double res = cell.getNumericCellValue(); + assertEquals(1922.06d, Math.round(res * 100d) / 100d); + + // Range + cell.setCellFormula("NPV(A2, A4:A8)+A3"); + + fe.clearAllCachedResultValues(); + fe.evaluateFormulaCell(cell); + res = cell.getNumericCellValue(); + assertEquals(1922.06d, Math.round(res * 100d) / 100d); + } +} Property changes on: src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java ___________________________________________________________________ Added: svn:mime-type + text/plain Added: svn:keywords + Date Revision Added: svn:eol-style + native