Bug 58571 - Date formatting in formulas does not support double-quotes as escape-character as Excel does
Summary: Date formatting in formulas does not support double-quotes as escape-characte...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.14-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2015-11-02 06:40 UTC by Dominik Stadler
Modified: 2015-11-29 23:07 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Dominik Stadler 2015-11-02 06:40:45 UTC
This is a followup from Bug 54786, the fix applied there is not complete yet, the following unit-test still fails:

	public void testFormula() throws IOException {
		try (Workbook wb = new XSSFWorkbook()) {
			FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();

			Sheet sheet = wb.createSheet("test");
			Row row = sheet.createRow(0);
			Cell cell = row.createCell(0);
			cell.setCellFormula("TEXT(B1, \"[h]\"\"h\"\" m\"\"m\"\"\")");

			Cell cellValue = row.createCell(1);

			CellValue value = eval.evaluate(cell);
			assertEquals(Cell.CELL_TYPE_STRING, value.getCellType());
			assertEquals("1h 0m", value.getStringValue());

It seems the StringPtg internally stores the two double quotes ("") as one double quote and the format is passed this way to the TEXT-function and further on to DataFormatter.formatRawCellContents(). However in Bug 54786 we built the replacement there on two doulbe-quotes.

Two options:
* Adjust DataFormatter to replace one double quote with a single quote
* Adjust StringPtg to return the doulbe-quotes in the call to getValue() similar to toString()/toFormulaString()

I am not sure which one is the better option here as both may have side-effects to existing code/functionality.