You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ce...@apache.org on 2017/09/28 14:36:53 UTC
svn commit: r1810007 - in /poi:
site/src/documentation/content/xdocs/status.xml
trunk/src/java/org/apache/poi/ss/formula/functions/MathX.java
trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java
Author: centic
Date: Thu Sep 28 14:36:53 2017
New Revision: 1810007
URL: http://svn.apache.org/viewvc?rev=1810007&view=rev
Log:
Bug 61064: Support behavior of function CEILING in newer versions of Microsoft Excel
Modified:
poi/site/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/ss/formula/functions/MathX.java
poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java
Modified: poi/site/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/site/src/documentation/content/xdocs/status.xml?rev=1810007&r1=1810006&r2=1810007&view=diff
==============================================================================
--- poi/site/src/documentation/content/xdocs/status.xml (original)
+++ poi/site/src/documentation/content/xdocs/status.xml Thu Sep 28 14:36:53 2017
@@ -61,10 +61,13 @@
<summary-item>Removed support for Java 6 and 7 making Java 8 the minimum version supported</summary-item>
<summary-item>Updated third party libraries for bouncy-castle, xmlsec, slf4j-api and curvesapi</summary-item>
<summary-item>Added a dependency on commons-math3 for certain functionality</summary-item>
+ <summary-item>Adjust return type of some methods in Cell and related classes from int to CellType</summary-item>
<summary-item>Removal of deprecated classes and methods that were marked for removal in v4.0</summary-item>
<summary-item>Removal of deprecated classes and methods that were marked for removal in v3.18</summary-item>
</summary>
<actions>
+ <action dev="PD" type="fix" fixes-bug="61064" module="SS Common">Support behavior of function CEILING in newer versions of Microsoft Excel</action>
+ <action dev="PD" type="fix" fixes-bug="61516" module="SS Common">Correctly handle references that end up outside the workbook when cells with formulas are copied</action>
<action dev="PD" type="add" fixes-bug="60737" module="XSSF">Add endSheet() to XSSFEventBasedExcelExtractor</action>
<action dev="PD" type="fix" fixes-bug="59747" module="OPC">Exchange order of writing parts into Zip to allow some tools to handle files better</action>
<action dev="PD" type="add" fixes-bug="github-69" module="SS Common">Support matrix functions</action>
@@ -82,7 +85,6 @@
<summary-item>SXSSF: fix XML processing - unicode surrogates and line breaks (#61048, #61246)</summary-item>
</summary>
<actions>
- <action dev="PD" type="fix" fixes-bug="61516" module="SS Common">Correctly handle references that end up outside the workbook when cells with formulas are copied</action>
<action dev="PD" type="fix" fixes-bug="61478" module="OPC">POI OOXML-Schema lookup uses wrong classloader</action>
<action dev="PD" type="fix" fixes-bug="61470" module="XWPF">Handle ruby (phonetic) elements in XWPFRun</action>
<action dev="PD" type="fix" fixes-bug="61381" module="POIFS">PushbackInputStreams passed to ZipHelper may not hold 8 bytes</action>
Modified: poi/trunk/src/java/org/apache/poi/ss/formula/functions/MathX.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/MathX.java?rev=1810007&r1=1810006&r2=1810007&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/formula/functions/MathX.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/MathX.java Thu Sep 28 14:36:53 2017
@@ -245,10 +245,9 @@ final class MathX {
public static double ceiling(double n, double s) {
double c;
- if ((n<0 && s>0) || (n>0 && s<0)) {
+ if (n>0 && s<0) {
c = Double.NaN;
- }
- else {
+ } else {
c = (n == 0 || s == 0) ? 0 : Math.ceil(n/s) * s;
}
Modified: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java?rev=1810007&r1=1810006&r2=1810007&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestMathX.java Thu Sep 28 14:36:53 2017
@@ -30,7 +30,7 @@ import org.apache.poi.ss.formula.functio
public class TestMathX extends AbstractNumericTestCase {
public void testAcosh() {
- double d = 0;
+ double d;
d = MathX.acosh(0);
assertTrue("Acosh 0 is NaN", Double.isNaN(d));
@@ -53,7 +53,7 @@ public class TestMathX extends AbstractN
}
public void testAsinh() {
- double d = 0;
+ double d;
d = MathX.asinh(0);
assertEquals("asinh 0", d, 0);
@@ -79,7 +79,7 @@ public class TestMathX extends AbstractN
}
public void testAtanh() {
- double d = 0;
+ double d;
d = MathX.atanh(0);
assertEquals("atanh 0", d, 0);
@@ -110,7 +110,7 @@ public class TestMathX extends AbstractN
}
public void testCosh() {
- double d = 0;
+ double d;
d = MathX.cosh(0);
assertEquals("cosh 0", 1, d);
@@ -141,7 +141,7 @@ public class TestMathX extends AbstractN
}
public void testTanh() {
- double d = 0;
+ double d;
d = MathX.tanh(0);
assertEquals("tanh 0", 0, d);
@@ -366,7 +366,7 @@ public class TestMathX extends AbstractN
final short minus = -1;
final short zero = 0;
final short plus = 1;
- double d = 0;
+ double d;
assertEquals("Sign ", minus, MathX.sign(minus));
@@ -415,7 +415,7 @@ public class TestMathX extends AbstractN
}
public void testSinh() {
- double d = 0;
+ double d;
d = MathX.sinh(0);
assertEquals("sinh 0", 0, d);
@@ -511,8 +511,8 @@ public class TestMathX extends AbstractN
}
public void testFactorial() {
- int n = 0;
- double s = 0;
+ int n;
+ double s;
n = 0;
s = MathX.factorial(n);
@@ -540,8 +540,8 @@ public class TestMathX extends AbstractN
}
public void testSumx2my2() {
- double[] xarr = null;
- double[] yarr = null;
+ double[] xarr;
+ double[] yarr;
xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10};
yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
@@ -565,8 +565,8 @@ public class TestMathX extends AbstractN
}
public void testSumx2py2() {
- double[] xarr = null;
- double[] yarr = null;
+ double[] xarr;
+ double[] yarr;
xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10};
yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
@@ -590,8 +590,8 @@ public class TestMathX extends AbstractN
}
public void testSumxmy2() {
- double[] xarr = null;
- double[] yarr = null;
+ double[] xarr;
+ double[] yarr;
xarr = new double[]{1, 2, 3, 4, 5, 6, 7, 8, 9, 10};
yarr = new double[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9};
@@ -634,8 +634,8 @@ public class TestMathX extends AbstractN
}
public void testRound() {
- double d = 0;
- int p = 0;
+ double d;
+ int p;
d = 0; p = 0;
assertEquals("round ", 0, MathX.round(d, p));
@@ -705,8 +705,8 @@ public class TestMathX extends AbstractN
}
public void testRoundDown() {
- double d = 0;
- int p = 0;
+ double d;
+ int p;
d = 0; p = 0;
assertEquals("roundDown ", 0, MathX.roundDown(d, p));
@@ -776,8 +776,8 @@ public class TestMathX extends AbstractN
}
public void testRoundUp() {
- double d = 0;
- int p = 0;
+ double d;
+ int p;
d = 0; p = 0;
assertEquals("roundUp ", 0, MathX.roundUp(d, p));
@@ -852,8 +852,8 @@ public class TestMathX extends AbstractN
}
public void testCeiling() {
- double d = 0;
- double s = 0;
+ double d;
+ double s;
d = 0; s = 0;
assertEquals("ceiling ", 0, MathX.ceiling(d, s));
@@ -902,11 +902,43 @@ public class TestMathX extends AbstractN
d = 2d/3; s = 3.33;
assertEquals("ceiling ", 3.33, MathX.ceiling(d, s));
+
+ // samples from http://www.excelfunctions.net/Excel-Ceiling-Function.html
+ // and https://support.office.com/en-us/article/CEILING-function-0a5cd7c8-0720-4f0a-bd2c-c943e510899f
+ d = 22.25; s = 0.1;
+ assertEquals("ceiling ", 22.3, MathX.ceiling(d, s));
+ d = 22.25; s = 0.5;
+ assertEquals("ceiling ", 22.5, MathX.ceiling(d, s));
+ d = 22.25; s = 1;
+ assertEquals("ceiling ", 23, MathX.ceiling(d, s));
+ d = 22.25; s = 10;
+ assertEquals("ceiling ", 30, MathX.ceiling(d, s));
+ d = 22.25; s = 20;
+ assertEquals("ceiling ", 40, MathX.ceiling(d, s));
+ d = -22.25; s = -0.1;
+ assertEquals("ceiling ", -22.3, MathX.ceiling(d, s));
+ d = -22.25; s = -1;
+ assertEquals("ceiling ", -23, MathX.ceiling(d, s));
+ d = -22.25; s = -5;
+ assertEquals("ceiling ", -25, MathX.ceiling(d, s));
+
+ d = 22.25; s = 1;
+ assertEquals("ceiling ", 23, MathX.ceiling(d, s));
+ d = 22.25; s = -1;
+ assertEquals("ceiling ", Double.NaN, MathX.ceiling(d, s));
+ d = -22.25; s = 1;
+ assertEquals("ceiling ", -22, MathX.ceiling(d, s)); // returns an error in Excel 2007 & earlier
+ d = -22.25; s = -1;
+ assertEquals("ceiling ", -23, MathX.ceiling(d, s));
+
+ // test cases for newer versions of Excel where d can be negative for
+ d = -11.12333; s = 0.03499;
+ assertEquals("ceiling ", -11.09183, MathX.ceiling(d, s));
}
public void testFloor() {
- double d = 0;
- double s = 0;
+ double d;
+ double s;
d = 0; s = 0;
assertEquals("floor ", 0, MathX.floor(d, s));
@@ -955,5 +987,18 @@ public class TestMathX extends AbstractN
d = 2d/3; s = 3.33;
assertEquals("floor ", 0, MathX.floor(d, s));
+
+ // samples from http://www.excelfunctions.net/Excel-Ceiling-Function.html
+ // and https://support.office.com/en-us/article/CEILING-function-0a5cd7c8-0720-4f0a-bd2c-c943e510899f
+ d = 3.7; s = 2;
+ assertEquals("floor ", 2, MathX.floor(d, s));
+ d = -2.5; s = -2;
+ assertEquals("floor ", -2, MathX.floor(d, s));
+ d = 2.5; s = -2;
+ assertEquals("floor ", Double.NaN, MathX.floor(d, s));
+ d = 1.58; s = 0.1;
+ assertEquals("floor ", 1.5, MathX.floor(d, s));
+ d = 0.234; s = 0.01;
+ assertEquals("floor ", 0.23, MathX.floor(d, s));
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org