aboutsummaryrefslogtreecommitdiffstats
path: root/src/testcases
diff options
context:
space:
mode:
authorDavid North <dnorth@apache.org>2015-10-06 09:56:26 +0000
committerDavid North <dnorth@apache.org>2015-10-06 09:56:26 +0000
commit940f3c5af39ce547f063b694b9404394301c5844 (patch)
treeeb2318db82cfebcb59f047d7ab14a9e9cd89c36f /src/testcases
parent72bae7a1e66f911a631b8b6cfc7aa69125babcac (diff)
downloadpoi-940f3c5af39ce547f063b694b9404394301c5844.tar.gz
poi-940f3c5af39ce547f063b694b9404394301c5844.zip
Format numbers more like Excel does
Thanks to Chris Boyle for the patch https://bz.apache.org/bugzilla/show_bug.cgi?id=58471 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1706971 13f79535-47bb-0310-9956-ffa450edef68
Diffstat (limited to 'src/testcases')
-rw-r--r--src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java13
-rw-r--r--src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java55
2 files changed, 62 insertions, 6 deletions
diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
index 3b2588f075..dbb99c0a86 100644
--- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
+++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
@@ -171,7 +171,8 @@ public final class TestHSSFDataFormatter {
// create cells with bad num patterns
for (int i = 0; i < badNumPatterns.length; i++) {
HSSFCell cell = row.createCell(i);
- cell.setCellValue(1234567890.12345);
+ // If the '.' is any later, ExcelGeneralNumberFormat will render an integer, as Excel does.
+ cell.setCellValue(12345678.9012345);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(format.getFormat(badNumPatterns[i]));
cell.setCellStyle(cellStyle);
@@ -276,10 +277,11 @@ public final class TestHSSFDataFormatter {
log("\n==== VALID NUMBER FORMATS ====");
while (it.hasNext()) {
HSSFCell cell = (HSSFCell) it.next();
- log(formatter.formatCellValue(cell));
+ final String formatted = formatter.formatCellValue(cell);
+ log(formatted);
- // should not be equal to "1234567890.12345"
- assertTrue( ! "1234567890.12345".equals(formatter.formatCellValue(cell)));
+ // should not include "12345678" - note that the input value was negative
+ assertTrue(formatted != null && ! formatted.contains("12345678"));
}
// test bad number formats
@@ -289,10 +291,9 @@ public final class TestHSSFDataFormatter {
while (it.hasNext()) {
HSSFCell cell = (HSSFCell) it.next();
log(formatter.formatCellValue(cell));
- // should be equal to "1234567890.12345"
// in some locales the the decimal delimiter is a comma, not a dot
char decimalSeparator = new DecimalFormatSymbols(LocaleUtil.getUserLocale()).getDecimalSeparator();
- assertEquals("1234567890" + decimalSeparator + "12345", formatter.formatCellValue(cell));
+ assertEquals("12345678" + decimalSeparator + "9", formatter.formatCellValue(cell));
}
// test Zip+4 format
diff --git a/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java b/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
index 1251f1e8b3..cacb02e9f1 100644
--- a/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
+++ b/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
@@ -660,4 +660,59 @@ public class TestDataFormatter {
assertTrue(DateUtil.isADateFormat(-1, "dd/mm/yy;[red]dd/mm/yy"));
assertTrue(DateUtil.isADateFormat(-1, "[h]"));
}
+
+
+ @Test
+ public void testLargeNumbersAndENotation() throws IOException{
+ assertFormatsTo("1E+86", 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999d);
+ assertFormatsTo("1E-84", 0.000000000000000000000000000000000000000000000000000000000000000000000000000000000001d);
+ // Smallest double
+ assertFormatsTo("1E-323", 0.00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001d);
+
+ // "up to 11 numeric characters, with the decimal point counting as a numeric character"
+ // https://support.microsoft.com/en-us/kb/65903
+ assertFormatsTo( "12345678911", 12345678911d);
+ assertFormatsTo( "1.23457E+11", 123456789112d); // 12th digit of integer -> scientific
+ assertFormatsTo( "-12345678911", -12345678911d);
+ assertFormatsTo( "-1.23457E+11", -123456789112d);
+ assertFormatsTo( "0.1", 0.1);
+ assertFormatsTo( "0.000000001", 0.000000001);
+ assertFormatsTo( "1E-10", 0.0000000001); // 12th digit
+ assertFormatsTo( "-0.000000001", -0.000000001);
+ assertFormatsTo( "-1E-10", -0.0000000001);
+ assertFormatsTo( "123.4567892", 123.45678919); // excess decimals are simply rounded away
+ assertFormatsTo("-123.4567892", -123.45678919);
+ assertFormatsTo( "1.234567893", 1.2345678925); // rounding mode is half-up
+ assertFormatsTo("-1.234567893", -1.2345678925);
+ assertFormatsTo( "1.23457E+19", 12345650000000000000d);
+ assertFormatsTo("-1.23457E+19", -12345650000000000000d);
+ assertFormatsTo( "1.23457E-19", 0.0000000000000000001234565d);
+ assertFormatsTo("-1.23457E-19", -0.0000000000000000001234565d);
+ assertFormatsTo( "1.000000001", 1.000000001);
+ assertFormatsTo( "1", 1.0000000001);
+ assertFormatsTo( "1234.567891", 1234.567891123456789d);
+ assertFormatsTo( "1234567.891", 1234567.891123456789d);
+ assertFormatsTo( "12345678912", 12345678911.63456789d); // integer portion uses all 11 digits
+ assertFormatsTo( "12345678913", 12345678912.5d); // half-up here too
+ assertFormatsTo("-12345678913", -12345678912.5d);
+ assertFormatsTo( "1.23457E+11", 123456789112.3456789d);
+ }
+
+ private static void assertFormatsTo(String expected, double input) throws IOException {
+ Workbook wb = new HSSFWorkbook();
+ try {
+ Sheet s1 = wb.createSheet();
+ Row row = s1.createRow(0);
+ Cell rawValue = row.createCell(0);
+ rawValue.setCellValue(input);
+ CellStyle newStyle = wb.createCellStyle();
+ DataFormat dataFormat = wb.createDataFormat();
+ newStyle.setDataFormat(dataFormat.getFormat("General"));
+ String actual = new DataFormatter().formatCellValue(rawValue);
+ assertEquals(expected, actual);
+ }
+ finally {
+ wb.close();
+ }
+ }
}