| Summary: | cell format evaluation doesn't handle range conditions | ||
|---|---|---|---|
| Product: | POI | Reporter: | Greg Woolsey <gwoolsey> |
| Component: | SS Common | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | P2 | ||
| Version: | 3.17-dev | ||
| Target Milestone: | --- | ||
| Hardware: | All | ||
| OS: | All | ||
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.
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. |
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: @Test 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)); }