Bug 62084 - Issue with date format
Summary: Issue with date format
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.17-FINAL
Hardware: All All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2018-02-07 12:42 UTC by Kushal
Modified: 2018-10-23 20:29 UTC (History)
0 users

Screenshot of excel and debug and exception (404.93 KB, application/pdf)
2018-02-07 12:42 UTC, Kushal
Cell format string (45.72 KB, image/jpeg)
2018-02-07 13:06 UTC, Kushal
Contains a formatted date cell to reproduce the behavior (4.33 KB, application/octet-stream)
2018-10-23 20:29 UTC, Sven

Note You need to log in before you can comment on or make changes to this bug.
Description Kushal 2018-02-07 12:42:52 UTC
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 :


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.

Kushal J
Comment 1 Nick Burch 2018-02-07 12:46:03 UTC
What is the format string applied to that cell?
Comment 2 Kushal 2018-02-07 13:06:51 UTC
Created attachment 35716 [details]
Cell format string

As requested attaching the image of cell format
Comment 3 Dominik Stadler 2018-02-07 16:59:37 UTC
Please provide a sample file which allows to reproduce the problem.
Comment 4 Dominik Stadler 2018-04-02 21:10:20 UTC
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.
Comment 5 Sven 2018-10-23 20:26:58 UTC

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 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;@"/>
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();
		Sheet sheet = workbook.getSheetAt(0);

		// print input cell
		for (Row row : sheet) {
			for (Cell cell : row) {
				String formattedValue = new DataFormatter(Locale.ENGLISH).formatCellValue(cell,


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.
Comment 6 Sven 2018-10-23 20:29:03 UTC
Created attachment 36207 [details]
Contains a formatted date cell to reproduce the behavior