diff options
author | Dominik Stadler <centic@apache.org> | 2014-11-07 12:29:05 +0000 |
---|---|---|
committer | Dominik Stadler <centic@apache.org> | 2014-11-07 12:29:05 +0000 |
commit | c2113c8a7107942314f4250802c8079ede60d97f (patch) | |
tree | a7b223d87ed4fd5ca2795192a427976d88685734 /src | |
parent | 08c876f7926e7b824589f5e672fcc1412af002c3 (diff) | |
download | poi-c2113c8a7107942314f4250802c8079ede60d97f.tar.gz poi-c2113c8a7107942314f4250802c8079ede60d97f.zip |
Bug 57003: Add implementation of function FIXED
git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1637361 13f79535-47bb-0310-9956-ffa450edef68
Diffstat (limited to 'src')
5 files changed, 267 insertions, 2 deletions
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 2809ee63f4..5622225fcb 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; 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 0000000000..d9026dcc73 --- /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 36775c859d..f9e91a1e3b 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 @@ -16,6 +16,7 @@ # Created by (org.apache.poi.hssf.record.formula.function.ExcelFileFormatDocFunctionExtractor) # from source file 'excelfileformat.odt' (size=356107, md5=0x8f789cb6e75594caf068f8e193004ef4) # ! + some manual edits ! +# See https://issues.apache.org/ooo/show_bug.cgi?id=125837 for difference in "FIXED" # #Columns: (index, name, minParams, maxParams, returnClass, paramClasses, isVolatile, hasFootnote ) @@ -184,7 +185,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 0000000000..d6834ebcbe --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java @@ -0,0 +1,128 @@ +/* ==================================================================== + 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.io.IOException; + +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.formula.eval.BoolEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +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; + + @Override + public void setUp() throws IOException { + HSSFWorkbook wb = new HSSFWorkbook(); + try { + HSSFSheet sheet = wb.createSheet("new sheet"); + cell11 = sheet.createRow(0).createCell(0); + cell11.setCellType(HSSFCell.CELL_TYPE_FORMULA); + evaluator = new HSSFFormulaEvaluator(wb); + } finally { + wb.close(); + } + } + + 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"); + } + + public void testOptionalParams() { + Fixed fixed = new Fixed(); + ValueEval evaluate = fixed.evaluate(0, 0, new NumberEval(1234.56789)); + assertTrue(evaluate instanceof StringEval); + assertEquals("1,234.57", ((StringEval)evaluate).getStringValue()); + + evaluate = fixed.evaluate(0, 0, new NumberEval(1234.56789), new NumberEval(1)); + assertTrue(evaluate instanceof StringEval); + assertEquals("1,234.6", ((StringEval)evaluate).getStringValue()); + + evaluate = fixed.evaluate(0, 0, new NumberEval(1234.56789), new NumberEval(1), BoolEval.TRUE); + assertTrue(evaluate instanceof StringEval); + assertEquals("1234.6", ((StringEval)evaluate).getStringValue()); + + evaluate = fixed.evaluate(new ValueEval[] {}, 1, 1); + assertTrue(evaluate instanceof ErrorEval); + + evaluate = fixed.evaluate(new ValueEval[] { new NumberEval(1), new NumberEval(1), new NumberEval(1), new NumberEval(1) }, 1, 1); + assertTrue(evaluate instanceof ErrorEval); + } + + private void confirm(String formulaText, String expectedResult) { + cell11.setCellFormula(formulaText); + evaluator.clearAllCachedResultValues(); + CellValue cv = evaluator.evaluate(cell11); + assertEquals("Wrong result type: " + cv.formatAsString(), Cell.CELL_TYPE_STRING, cv.getCellType()); + String actualValue = cv.getStringValue(); + assertEquals(expectedResult, actualValue); + } + + private void confirmValueError(String formulaText) { + cell11.setCellFormula(formulaText); + evaluator.clearAllCachedResultValues(); + CellValue cv = evaluator.evaluate(cell11); + assertTrue("Wrong result type: " + cv.formatAsString(), + cv.getCellType() == Cell.CELL_TYPE_ERROR + && cv.getErrorValue() == ErrorConstants.ERROR_VALUE); + } +} 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 0000000000..70a8a3dbfd --- /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 "57003-FixedFunctionTestCaseData.xls"; + } +} |