From 66e16a89578254f7a20d350ed7b58bcaa4cc5268 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Sat, 15 Jan 2022 00:35:18 +0000 Subject: [PATCH] [bug-65792] change way multiply and divide are calculated due to rounding issues git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1897066 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/ss/excelant/TestBuildFile.java | 8 ++-- .../eval/TwoOperandNumericOperation.java | 12 +++++- .../ss/formula/functions/TestFinanceLib.java | 4 +- .../functions/TestNumericFunction.java | 37 +++++++++++++++++++ .../ptg/TestExternalNameReference.java | 6 ++- 5 files changed, 57 insertions(+), 10 deletions(-) create mode 100644 poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java diff --git a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java index 9e580bd48e..8e4d783348 100644 --- a/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java +++ b/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java @@ -256,18 +256,18 @@ public class TestBuildFile { assertLogContaining("Using input file: " + TestBuildFile.getDataDir() + "/spreadsheet/excelant.xls"); assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4. " + - "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-4"); + "It evaluated to 2285.5761494145568 when the value of 2285.576149 with precision of 1.0E-4"); assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4. " + - "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-5"); + "It evaluated to 2285.5761494145568 when the value of 2285.576149 with precision of 1.0E-5"); assertLogContaining("Failed to evaluate cell 'MortgageCalculator'!$B$4. " + - "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-10 was expected."); + "It evaluated to 2285.5761494145568 when the value of 2285.576149 with precision of 1.0E-10 was expected."); assertLogContaining("2/3 tests passed"); } @Test void testPrecisionFail() { expectBuildException("test-precision-fails", "precision not matched", - "\tFailed to evaluate cell 'MortgageCalculator'!$B$4. It evaluated to 2285.5761494145563 " + + "\tFailed to evaluate cell 'MortgageCalculator'!$B$4. It evaluated to 2285.5761494145568 " + "when the value of 2285.576149 with precision of 1.0E-10 was expected."); } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java b/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java index 9e3f3610d5..0acd9d1871 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java @@ -20,6 +20,10 @@ package org.apache.poi.ss.formula.eval; import org.apache.poi.ss.formula.functions.ArrayFunction; import org.apache.poi.ss.formula.functions.Fixed2ArgFunction; import org.apache.poi.ss.formula.functions.Function; +import org.apache.poi.ss.util.NumberToTextConverter; + +import java.math.BigDecimal; +import java.math.MathContext; public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction implements ArrayFunction { @@ -85,13 +89,17 @@ public abstract class TwoOperandNumericOperation extends Fixed2ArgFunction imple if (d1 == 0.0) { throw new EvaluationException(ErrorEval.DIV_ZERO); } - return d0/d1; + BigDecimal bd0 = new BigDecimal(NumberToTextConverter.toText(d0)); + BigDecimal bd1 = new BigDecimal(NumberToTextConverter.toText(d1)); + return bd0.divide(bd1, MathContext.DECIMAL128).doubleValue(); } }; public static final Function MultiplyEval = new TwoOperandNumericOperation() { @Override protected double evaluate(double d0, double d1) { - return d0*d1; + BigDecimal bd0 = new BigDecimal(NumberToTextConverter.toText(d0)); + BigDecimal bd1 = new BigDecimal(NumberToTextConverter.toText(d1)); + return bd0.multiply(bd1).doubleValue(); } }; public static final Function PowerEval = new TwoOperandNumericOperation() { diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java index 7028743dbf..c38da7b05e 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java @@ -152,8 +152,8 @@ class TestFinanceLib extends BaseTestNumeric { addRow(sheet, 7, 14500); HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100); - Utils.assertDouble(fe, cell, "NPV(A2, A4:A8)+A3", 1922.061554932363); - Utils.assertDouble(fe, cell, "NPV(A2, A4:A8, -9000)+A3", -3749.4650870155747); + Utils.assertDouble(fe, cell, "NPV(A2, A4:A8)+A3", 1922.061554932363, 0.00000000001); + Utils.assertDouble(fe, cell, "NPV(A2, A4:A8, -9000)+A3", -3749.4650870155747, 0.00000000001); } } diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java new file mode 100644 index 0000000000..bdb75f027f --- /dev/null +++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java @@ -0,0 +1,37 @@ +/* ==================================================================== + 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.HSSFWorkbook; +import org.junit.jupiter.api.Test; + +import static org.apache.poi.ss.util.Utils.assertDouble; + +final class TestNumericFunction { + + @Test + void testINT() { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFCell cell = wb.createSheet().createRow(0).createCell(0); + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + assertDouble(fe, cell, "880000000*0.00849", 7471200.0, 0.00000001); + assertDouble(fe, cell, "880000000*0.00849/3", 2490400.0, 0.00000001); + assertDouble(fe, cell, "INT(880000000*0.00849/3)", 2490400.0, 0.00000001); + } +} diff --git a/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java b/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java index b42ca8bedf..0e842e60ee 100644 --- a/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java +++ b/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java @@ -29,6 +29,8 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellReference; import org.junit.jupiter.api.Test; +import java.math.BigDecimal; + /** * Tests for proper calculation of named ranges from external workbooks. */ @@ -123,7 +125,7 @@ final class TestExternalNameReference { evaluator.evaluateFormulaCell(ccell); evaluator.evaluateFormulaCell(tccell); assertEquals(NEW_PART_COST, uccell.getNumericCellValue(), 0); - assertEquals(NEW_PART_COST*NEW_QUANT, ccell.getNumericCellValue(), 0); - assertEquals(NEW_PART_COST*NEW_QUANT*MARKUP_COST_2, tccell.getNumericCellValue(), 0); + assertEquals(new BigDecimal(NEW_PART_COST).multiply(new BigDecimal(NEW_QUANT)).doubleValue(), ccell.getNumericCellValue(), 0.000000001); + assertEquals(NEW_PART_COST*NEW_QUANT*MARKUP_COST_2, tccell.getNumericCellValue(), 0.000000001); } } -- 2.39.5