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

(-)a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (-2 / +2 lines)
Lines 806-813 public final class WorkbookEvaluator { Link Here
806
     * Register a function in runtime.
806
     * Register a function in runtime.
807
     *
807
     *
808
     * @param name  the function name
808
     * @param name  the function name
809
     * @param func  the functoin to register
809
     * @param func  the function to register
810
     * @throws IllegalArgumentException if the function is unknown or already  registered.
810
     * @throws IllegalArgumentException If the function is unknown or already registered.
811
     * @since 3.8 beta6
811
     * @since 3.8 beta6
812
     */
812
     */
813
    public static void registerFunction(String name, Function func){
813
    public static void registerFunction(String name, Function func){
(-)a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (-6 / +2 lines)
Lines 41-46 import org.apache.poi.ss.formula.functions.Days360; Link Here
41
import org.apache.poi.ss.formula.functions.Errortype;
41
import org.apache.poi.ss.formula.functions.Errortype;
42
import org.apache.poi.ss.formula.functions.Even;
42
import org.apache.poi.ss.formula.functions.Even;
43
import org.apache.poi.ss.formula.functions.FinanceFunction;
43
import org.apache.poi.ss.formula.functions.FinanceFunction;
44
import org.apache.poi.ss.formula.functions.Fixed;
44
import org.apache.poi.ss.formula.functions.Function;
45
import org.apache.poi.ss.formula.functions.Function;
45
import org.apache.poi.ss.formula.functions.Hlookup;
46
import org.apache.poi.ss.formula.functions.Hlookup;
46
import org.apache.poi.ss.formula.functions.Hyperlink;
47
import org.apache.poi.ss.formula.functions.Hyperlink;
Lines 133-139 public final class FunctionEval { Link Here
133
        retval[11] = new Npv();
134
        retval[11] = new Npv();
134
        retval[12] = AggregateFunction.STDEV;
135
        retval[12] = AggregateFunction.STDEV;
135
        retval[13] = NumericFunction.DOLLAR;
136
        retval[13] = NumericFunction.DOLLAR;
136
137
        retval[14] = new Fixed();
137
        retval[15] = NumericFunction.SIN;
138
        retval[15] = NumericFunction.SIN;
138
        retval[16] = NumericFunction.COS;
139
        retval[16] = NumericFunction.COS;
139
        retval[17] = NumericFunction.TAN;
140
        retval[17] = NumericFunction.TAN;
Lines 150-156 public final class FunctionEval { Link Here
150
        retval[28] = new Lookup();
151
        retval[28] = new Lookup();
151
        retval[29] = new Index();
152
        retval[29] = new Index();
152
        retval[30] = new Rept();
153
        retval[30] = new Rept();
153
154
        retval[31] = TextFunction.MID;
154
        retval[31] = TextFunction.MID;
155
        retval[32] = TextFunction.LEN;
155
        retval[32] = TextFunction.LEN;
156
        retval[33] = new Value();
156
        retval[33] = new Value();
Lines 168-177 public final class FunctionEval { Link Here
168
        retval[57] = FinanceFunction.FV;
168
        retval[57] = FinanceFunction.FV;
169
        retval[58] = FinanceFunction.NPER;
169
        retval[58] = FinanceFunction.NPER;
170
        retval[59] = FinanceFunction.PMT;
170
        retval[59] = FinanceFunction.PMT;
171
172
        retval[60] = new Rate();
171
        retval[60] = new Rate();
173
        retval[61] = new Mirr();
172
        retval[61] = new Mirr();
174
175
        retval[62] = new Irr();
173
        retval[62] = new Irr();
176
        retval[63] = NumericFunction.RAND;
174
        retval[63] = NumericFunction.RAND;
177
        retval[64] = new Match();
175
        retval[64] = new Match();
Lines 180-186 public final class FunctionEval { Link Here
180
        retval[67] = CalendarFieldFunction.DAY;
178
        retval[67] = CalendarFieldFunction.DAY;
181
        retval[68] = CalendarFieldFunction.MONTH;
179
        retval[68] = CalendarFieldFunction.MONTH;
182
        retval[69] = CalendarFieldFunction.YEAR;
180
        retval[69] = CalendarFieldFunction.YEAR;
183
184
        retval[70] = WeekdayFunc.instance;
181
        retval[70] = WeekdayFunc.instance;
185
        retval[71] = CalendarFieldFunction.HOUR;
182
        retval[71] = CalendarFieldFunction.HOUR;
186
        retval[72] = CalendarFieldFunction.MINUTE;
183
        retval[72] = CalendarFieldFunction.MINUTE;
Lines 293-299 public final class FunctionEval { Link Here
293
290
294
        retval[342] = NumericFunction.RADIANS;
291
        retval[342] = NumericFunction.RADIANS;
295
        retval[343] = NumericFunction.DEGREES;
292
        retval[343] = NumericFunction.DEGREES;
296
297
        retval[344] = new Subtotal();
293
        retval[344] = new Subtotal();
298
        retval[345] = new Sumif();
294
        retval[345] = new Sumif();
299
        retval[346] = new Countif();
295
        retval[346] = new Countif();
(-)a/src/java/org/apache/poi/ss/formula/functions/Fixed.java (+106 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 java.math.BigDecimal;
21
import java.math.RoundingMode;
22
import java.text.DecimalFormat;
23
import java.text.NumberFormat;
24
import java.util.Locale;
25
26
import org.apache.poi.ss.formula.eval.BoolEval;
27
import org.apache.poi.ss.formula.eval.ErrorEval;
28
import org.apache.poi.ss.formula.eval.EvaluationException;
29
import org.apache.poi.ss.formula.eval.NumberEval;
30
import org.apache.poi.ss.formula.eval.OperandResolver;
31
import org.apache.poi.ss.formula.eval.StringEval;
32
import org.apache.poi.ss.formula.eval.ValueEval;
33
34
public final class Fixed implements Function1Arg, Function2Arg, Function3Arg {
35
    @Override
36
    public ValueEval evaluate(
37
            int srcRowIndex, int srcColumnIndex,
38
            ValueEval arg0, ValueEval arg1, ValueEval arg2) {
39
        return fixed(arg0, arg1, arg2, srcRowIndex, srcColumnIndex);
40
    }
41
42
    @Override
43
    public ValueEval evaluate(
44
            int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
45
        return fixed(arg0, arg1, BoolEval.FALSE, srcRowIndex, srcColumnIndex);
46
    }
47
48
    @Override
49
    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
50
        return fixed(arg0, new NumberEval(2), BoolEval.FALSE, srcRowIndex, srcColumnIndex);
51
    }
52
53
    @Override
54
    public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
55
        switch (args.length) {
56
            case 1:
57
                return fixed(args[0], new NumberEval(2), BoolEval.FALSE,
58
                        srcRowIndex, srcColumnIndex);
59
            case 2:
60
                return fixed(args[0], args[1], BoolEval.FALSE,
61
                        srcRowIndex, srcColumnIndex);
62
            case 3:
63
                return fixed(args[0], args[1], args[2], srcRowIndex, srcColumnIndex);
64
        }
65
        return ErrorEval.VALUE_INVALID;
66
    }
67
    
68
    private ValueEval fixed(
69
            ValueEval numberParam, ValueEval placesParam,
70
            ValueEval skipThousandsSeparatorParam,
71
            int srcRowIndex, int srcColumnIndex) {
72
        try {
73
            ValueEval numberValueEval =
74
                    OperandResolver.getSingleValue(
75
                    numberParam, srcRowIndex, srcColumnIndex);
76
            BigDecimal number =
77
                    new BigDecimal(OperandResolver.coerceValueToDouble(numberValueEval));
78
            ValueEval placesValueEval =
79
                    OperandResolver.getSingleValue(
80
                    placesParam, srcRowIndex, srcColumnIndex);
81
            int places = OperandResolver.coerceValueToInt(placesValueEval);
82
            ValueEval skipThousandsSeparatorValueEval =
83
                    OperandResolver.getSingleValue(
84
                    skipThousandsSeparatorParam, srcRowIndex, srcColumnIndex);
85
            Boolean skipThousandsSeparator =
86
                    OperandResolver.coerceValueToBoolean(
87
                    skipThousandsSeparatorValueEval, false);
88
            
89
            // Round number to respective places.
90
            number = number.setScale(places, RoundingMode.HALF_UP);
91
            
92
            // Format number conditionally using a thousands separator.
93
            NumberFormat nf = NumberFormat.getNumberInstance(Locale.US);
94
            DecimalFormat formatter = (DecimalFormat)nf;
95
            formatter.setGroupingUsed(! skipThousandsSeparator);
96
            formatter.setMinimumFractionDigits(places >= 0 ? places : 0);
97
            formatter.setMaximumFractionDigits(places >= 0 ? places : 0);
98
            String numberString = formatter.format(number.doubleValue());
99
100
            // Return the result as a StringEval.
101
            return new StringEval(numberString);
102
        } catch (EvaluationException e) {
103
            return e.getErrorEval();
104
        }
105
    }
106
}
(-)a/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt (-1 / +1 lines)
Lines 184-190 Link Here
184
235	DGET	3	3	V	R R R		
184
235	DGET	3	3	V	R R R		
185
244	INFO	1	1	V	V		
185
244	INFO	1	1	V	V		
186
# New Built-In Sheet Functions in BIFF4
186
# New Built-In Sheet Functions in BIFF4
187
14	FIXED	2	3	V	V V V		x
187
14	FIXED	1	3	V	V V V		x
188
204	USDOLLAR	1	2	V	V V		x
188
204	USDOLLAR	1	2	V	V V		x
189
215	DBCS	1	1	V	V		x
189
215	DBCS	1	1	V	V		x
190
216	RANK	2	3	V	V R V		
190
216	RANK	2	3	V	V R V		
(-)a/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java (+99 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.AssertionFailedError;
21
import junit.framework.TestCase;
22
23
import org.apache.poi.hssf.usermodel.HSSFCell;
24
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
25
import org.apache.poi.hssf.usermodel.HSSFSheet;
26
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
27
import org.apache.poi.ss.usermodel.Cell;
28
import org.apache.poi.ss.usermodel.CellValue;
29
import org.apache.poi.ss.usermodel.ErrorConstants;
30
31
public final class TestFixed extends TestCase {
32
33
    private HSSFCell cell11;
34
    private HSSFFormulaEvaluator evaluator;
35
36
    public void setUp() {
37
        HSSFWorkbook wb = new HSSFWorkbook();
38
        HSSFSheet sheet = wb.createSheet("new sheet");
39
        cell11 = sheet.createRow(0).createCell(0);
40
        cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA);
41
        evaluator = new HSSFFormulaEvaluator(wb);
42
    }
43
44
    public void testValid() {
45
        // thousands separator
46
        confirm("FIXED(1234.56789,2,TRUE)", "1234.57");
47
        confirm("FIXED(1234.56789,2,FALSE)", "1,234.57");
48
        // rounding
49
        confirm("FIXED(1.8,0,TRUE)", "2");
50
        confirm("FIXED(1.2,0,TRUE)", "1");
51
        confirm("FIXED(1.5,0,TRUE)", "2");
52
        confirm("FIXED(1,0,TRUE)", "1");
53
        // fractional digits
54
        confirm("FIXED(1234.56789,7,TRUE)", "1234.5678900");
55
        confirm("FIXED(1234.56789,0,TRUE)", "1235");
56
        confirm("FIXED(1234.56789,-1,TRUE)", "1230");
57
        // less than three arguments
58
        confirm("FIXED(1234.56789)", "1,234.57");
59
        confirm("FIXED(1234.56789,3)", "1,234.568");
60
        // invalid arguments
61
        confirmValueError("FIXED(\"invalid\")");
62
        confirmValueError("FIXED(1,\"invalid\")");
63
        confirmValueError("FIXED(1,2,\"invalid\")");
64
        // strange arguments
65
        confirm("FIXED(1000,2,8)", "1000.00");
66
        confirm("FIXED(1000,2,0)", "1,000.00");
67
        // corner cases
68
        confirm("FIXED(1.23456789012345,15,TRUE)", "1.234567890123450");
69
        // Seems POI accepts longer numbers than Excel does, excel trims the
70
        // number to 15 digits and removes the "9" in the formula itself.
71
        // Not the fault of FIXED though.
72
        // confirm("FIXED(1.234567890123459,15,TRUE)", "1.234567890123450");
73
        confirm("FIXED(60,-2,TRUE)", "100");
74
        confirm("FIXED(10,-2,TRUE)", "0");
75
        // rounding propagation
76
        confirm("FIXED(99.9,0,TRUE)", "100");
77
    }
78
79
    private void confirm(String formulaText, String expectedResult) {
80
        cell11.setCellFormula(formulaText);
81
        evaluator.clearAllCachedResultValues();
82
        CellValue cv = evaluator.evaluate(cell11);
83
        if (cv.getCellType() != Cell.CELL_TYPE_STRING) {
84
            throw new AssertionFailedError("Wrong result type: " + cv.formatAsString());
85
        }
86
        String actualValue = cv.getStringValue();
87
        assertEquals(expectedResult, actualValue);
88
    }
89
    
90
    private void confirmValueError(String formulaText) {
91
        cell11.setCellFormula(formulaText);
92
        evaluator.clearAllCachedResultValues();
93
        CellValue cv = evaluator.evaluate(cell11);
94
        if (cv.getCellType() != Cell.CELL_TYPE_ERROR
95
            || cv.getErrorValue() != ErrorConstants.ERROR_VALUE) {
96
            throw new AssertionFailedError("Wrong result type: " + cv.formatAsString());
97
        }
98
    }
99
}
(-)a/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java (+29 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
/**
21
 * Tests FIXED() as loaded from a test data spreadsheet.
22
 */
23
public class TestFixedFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet {
24
25
    @Override
26
    protected String getFilename() {
27
        return "FixedFunctionTestCaseData.xls";
28
    }
29
}

Return to bug 57003