poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 54786] New: Date formatting does not support double-quotes as escape-character as Excel does
Date Tue, 02 Apr 2013 13:46:43 GMT
https://issues.apache.org/bugzilla/show_bug.cgi?id=54786

            Bug ID: 54786
           Summary: Date formatting does not support double-quotes as
                    escape-character as Excel does
           Product: POI
           Version: 3.9
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: XSSF
          Assignee: dev@poi.apache.org
          Reporter: dominik.stadler@gmx.at
    Classification: Unclassified

I have an excel sheet with a elapsed-time-column and a second column which
executes a TEXT() function to format the data in a special form. It uses
double-quotes to include literals in the resulting text.

I.e. the date-column itself contains something like "0.041666667", which is one
hour elapsed time. The formula used is TEXT(AW598; "[h]""h"" m""m"""), i.e. it
tries to state "h" for elapsed hours and "m" for elapsed minutes. The result in
Excel in this case is "1h 0m", however in POI, the result is [1""1"" 0""0""],
i.e. it keeps the double quotes and replace hour and minute in both places.

The following testcase verifies this:

    @Test
    public void testTEXT() {
        DataFormatter formatter = new DataFormatter();
        String format = "[h]\"\"h\"\" m\"\"m\"\"";
        assertTrue(DateUtil.isADateFormat(-1,format));
        String formattedStr = formatter.formatRawCellContents(0.041666667, -1,
format);
        assertEquals("1h 0m", formattedStr);
    }

It seems the DateFormatter does not support the double-quoting which Excel
supports. I also did not find a simple workaround, using single quote to use
the escaping from the underlying SimpleDateFormat did not work as well because
DateUtil.isADateFormat() does not match any more at all then.

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org


Mime
View raw message