Bug 63994 - custom date formats with literals not supported
Summary: custom date formats with literals not supported
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-12-09 10:21 UTC by yuqiu1979
Modified: 2021-07-28 21:06 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description yuqiu1979 2019-12-09 10:21:30 UTC
In MS Excel Version 2016, the following format yields the following example values:

yyyy-mm-ddThh:mm:ss       => 2019-12-09T00:00:00
yyyy-mm-dd"T"hh:mm:ss     => 2019-12-09T00:00:00
yyyy-mm-dd""T""hh:mm:ss   => 2019-12-09T00:00:00
yyyy-mm-dd"""T"""hh:mm:ss => 2019-12-09T00:00:00
yyyy-mm-dd "CUSTOM"       => 2019-12-09 CUSTOM
yyyy-mm-dd"Z"             => 2019-12-09Z
"yyyy-mm-ddThh:mm:ss"     => yyyy-mm-ddThh:mm:ss
"yyyy-mm-dd"T"hh:mm:ss"   => yyyy-mm-ddThh:mm:ss

Only the first one works well for POI.
POI does not treat any custom format using double quotes, as date.



I believe the fix should be in DataFormatter.createDateFormat().

Current Codes (trunk version @ 5th Dec 2019, line 478+):
        String formatStr = pFormatStr;
        formatStr = formatStr.replaceAll("\\\\-","-");
        formatStr = formatStr.replaceAll("\\\\,",",");
        formatStr = formatStr.replaceAll("\\\\\\.","."); // . is a special regexp char
        formatStr = formatStr.replaceAll("\\\\ "," ");
        formatStr = formatStr.replaceAll("\\\\/","/"); // weird: m\\/d\\/yyyy 
        formatStr = formatStr.replaceAll(";@", "");
        formatStr = formatStr.replaceAll("\"/\"", "/"); // "/" is escaped for no reason in: mm"/"dd"/"yyyy
        formatStr = formatStr.replace("\"\"", "'");	// replace Excel quoting with Java style quoting
        formatStr = formatStr.replaceAll("\\\\T","'T'"); // Quote the T is iso8601 style dates

Patched Codes:
        String formatStr = pFormatStr;
        formatStr = formatStr.replaceAll("\\\\-","-");
        formatStr = formatStr.replaceAll("\\\\,",",");
        formatStr = formatStr.replaceAll("\\\\\\.","."); // . is a special regexp char
        formatStr = formatStr.replaceAll("\\\\ "," ");
        formatStr = formatStr.replaceAll("\\\\/","/"); // weird: m\\/d\\/yyyy 
        formatStr = formatStr.replaceAll(";@", "");
        formatStr = formatStr.replaceAll("\"/\"", "/"); // "/" is escaped for no reason in: mm"/"dd"/"yyyy
        formatStr = formatStr.replace("\"", "'");	// ++ replace Excel quoting with Java style quoting, change double to single quotes, char for char.
        formatStr = formatStr.replaceAll("'+", "'");    // ++ flatten multiple quotes as a single quote (presume user quoted correctly)
        formatStr = formatStr.replaceAll("\\\\T","'T'"); // Quote the T is iso8601 style dates
Comment 1 yuqiu1979 2019-12-09 10:52:40 UTC
The suggested patch is incomplete.

DateUtil.isADateFormat(), would also need to be changed, to ignore literals within the custom date/time formats.
Comment 2 PJ Fanning 2021-07-28 21:06:13 UTC
https://bz.apache.org/bugzilla/show_bug.cgi?id=65471 fix handles support for the T literal