Laravel - How to convert date value of value in a cell on imported excel files-CodePudding

I tried to make a function where User could import formatted file such as .csv or .xlsx. I make the function using Maatwebsite excel library.

When I tried to insert the data to database, a cell that has value of today date 24/01/2023 is converted to 44950

My Import model kinda like this

public function model(array $row)
{
    return new Event([
        /**
    * Other attributes and columns
    *
    */
        'date' => $row[1],
    ]);
}

How to convert those value to 'Y-m-d'?

CodePudding user response：

I found the best solutions, idk what happened to the excel imported files, but this helps me.

use PhpOffice\PhpSpreadsheet\Shared\Date;

public function model(array $row)
{
    return new Event([
        /**
    * Other attributes and columns
    *
    */
        'date' => Date::excelToDateTimeObject($row[1]),
    ]);
}

Source: https://github.com/SpartnerNL/Laravel-Excel/issues/1978

CodePudding user response：

Here are a couple of options to handle this issue:

You can add a try-catch block to handle the exception that is thrown when the date value is not in the correct format.

use Carbon\Carbon;

public function model(array $row)
{
    try {
        $date = Carbon::createFromFormat('d/m/Y', $row[1])->toDateString();
    } catch (\Exception $e) {
        // code to handle the exception, such as logging the error
    }

    return new Event([
        /**
    * Other attributes and columns
    *
    */
        'date' => $date,
    ]);
}

You can check if the date value is in the correct format before passing it to the Carbon method.

use Carbon\Carbon;

public function model(array $row)
{
    $date = null;
    $date_regex = '/^(0?[1-9]|[12][0-9]|3[01])[\/](0?[1-9]|1[012])[\/](19|20)\d\d$/';
    if(preg_match($date_regex, $row[1]) === 1) {
        $date = Carbon::createFromFormat('d/m/Y', $row[1])->toDateString();
    }
    return new Event([
        /**
    * Other attributes and columns
    *
    */
        'date' => $date,
    ]);
}

It will check if the date format is matching the given format in the regular expression. If it matches then it will proceed to parse the date otherwise it will not parse the date.

It is also important to note that, you should check the format of the date in the excel sheet and adjust the format in the createFromFormat() method accordingly.

CodePudding user response：

You can use the Carbon library's createFromFormat() method to convert the date to the desired format. Here's an example of how you can modify your model() function to do this:

public function model(array $row) {
return new Event([
    /**
    * Other attributes and columns
    *
    */
    'date' => Carbon::createFromFormat('d/m/Y', $row[1])->format('Y-m-d'),
]); }

In this example, Carbon::createFromFormat('d/m/Y', $row[1]) will create a Carbon instance from the date in the format "d/m/Y" (e.g. "24/01/2023"). Then, ->format('Y-m-d') will convert that Carbon instance to the format "Y-m-d" (e.g. "2023-01-24").