Laravel Excel is converting dates from heading into some numbers

Question

I have a problem which is connected with Laravel Excel. I have heading filled with dates for example: 2018-05-23. But when I read it with excel extension by using:

 $data = Excel::load($request['file'], function($reader) {})->get();

The extension is not treating the heading dates as dates. Instead of that it converts the dates into some numbers - for example: 43243.

Here is dd of the sheet:

Important note: When the date is in a normal row (not in heading) then the date is converted well.

Version of Laravel Excel (maatwebsite/excel) - 2.1.0
Version of Laravel - 5.5

Possibly related to [this](https://stackoverflow.com/questions/32944964/date-and-datetime-using-phpexcel-function-writes-in-excel-as-text) — Quezler, May 23 '18 at 07:10
If possible try to use the header like this "2018-05-23". use the date inside double quotes in header column. — Chiru Adi, May 23 '18 at 07:20
@ChiruAdi I've put it like you said and its displayed well, so maybe that is the way to do it. Maybe put your comment as an answer. — Martin, May 23 '18 at 07:24
May be duplicated, see https://stackoverflow.com/questions/37044353/laravel-excel-import-date-column-in-excel-cell-returns-as-floating-value-how-t/62138324#62138324 — Nicolas Scordamaglia, Jun 22 '20 at 16:27

Jakhongir · Answer 1 · 2019-03-13T11:51:06.090

The numbers come from excel itself, dates stored in excel as numeric values. http://www.cpearson.com/excel/datetime.htm

For Laravel framework 5.6 and maatwebsite/excel package version 3.1, to convert date from excel numbers to normal date format, this function PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($dateFromExcel) can be used. It accepts integer(excel date) and returns DateTime object.

More information can be found here https://github.com/Maatwebsite/Laravel-Excel/issues/1832

score 5 · Accepted Answer · answered May 23 '18 at 07:29

I think due to ExcelParser. It converts the date header field into some random numbers.

So to make it work for your requirement then add the column header inside the double quotes.

Example : "2018-05-23"

https://github.com/Maatwebsite/Laravel-Excel/blob/2.1/src/Maatwebsite/Excel/Parsers/ExcelParser.php#L284

score 0 · Answer 3 · answered Jan 30 '20 at 11:06

maatwebsite package insert ' before the date. For fix this

class yourExport implements  , WithMapping,  WithCustomCsvSettings, WithColumnFormatting
{
.....
public function map($map): array
    {
        return [
            Date::dateTimeToExcel($map->created_at),
            ...
         ];
     } 
public function columnFormats(): array
    {
        return [
            'A' => NumberFormat::FORMAT_DATE_DDMMYYYY    
        ];
    }
}

where A is the excel column where the date goes

Laravel Excel is converting dates from heading into some numbers

3 Answers3

Linked