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

If possible try to use the header like this "2018-05-23". use the date inside double quotes in header column. — Chiru Adi
– Chiru Adi, Commented May 23, 2018 at 7: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
– Martin, Commented May 23, 2018 at 7:24

Jakhongir · Accepted Answer · 2019-03-13 11:51:06Z

13

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

edited Mar 13, 2019 at 11:51

answered Mar 13, 2019 at 10:44

Jakhongir

6169 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chiru Adi · Accepted Answer · 2018-05-23 07:29:44Z

5

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

answered May 23, 2018 at 7:29

Chiru Adi

6475 silver badges21 bronze badges

Comments

Francesco Taioli · Accepted Answer · 2020-01-30 11:06:14Z

1

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

answered Jan 30, 2020 at 11:06

Francesco Taioli

2,8762 gold badges24 silver badges37 bronze badges

Collectives™ on Stack Overflow

Laravel Excel is converting dates from heading into some numbers

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related