Laravel excel maatwebsite 3.1 import, date column in excel cell returns as unknown format number. How to solve this?

Question

By using Maatwebsite/Laravel-Excel version 3.1 to import excel sheet, here I faced an issue date time column of the excel sheet returns unknown number. How to solve this? Example : Consider Cell value "29/07/1989" and returns as "32178" when import.

please see this answer https://stackoverflow.com/questions/37044353/laravel-excel-import-date-column-in-excel-cell-returns-as-floating-value-how-t/62138324#62138324 — Nicolas Scordamaglia, Jun 22 '20 at 16:28

score 46 · Accepted Answer · answered Mar 28 '19 at 05:33

46

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

From this answer: https://stackoverflow.com/a/55139981/9133724

answered Mar 28 '19 at 05:33

Sergei Kuraksin

772
9
10

8

thanks you, im using this code to solve problem Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value)); – Reno Anthus Mar 30 '19 at 09:11
dont work if your first row in excel or csv is the colum name – Luis Alfredo Serrano Díaz Aug 31 '20 at 22:47

score 32 · Answer 2 · edited Oct 23 '19 at 11:58

32

Solved! This is the code I used to solve my problem:

Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));

edited Oct 23 '19 at 11:58

D Malan

10,272
3
25
50

answered Oct 23 '19 at 07:18

Reno Anthus

714
1
9
13

score 18 · Answer 3 · edited Sep 03 '21 at 17:25

18

I tried the above solution but always get stuck with non-numeric value error

I manage to solve this using

$date = intval($row['value']);

\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($date)->format('d/m/Y')

edited Sep 03 '21 at 17:25

Nipun Tharuksha

2,496
4
17
40

answered Aug 18 '20 at 11:55

Skyrem Brilliant

181
1
2

Great! Comment Best! – Taffarel Xavier Mar 02 '23 at 15:07

score 6 · Answer 4 · answered Apr 08 '21 at 02:52

Based on Skyrem Brilliant's @skyrem-brilliant answer, I solved it this way:

<?php

//...

class YourExcelImport implements OnEachRow, WithValidation, WithHeadingRow
{
   // ...

    /**
     * Tweak the data slightly before sending it to the validator
     * @param $data
     * @param $index
     * @return mixed
     */
    public function prepareForValidation($data, $index)
    {
        //Fix that Excel's numeric date (counting in days since 1900-01-01)
        $data['your_date_column'] = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($data['your_date_column'])->format('Y-m-d');
        //...
    }

    /**
     * List the validation rules
     * @return array
     */
    public function rules(): array
    {
        return [
            'your_date_column'=>'required|date_format:Y-m-d',
            //..
        ];
    }
}
?>

That did the trick and the validation passes.

score 1 · Answer 5 · answered Nov 21 '20 at 23:20

That "unknown number" is an excel timestamp, that way it stores the date and time data internally.

for example:

123213.0: it's just a date
213233.1233: is a date and time
0.1233: it's one hour

If you can map the cell and you know which column will always have a date / time / date-time you can use mapped cells or convert it manually, see: https://stackoverflow.com/a/59049044

Otherwise, if your need involves resolving datetime fields dynamically, I have written a method that is responsible for automatically detecting if the value is a datetime dynamically (regardless of whether or not you know if there will be a datetime in that column) or I have tried various data types and it works fine

   /**
 * @param Cell $cell
 * @param $value
 * 
 * @return boolean;
 */
public function bindValue(Cell $cell, $value)
{
    $formatedCellValue = $this->formatDateTimeCell($value, $datetime_output_format = "d-m-Y H:i:s", $date_output_format = "d-m-Y", $time_output_format = "H:i:s" );
    if($formatedCellValue != false){
        $cell->setValueExplicit($formatedCellValue, DataType::TYPE_STRING);
        return true;
    }

    // else return default behavior
    return parent::bindValue($cell, $value);
}


/**
 * 
 * Convert excel-timestamp to Php-timestamp and again to excel-timestamp to compare both compare
 * By Leonardo J. Jauregui ( @Nanod10 | siskit dot com )
 * 
 * @param $value (cell value)
 * @param String $datetime_output_format
 * @param String $date_output_format
 * @param String $time_output_format
 * 
 * @return $formatedCellValue
 */
private function formatDateTimeCell( $value, $datetime_output_format = "Y-m-d H:i:s", $date_output_format = "Y-m-d", $time_output_format = "H:i:s" )
{

    // is only time flag
    $is_only_time = false;
    
    // Divide Excel-timestamp to know if is Only Date, Only Time or both of them
    $excel_datetime_exploded = explode(".", $value);

    // if has dot, maybe date has time or is only time
    if(strstr($value,".")){
        // Excel-timestamp to Php-DateTimeObject
        $dateTimeObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
        // if Excel-timestamp > 0 then has Date and Time 
        if(intval($excel_datetime_exploded[0]) > 0){
            // Date and Time
            $output_format = $datetime_output_format;
            $is_only_time = false;
        }else{
            // Only time
            $output_format = $time_output_format;
            $is_only_time = true;
        }
    }else{
        // Only Date
        // Excel-timestamp to Php-DateTimeObject
        $dateTimeObject = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value);
        $output_format = $date_output_format;
        $is_only_time = false;
    }
        
    // Php-DateTimeObject to Php-timestamp
    $phpTimestamp = $dateTimeObject->getTimestamp();

    // Php-timestamp to Excel-timestamp
    $excelTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel( $phpTimestamp );
        
    // if is only Time
    if($is_only_time){
        // 01-01-1970 = 25569
        // Substract to match PhpToExcel conversion
        $excelTimestamp = $excelTimestamp - 25569;
    }

    /* 
    // uncoment to debug manualy and see if working
    $debug_arr = [
            "value"=>$value,
            "value_float"=>floatval($value),
            "dateTimeObject"=>$dateTimeObject,
            "phpTimestamp"=>$phpTimestamp,
            "excelTimestamp"=>$excelTimestamp,
            "default_date_format"=>$dateTimeObject->format('Y-m-d H:i:s'),
            "custom_date_format"=>$dateTimeObject->format($output_format)
        ];
        
    if($cell->getColumn()=="Q"){
        if($cell->getRow()=="2"){
            if(floatval($value)===$excelTimestamp){
                dd($debug_arr);
            }
        }
    }

    */
    
    // if the values match
    if( floatval($value) === $excelTimestamp ){
        // is a fucking date! ;)
        $formatedCellValue = $dateTimeObject->format($output_format);
        return $formatedCellValue;
    }else{
        // return normal value
        return false;
    }
    
}

This is a really good solution, but there is a case when you have numeric values and dates on the same sheet and some numbers can be a date as well, so I am looking for a solution on how to determine if the number is a date or a numeric value :) — DokiCRO, Oct 25 '22 at 11:51

score 1 · Answer 6 · edited May 01 '22 at 01:54

Simply use this function to store date.

use PhpOffice\PhpSpreadsheet\Shared;

public function collection(Collection $collection)
{
    $errors = $this->validateBulk($collection);
    if (!empty($errors)) {
        return;
    }

    $holidays = [];
    foreach ($collection as $col) {
        Validator::make($col->toArray(), $this->rules())->validate();
        $holidays[] = [
            'title' => $col['title'],
            'holiday_date' => Date::excelToDateTimeObject($col['holiday_date'])
                ->format('y-m-d'),
            'holiday_year' => Date::excelToDateTimeObject($col['holiday_date'])
                ->format('y'),
        ];
        $this->rows++;
    }
    
    Holidays::insert($holidays);
}

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, May 02 '22 at 04:20

score 0 · Answer 7 · answered Jun 16 '22 at 13:56

In this case, You can refer my code. It is working well. <?php

namespace App\Imports;

use App\Models\Sale;
use Illuminate\Support\Facades\DB;
// use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\ToModel;

class SalesImport implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new Sale([
            "name" => $row[0],
            "birthday" => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row[1]),
            "gender" => $row[2],
            "age" => $row[3],
            "card_type" => $row[4],
            "identify_no" => $row[5],
            "address" => $row[6],
            "phone" => $row[7],
            "email" => $row[8],
        ]);
    }
}

Divesh Chakrayat · Answer 8 · 2022-12-21T08:21:49.973

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

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());
                 }
         }

Inside the FileImportRequest Class.

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

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;
       }
    }

Laravel excel maatwebsite 3.1 import, date column in excel cell returns as unknown format number. How to solve this?

8 Answers8