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

Pizzeria

Possibly related to this – Mistake

Please show data of items array of dd data. – Estrogen

Okay, I've updated the question. – Pizzeria

If possible try to use the header like this "2018-05-23". use the date inside double quotes in header column. – Inflexed

@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. – Pizzeria

May be duplicated, see #37044853 – Anastaciaanastas

Answer 1

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

Inflexed

Answer 2

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

Galosh

Answer 3

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

Venery