Author: centic
Date: Fri Nov 7 12:29:05 2014
New Revision: 1637361
URL: http://svn.apache.org/r1637361
Log:
Bug 57003: Add implementation of function FIXED
Added:
poi/trunk/src/java/org/apache/poi/ss/formula/functions/Fixed.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java
poi/trunk/test-data/spreadsheet/57003-FixedFunctionTestCaseData.xls (with
props)
Modified:
poi/site/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
poi/trunk/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt
Modified: poi/site/src/documentation/content/xdocs/status.xml
URL:
http://svn.apache.org/viewvc/poi/site/src/documentation/content/xdocs/status.xml?rev=1637361&r1=1637360&r2=1637361&view=diff
==============================================================================
--- poi/site/src/documentation/content/xdocs/status.xml (original)
+++ poi/site/src/documentation/content/xdocs/status.xml Fri Nov 7 12:29:05 2014
@@ -38,6 +38,7 @@
</devs>
<release version="3.11-beta3" date="2014-??-??">
+ <action dev="PD" type="add" fixes-bug="57003">Implement FIXED
function</action>
<action dev="PD" type="add">github-7 - Form check box extraction with
XWPFWordExtractor</action>
<action dev="PD" type="add">github-11 - Add Sheet.getDataValidations()
for HSSF and XSSF</action>
<action dev="PD" type="add">github-12 - Add Comment.getClientAnchor()
for HSSF and XSSF</action>
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1637361&r1=1637360&r2=1637361&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
(original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java Fri Nov
7 12:29:05 2014
@@ -41,6 +41,7 @@ import org.apache.poi.ss.formula.functio
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;
Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Fixed.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Fixed.java?rev=1637361&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Fixed.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Fixed.java Fri Nov
7 12:29:05 2014
@@ -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();
+ }
+ }
+}
Modified:
poi/trunk/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt
URL:
http://svn.apache.org/viewvc/poi/trunk/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt?rev=1637361&r1=1637360&r2=1637361&view=diff
==============================================================================
---
poi/trunk/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt
(original)
+++
poi/trunk/src/resources/main/org/apache/poi/ss/formula/function/functionMetadata.txt
Fri Nov 7 12:29:05 2014
@@ -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
Added:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java?rev=1637361&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java
(added)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixed.java
Fri Nov 7 12:29:05 2014
@@ -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);
+ }
+}
Added:
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java?rev=1637361&view=auto
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java
(added)
+++
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFixedFunctionsFromSpreadsheet.java
Fri Nov 7 12:29:05 2014
@@ -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";
+ }
+}
Added: poi/trunk/test-data/spreadsheet/57003-FixedFunctionTestCaseData.xls
URL:
http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/57003-FixedFunctionTestCaseData.xls?rev=1637361&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/57003-FixedFunctionTestCaseData.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
