Bug 58571

Summary: Date formatting in formulas does not support double-quotes as escape-character as Excel does
Product: POI Reporter: Dominik Stadler <dominik.stadler>
Component: SS CommonAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal    
Priority: P2    
Version: 3.14-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   

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:

	@Test
	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.setCellValue(0.0104166666666666);;

			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.