Bug 54786 - Date formatting does not support double-quotes as escape-character as Excel does
Summary: Date formatting does not support double-quotes as escape-character as Excel does
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.9-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2013-04-02 13:46 UTC by Dominik Stadler
Modified: 2013-08-16 11:07 UTC (History)
0 users

Sample Excel file having formatting using doulbe quotes as escape characters (23.60 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-04-04 06:12 UTC, Dominik Stadler

Note You need to log in before you can comment on or make changes to this bug.
Description Dominik Stadler 2013-04-02 13:46:43 UTC
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, 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.
Comment 1 Dominik Stadler 2013-04-02 14:08:04 UTC
FYI, the full documentation of the TEXT() function is at http://office.microsoft.com/en-us/excel-help/text-funktion-HP010342952.aspx, interestingly there is no mentioning of double-quotes there, but it works as escape character in date-related formatting at least in Excel 2010...
Comment 2 Nick Burch 2013-04-03 17:13:17 UTC
Would you be able to create a file with this formatting in Excel, then read back the contents in POI? Is it as you've shown in your test, or different?

What Excel displays, and what Excel writes to the file aren't always the same... (POI is a file format library, so generally goes for what's in the file)
Comment 3 Dominik Stadler 2013-04-04 06:12:05 UTC
Created attachment 30146 [details]
Sample Excel file having formatting using doulbe quotes as escape characters

The attached file shows the conditional date formatting using TEXT() with two double quotes as escape characters. I.e. cell A1 contains the formula 

=TEXT(B1; "[h]""h"" m""m""")

In Excel, this formats Cell B1 the number of hours with a trailing "h" and the number of minutes with a trailing "m". If I calculate this formula with POI, I get 0"0" 15"15", i.e. h and m are replaced twice with hours/minutes and one of the two double-quotes are removed.

The following unit test fails with the attached file, but I would expect it to work fine:

	public void test() throws IOException {
		InputStream inp = new FileInputStream(new File("testsrc/test.xlsx"));
		final Workbook wb;
		try {
			wb = new XSSFWorkbook(inp);
		} finally {

		Sheet sheet = wb.getSheetAt(0);

    	Row row = sheet.getRow(0);
    	Cell cell = row.getCell(0);

    	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    	CellValue cellValue = evaluator.evaluate(cell);
    	assertEquals(Cell.CELL_TYPE_STRING, cellValue.getCellType());

    	String stringValue = cellValue.getStringValue();
    	assertEquals("0h 15m", stringValue);
Comment 4 Dominik Stadler 2013-08-16 11:07:24 UTC
I have implemented this so that the """ are converted to ' internally for the SimpleDateFormatter under r1514632.