Bug 61007

Summary: cell format evaluation doesn't handle range conditions
Product: POI Reporter: Greg Woolsey <gwoolsey>
Component: SS CommonAssignee: POI Developers List <dev>
Severity: normal    
Priority: P2    
Version: 3.17-dev   
Target Milestone: ---   
Hardware: All   
OS: All   

Description Greg Woolsey 2017-04-19 16:53:43 UTC
It is poorly documented, but Excel supports numeric range conditions in cell format strings.  POI currently doesn't handle these.

There is a comment in DataFormatter.getFormat(double, int, String) that mentions needing to combine the Java Format compatible parsing with the general-case CellFormat logic.

Interestingly, the following test passes when I change the condition in the above method that calls CellFormat in all cases where the format contains a semicolon, instead of only when it contains 2 or more.  Doing that breaks testFractions(), however, so CellFormat doesn't handle everything.

Not sure the best way to handle this, perhaps just send conditional format strings to CellFormat also, with another targeted boolean expression?

Test case I added to TestDataFormatter:

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

        String format = "[>=10]#,##0;[<10]0.0";
        assertEquals("Wrong format for " + format, "17,876", dfUS.formatRawCellContents(17876.000, -1, format));
        assertEquals("Wrong format for " + format, "9.7", dfUS.formatRawCellContents(9.71, -1, format));
Comment 1 Greg Woolsey 2017-04-19 17:50:03 UTC
Turns out the test that failed when I sent all segmented formats to CellFormat was checking an invalid expected result.  The value it has, which is currently returned by POI, doesn't match the value produced by Excel for the same format string.

Excel produces the value returned by CellFormat.  So I will change the test to expect the correct value ("correct" defined as the value shown by Excel), and the logic in DataFormatter to send all multi-part formats to CellFormat.  It already has a cache of format strings, so parsing will only happen once per format string per ClassLoader hierarchy.
Comment 2 Greg Woolsey 2017-04-20 05:32:05 UTC
Fixed in r1791964.  Kept existing test behavior, as changing that broke other tests too.  Implemented specific logic to send formats with multiple conditional segments to CellFormat in addition to the previous logic to send anything with 3 or more segments there.  Added some tests for this.