diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java index 7cc1408..c86dae9 100644 --- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -806,8 +806,8 @@ public final class WorkbookEvaluator { * Register a function in runtime. * * @param name the function name - * @param func the functoin to register - * @throws IllegalArgumentException if the function is unknown or already registered. + * @param func the function to register + * @throws IllegalArgumentException If the function is unknown or already registered. * @since 3.8 beta6 */ public static void registerFunction(String name, Function func){ diff --git a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java index 2809ee6..53feaab 100644 --- a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java +++ b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java @@ -41,6 +41,7 @@ import org.apache.poi.ss.formula.functions.Days360; import org.apache.poi.ss.formula.functions.Errortype; import org.apache.poi.ss.formula.functions.Even; import org.apache.poi.ss.formula.functions.FinanceFunction; +import org.apache.poi.ss.formula.functions.Fixed; import org.apache.poi.ss.formula.functions.Function; import org.apache.poi.ss.formula.functions.Hlookup; import org.apache.poi.ss.formula.functions.Hyperlink; @@ -133,7 +134,7 @@ public final class FunctionEval { retval[11] = new Npv(); retval[12] = AggregateFunction.STDEV; retval[13] = NumericFunction.DOLLAR; - + retval[14] = new Fixed(); retval[15] = NumericFunction.SIN; retval[16] = NumericFunction.COS; retval[17] = NumericFunction.TAN; @@ -150,7 +151,6 @@ public final class FunctionEval { retval[28] = new Lookup(); retval[29] = new Index(); retval[30] = new Rept(); - retval[31] = TextFunction.MID; retval[32] = TextFunction.LEN; retval[33] = new Value(); @@ -168,10 +168,8 @@ public final class FunctionEval { retval[57] = FinanceFunction.FV; retval[58] = FinanceFunction.NPER; retval[59] = FinanceFunction.PMT; - retval[60] = new Rate(); retval[61] = new Mirr(); - retval[62] = new Irr(); retval[63] = NumericFunction.RAND; retval[64] = new Match(); @@ -180,7 +178,6 @@ public final class FunctionEval { retval[67] = CalendarFieldFunction.DAY; retval[68] = CalendarFieldFunction.MONTH; retval[69] = CalendarFieldFunction.YEAR; - retval[70] = WeekdayFunc.instance; retval[71] = CalendarFieldFunction.HOUR; retval[72] = CalendarFieldFunction.MINUTE; @@ -293,7 +290,6 @@ public final class FunctionEval { retval[342] = NumericFunction.RADIANS; retval[343] = NumericFunction.DEGREES; - retval[344] = new Subtotal(); retval[345] = new Sumif(); retval[346] = new Countif(); diff --git a/src/java/org/apache/poi/ss/formula/functions/Fixed.java b/src/java/org/apache/poi/ss/formula/functions/Fixed.java new file mode 100644 index 0000000..d9026dc --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/functions/Fixed.java @@ -0,0 +1,106 @@ +/* ==================================================================== + 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 java.math.BigDecimal; +import java.math.RoundingMode; +import java.text.DecimalFormat; +import java.text.NumberFormat; +import java.util.Locale; + +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.NumberEval; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; + +public final class Fixed implements Function1Arg, Function2Arg, Function3Arg { + @Override + public ValueEval evaluate( + int srcRowIndex, int srcColumnIndex, + ValueEval arg0, ValueEval arg1, ValueEval arg2) { + return fixed(arg0, arg1, arg2, srcRowIndex, srcColumnIndex); + } + + @Override + public ValueEval evaluate( + int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) { + return fixed(arg0, arg1, BoolEval.FALSE, srcRowIndex, srcColumnIndex); + } + + @Override + public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) { + return fixed(arg0, new NumberEval(2), BoolEval.FALSE, srcRowIndex, srcColumnIndex); + } + + @Override + public ValueEval evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) { + switch (args.length) { + case 1: + return fixed(args[0], new NumberEval(2), BoolEval.FALSE, + srcRowIndex, srcColumnIndex); + case 2: + return fixed(args[0], args[1], BoolEval.FALSE, + srcRowIndex, srcColumnIndex); + case 3: + return fixed(args[0], args[1], args[2], srcRowIndex, srcColumnIndex); + } + return ErrorEval.VALUE_INVALID; + } + + private ValueEval fixed( + ValueEval numberParam, ValueEval placesParam, + ValueEval skipThousandsSeparatorParam, + int srcRowIndex, int srcColumnIndex) { + try { + ValueEval numberValueEval = + OperandResolver.getSingleValue( + numberParam, srcRowIndex, srcColumnIndex); + BigDecimal number = + new BigDecimal(OperandResolver.coerceValueToDouble(numberValueEval)); + ValueEval placesValueEval = + OperandResolver.getSingleValue( + placesParam, srcRowIndex, srcColumnIndex); + int places = OperandResolver.coerceValueToInt(placesValueEval); + ValueEval skipThousandsSeparatorValueEval = + OperandResolver.getSingleValue( + skipThousandsSeparatorParam, srcRowIndex, srcColumnIndex); + Boolean skipThousandsSeparator = + OperandResolver.coerceValueToBoolean( + skipThousandsSeparatorValueEval, false); + + // Round number to respective places. + number = number.setScale(places, RoundingMode.HALF_UP); + + // Format number conditionally using a thousands separator. + NumberFormat nf = NumberFormat.getNumberInstance(Locale.US); + DecimalFormat formatter = (DecimalFormat)nf; + formatter.setGroupingUsed(! skipThousandsSeparator); + formatter.setMinimumFractionDigits(places >= 0 ? places : 0); + formatter.setMaximumFractionDigits(places >= 0 ? places : 0); + String numberString = formatter.format(number.doubleValue()); + + // Return the result as a StringEval. + return new StringEval(numberString); + } catch (EvaluationException e) { + return e.getErrorEval(); + } + } +} diff --git a/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt b/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt index 36775c8..8d7d86e 100644 --- a/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt +++ b/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt @@ -184,7 +184,7 @@ 235 DGET 3 3 V R R R 244 INFO 1 1 V V # New Built-In Sheet Functions in BIFF4 -14 FIXED 2 3 V V V V x +14 FIXED 1 3 V V V V x 204 USDOLLAR 1 2 V V V x 215 DBCS 1 1 V V x 216 RANK 2 3 V V R V diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java b/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java new file mode 100644 index 0000000..dec62f3 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java @@ -0,0 +1,99 @@ +/* ==================================================================== + 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.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +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.ErrorConstants; + +public final class TestFixed extends TestCase { + + private HSSFCell cell11; + private HSSFFormulaEvaluator evaluator; + + public void setUp() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet sheet = wb.createSheet("new sheet"); + cell11 = sheet.createRow(0).createCell(0); + cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA); + evaluator = new HSSFFormulaEvaluator(wb); + } + + public void testValid() { + // thousands separator + confirm("FIXED(1234.56789,2,TRUE)", "1234.57"); + confirm("FIXED(1234.56789,2,FALSE)", "1,234.57"); + // rounding + confirm("FIXED(1.8,0,TRUE)", "2"); + confirm("FIXED(1.2,0,TRUE)", "1"); + confirm("FIXED(1.5,0,TRUE)", "2"); + confirm("FIXED(1,0,TRUE)", "1"); + // fractional digits + confirm("FIXED(1234.56789,7,TRUE)", "1234.5678900"); + confirm("FIXED(1234.56789,0,TRUE)", "1235"); + confirm("FIXED(1234.56789,-1,TRUE)", "1230"); + // less than three arguments + confirm("FIXED(1234.56789)", "1,234.57"); + confirm("FIXED(1234.56789,3)", "1,234.568"); + // invalid arguments + confirmValueError("FIXED(\"invalid\")"); + confirmValueError("FIXED(1,\"invalid\")"); + confirmValueError("FIXED(1,2,\"invalid\")"); + // strange arguments + confirm("FIXED(1000,2,8)", "1000.00"); + confirm("FIXED(1000,2,0)", "1,000.00"); + // corner cases + confirm("FIXED(1.23456789012345,15,TRUE)", "1.234567890123450"); + // Seems POI accepts longer numbers than Excel does, excel trims the + // number to 15 digits and removes the "9" in the formula itself. + // Not the fault of FIXED though. + // confirm("FIXED(1.234567890123459,15,TRUE)", "1.234567890123450"); + confirm("FIXED(60,-2,TRUE)", "100"); + confirm("FIXED(10,-2,TRUE)", "0"); + // rounding propagation + confirm("FIXED(99.9,0,TRUE)", "100"); + } + + private void confirm(String formulaText, String expectedResult) { + cell11.setCellFormula(formulaText); + evaluator.clearAllCachedResultValues(); + CellValue cv = evaluator.evaluate(cell11); + if (cv.getCellType() != Cell.CELL_TYPE_STRING) { + throw new AssertionFailedError("Wrong result type: " + cv.formatAsString()); + } + String actualValue = cv.getStringValue(); + assertEquals(expectedResult, actualValue); + } + + private void confirmValueError(String formulaText) { + cell11.setCellFormula(formulaText); + evaluator.clearAllCachedResultValues(); + CellValue cv = evaluator.evaluate(cell11); + if (cv.getCellType() != Cell.CELL_TYPE_ERROR + || cv.getErrorValue() != ErrorConstants.ERROR_VALUE) { + throw new AssertionFailedError("Wrong result type: " + cv.formatAsString()); + } + } +} diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java new file mode 100644 index 0000000..4cb5497 --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java @@ -0,0 +1,29 @@ +/* ==================================================================== + 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; + +/** + * Tests FIXED() as loaded from a test data spreadsheet. + */ +public class TestFixedFunctionsFromSpreadsheet extends BaseTestFunctionsFromSpreadsheet { + + @Override + protected String getFilename() { + return "FixedFunctionTestCaseData.xls"; + } +}