Improve DateTime formatting in other locales

[mikehaertl]

This is:

• a bug report
• not a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

A cell with a datetime value should be rendered in the correct local format.

So in Germany the a value should be displayed as 20.12.2017 09:15:00 (PHP format string:d.m.Y H:i:s) .

What is the current behavior?

At least in LibreOffice (could not test MS Excel) a datetime value is completely broken. It's displayed as 20.12.y 9:15.

The default FORMAT_DATE_DATETIME format string for datetimes in the Date class is d/m/y h:mm which seems a bit unusual to me. This is a very uncommon format in many locales. It should be DD/MM/YYYY\ HH:MM:SS. This is what I get when I save a file with a full datetime cell. LibreOffice (and presumably also Excel) seem to auto-convert this to the correct local format string.

If this can't be changed, then there should at least be more formatting options provided as Date::FORMAT_* constants to make it easier for international users.

What are the steps to reproduce?

$s = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$d = new DateTime('2017-12-20 09:15:00');
$excelD = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($d);

$sheet = $s->getActiveSheet();
$sheet->setCellValue('A1', $excelD);
$sheet->getStyle('A1')
    ->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);

Opening this in LibreOffice on a system with German locale leads to the above wrong format.

Which versions of PhpSpreadsheet and PHP are affected?

Development branch.

[mikehaertl]

@PowerKiKi Thanks.

What about my suggestion to add another constant for a long datetime format DD/MM/YYYY\ HH:MM:SS e.g. as NumberFormat::FORMAT_DATE_DATETIME_FULL?

[PowerKiKi]

As a matter of fact I feel there are already way too many hardcoded formats (+20). I don't see much values in having so many. So I am unsure about adding even more. If we had to a new one though, that would most probably be ISO 8601, which is the only date(/time) format that make sense and is not strongly dependent on regional preferences.