Formatting a date in Excel sheet

[TonioIncredible]

Hi,

I am generating excel files, and it works nicely. Except that the dates are not recognized by Excel as dates.

I am applying styles using applyFromArray.
My style for dates is defined by

	->setStyle("date", 'alignment', ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
		'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER])
	->setStyle("date", 'numberFormat', ['formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DDMMYYYY])

(setStyle is a function of my own to build the style array).

The dates are correctly formatted but are considered as strings by Excel (which means I can't sort them correctly). I first passed them as strings, then use a PHP DateTime object, the result is the same, correctly formatted but still considered as string by Excel.

I then discovered in the doc the AdvancedValueBinder.
When I use it, dates are recognised as actual dates, but the format NumberFormat::FORMAT_DATE_DDMMYYYY is simply ignored, the are displayed in the anglo-saxon format YYYY-MM-DD.

Does anyone have an idea on how to have dates recognized as dates AND formatted as I want?

Thanks in advance,

Antoine

[MarkBaker]

If you are generating Excel files using the Default Value Binder, then you need to set the cell values to an Excel serialized DateTime value yourself, using the functions available in \PhpOffice\PhpSpreadsheet\Shared\Date, as described in the documentation. And then you can set the Number Format Mask to display the value however you choose.

e.g.

$date = new DateTimeImmutable('2023-03-16');
$spreadsheet->getActiveSheet()
    ->setCellValue('A2', Date::PHPToExcel($date));
$spreadsheet->getActiveSheet()->getStyle('A2')
    ->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

or

$date = '2023-03-16';
$spreadsheet->getActiveSheet()
    ->setCellValue('A2', Date::PHPToExcel($date));
$spreadsheet->getActiveSheet()->getStyle('A2')
    ->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

The Date::PHPToExcel() method will recognise most types that can be used for dates: a DateTime Object; an integer, which will be treated as a Unix Timestamp; or the string representation of a date.

Likewise, the Advanced Value Binder will recognise most string representations of dates, and automate this conversion to an Excel serialized DateTime value for you; but it does set its own arbitrary Number Format Mask. You can then still change that mask to display the value however you choose.

\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder(new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder());

$date = '2023-03-16';
$spreadsheet->getActiveSheet()
    ->setCellValue('A2', $date);
$spreadsheet->getActiveSheet()->getStyle('A2')
    ->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

or

$date = '2023-03-16';
$spreadsheet->getActiveSheet()
    ->setCellValue('A2', $date, new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder());
$spreadsheet->getActiveSheet()->getStyle('A2')
    ->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

Note that the string representation of dates may be ambiguous: is 07-01-2023 the 1st of July, or the 7th of January. While PhpSpreadsheet will attempt to interpret formatted strings correctly, it can't always resolve those ambiguities.

[TonioIncredible]

Thank you,

This will be very helpful.