How to use time format excel cell in laravel uploaded excel?

1
votes

i am using laravel package maatwebsite/excel version 3.1 for excel file upload.

   $collection = Excel::toCollection(new UsersImport, storage_path('app/' . $newFile));

i have uploaded a file where the format of cell is time. enter image description here

Here time in and time out cell has time format. when i upload this file the resulting output is: enter image description here

in resulting output time in and time out cells data is converted into string. how to prevent this from converting or how to change this from text to time?

1
phpexcellaravelmaatwebsite-excel

1 Answers

1
votes

You need to manually convert them or use a mapper.

Can use the built in function to convert one of the date as so in the following example:

\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($collection->first()[4])

You can also use Carbon I think

Carbon::parse($collection->first()[4])

If you use a mapper you can define that beforehand and don't worry about it.

https://docs.laravel-excel.com/3.1/imports/mapped-cells.html

namespace App\Imports;

use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithMappedCells;
use PhpOffice\PhpSpreadsheet\Shared\Date;

new Import implements WithMappedCells, ToModel 
{
public function mapping(): array
{
    return [
        'timecellA'  => 'A5',
        'timecellB' => 'A6',
        'timecellC' => 'A7',
        'timecellD' => 'A8',

    ];
}

public function model(array $row)
{
    return [
         'timecellA'  => Date::excelToDateTimeObject($row['timecellA'],
         'timecellB'  => Date::excelToDateTimeObject($row['timecellB'],
         'timecellC'  => Date::excelToDateTimeObject($row['timecellC'],
         'timecellD'  => Date::excelToDateTimeObject($row['timecellD'],
    ];
}
}