Created attachment 35715 [details] Screenshot of excel and debug and exception Hi Team, We have observed an issue while converting and excel file to csv. This issue is only for a specific cell value in a file. I am attaching the screen shot of Excel file format from Microsoft Excel. Also attaching the screen shot of Debug Mode of that particular cell. Even just applying the function to check if its date format is failing. Below is the code applied : org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell) Attached PDF contains the images of the Excel Format from Microsoft Excel, Debug reports of a cell, exception occurred when above code is executed on that cell value. Regards, Kushal J
What is the format string applied to that cell?
Created attachment 35716 [details] Cell format string As requested attaching the image of cell format
Please provide a sample file which allows to reproduce the problem.
We cannot reproduce the problem with only screenshots, no sample file was provided, so we cannot do much here unless we get more information. Thus closing this as LATER for now, please reopen with more information if this is still a problem for you.
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.
Created attachment 36207 [details] Contains a formatted date cell to reproduce the behavior