View | Details | Raw Unified | Return to bug 50437
Collapse All | Expand All

(-)src/java/org/apache/poi/ss/formula/functions/Npv.java (-59 / +36 lines)
Lines 17-22 Link Here
17
17
18
package org.apache.poi.ss.formula.functions;
18
package org.apache.poi.ss.formula.functions;
19
19
20
import org.apache.poi.ss.formula.TwoDEval;
20
import org.apache.poi.ss.formula.eval.ErrorEval;
21
import org.apache.poi.ss.formula.eval.ErrorEval;
21
import org.apache.poi.ss.formula.eval.EvaluationException;
22
import org.apache.poi.ss.formula.eval.EvaluationException;
22
import org.apache.poi.ss.formula.eval.NumberEval;
23
import org.apache.poi.ss.formula.eval.NumberEval;
Lines 30-95 Link Here
30
 * income.
31
 * income.
31
 *
32
 *
32
 * @author SPetrakovsky
33
 * @author SPetrakovsky
34
 * @author Marcel May
33
 */
35
 */
34
public final class Npv implements Function2Arg, Function3Arg, Function4Arg {
36
public final class Npv implements Function {
35
37
36
37
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
38
		double result;
39
		try {
40
			double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
41
			double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
42
			result = evaluate(rate, d1);
43
			NumericFunction.checkValue(result);
44
		} catch (EvaluationException e) {
45
			return e.getErrorEval();
46
		}
47
		return new NumberEval(result);
48
	}
49
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
50
			ValueEval arg2) {
51
		double result;
52
		try {
53
			double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
54
			double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
55
			double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
56
			result = evaluate(rate, d1, d2);
57
			NumericFunction.checkValue(result);
58
		} catch (EvaluationException e) {
59
			return e.getErrorEval();
60
		}
61
		return new NumberEval(result);
62
	}
63
	public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1,
64
			ValueEval arg2, ValueEval arg3) {
65
		double result;
66
		try {
67
			double rate = NumericFunction.singleOperandEvaluate(arg0, srcRowIndex, srcColumnIndex);
68
			double d1 = NumericFunction.singleOperandEvaluate(arg1, srcRowIndex, srcColumnIndex);
69
			double d2 = NumericFunction.singleOperandEvaluate(arg2, srcRowIndex, srcColumnIndex);
70
			double d3 = NumericFunction.singleOperandEvaluate(arg3, srcRowIndex, srcColumnIndex);
71
			result = evaluate(rate, d1, d2, d3);
72
			NumericFunction.checkValue(result);
73
		} catch (EvaluationException e) {
74
			return e.getErrorEval();
75
		}
76
		return new NumberEval(result);
77
	}
78
79
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
38
	public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
80
		int nArgs = args.length;
39
		int nArgs = args.length;
81
		if (nArgs<2) {
40
		if (nArgs<2) {
82
			return ErrorEval.VALUE_INVALID;
41
			return ErrorEval.VALUE_INVALID;
83
		}
42
		}
84
		int np = nArgs-1;
43
        double result;
85
		double[] ds = new double[np];
44
        try {
86
		double result;
45
            double[] ds;
87
		try {
46
            if (2==nArgs && args[1] instanceof TwoDEval) {
47
                // eg A4:A10
48
                ds = extractNumbers((TwoDEval) args[1]);
49
            } else {
50
                // eg A4,A5,...
51
                ds = new double[args.length-1];
52
                for (int i = 0; i < ds.length; i++) {
53
			    	ds[i] =  NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex);
54
			    }
55
            }
88
			double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
56
			double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
89
			for (int i = 0; i < ds.length; i++) {
57
			result = FinanceLib.npv(rate, ds);
90
				ds[i] =  NumericFunction.singleOperandEvaluate(args[i+1], srcRowIndex, srcColumnIndex);
91
			}
92
			result = evaluate(rate, ds);
93
			NumericFunction.checkValue(result);
58
			NumericFunction.checkValue(result);
94
		} catch (EvaluationException e) {
59
		} catch (EvaluationException e) {
95
			return e.getErrorEval();
60
			return e.getErrorEval();
Lines 97-107 Link Here
97
		return new NumberEval(result);
62
		return new NumberEval(result);
98
	}
63
	}
99
64
100
	private static double evaluate(double rate, double...ds) {
65
    private double[] extractNumbers(final TwoDEval pArg) {
101
		double sum = 0;
66
        // Validate
102
		for (int i = 0; i < ds.length; i++) {
67
        LookupUtils.ValueVector vector = LookupUtils.createVector(pArg);
103
			sum += ds[i] / Math.pow(rate + 1, i);
68
        if (null == vector) {
104
		}
69
            throw new RuntimeException("area" + pArg.getWidth() + "x" + pArg.getHeight() + " must be either row or column");
105
		return sum;
70
        }
106
	}
71
        double[] res = new double[vector.getSize()];
72
        for (int i = 0; i < res.length; i++) {
73
            res[i] = extractDouble(vector.getItem(i));
74
        }
75
        return res;
76
    }
77
78
    private double extractDouble(final ValueEval pValue) {
79
        if (pValue instanceof NumberEval) {
80
            return ((NumberEval) pValue).getNumberValue();
81
        }
82
        throw new RuntimeException("Can not convert to number: " + pValue);
83
    }
107
}
84
}
(-)src/testcases/org/apache/poi/ss/formula/functions/TestNpv.java (+64 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.ss.formula.functions;
19
20
import junit.framework.TestCase;
21
import org.apache.poi.hssf.usermodel.*;
22
import org.apache.poi.ss.usermodel.Cell;
23
import org.apache.poi.ss.usermodel.Row;
24
25
/**
26
 * Tests for {@link Npv}
27
 *
28
 * @author Marcel May
29
 * @see <a href="http://office.microsoft.com/en-us/excel-help/npv-HP005209199.aspx">Excel Help</a>
30
 */
31
public final class TestNpv extends TestCase {
32
33
    public void testEvaluateInSheetExample2() {
34
        HSSFWorkbook wb = new HSSFWorkbook();
35
        HSSFSheet sheet = wb.createSheet("Sheet1");
36
        HSSFRow row = sheet.createRow(0);
37
38
        sheet.createRow(1).createCell(0).setCellValue(0.08d);
39
        sheet.createRow(2).createCell(0).setCellValue(-40000d);
40
        sheet.createRow(3).createCell(0).setCellValue(8000d);
41
        sheet.createRow(4).createCell(0).setCellValue(9200d);
42
        sheet.createRow(5).createCell(0).setCellValue(10000d);
43
        sheet.createRow(6).createCell(0).setCellValue(12000d);
44
        sheet.createRow(7).createCell(0).setCellValue(14500d);
45
46
        HSSFCell cell = row.createCell(8);
47
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
48
49
        // Enumeration
50
        cell.setCellFormula("NPV(A2, A4,A5,A6,A7,A8)+A3");
51
        fe.clearAllCachedResultValues();
52
        fe.evaluateFormulaCell(cell);
53
        double res = cell.getNumericCellValue();
54
        assertEquals(1922.06d, Math.round(res * 100d) / 100d);
55
56
        // Range
57
        cell.setCellFormula("NPV(A2, A4:A8)+A3");
58
59
        fe.clearAllCachedResultValues();
60
        fe.evaluateFormulaCell(cell);
61
        res = cell.getNumericCellValue();
62
        assertEquals(1922.06d, Math.round(res * 100d) / 100d);
63
    }
64
}
0
  + text/plain
65
  + text/plain
1
  + Date Revision
66
  + Date Revision
2
  + native
67
  + native

Return to bug 50437