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