ASF Bugzilla – Attachment 26382 Details for
Bug 50437
NPV Function doesn't properly support ranges
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
Remember
[x]
|
Forgot Password
Login:
[x]
[patch]
Patch fixing NPV computation and adding range support
npv.patch.txt (text/plain), 7.84 KB, created by
Marcel May
on 2010-12-09 00:28:54 UTC
(
hide
)
Description:
Patch fixing NPV computation and adding range support
Filename:
MIME Type:
Creator:
Marcel May
Created:
2010-12-09 00:28:54 UTC
Size:
7.84 KB
patch
obsolete
>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 <a href="http://office.microsoft.com/en-us/excel-help/npv-HP005209199.aspx">Excel Help</a> >+ */ >+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 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
Attachments on
bug 50437
:
26379
| 26382