From e6039d0deb1be3868129afceb7c8f62c17d7b8a3 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Tue, 25 Jan 2022 18:45:01 +0000 Subject: [PATCH] [bug-65846] support numbervalue function git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1897463 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/atp/AnalysisToolPak.java | 1 + .../functions/NumberValueFunction.java | 125 ++++++++++++++++++ .../ss/formula/functions/TestNumberValue.java | 62 +++++++++ 3 files changed, 188 insertions(+) create mode 100644 poi/src/main/java/org/apache/poi/ss/formula/functions/NumberValueFunction.java create mode 100644 poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumberValue.java diff --git a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java index fdc955313a..0016b3af60 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java @@ -149,6 +149,7 @@ public final class AnalysisToolPak implements UDFFinder { r(m, "MULTINOMIAL", null); r(m, "NETWORKDAYS", NetworkdaysFunction.instance); r(m, "NOMINAL", null); + r(m, "NUMBERVALUE", NumberValueFunction.instance); r(m, "OCT2BIN", null); r(m, "OCT2DEC", Oct2Dec.instance); r(m, "OCT2HEX", null); diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/NumberValueFunction.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumberValueFunction.java new file mode 100644 index 0000000000..76a2036897 --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/NumberValueFunction.java @@ -0,0 +1,125 @@ +/* ==================================================================== + 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.util.Locale; +import java.text.DecimalFormatSymbols; +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.eval.NumberEval; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.EvaluationException; +import org.apache.poi.ss.formula.eval.OperandResolver; +import org.apache.poi.util.LocaleUtil; + +/** + /** + * Implementation for the NUMBERVALUE() Excel function.

+ * + * https://support.microsoft.com/en-us/office/numbervalue-function-1b05c8cf-2bfa-4437-af70-596c7ea7d879 + */ +public final class NumberValueFunction implements FreeRefFunction { + + public static final FreeRefFunction instance = new NumberValueFunction(); + + @Override + public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) { + + Locale locale = LocaleUtil.getUserLocale(); + DecimalFormatSymbols decimalFormatSymbols = new DecimalFormatSymbols(locale); + + String text = null; + //If the Decimal_separator and Group_separator arguments are not specified, separators from the current locale are used. + String decSep = String.valueOf(decimalFormatSymbols.getDecimalSeparator()); + String groupSep = String.valueOf(decimalFormatSymbols.getGroupingSeparator()); + + Double result = Double.NaN; + ValueEval v1 = null; + ValueEval v2 = null; + ValueEval v3 = null; + + try { + if (args.length == 1) { + v1 = OperandResolver.getSingleValue( args[0], ec.getRowIndex(), ec.getColumnIndex()); + text = OperandResolver.coerceValueToString(v1); + } else if (args.length == 2) { + v1 = OperandResolver.getSingleValue( args[0], ec.getRowIndex(), ec.getColumnIndex()); + v2 = OperandResolver.getSingleValue( args[1], ec.getRowIndex(), ec.getColumnIndex()); + text = OperandResolver.coerceValueToString(v1); + decSep = OperandResolver.coerceValueToString(v2).substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used. + } else if (args.length == 3) { + v1 = OperandResolver.getSingleValue( args[0], ec.getRowIndex(), ec.getColumnIndex()); + v2 = OperandResolver.getSingleValue( args[1], ec.getRowIndex(), ec.getColumnIndex()); + v3 = OperandResolver.getSingleValue( args[2], ec.getRowIndex(), ec.getColumnIndex()); + text = OperandResolver.coerceValueToString(v1); + decSep = OperandResolver.coerceValueToString(v2).substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used. + groupSep = OperandResolver.coerceValueToString(v3).substring(0, 1); //If multiple characters are used in the Decimal_separator or Group_separator arguments, only the first character is used. + } + } catch (EvaluationException e) { + e.printStackTrace() ; + return e.getErrorEval(); + } + + if("".equals(text)) text = "0"; //If an empty string ("") is specified as the Text argument, the result is 0. + text = text.replace(" ", ""); //Empty spaces in the Text argument are ignored, even in the middle of the argument. For example, " 3 000 " is returned as 3000. + String[] parts = text.split("["+decSep+"]"); + String sigPart = ""; + String decPart = ""; + if (parts.length > 2) return ErrorEval.VALUE_INVALID; //If a decimal separator is used more than once in the Text argument, NUMBERVALUE returns the #VALUE! error value. + if (parts.length > 1) { + sigPart = parts[0]; + decPart = parts[1]; + if (decPart.contains(groupSep)) return ErrorEval.VALUE_INVALID; //If the group separator occurs after the decimal separator in the Text argument, NUMBERVALUE returns the #VALUE! error value. + sigPart = sigPart.replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored. + text = sigPart + "." + decPart; + } else if (parts.length > 0) { + sigPart = parts[0]; + sigPart = sigPart.replace(groupSep, ""); //If the group separator occurs before the decimal separator in the Text argument , the group separator is ignored. + text = sigPart; + } + + //If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result. + //Multiple percent signs are additive if they are used in the Text argument just as they are if they are used in a formula. + //For example, =NUMBERVALUE("9%%") returns the same result (0.0009) as the formula =9%%. + int countPercent = 0; + while (text.endsWith("%")) { + countPercent++; + text = text.substring(0, text.length()-1); + } + + try { + result = Double.valueOf(text); + result = result / Math.pow(100, countPercent); //If the Text argument ends in one or more percent signs (%), they are used in the calculation of the result. + checkValue(result); + } catch (EvaluationException e) { + e.printStackTrace() ; + return e.getErrorEval(); + } catch (Exception anyex) { + return ErrorEval.VALUE_INVALID; //If any of the arguments are not valid, NUMBERVALUE returns the #VALUE! error value. + } + + return new NumberEval(result); + + } + + private static void checkValue(double result) throws EvaluationException { + if (Double.isNaN(result) || Double.isInfinite(result)) { + throw new EvaluationException(ErrorEval.NUM_ERROR); + } + } +} diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumberValue.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumberValue.java new file mode 100644 index 0000000000..a2a512bc6a --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumberValue.java @@ -0,0 +1,62 @@ + +/* ==================================================================== + 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 org.apache.poi.hssf.usermodel.HSSFCell; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; +import org.apache.poi.hssf.usermodel.HSSFRow; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.usermodel.FormulaError; +import org.junit.jupiter.api.Test; + +import java.io.IOException; + +import static org.apache.poi.ss.util.Utils.addRow; +import static org.apache.poi.ss.util.Utils.assertDouble; +import static org.junit.jupiter.api.Assertions.assertEquals; + +/** + * Testcase for function NUMBERVALUE() + */ +public class TestNumberValue { + + //https://support.microsoft.com/en-us/office/numbervalue-function-1b05c8cf-2bfa-4437-af70-596c7ea7d879 + @Test + void testMicrosoftExample1() throws IOException { + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sheet = wb.createSheet(); + HSSFRow row = sheet.createRow(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = row.createCell(0); + assertDouble(fe, cell, "NUMBERVALUE(\"2.500,27\",\",\",\".\")", 2500.27, 0.000000000001); + } + } + + @Test + void testMicrosoftExample2() throws IOException { + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sheet = wb.createSheet(); + HSSFRow row = sheet.createRow(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + HSSFCell cell = row.createCell(0); + assertDouble(fe, cell, "NUMBERVALUE(\"3.5%\")", 0.035, 0.000000000001); + } + } +} -- 2.39.5