SolvedLaravel Excel [BUG] import date format issue

  • 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.

Versions

  • PHP version: 7.1
  • Laravel version: 5.7
  • Package version: 3.1

Description

I created new import with WithChunkReading and batch size. The issue i am facing is importer converts date columns to timestamp( i believe its timestamp) 43257.0. after investing the issue i found very old thread #404 and one of the solution that fixed it for me was setting true value to false in class ReadChunk available at vendor/maatwebsite/excel/src/Jobs/ReadChunk.php. line is $this->reader->setReadDataOnly(true);
This solution works for now but when we will do composer update it will be gone as its not configurable in library.

Steps to Reproduce

  1. create excel
  2. add column with any date format.
  3. import excel using chunk method importer via library.

Expected behavior:

I would expect the library to upload the date as expected.

Actual behavior:

library converts date to timestamp( assuming its timestamp)

Additional Information

Here is my import class,

`<?php
namespace App\Imports;

use App\Sample;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Imports\HeadingRowFormatter;

HeadingRowFormatter::default('none');

class Sample implements ToModel, WithHeadingRow, WithBatchInserts, WithChunkReading
{

public function model(array $row)
{


    return new user([
        'UserName'           => $row['UserName'],
        'Password'           => $row['Password'],
        'date'               => $row['date'],
    ]);
}

public function batchSize(): int
{
    return 1000;
}

public function chunkSize(): int
{
    return 1000;
}

}`

39 Answers

✔️Accepted Answer

I started working on a PR but quickly realized that it was going to be complicated to implement. @patrickbrouwers was going to review the codebase to see what the best way forward would be.

In the mean time I am just using a helper method on my Import object:

/**
 * Transform a date value into a Carbon object.
 *
 * @return \Carbon\Carbon|null
 */
public function transformDate($value, $format = 'Y-m-d')
{
    try {
        return \Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
    } catch (\ErrorException $e) {
        return \Carbon\Carbon::createFromFormat($format, $value);
    }
}
class Sample implements ToModel
{
    public function model(array $row)
    {
        return new user([
            'name' => $row[0],
            'email' => $row[1],
            'birth-date' => $this->transformDate($row[2]),
        ]);
    }
}

Other Answers:

Untested, but I believe you need to do:

 return new user([
        'UserName'           => $row['UserName'],
        'Password'           => $row['Password'],
        'date'               => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['date']),
    ]);

I remember setReadDataOnly to cause problems in some other situations, so I'm not comfortable just adding that again. Perhaps we can make it an opt-in concern or something.

hmmm it already formatted on date

screen shot 2018-10-25 at 16 48 06

i am using "maatwebsite/excel": "^3.1",

I can send a PR with WithDates but it will be a least a week. I am at a conference and cant focus on that right now.

Hi, i find this solution, works for me!

protected function formatDateExcel($date){ if (gettype($date) === 'double') { $birthday = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date); return $birthday->format('n/j/Y'); } return $date; }

and i use

$this->formatDateExcel($row['birthday']);

More Issues: