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

            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:

    public void testTEXT() {
        DataFormatter formatter = new DataFormatter();
        String format = "[h]\"\"h\"\" m\"\"m\"\"";
        String formattedStr = formatter.formatRawCellContents(0.041666667, -1,
        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

View raw message