Ability to detect ISO8601 dates

[tchapi]

This is:

- [x] a feature request
- [x] **not** a usage question

Related : #70 and #298 (comment) (kindof)

What is the expected behavior?

ISO 8601 date format should be recognised in https://github.com/PHPOffice/PhpSpreadsheet/blob/master/src/PhpSpreadsheet/Shared/Date.php#L435

How ?

We could change the regex to something along those lines :

^(\d{1,4}[ \.\/\-][A-Z]{3,9}([ \.\/\-]\d{1,4})?|[A-Z]{3,9}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?|\d{1,4}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?)((?: |T)\d{1,2}:\d{1,2}(:\d{1,2})?)?(?:\.\d{1,3})?Z?(?:\+\d{1,2}:?\d{1,2})?$

Adding the following blocks :

• (?: |T) instead of (the space character) allows to retrieve the "T" delimiter. Group is not capturing
• (?:\.\d{1,3})? gets the optional microsecs in +000 format. Group is not capturing
• Z? gets the optional "Z" modifier following the date and time string
• (?:\+\d{1,2}:?\d{1,2})? gets the optional timezone in +0200 or +02:00 format. Group is not capturing

This is a first naïve implementation that does not account for the timezone, but I think it's still a plus.

See this link for testing.

I can make a PR if this is something you'll be willing to add.
AFAIK this change is not BC breaking anything and is relatively safe.

(The DateTime::DATEVALUE function should be changed accordingly of course)

Happy to discuss it
Thanks

[tchapi]

:tumbleweed:

[PowerKiKi]

Sounds like a good idea to me, but you'll have to create unit test, including for non-ISO dates, so that the method is entirely covered.

I'd be OK without supporting timezone for a first version.

@MarkBaker any opinion on supporting ISO8601 format ?

[dkarlovi]

Having gone through iterating over a large-ish Excel file, I'd advise against any automagical features based on regex.

They cause a lot of overhead when processing files already (just for figuring out the cell coordinates).

[tchapi]

Well the regex is already there anyway. Moreover this is in the data → Excel direction, so not used when processing Excel files.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.