You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by dn...@apache.org on 2015/10/06 11:56:26 UTC
svn commit: r1706971 - in /poi/trunk/src: java/org/apache/poi/ss/usermodel/
testcases/org/apache/poi/hssf/usermodel/
testcases/org/apache/poi/ss/usermodel/
Author: dnorth
Date: Tue Oct 6 09:56:26 2015
New Revision: 1706971
URL: http://svn.apache.org/viewvc?rev=1706971&view=rev
Log:
Format numbers more like Excel does
Thanks to Chris Boyle for the patch
https://bz.apache.org/bugzilla/show_bug.cgi?id=58471
Added:
poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java
Modified:
poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java?rev=1706971&r1=1706970&r2=1706971&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java (original)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/DataFormatter.java Tue Oct 6 09:56:26 2015
@@ -162,11 +162,8 @@ public class DataFormatter implements Ob
*/
private DateFormatSymbols dateSymbols;
- /** <em>General</em> format for whole numbers. */
- private Format generalWholeNumFormat;
-
- /** <em>General</em> format for decimal numbers. */
- private Format generalDecimalNumFormat;
+ /** <em>General</em> 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 Ob
// 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 Ob
}
if ("General".equalsIgnoreCase(formatStr) || "@".equals(formatStr)) {
- if (isWholeNumber(cellValue)) {
- return generalWholeNumFormat;
- }
- return generalDecimalNumFormat;
+ return generalNumberFormat;
}
if(DateUtil.isADateFormat(formatIndex,formatStr) &&
@@ -657,15 +648,6 @@ public class DataFormatter implements Ob
}
/**
- * Return true if the double value represents a whole number
- * @param d the double value to check
- * @return <code>true</code> if d is a whole number
- */
- private static boolean isWholeNumber(double d) {
- return d == Math.floor(d);
- }
-
- /**
* Returns a default format for a cell.
* @param cell The cell
* @return a default format
@@ -682,10 +664,7 @@ public class DataFormatter implements Ob
// otherwise use general format
}
- if (isWholeNumber(cellValue)){
- return generalWholeNumFormat;
- }
- return generalDecimalNumFormat;
+ return generalNumberFormat;
}
/**
@@ -735,7 +714,8 @@ public class DataFormatter implements Ob
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 +868,7 @@ public class DataFormatter implements Ob
Iterator<Map.Entry<String,Format>> itr = formats.entrySet().iterator();
while(itr.hasNext()) {
Map.Entry<String,Format> entry = itr.next();
- if (entry.getValue() == generalDecimalNumFormat
- || entry.getValue() == generalWholeNumFormat) {
+ if (entry.getValue() == generalNumberFormat) {
entry.setValue(format);
}
}
@@ -969,8 +948,7 @@ public class DataFormatter implements Ob
dateSymbols = DateFormatSymbols.getInstance(locale);
decimalSymbols = DecimalFormatSymbols.getInstance(locale);
- generalWholeNumFormat = new DecimalFormat("#", decimalSymbols);
- generalDecimalNumFormat = new DecimalFormat("#.##########", decimalSymbols);
+ generalNumberFormat = new ExcelGeneralNumberFormat(locale);
// init built-in formats
Added: poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java?rev=1706971&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java (added)
+++ poi/trunk/src/java/org/apache/poi/ss/usermodel/ExcelGeneralNumberFormat.java Tue Oct 6 09:56:26 2015
@@ -0,0 +1,87 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ 2012 - Alfresco Software, Ltd.
+ Alfresco Software has modified source of this file
+ The details of changes as svn diff can be found in svn at location root/projects/3rd-party/src
+==================================================================== */
+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 DecimalFormat wholeNumFormat;
+ private final DecimalFormat decimalNumFormat;
+
+ public ExcelGeneralNumberFormat(final Locale locale) {
+ decimalSymbols = new DecimalFormatSymbols(locale);
+ wholeNumFormat = new DecimalFormat("#", decimalSymbols);
+ DataFormatter.setExcelStyleRoundingMode(wholeNumFormat);
+ decimalNumFormat = new DecimalFormat("#.##########", decimalSymbols);
+ DataFormatter.setExcelStyleRoundingMode(decimalNumFormat);
+ }
+
+ 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();
+ }
+
+}
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java?rev=1706971&r1=1706970&r2=1706971&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDataFormatter.java Tue Oct 6 09:56:26 2015
@@ -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
Modified: poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java?rev=1706971&r1=1706970&r2=1706971&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/ss/usermodel/TestDataFormatter.java Tue Oct 6 09:56:26 2015
@@ -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();
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org