From 400f22037d0e4ed82ec9142d6607357c679113ae Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Mon, 23 May 2022 13:12:41 +0000 Subject: [PATCH] partial implementation CEILING.MATH function (still need to support optional 2nd param) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901167 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/atp/AnalysisToolPak.java | 1 + .../poi/ss/formula/functions/CeilingMath.java | 68 ++++++++++++++++ .../ss/formula/functions/TestCeilingMath.java | 78 +++++++++++++++++++ 3 files changed, 147 insertions(+) create mode 100644 poi/src/main/java/org/apache/poi/ss/formula/functions/CeilingMath.java create mode 100644 poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.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 f12c86ad7a..1482a39197 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 @@ -79,6 +79,7 @@ public final class AnalysisToolPak implements UDFFinder { r(m, "BIN2HEX", null); r(m, "BIN2OCT", null); r(m, "COMPLEX", Complex.instance); + r(m, "CEILING.MATH", CeilingMath.instance); r(m, "CONCAT", TextFunction.CONCAT); r(m, "CONVERT", null); r(m, "COUNTIFS", Countifs.instance); diff --git a/poi/src/main/java/org/apache/poi/ss/formula/functions/CeilingMath.java b/poi/src/main/java/org/apache/poi/ss/formula/functions/CeilingMath.java new file mode 100644 index 0000000000..566e2ae953 --- /dev/null +++ b/poi/src/main/java/org/apache/poi/ss/formula/functions/CeilingMath.java @@ -0,0 +1,68 @@ +/* ==================================================================== + 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.ss.formula.OperationEvaluationContext; +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.ValueEval; + +/** + * Implementation for Excel CEILING.MATH() function. + * + */ +public final class CeilingMath implements FreeRefFunction { + + public static final CeilingMath instance = new CeilingMath(); + + private CeilingMath() {} + + @Override + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length == 0) { + return ErrorEval.VALUE_INVALID; + } + try { + Double xval = evaluateValue(args[0], ec.getRowIndex(), ec.getColumnIndex()); + if (xval == null) { + return ErrorEval.NUM_ERROR; + } + boolean roundNegativeNumsDown = false; + if (args.length > 2) { + Double arg2Val = evaluateValue(args[2], ec.getRowIndex(), ec.getColumnIndex()); + roundNegativeNumsDown = arg2Val != null && arg2Val.doubleValue() < 0.0; + } + if (roundNegativeNumsDown && xval < 0.0) { + return new NumberEval(Math.floor(xval)); + } + return new NumberEval(Math.ceil(xval)); + } catch (EvaluationException evaluationException) { + return evaluationException.getErrorEval(); + } + } + + private static Double evaluateValue(ValueEval arg, int srcRowIndex, int srcColumnIndex) throws EvaluationException { + ValueEval veText = OperandResolver.getSingleValue(arg, srcRowIndex, srcColumnIndex); + String strText1 = OperandResolver.coerceValueToString(veText); + return OperandResolver.parseDouble(strText1); + } +} \ No newline at end of file diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.java new file mode 100644 index 0000000000..a80ea8e45e --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestCeilingMath.java @@ -0,0 +1,78 @@ +/* ==================================================================== + 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.formula.OperationEvaluationContext; +import org.apache.poi.ss.formula.eval.ErrorEval; +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.assertDouble; +import static org.apache.poi.ss.util.Utils.assertError; + +/** + * Tests for {@link CeilingMath} + */ +final class TestCeilingMath { + + private static final OperationEvaluationContext ec = new OperationEvaluationContext(null, null, 0, 0, 0, null); + + //https://support.microsoft.com/en-us/office/ceiling-math-function-80f95d2f-b499-4eee-9f16-f795a8e306c8 + @Test + void testMicrosoftExamples() throws IOException { + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sheet = wb.createSheet(); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertDouble(fe, cell, "CEILING.MATH(24.3,5)", 25.0, 0.00000000000001); + assertDouble(fe, cell, "CEILING.MATH(6.7)", 7.0, 0.00000000000001); + assertDouble(fe, cell, "CEILING.MATH(-8.1,2)", -8.0, 0.00000000000001); + assertDouble(fe, cell, "CEILING.MATH(-5.5,2,-1)", -6.0, 0.00000000000001); + } + } + + @Test + void testInvalid() throws IOException { + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sheet = wb.createSheet(); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertError(fe, cell, "CEILING.MATH()", FormulaError.VALUE); + } + } + + @Test + void testNumError() throws IOException { + try (HSSFWorkbook wb = new HSSFWorkbook()) { + HSSFSheet sheet = wb.createSheet(); + HSSFRow row = sheet.createRow(0); + HSSFCell cell = row.createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertError(fe, cell, "CEILING.MATH(\"abc\")", FormulaError.NUM); + } + } +} -- 2.39.5