https://bz.apache.org/bugzilla/show_bug.cgi?id=62084
Sven <[email protected]> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|LATER |--- Status|RESOLVED |REOPENED --- Comment #5 from Sven <[email protected]> --- Hello, I've noticed the same behavior with POI 4.0.0-final. In my attached xlsx-file there is only a single cell with the formula „=date(2018;1;1)“. This cell must be formatted with a format that contains at least one dot (e.g. „T. MMM. JJJJ“). Whether this xlsx-file is created with Excel 365 or LibreOffice 6.1.2.1 does not change anything. It's the same behavior in both situations. In org.apache.poi.xssf.model.StylesTable.readFrom(InputStream) there is a call „styleSheet.getNumFmts()“. This returns a ctfmts with ctfmts._textsource._user.toString() as follows: <xml-fragment count="16" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"; xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"; xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main"; xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"; xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main";> <main:numFmt numFmtId="165" formatCode="yyyy\-mm\-dd;@"/> <main:numFmt numFmtId="166" formatCode="d/m;@"/> <main:numFmt numFmtId="167" formatCode="d/m/yy;@"/> <main:numFmt numFmtId="168" formatCode="dd/mm/yy;@"/> <main:numFmt numFmtId="169" formatCode="[$-407]d/\ mmm/;@"/> <main:numFmt numFmtId="170" formatCode="[$-407]d/\ mmm/\ yy;@"/> <main:numFmt numFmtId="171" formatCode="[$-407]d/\ mmm\ yy;@"/> <main:numFmt numFmtId="172" formatCode="[$-407]mmm/\ yy;@"/> <main:numFmt numFmtId="173" formatCode="[$-407]mmmm\ yy;@"/> <main:numFmt numFmtId="174" formatCode="[$-407]d/\ mmmm\ yyyy;@"/> <main:numFmt numFmtId="175" formatCode="[$-409]d/m/yy\ h:mm\ AM/PM;@"/> <main:numFmt numFmtId="176" formatCode="d/m/yy\ h:mm;@"/> <main:numFmt numFmtId="177" formatCode="[$-407]mmmmm;@"/> <main:numFmt numFmtId="178" formatCode="[$-407]mmmmm\ yy;@"/> <main:numFmt numFmtId="179" formatCode="d/m/yyyy;@"/> <main:numFmt numFmtId="180" formatCode="[$-407]d/\ mmm/\ yyyy;@"/> </xml-fragment> It seems to me as if the format strings contain „/\“ where dots should be. Code used to read the xlsx-file: import java.io.File; import java.util.Locale; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; public class ReadXlsxTest { public static void main(String[] args) throws Exception { // read workbook File xlsxFile = new File("date.xlsx"); Workbook workbook = WorkbookFactory.create(xlsxFile); FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); formulaEvaluator.clearAllCachedResultValues(); Sheet sheet = workbook.getSheetAt(0); // print input cell for (Row row : sheet) { for (Cell cell : row) { System.out.println(cell.getAddress()); String formattedValue = new DataFormatter(Locale.ENGLISH).formatCellValue(cell, formulaEvaluator); System.out.println(formattedValue); } } } } Result in Excel and in LibreOffice: „1. Jan. 2018“. Result in POI 4.0.0-final: „1/ Jan/ 2018“. This behavior is the same with other locales, e.g. German. So I'm reopening this issue as Dominik suggested. -- You are receiving this mail because: You are the assignee for the bug. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
