Laravel-Excel: [BUG] import date format issue

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.

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]),
        ]);
    }
}

hmmm it already formatted on date

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

Hi, i find this solution, works for me!!!

protected function formatDateExcel($date) { 
        if ('double' === gettype($date)) {
            $date = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date);

            return $date->format('n/j/Y');
        }

        return $date;
 }

and i use

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

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.

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]),
        ]);
    }
}

Works like a Charm. Thank You

PhpSpreadsheet does know about this format. However for performance reasons it’s recommend to read the Excel file in read_only mode. If you can disable that here: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/config/excel.php#L47 PhpSpreadsheet should then read the date as date automatically. If you prefer the more performant setting, you can format the dates yourself using the PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date)

Did anyone solve the problem??

@Jaikangam93 A little late, but I happened to fix it with the help of WithCustomValueBinder wrote a little check for the coordinate of date column and used excelToDateTimeObject method on PhpOffice\PhpSpreadsheet\Shared\Date and finally set the value as for the cell as string.

Here is the code:

   public function bindValue(Cell $cell, $value)
   {
       if(preg_match('/^E*\d*$/', $cell->getCoordinate())){
                $cell->setValueExplicit(Date::excelToDateTimeObject($value)->format('Y-m-d'), DataType::TYPE_STRING);
        }
        else{
            $cell->setValueExplicit($value, DataType::TYPE_STRING);
        }

        return true;
    }

Make sure to import all the namespaces and implement WithCustomValueBinder on the importer.

Anybody can post the code of progress status show, on import excel file.

I have created a function to check the file extensions. which parse date as per the file extension

1. Controller code

      public function store(FileImportRequest $request)
      {
          try {
            Excel::import(new FileImport($request->file->extension()), $request->file);
            }catch (\Error $ex) {
                        throw new \Exception('Error:' . $ex->getMessage());
                    }
            }
   

2. Inside the FileImportRequest Class.

              public function rules()
                {
                    return [
                        'file' => 'required|file|mimes:csv,xlsx,xsl'
                    ];
                  }
   

3. Inside the FileImport class.

   protected $extension;

        public function __construct($extension)
        {
            $this->extension = $extension;
        }   
   
        public function model(array $row)
        {
            return new FileImport([
                'expires_at' => $this->checkExtension($row[8]),
                'created_at' => $this->checkExtension($row[9]),
            ]);
        }
   
    private function  checkExtension($dateTime)
      {
          switch ($this->extension) {
              case 'csv':
                  return Carbon::parse($dateTime)->toDateTimeString();
                  break;
              case 'xlsx':
              case 'xsl':
                  return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($dateTime);
                  break;
          }
       }
   

I have a problem, I’m importing a file that in some cells can have any value (numeric, string, date…), how can I know that the original value in that cell was a date, so I can format it correctly?