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.

Answer 1

2024, you can use map method:

1. Install carbon:

composer require nesbot/carbon

2. include carbon

use Carbon\Carbon;

3. • 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);

    }
}

Answer 2

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