Bug 61007 - cell format evaluation doesn't handle range conditions
Summary: cell format evaluation doesn't handle range conditions
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-dev
Hardware: All All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2017-04-19 16:53 UTC by Greg Woolsey
Modified: 2017-04-20 05:32 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
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.