How to format dates in Laravel Excel?

Question

I want to ask, how can we validate dates from Excel file. I have encountered some weird test cases below.

Firstly, I have inputted 5/13/2021 in my excel file, but when I dump in, it doesn't display same, instead it displays 44329.

But fortunately I could able to display to 5/13/2021 using the following codes:

$temp = Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
$datetime = Carbon::parse($temp);

So, my big problem here is I can't use before or after validations. Like below codes it always fail even though, I fill it in correctly.

return Validator::make($rows->toArray(), [
            '*.0' => 'required|after:now|before:0.1' //publish_at
            '*.1' => 'required|before:0.0' // expired_at
        ])->validate();

As you can see in the picture below, the value of publish_at is 44329, and expired_at is 44330. I don't know why it fails. I tried also gt or lt validation it still fails.

Someone knows how to do it. would appreciate it.

matticustard · Accepted Answer · 2021-05-21 13:03:31Z

3

Based on the validation, it looks like you know which columns are going to be dates. With this knowledge, I believe you may be able to implement the WithCustomValueBinder concern to achieve dates formatted to your liking.

This is a quick and dirty example to show how a predefined array of columns could be formatted as date strings. Update column letters and date format as desired. Obviously, you will need to add in your preferred import method and validation.

<?php

namespace App\Imports;

use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\DefaultValueBinder;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class SampleImport extends DefaultValueBinder implements WithCustomValueBinder
{
    // set the preferred date format
    private $date_format = 'Y-m-d';

    // set the columns to be formatted as dates
    private $date_columns = ['A','B'];

    // bind date formats to column defined above
    public function bindValue(Cell $cell, $value)
    {
        if (in_array($cell->getColumn(), $this->date_columns)) {
            $cell->setValueExplicit(Date::excelToDateTimeObject($value)->format($this->date_format), DataType::TYPE_STRING);

            return true;
        }

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

edited May 21, 2021 at 13:03

answered May 20, 2021 at 3:43

matticustard

5,1691 gold badge17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

schutte Over a year ago

thank you very much. i fix it through this documentation, pls. accept my edits. docs.laravel-excel.com/3.1/imports/…

matticustard Over a year ago

Glad you got it working. Someone had already rejected the edits, but I made updates based on the docs linked. The original code had worked in my basic testing, but this update makes sense.

schutte Over a year ago

np. by the way also read. this another way of formatting dates in this doc.link docs.laravel-excel.com/2.1/import/dates.html#format-dates

matticustard Over a year ago

Is that for a different version of Laravel Excel? I don't see those methods in the PHPSpreadSheet docs. I tried applying them to the reader using events but it did not work. I'd be interested to know if you got that working on 3.x.

schutte Over a year ago

haven't tried it yet. just sharin. but i'm now okay with your answer.

Alejandro De Castro · Accepted Answer · 2024-06-14 08:54:28Z

2024, you can use map method:

Install carbon:

composer require nesbot/carbon

include carbon

use Carbon\Carbon;

- Now you can format date this way:

$employee->fecha_nacimiento ? Carbon::parse($employee->fecha_nacimiento)->format('d/m/Y') : null,

full example:

<?php

namespace App\Exports;

use Carbon\Carbon;
use DateTimeInterface;
use App\Models\Employee;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class ExportEmployee implements FromCollection, WithHeadings, WithMapping, ShouldAutoSize, WithStyles
{
    public function collection()
    {
        return Employee::all();
    }

    public function headings(): array
    {
        return [
            'ID',
            'Nombre',
            'Apellido',
            'Fecha de Nacimiento',
        ];
    }

    public function map($employee): array
    {
        return [
            $employee->id,
            $employee->nombre,
            $employee->apellido,
            
            // FORMAT DATE
            $employee->fecha_nacimiento ? Carbon::parse($employee->fecha_nacimiento)->format('d/m/Y') : null,
        ];
    }

    //FORMAT HEADER
    public function styles(Worksheet $sheet)
    {
        $sheet->getStyle('A1:E1')->applyFromArray([
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => 'solid',
                'color' => ['rgb' => '2865A4'],
            ],
            'font' => [
                'color' => ['rgb' => 'FFFFFF'],
            ],
        ]);

        $sheet->getStyle('A:E')->getAlignment()->setHorizontal('center');
        $sheet->getStyle('A1:E1')->getFont()->setBold(true);

    }
}

Collectives™ on Stack Overflow

How to format dates in Laravel Excel?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related