Hi,

when exporting to XLSX a set of results containing datetime columns, the results get converted from datetime to float, even when explicitly specifying

"type" => "datetime",
"format" => "Y-m-d H:i:s",
"displayFormat" => "Y-m-d H:i:s",

in the ColumnMeta for the column.

After some research and some headache it appears to me this being a bug:

• a date like "2023-05-21 09:50:22", so with "Y-m-d H:i:s" format, gets automatically converted to 45067,409976852 which is a float
• this format, I discovered, is a MS Excel datetime format, which is forced into the export process when doing

$report->run()
    ->exportToXLSX($report_params)
    ->toBrowser($report_name . '.xlsx');
exit();

by the following steps:

BigSpreadsheetExportable -> exportToXLSX (L56)
...
TableBuilder -> configToBigSpreadsheet (L47)
    -> saveDataStoreToBigSpreadsheet (L889)
    -> buildTableBody (L298)
    -> buildTableBodyRow (L267)
    -> getFormatted (L129)
    -> ps\Shared\Date::PHPToExcel (L169)
    -> PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel (L268)
    -> formattedPHPToExcel (L330)

which converts a normal PHP DateTime object into a (hell of) MS Office Excel DateTime format (float).

in TableBuilder method getFormatted, you get the data type, and in case is datetime or date or time, it sets

$isDateTime = true;

which then triggers this piece of code:

if ($isDateTime) {
    $formatCode = Util::get($meta, "displayFormat", $defaultFormat);
    if ($date = \DateTime::createFromFormat($datetimeFormat, $value)) {
        $value = $date;
    }
    $value = ps\Shared\Date::PHPToExcel($value);
}

which always converts the datetime format to MS Excel, ignoring any settings you may pass to it with ColumnMeta.

So the only way to get a datetime value in the format you may need is to retrieve the value before rendering it, cast it to a string, convert it in any format you may need, then execute the export remapping the field as a string to avoid this forced MS Excel conversion.

To me it makes not much sense, it would be probably better to do the reverse in case one needs explicitly the MS Excel format when exporting a report to excel, and make the default behaviour taking in account any format passed by the ColumnMeta process

Thanks