[BUG] Problem importing date

[mariavilaro]

• Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• Checked that your issue isn't already filed.
• Checked if no PR was submitted that fixes this problem.
• Filled in the entire issue template

Versions

• PHP version: 7.2.18
• Laravel version: v5.5.43
• Package version: 3.1.15

Description

When I import a file with a date, it is imported as a number (47112.0) instead of a date (12/25/2028)

If I load a file with a date using PhpSpreadsheet, the date value is loaded ok:

    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
    $spreadsheet = $reader->load($_FILES['file']['tmp_name']);
    $sheetData = $spreadsheet->getActiveSheet()->toArray();
    dd($sheetData);

Output:

array:1 [
  0 => array:1 [
    0 => "12/25/2028"
  ]
]

But If I load the same file using an Importable, and in the BeforeSheet event I check the sheet values, the value is numeric:

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Events\BeforeSheet;

class DatatablesImport implements WithEvents
{
    use Importable, RegistersEventListeners;
    
    public static function beforeSheet(BeforeSheet $event)
    {
        dd($event->sheet->toArray());
    }
}

Output:

array:1 [
  0 => array:1 [
    0 => array:1 [
      0 => 47112.0
    ]
  ]
]

Expected behavior:

I expect that the values that are formatted as dates in the excel file are correctly loaded as dates.

Actual behavior:

The values formatted as dates in the excel file are loaded as numbers.

Additional Information

I already checked this issue #1832 but the solution doesn't work for me, as I don't know which cells are dates in the excel file.

Also, seeing that PhpSpreadsheet returns the correct value, I think there's some problem somewhere in Laravel Excel.

[GlennM]

Did you have a look at Formatting columns - Dates already?

[mariavilaro]

Did you have a look at Formatting columns - Dates already?

I'm talking about imports, not exports.

[clyall]

Just use the function:

\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject()

at least until it's fixed ;)

[jochemfuchs]

@mariavilaro Do you need any more help with this ? Or is the solution provided by @clyall sufficient for your needs?

[mariavilaro]

No, I can't use that solution because I don't know which cells are dates in my excel file. The only "solution" that works for me right now is using PhpSpreadsheet directly to load the excel data.

[gcjbr]

+1 for the same bug

[hoangnkvti]

+1 for same bug. I don't know which cells are dates in my excel file, too.
When other columns also have integer value --> I cannot use \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject() to convert all

[masterix21]

I have the same problem with the import process: all dates imported as numbers. I tried to extend my import class with StringValueBinder, implementing WithCustomValueBinder, but it seems that it doesn't work.

Does anybody found a solution?

I'm using:

• Laravel-Excel v3.1.18
• Laravel 6.17.1
• PHP 7.4.2

[mattglover11]

I have the same problem with the import process: all dates imported as numbers. I tried to extend my import class with StringValueBinder, implementing WithCustomValueBinder, but it seems that it doesn't work.

Does anybody found a solution?

I'm using:

• Laravel-Excel v3.1.18
• Laravel 6.17.1
• PHP 7.4.2

have you tried \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject() ?

[masterix21]

Yes, now it works. Thanks

[glennkarlsen]

+1

[SebastianUhlig]

+1

[erndaleiht]

+1

[miraclegly]

+1

[patrickbrouwers]

Will close this, as a more permanent solution will be part of the 4.x release