Bug 57951

Summary: DataFormatter.formatRawCellContents doesn't round properly with JDK8 due to not using BigDecimal
Product: POI Reporter: Robert Kish <robert_kish>
Component: SS CommonAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.12-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Robert Kish 2015-05-26 22:59:00 UTC
JDK8 corrected some rounding issues with NumberFormat/DecimalFormat that effect some number formatting while processing Excel files. (See http://stackoverflow.com/questions/22797964/is-inconsistency-in-rounding-between-java-7-and-java-8-a-bug). These problems are noticed when not using BigDecimal.

If I add the Junit mod below to org.apache.poi.ss.usermodel.TestDataFormatter, it passes with JDK6, but fails with JDK8.

    public void testRounding() {
        DataFormatter dfUS = new DataFormatter(Locale.US);

        assertEquals("13.90", dfUS.formatRawCellContents(13.895, 2, "0.00"));
        assertEquals("13.91", dfUS.formatRawCellContents(13.905, 2, "0.00"));
        assertEquals("13.92", dfUS.formatRawCellContents(13.915, 2, "0.00"));
        assertEquals("13.93", dfUS.formatRawCellContents(13.925, 2, "0.00"));
        assertEquals("13.94", dfUS.formatRawCellContents(13.935, 2, "0.00"));
        assertEquals("13.95", dfUS.formatRawCellContents(13.945, 2, "0.00"));
        assertEquals("13.96", dfUS.formatRawCellContents(13.955, 2, "0.00"));
        assertEquals("13.97", dfUS.formatRawCellContents(13.965, 2, "0.00"));
        assertEquals("13.98", dfUS.formatRawCellContents(13.975, 2, "0.00"));
        assertEquals("13.99", dfUS.formatRawCellContents(13.985, 2, "0.00"));
        assertEquals("14.00", dfUS.formatRawCellContents(13.995, 2, "0.00"));
        assertEquals("14.01", dfUS.formatRawCellContents(14.005, 2, "0.00"));
     }

If you make the code change below to org.apache.poi.ss.usermodel.DataFormatter, the test above will pass for JDK6 and JDK8.

public String formatRawCellContents(double value, int formatIndex, String formatString, boolean use1904Windowing) {
     :
     :

     // else Number
         Format numberFormat = getFormat(value, formatIndex, formatString);
         if (numberFormat == null) {
             return String.valueOf(value);
         }
         
         String result;
         // When formatting 'value', double to text to BigDecimal produces more
         // accurate results than double to Double in JDK8 (as compared to
         // previous versions). However, if the value contains E notation, this
         // would expand the values, which we do not want, so revert to
         // original method.
         final String textValue = NumberToTextConverter.toText(value);
         if (textValue.indexOf('E') > -1) {
             result = numberFormat.format(new Double(value));
         }
         else {
             result = numberFormat.format(new BigDecimal(textValue));
         }
         // Complete scientific notation by adding the missing +.
         if (result.indexOf('E') > -1 && !result.contains("E-")) {
             result = result.replaceFirst("E", "E+");
         }
         return result;
}

I apologize for not taking the time to learn how to submit a proper patch, but I believe this code is all that is needed for this issue. Also, I changed some of the previous comments regarding the E notation (I was the RK in those comments).

Thanks
Comment 1 Nick Burch 2015-05-27 08:59:06 UTC
Any chance you could get a JDK9 snapshot, and check with that too? While we're doing Java version compatibility fixes, it would seem a good chance to do all of them at once!
Comment 2 Robert Kish 2015-05-27 16:03:14 UTC
Using build 1.9.0-ea-b65, I get the same behavior as in build 1.8.0_45-b14; my new Junit test fails with poi 3.12 code and it succeeds with modified code using BigDecimal.
Comment 3 Nick Burch 2015-05-27 17:15:41 UTC
Thanks for this investigation!

I've applied your change in r1682083.