diff options
author | David North <dnorth@apache.org> | 2015-10-06 09:56:26 +0000 |
---|---|---|
committer | David North <dnorth@apache.org> | 2015-10-06 09:56:26 +0000 |
commit | 940f3c5af39ce547f063b694b9404394301c5844 (patch) | |
tree | eb2318db82cfebcb59f047d7ab14a9e9cd89c36f /src/testcases | |
parent | 72bae7a1e66f911a631b8b6cfc7aa69125babcac (diff) | |
download | poi-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.java | 13 | ||||
-rw-r--r-- | src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java | 55 |
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(); + } + } } |