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: [email protected]
Reporter: [email protected]
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: [email protected]
For additional commands, e-mail: [email protected]
