Summary: | Date formatting does not support double-quotes as escape-character as Excel does | ||
---|---|---|---|
Product: | POI | Reporter: | Dominik Stadler <dominik.stadler> |
Component: | XSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED FIXED | ||
Severity: | normal | ||
Priority: | P2 | ||
Version: | 3.9-FINAL | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | All | ||
Attachments: | Sample Excel file having formatting using doulbe quotes as escape characters |
Description
Dominik Stadler
2013-04-02 13:46:43 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... 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);
}
|