You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2022/01/15 00:35:18 UTC
svn commit: r1897066 - in /poi/trunk: poi-excelant/src/test/java/org/apache/poi/ss/excelant/ poi/src/main/java/org/apache/poi/ss/formula/eval/ poi/src/test/java/org/apache/poi/ss/formula/functions/ poi/src/test/java/org/apache/poi/ss/formula/ptg/
Author: fanningpj
Date: Sat Jan 15 00:35:18 2022
New Revision: 1897066
URL: http://svn.apache.org/viewvc?rev=1897066&view=rev
Log:
[bug-65792] change way multiply and divide are calculated due to rounding issues
Added:
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java (with props)
Modified:
poi/trunk/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java
poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java
Modified: poi/trunk/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java?rev=1897066&r1=1897065&r2=1897066&view=diff
==============================================================================
--- poi/trunk/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java (original)
+++ poi/trunk/poi-excelant/src/test/java/org/apache/poi/ss/excelant/TestBuildFile.java Sat Jan 15 00:35:18 2022
@@ -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.");
}
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java?rev=1897066&r1=1897065&r2=1897066&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/eval/TwoOperandNumericOperation.java Sat Jan 15 00:35:18 2022
@@ -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 TwoOperandNumericO
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() {
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java?rev=1897066&r1=1897065&r2=1897066&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java Sat Jan 15 00:35:18 2022
@@ -152,8 +152,8 @@ class TestFinanceLib extends BaseTestNum
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);
}
}
Added: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java?rev=1897066&view=auto
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java (added)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java Sat Jan 15 00:35:18 2022
@@ -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);
+ }
+}
Propchange: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/functions/TestNumericFunction.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java?rev=1897066&r1=1897065&r2=1897066&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/ss/formula/ptg/TestExternalNameReference.java Sat Jan 15 00:35:18 2022
@@ -29,6 +29,8 @@ import org.apache.poi.hssf.usermodel.HSS
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);
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org