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
Status: NEW
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
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-10-06 20:49 UTC by hethal
Modified: 2021-10-09 14:47 UTC (History)
0 users



Attachments
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
Details

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() {
                @Override
                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 
        yyyy"))));
}

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.

Code:
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 {

    @Test
    public void test () throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("Test Sheet 1");
        Font font = workbook.createFont();
        CellStyle cs = workbook.createCellStyle();
        cs.setFont(font);
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(1);
        cell.setCellValue(new Date());
        cell.setCellStyle(cs);
        CreationHelper ch = workbook.getCreationHelper();
        cs.setDataFormat(ch.createDataFormat().getFormat("dd MMM yyyy HH:mm:ss"));
        cs.setAlignment(HorizontalAlignment.RIGHT);
        FileOutputStream fos = new FileOutputStream("c:/temp/test.xlsx");
        workbook.write(fos);
        fos.close();
        workbook.close();
        workbook.dispose();
    }
}
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