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.
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...
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)
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: @Test public void test() throws IOException { InputStream inp = new FileInputStream(new File("testsrc/test.xlsx")); final Workbook wb; try { wb = new XSSFWorkbook(inp); } finally { inp.close(); } Sheet sheet = wb.getSheetAt(0); assertNotNull(sheet); Row row = sheet.getRow(0); assertNotNull(row); Cell cell = row.getCell(0); assertNotNull(cell); 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); }
I have implemented this so that the """ are converted to ' internally for the SimpleDateFormatter under r1514632.