Bug 65619 - with POI upgrade to 5.0.0, we see that it formats dates with extra slash
Summary: with POI upgrade to 5.0.0, we see that it formats dates with extra slash
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.0.0-FINAL
Hardware: PC All
: P2 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2021-10-06 20:49 UTC by hethal
Modified: 2021-11-21 17:41 UTC (History)
0 users

spreadsheet compare file with date format mismatch between 4.1.2 and 5.0.0. (96.96 KB, image/jpeg)
2021-10-07 19:40 UTC, hethal

Note You need to log in before you can comment on or make changes to this bug.
Description hethal 2021-10-06 20:49:34 UTC
We upgraded our POI from 4.1.2 to 5.0.0 and we are seeing the POI 5.0.0 upgrade formats dates with extra slash
We are using SXSSFWorkbook and XSSFCellStyle and after upgrading to poi 5.0.0 when we compare our data Exports between the 4.1.2 POI libraries and the upgraded 5.0.0 version we are encountering that there is format code difference.

Prior to upgarde ( POI 4.1.2)

'Background color: Color [Window],   Foreground color: Color [WindowText],   Font: [Font: Name=Calibri, Size=11, Units=3, GdiCharSet=1, GdiVerticalFont=False],   Format code: dd mmm yyyy HH:mm:ss'

Post-upgrade( POI 5.0.0)

'Background color: Color [Window],   Foreground color: Color [WindowText],   Font: [Font: Name=Calibri, Size=11, Units=3, GdiCharSet=1, GdiVerticalFont=False],   Format code: dd\ mmm\ yyyy\ hh:mm:ss'

After the upgarde to poi 5.0.0, we are seeing the extra slash added to the date format.

Is this a bug or intended?
Comment 1 PJ Fanning 2021-10-06 20:52:59 UTC
If this has changed, it is not likely to be intentional. Could you provide a more complex code sample for review?
Comment 2 hethal 2021-10-07 04:39:10 UTC
here is the code that we are using in our app

return new DataFormatter() {
                public String formatRawCellContents (double value, int formatIndex, String formatString) {
                    if ("GENERAL".equalsIgnoreCase(formatString)) {
                        return decimalFormat.format(value);
                    } else if (DateUtil.isADateFormat(formatIndex, formatString) && DateUtil.isValidExcelDate(value)) {
                        DateFormat dateFormat;
                        if (StringUtils.containsIgnoreCase(formatString, "h")) {
                            dateFormat = new SimpleDateFormat("dd MMM yyyy HH:mm:ss");
                        } else {
                            dateFormat =  new SimpleDateFormat("dd MMM yyyy");
                        Date date = DateUtil.getJavaDate(value, false);
                        return dateFormat.format(date);
                    } else {
                        return super.formatRawCellContents(value, formatIndex, formatString);
Comment 3 hethal 2021-10-07 04:41:31 UTC
you can take a look at the below code:
dateFormat = new SimpleDateFormat("dd MMM yyyy HH:mm:ss");
Comment 4 PJ Fanning 2021-10-07 08:17:58 UTC
I can't reproduce any issue - I put a test case at https://github.com/pjfanning/poi-gradle-example/tree/bug-65619 (NB a branch of this project called bug-65619).

The code in this branch gives the same result if the build.gradle has poi 5.0.0 or poi 4.1.2.
Comment 5 hethal 2021-10-07 13:31:32 UTC
Please take a look at the below code that we are using for our app:

public Builder dataFormat (String format) 
  return new Builder(fontTransform, cellStyleTransform.andThen(
    (cs, ch) -> cs.setDataFormat(ch.createDataFormat().getFormat("dd mmm 

and looks like this code is using the `setDataFormat`, `createDataFormat` and `getFormat` methods that are of the POI 5.0.0 jar.
Comment 6 PJ Fanning 2021-10-07 13:34:26 UTC
This is not a reproducible use case - maybe someone else will have a look but I don't have time to look at this without a reproducible use case.
Comment 7 PJ Fanning 2021-10-07 13:35:54 UTC
the code in last comment is not POI code - you probably need someone in your company to use a Java debugger to debug the code
Comment 8 hethal 2021-10-07 19:40:34 UTC
Created attachment 38059 [details]
spreadsheet compare file with date format mismatch between 4.1.2 and 5.0.0.

Showing that there are back slashes in POI 5.0.0 that are not present in POI 4.1.2
Comment 9 hethal 2021-10-07 19:41:22 UTC
Below here is the code that we tried on POI 5.0.0 and POI 4.1.2 as well and were able to reproduce that the POI 5.0.0 added the extra slashes as attache d in the jpg image.

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;

public class TestPoi {

    public void test () throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("Test Sheet 1");
        Font font = workbook.createFont();
        CellStyle cs = workbook.createCellStyle();
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(1);
        cell.setCellValue(new Date());
        CreationHelper ch = workbook.getCreationHelper();
        cs.setDataFormat(ch.createDataFormat().getFormat("dd MMM yyyy HH:mm:ss"));
        FileOutputStream fos = new FileOutputStream("c:/temp/test.xlsx");
Comment 10 PJ Fanning 2021-10-07 20:03:08 UTC
I ran the sample code and my output was fine (no slashes) - I have this in the styles.xml - <numFmt numFmtId="164" formatCode="dd MMM yyyy HH:mm:ss"/>
Comment 11 PJ Fanning 2021-10-08 08:11:02 UTC
I added this passing regression test - r1894016
Comment 12 Dominik Stadler 2021-11-21 17:41:35 UTC
It seems even with your test-case, it is not possible to reproduce this for us. There are various reasons that can be related, e.g. using different locales, using some unusual version of Java or other differences in your environment compared to more usual setups. 

Maybe you can try some more to narrow down why the test-case works fine for us, but seems to cause problems for you. 

If you find something that should be improved/fixed in Apache POI, please re-open this issue with more comments.