From bf72b9c5f57381ce6b2c91ddbad5734812f37455 Mon Sep 17 00:00:00 2001 From: Chris Boyle Date: Mon, 1 Jun 2015 16:47:46 +0100 Subject: [PATCH] Format numbers more like Excel does. Tweak the value in TestHSSFDataFormatter's case that falls back to General to still produce a decimal separator. --- .../org/apache/poi/ss/usermodel/DataFormatter.java | 31 +++-------- .../poi/ss/usermodel/ExcelGeneralNumberFormat.java | 65 ++++++++++++++++++++++ .../poi/hssf/usermodel/TestHSSFDataFormatter.java | 12 ++-- .../apache/poi/ss/usermodel/TestDataFormatter.java | 53 ++++++++++++++++++ 4 files changed, 134 insertions(+), 27 deletions(-) create mode 100644 src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java diff --git a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java index b5bf2a8..b74b9e8 100644 --- a/src/java/org/apache/poi/ss/usermodel/DataFormatter.java +++ b/src/java/org/apache/poi/ss/usermodel/DataFormatter.java @@ -162,11 +162,8 @@ public class DataFormatter implements Observer { */ private DateFormatSymbols dateSymbols; - /** General format for whole numbers. */ - private Format generalWholeNumFormat; - - /** General format for decimal numbers. */ - private Format generalDecimalNumFormat; + /** General format for numbers. */ + private Format generalNumberFormat; /** A default format to use when a number pattern cannot be parsed. */ private Format defaultNumFormat; @@ -308,10 +305,7 @@ public class DataFormatter implements Observer { // Is it one of the special built in types, General or @? if ("General".equalsIgnoreCase(formatStr) || "@".equals(formatStr)) { - if (isWholeNumber(cellValue)) { - return generalWholeNumFormat; - } - return generalDecimalNumFormat; + return generalNumberFormat; } // Build a formatter, and cache it @@ -378,10 +372,7 @@ public class DataFormatter implements Observer { } if ("General".equalsIgnoreCase(formatStr) || "@".equals(formatStr)) { - if (isWholeNumber(cellValue)) { - return generalWholeNumFormat; - } - return generalDecimalNumFormat; + return generalNumberFormat; } if(DateUtil.isADateFormat(formatIndex,formatStr) && @@ -682,10 +673,7 @@ public class DataFormatter implements Observer { // otherwise use general format } - if (isWholeNumber(cellValue)){ - return generalWholeNumFormat; - } - return generalDecimalNumFormat; + return generalNumberFormat; } /** @@ -735,7 +723,8 @@ public class DataFormatter implements Observer { if (numberFormat == null) { return String.valueOf(d); } - return numberFormat.format(new Double(d)); + String formatted = numberFormat.format(new Double(d)); + return formatted.replaceFirst("E(\\d)", "E+$1"); // to match Excel's E-notation } /** @@ -888,8 +877,7 @@ public class DataFormatter implements Observer { Iterator> itr = formats.entrySet().iterator(); while(itr.hasNext()) { Map.Entry entry = itr.next(); - if (entry.getValue() == generalDecimalNumFormat - || entry.getValue() == generalWholeNumFormat) { + if (entry.getValue() == generalNumberFormat) { entry.setValue(format); } } @@ -969,8 +957,7 @@ public class DataFormatter implements Observer { dateSymbols = DateFormatSymbols.getInstance(locale); decimalSymbols = DecimalFormatSymbols.getInstance(locale); - generalWholeNumFormat = new DecimalFormat("#", decimalSymbols); - generalDecimalNumFormat = new DecimalFormat("#.##########", decimalSymbols); + generalNumberFormat = new ExcelGeneralNumberFormat(locale); // init built-in formats diff --git a/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java b/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java new file mode 100644 index 0000000..8754fd8 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java @@ -0,0 +1,65 @@ +package org.apache.poi.ss.usermodel; + +import java.math.BigDecimal; +import java.math.MathContext; +import java.math.RoundingMode; +import java.text.DecimalFormat; +import java.text.DecimalFormatSymbols; +import java.text.FieldPosition; +import java.text.Format; +import java.text.ParsePosition; +import java.util.Locale; + +/** A format that formats a double as Excel would, ignoring FieldPosition. All other operations are unsupported. */ +public class ExcelGeneralNumberFormat extends Format { + + private static final long serialVersionUID = 1L; + + private static final DecimalFormat SCIENTIFIC_FORMAT = new DecimalFormat("0.#####E0"); + static { + SCIENTIFIC_FORMAT.setRoundingMode(RoundingMode.HALF_UP); + } + private static final MathContext TO_10_SF = new MathContext(10, RoundingMode.HALF_UP); + + private final DecimalFormatSymbols decimalSymbols; + private final Format wholeNumFormat; + private final Format decimalNumFormat; + + public ExcelGeneralNumberFormat(final Locale locale) { + decimalSymbols = new DecimalFormatSymbols(locale); + wholeNumFormat = new DecimalFormat("#", decimalSymbols); + decimalNumFormat = new DecimalFormat("#.##########", decimalSymbols); + } + + public StringBuffer format(Object number, StringBuffer toAppendTo, FieldPosition pos) { + final double value; + if (number instanceof Number) { + value = ((Number)number).doubleValue(); + if (Double.isInfinite(value) || Double.isNaN(value)) { + return wholeNumFormat.format(number, toAppendTo, pos); + } + } else { + // testBug54786 gets here with a date, so retain previous behaviour + return wholeNumFormat.format(number, toAppendTo, pos); + } + + final double abs = Math.abs(value); + if (abs >= 1E11 || (abs <= 1E-10 && abs > 0)) { + return SCIENTIFIC_FORMAT.format(number, toAppendTo, pos); + } else if (Math.floor(value) == value || abs >= 1E10) { + // integer, or integer portion uses all 11 allowed digits + return wholeNumFormat.format(number, toAppendTo, pos); + } + // Non-integers of non-scientific magnitude are formatted as "up to 11 + // numeric characters, with the decimal point counting as a numeric + // character". We know there is a decimal point, so limit to 10 digits. + // https://support.microsoft.com/en-us/kb/65903 + final double rounded = new BigDecimal(value).round(TO_10_SF).doubleValue(); + return decimalNumFormat.format(rounded, toAppendTo, pos); + } + + public Object parseObject(String source, ParsePosition pos) { + throw new UnsupportedOperationException(); + } + +} diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java index 3b2588f..0f0a09f 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 @@ -292,7 +294,7 @@ public final class TestHSSFDataFormatter { // 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 1251f1e..0afc7d1 100644 --- a/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java +++ b/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java @@ -660,4 +660,57 @@ public class TestDataFormatter { assertTrue(DateUtil.isADateFormat(-1, "dd/mm/yy;[red]dd/mm/yy")); assertTrue(DateUtil.isADateFormat(-1, "[h]")); } + + 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( "123.4567893", 123.45678925); // rounding mode is half-up + assertFormatsTo("-123.4567893", -123.45678925); + 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(); + } + } } -- 1.9.3