From d9bf061db757f85fd50df1c6de22a4a742a12548 Mon Sep 17 00:00:00 2001 From: Dominik Stadler Date: Fri, 18 Apr 2014 18:20:15 +0000 Subject: [PATCH] Bug 56315: Make rounding in MathX.round() behave equal to Excel, adjust/enhance some more tests in MathX git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1588538 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/ss/formula/functions/MathX.java | 3 +- .../poi/xssf/usermodel/TestXSSFBugs.java | 9 ++ .../poi/ss/formula/functions/TestMathX.java | 113 +++++++++++++++--- test-data/spreadsheet/56315.xlsx | Bin 0 -> 7815 bytes 4 files changed, 108 insertions(+), 17 deletions(-) create mode 100644 test-data/spreadsheet/56315.xlsx diff --git a/src/java/org/apache/poi/ss/formula/functions/MathX.java b/src/java/org/apache/poi/ss/formula/functions/MathX.java index 5696c51f81..710e611081 100644 --- a/src/java/org/apache/poi/ss/formula/functions/MathX.java +++ b/src/java/org/apache/poi/ss/formula/functions/MathX.java @@ -17,6 +17,7 @@ package org.apache.poi.ss.formula.functions; +import org.apache.poi.ss.util.NumberToTextConverter; /** * @author Amol S. Deshmukh < amolweb at ya hoo dot com > @@ -53,7 +54,7 @@ final class MathX { retval = Double.NaN; } else { - retval = java.math.BigDecimal.valueOf(n).setScale(p, java.math.RoundingMode.HALF_UP).doubleValue(); + retval = new java.math.BigDecimal(NumberToTextConverter.toText(n)).setScale(p, java.math.RoundingMode.HALF_UP).doubleValue(); } return retval; diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index e456992a5e..83623a780d 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -1455,4 +1455,13 @@ public final class TestXSSFBugs extends BaseTestBugzillaIssues { Workbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb); assertEquals(0, nwb.getSheetIndex("Market Rates")); } + + @Test + public void bug56315() { + XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56315.xlsx"); + Cell c = wb.getSheetAt(0).getRow(1).getCell(0); + CellValue cv = wb.getCreationHelper().createFormulaEvaluator().evaluate(c); + double rounded = cv.getNumberValue(); + assertEquals(0.1, rounded, 0.0); + } } diff --git a/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java b/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java index 1f5c4c8e84..63e0c6c97c 100644 --- a/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java +++ b/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java @@ -20,6 +20,8 @@ */ package org.apache.poi.ss.formula.functions; +import java.lang.reflect.Constructor; + import org.apache.poi.ss.formula.functions.XYNumericFunction.Accumulator; @@ -249,6 +251,16 @@ public class TestMathX extends AbstractNumericTestCase { } public void testProduct() { + assertEquals("Product ", 0, MathX.product(null)); + assertEquals("Product ", 0, MathX.product(new double[] {})); + assertEquals("Product ", 0, MathX.product(new double[] {1, 0})); + + assertEquals("Product ", 1, MathX.product(new double[] { 1 })); + assertEquals("Product ", 1, MathX.product(new double[] { 1, 1 })); + assertEquals("Product ", 10, MathX.product(new double[] { 10, 1 })); + assertEquals("Product ", -2, MathX.product(new double[] { 2, -1 })); + assertEquals("Product ", 99988000209999d, MathX.product(new double[] { 99999, 99999, 9999 })); + double[] d = new double[100]; d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; d[4] = 5.1; d[5] = 6.1; d[6] = 7.1; d[7] = 8.1; @@ -256,8 +268,8 @@ public class TestMathX extends AbstractNumericTestCase { d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; - double m = MathX.min(d); - assertEquals("Min ", 0, m); + double m = MathX.product(d); + assertEquals("Product ", 0, m); d = new double[20]; d[0] = 1.1; d[1] = 2.1; d[2] = 3.1; d[3] = 4.1; @@ -266,20 +278,12 @@ public class TestMathX extends AbstractNumericTestCase { d[12] = 13.1; d[13] = 14.1; d[14] = 15.1; d[15] = 16.1; d[16] = 17.1; d[17] = 18.1; d[18] = 19.1; d[19] = 20.1; - m = MathX.min(d); - assertEquals("Min ", 1.1, m); + m = MathX.product(d); + assertEquals("Product ", 3459946360003355534d, m); d = new double[1000]; - m = MathX.min(d); - assertEquals("Min ", 0, m); - - d[0] = -1.1; d[1] = 2.1; d[2] = -3.1; d[3] = 4.1; - d[4] = -5.1; d[5] = 6.1; d[6] = -7.1; d[7] = 8.1; - d[8] = -9.1; d[9] = 10.1; d[10] = -11.1; d[11] = 12.1; - d[12] = -13.1; d[13] = 14.1; d[14] = -15.1; d[15] = 16.1; - d[16] = -17.1; d[17] = 18.1; d[18] = -19.1; d[19] = 20.1; - m = MathX.min(d); - assertEquals("Min ", -19.1, m); + m = MathX.product(d); + assertEquals("Product ", 0, m); d = new double[20]; d[0] = -1.1; d[1] = -2.1; d[2] = -3.1; d[3] = -4.1; @@ -287,8 +291,8 @@ public class TestMathX extends AbstractNumericTestCase { d[8] = -9.1; d[9] = -10.1; d[10] = -11.1; d[11] = -12.1; d[12] = -13.1; d[13] = -14.1; d[14] = -15.1; d[15] = -16.1; d[16] = -17.1; d[17] = -18.1; d[18] = -19.1; d[19] = -20.1; - m = MathX.min(d); - assertEquals("Min ", -20.1, m); + m = MathX.product(d); + assertEquals("Product ", 3459946360003355534d, m); } public void testMod() { @@ -299,10 +303,14 @@ public class TestMathX extends AbstractNumericTestCase { assertEquals(-1.0, MathX.mod(3, -2)); assertEquals(-1.0, MathX.mod(-3, -2)); + assertEquals(0.0, MathX.mod(0, 2)); + assertEquals(Double.NaN, MathX.mod(3, 0)); assertEquals((double) 1.4, MathX.mod(3.4, 2)); assertEquals((double) -1.4, MathX.mod(-3.4, -2)); assertEquals((double) 0.6000000000000001, MathX.mod(-3.4, 2.0));// should actually be 0.6 assertEquals((double) -0.6000000000000001, MathX.mod(3.4, -2.0));// should actually be -0.6 + assertEquals(3.0, MathX.mod(3, Double.MAX_VALUE)); + assertEquals(2.0, MathX.mod(Double.MAX_VALUE, 3)); // Bugzilla 50033 assertEquals(1.0, MathX.mod(13, 12)); @@ -675,6 +683,27 @@ public class TestMathX extends AbstractNumericTestCase { d = 2162.615d; p = 2; assertEquals("round ", 2162.62d, MathX.round(d, p)); + + d = 0.049999999999999975d; p = 2; + assertEquals("round ", 0.05d, MathX.round(d, p)); + + d = 0.049999999999999975d; p = 1; + assertEquals("round ", 0.1d, MathX.round(d, p)); + + d = Double.NaN; p = 1; + assertEquals("round ", Double.NaN, MathX.round(d, p)); + + d = Double.POSITIVE_INFINITY; p = 1; + assertEquals("round ", Double.NaN, MathX.round(d, p)); + + d = Double.NEGATIVE_INFINITY; p = 1; + assertEquals("round ", Double.NaN, MathX.round(d, p)); + + d = Double.MAX_VALUE; p = 1; + assertEquals("round ", Double.MAX_VALUE, MathX.round(d, p)); + + d = Double.MIN_VALUE; p = 1; + assertEquals("round ", 0.0d, MathX.round(d, p)); } public void testRoundDown() { @@ -722,6 +751,27 @@ public class TestMathX extends AbstractNumericTestCase { d = 150.0; p = -2; assertEquals("roundDown ", 100, MathX.roundDown(d, p)); + + d = 0.049999999999999975d; p = 2; + assertEquals("round ", 0.04d, MathX.roundDown(d, p)); + + d = 0.049999999999999975d; p = 1; + assertEquals("round ", 0.0d, MathX.roundDown(d, p)); + + d = Double.NaN; p = 1; + assertEquals("round ", Double.NaN, MathX.roundDown(d, p)); + + d = Double.POSITIVE_INFINITY; p = 1; + assertEquals("round ", Double.NaN, MathX.roundDown(d, p)); + + d = Double.NEGATIVE_INFINITY; p = 1; + assertEquals("round ", Double.NaN, MathX.roundDown(d, p)); + + d = Double.MAX_VALUE; p = 1; + assertEquals("round ", Double.MAX_VALUE, MathX.roundDown(d, p)); + + d = Double.MIN_VALUE; p = 1; + assertEquals("round ", 0.0d, MathX.roundDown(d, p)); } public void testRoundUp() { @@ -769,6 +819,27 @@ public class TestMathX extends AbstractNumericTestCase { d = 150.0; p = -2; assertEquals("roundUp ", 200, MathX.roundUp(d, p)); + + d = 0.049999999999999975d; p = 2; + assertEquals("round ", 0.05d, MathX.roundUp(d, p)); + + d = 0.049999999999999975d; p = 1; + assertEquals("round ", 0.1d, MathX.roundUp(d, p)); + + d = Double.NaN; p = 1; + assertEquals("round ", Double.NaN, MathX.roundUp(d, p)); + + d = Double.POSITIVE_INFINITY; p = 1; + assertEquals("round ", Double.NaN, MathX.roundUp(d, p)); + + d = Double.NEGATIVE_INFINITY; p = 1; + assertEquals("round ", Double.NaN, MathX.roundUp(d, p)); + + d = Double.MAX_VALUE; p = 1; + assertEquals("round ", Double.MAX_VALUE, MathX.roundUp(d, p)); + + d = Double.MIN_VALUE; p = 1; + assertEquals("round ", 0.1d, MathX.roundUp(d, p)); } public void testCeiling() { @@ -877,4 +948,14 @@ public class TestMathX extends AbstractNumericTestCase { assertEquals("floor ", 0, MathX.floor(d, s)); } + public void testCoverage() throws Exception { + // get the default constructor + final Constructor c = MathX.class.getDeclaredConstructor(new Class[] {}); + + // make it callable from the outside + c.setAccessible(true); + + // call it + c.newInstance((Object[]) null); + } } diff --git a/test-data/spreadsheet/56315.xlsx b/test-data/spreadsheet/56315.xlsx new file mode 100644 index 0000000000000000000000000000000000000000..10e167ae6a14a54236e619cd6a7bbc50c96763e4 GIT binary patch literal 7815 zcmeHM^wWgKbTlw9DFN63TmS&T3LyNxguIUi0K{Md z02BaRbaQ1lS8rQa?*|3}?zUd{1^iu{Sqd=Gx$*(%sOSG}|HUIvkv8D^PKZ?XB>kKu z<+UMXy`YQ-onS`5oca*MB1nnvBDGiN6rnw69j-^p12j*B?!0Y;iex!ga8Huz1{uEs z-?GAz+(`G0&k7k@7=RjCk!#~EF@AC;_`tb@XdVp)0totjO^Me$8oP8 zKebmau(jA@S=QqVlPAW$XAzj$Q6%C+?<^Y_d8a~?XI}g;V2vH7Xr$>E&RA~8hWVmM zZ8@#_xima2NJ;gX6t+3zUbwT+yvDlk753JF+=gR;kj|kN+Fe3~J-ccx%FBpNS2bLCQM0C6Gphkp2m20l!{Zn13Ma=A7%-6a z?C9~@h`jmy&Z>g}GXq3WyQ_&yu~rlkH09`DnP)_j4`G8kQ1;R%GJuWe=A9q-7p7%H zrVyod@$vLcX%{yjVvP4F3HG-FSr^4$<$6z$fb}O@Lf^1BX~J7TbiXbp#F&JAZMJc55Z=Qk;^$_aqke> zDCxnb5&KbX@j!MV&P(C@_i%wc`BED+boPs;wlNdZeb^PyH@<|=9?g#2l z9V$!c0k~-X&Vv6*6hAjlCy1My)6eSlXVRddN*Jon{dc#D)Dcj(5NXdYY?pM^b-ap= zEqh0i?3m{WO-b72U9oy!!o&09s5o<)jH9uE4vDVd1wrM;=IYLP63u0Zjt&(EE+J>5 zz#;y8R%SL1X}5I!Om7%IC;Rlol<}4YT*& zOrg41XA_0VGPj$G<{p^3tLGKvIQhiF&7OP}Eygfav6M{YPd!*>8=T7$x)XF@(?CEl zSbG#i7Vu+y(+xGze`ieS4Q;Jx6bm9zj3Gfifnv<>TuDe(bX#H}joeK?mS@?iV$7b_ zrfl9UYo4(v1I@Z5QH6UX#C4+AX(K~$pyOI@w~3O3nGcxezcAovUBM3ev!Vp5L_r<6 zyy>4M4o8+o3X8jOT-m246_gh`#;6Bnm;Nu++=T*KJ(SX43@_kP34t);>P-v6lWZ^I@oH!oV&v}Eo z#{0;=F8Pn(tQ7m-7l!82%d3|-bW}pY*ocoiV=TWNz(uipUmg7Qn%t)DS zVF}HtltPPLRrladXs-^Y5Oz*kPic_|eO#88TySdzK1mgI_ zP1j~uY5}`lqnR9@Wtiv}D2%Cx0B4rM(7KwynP|=1Le9J&(VM;VIL)IgG0v%u)j)N$ z0~%x4z{!bd6}E@L$c3dFK1VZ;V&KYb>?%%vfpe+f;h6%;|{^J0|T3GVrpQ-qV@_yWdO(|Kd9mS~S~*nMGP#ptO2f99c-}Fl zvlw#Lpm$c7u4+gl)165 z=Q(!ACy}bD?><2dIk{+73piSpe5zSTS9dC(jPiwTEs1QqHt1G1pQ2wLaXd4Bu3a(; zvaMAi`XD!%xd)uE3ke$P1SY?i$k;m?_I*9;y@zeHKg1D?1eKfm?PF7l(BI4aI0L^7 z1>KI;l$_;{bOQvx$a+V^6%!@fBo>dCG*M;}zZW8K4)~}LMfEr(>!uAXB<7ny;vE=d z2;fF;@w8UC&4PZcCHW9b%U~8E)R|015Lli25V?7#f4F(}WGQ+ik@?E|VtsVKiq zHurMuxrEn7oTylQp(Qu4Y9{# zKVyY`E)2`OIX@f52uQI#-Wm|R3^ni@jAm$3Q^OquMKF`EKHZnj4CPiH9|TkOT>(8VY5o@h6ke7pJ9`ci-MV^F zj2|WWAo9ujxVj0)Z+RZ=dPpcA%ABJ0^3H{1lK0M)(7ig7VK)|k_TPvbM`K~Qd3w0&(R$L-fk!a6Ud4HWZ))7_WAJh0I4?@}%_Y+-h$LS1VbV){S_Yb$2exo~E_47{-Awi|Z@OdE%1tuuef2f5Dikf+_JAu9 z8*<^jT%h?V`E&+t6y3N*h)5C3@6&2KOZ0jBkd#JO;jJ9@2{fYQ{2Jz$I;3?ZwtXrK z+}(~F^S0`BY00srus0&LkfaZ+VCDN5*@12+I=FDxGIcX-!X2B7x4RD5kJ0CrDG!ET z+MLZhc`fGc1Urj7(sN}LC(^P*T6DMEHrXokZ8go^ZdFx1>@FB8gPNiZ;+re-Y9Xd5 zzvh@%R+?zgSKku(+7UlM-(@vV%Hf_8amYN4EZuu{xd@l|#3~;)6Ss(n$Ws1dAt455cfmkVDYEI99lx1B|PEqXr`yKVau->hQ=z} z|5122ajb*(QNp7bRpMxW2@fxOTU&20!9Om4RI-F8T1n$Vq*1$S7dQhYkwuLZW}p{h z<#BZo5W8 z3bpBYS^jpH@q?$Njae@Kej@Otj5N83A6}PHx=~G|BQ&4jMzO>s~_yZHsC= zKlEO}>}5Q3_&0d^jJQ`+6%Pgo=t^0BD$yTx_Mw--#B9|L#Y)mcVKfNZ*D z`6qj5Gt`~jzI$s=t<^e4=;AK?g8O}C#J0+Ba2R+_Md1XUVm7&=7eB8(BfK4BWV7Ny zWCVDmdxsL3-a-L-9DRFB$6-lKjS7i5eFDvet{O1DyCpMni>!n8bM zXcx!IbBtBn1dE;brzG-1atfsuUNA&rc;ykq%m@mwFyi~4{_t;aGvja@KEu?wf+saT z)(bN2f^Im@1&k@(Iu$PIew>RJ((g~pn>2xBej3VJ!|py;u?+7 z1xkVbU7zxhjJMoD-C01naYX;2iI;bPv#r-pl~b5z?v^b?8Mbh9Tizt}CMVxUDsE|7 zv08eQqnU_9H8Cw@g2pDBT70|xgqA$DlqCBTO>l7H<@54bH*@Zf1fm&E>5Cie-7zmM zXeAQHylA5(vOzg?;w0W9Ku+x+qnKEchl0Fu- zgB3BO4Hc8GMGVQbEWaH!%5+7SEEq^#+X_k3sAE?EE$Q(>b*q3Y`9@*KOhzN1_Oik3 z7Ej6ao@0qXIJy`ar$detoXAs$4%d0}Rvbt?XWFBWFA!3_9-UzjV6DzUGiSB4OR+2c z81WwVoIn_xJRmP$&6+F5`i^al;@yC$iA7z-UD-^+wJVP%k^1aA2k``#xA&T-io;B$ zG8YR$)}0_6Cc9`W2Zdkd+976+sfOWg+Zrgjj&>tiU9W@MO_6zP`-!x$# zZ(zK#AK}0a!O3A>?F{6RF(|O&aEX|G+=H37;#RTvIH5;>;Vjqryk1Lvqc1YKkee7l4_h7qZ`fl|-^K)8iN^4x6HoetDNiaJmq zMAl$uYh`<|gX>Su86G^Mx*H#9u7BehD4<#U=d>1EK8V&_0d zOZc;mUMS(FqG>P$15u2n>O8&`Ane|3>3dg4FBwW0+t(3~*#mCg;KvT~T{oS;rpBmB zJ1T52xl2~0&g-GI!+n=;VpVk@3lHj{!ht>4u&vl@22gf06c_Tz555HO1RFf`z(SfJ z8>1yqfAjB|Ba1F{6^>e9uTa@Tit++%+^lsx-Q2wd!S3!qJA$&!{&fIQBbS5v#GMr) zjNHXIXC|DZka7_s46i7#H#__YK}+?88Wvl##@WPFq-;$kHKx4(9Ju*-ImrK9MSYIx zSk8$R9uFsd+&8Mb21+zdpC2yW#J&eI)=OkVny06-8UJ-)U>@=4tycZ&73Rzbzp}QKL(UHVU;6!I$TY z9o87&+X}Wg9xT&ve}cZ({L_W3vEPq(hnlLi4;O`7?&Y>DWJ21Ewdygx+mvglq*AKp z2k;ior`a0{exr-aftuA6Pv{h2NQzZgRpYyIx$(=+PKF(kTTp7w=(>XHkLGm}7#Cm= z0lWy}uT2ecXYTbtXIi8cu){P7dz`| z_K{UO;y@|SA{li42v3yAM{o|7_bz2#MhbR+isSzL6z(NnsJUG0;fo$UX217nhP{_! zgJ-rTD`v1aG6qSN2P{4Yy9(#%NNoI0L|mG=6_W}jQ&CCW+VZXThov;7{dyv?0 zy#B)`ppM301N?P|;!ohuYX(Z<{