Laravel Excel date format

@alcinos You should pass a date object that the package recommends.

Use the WithMapping implementation and pass the package's Date object to the sheet:

Use PhpOffice\PhpSpreadsheet\Shared\Date;

class TestExport implements FromQuery, WithHeadings, ShouldAutoSize, WithStyles, WithColumnFormatting, WithMapping
{
    use Exportable;

	// ...


	public function map($row): array
	    {
	        return [
	            Date::dateTimeToExcel($row->datnum),
	        ];
	    }
	
    public function columnFormats(): array {
        return [
            'A' => NumberFormat::FORMAT_DATE_DDMMYYYY
        ];
    }

	// ...

}

You'll likely find the above a satisfactory solution, as you may find yourself needing to use mapping more times than not. Unless you're lucky and can get away with directly dumping from your database, but in my experience, almost every time I'll need to resort to mapping for some kind of conditional logic.

Still not the right result. Now my excel did set 44109 in Column A

Excel Output:

44109

<?php

namespace App\Exports;

use App\Models\test;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithMapping;

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class TestExport implements FromQuery, WithHeadings, ShouldAutoSize, WithStyles, WithColumnFormatting, WithMapping
{
    use Exportable;

    public function query(){
        return test::query()
            ->select('datum');
    }

    public function headings(): array{
        return [
            "Datum"
        ];
    }

    public function map($row): array{
        return [
            Date::dateTimeToExcel($row->datum),
        ];
    }

    public function columnFormats(): array {
        return [
            'A' => NumberFormat::FORMAT_DATE_DDMMYYYY
        ];
    }

    public function styles(Worksheet $sheet) {
        return [ 1 => ['font' => ['bold' => true ]]];
    }


}

When I remove WithMapping from implements the column is now a date value, there are only a lot of characters behind the date.

I get the following result:

2020-10-04T22:00:00.000000Z

code:

<?php

namespace App\Exports;

use App\Models\test;

use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithMapping;

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class TestExport implements FromQuery, WithHeadings, ShouldAutoSize, WithStyles, WithColumnFormatting
{
    use Exportable;

    public function query(){
        return test::query()
            ->select('datum');
    }

    public function headings(): array{
        return [
            "Datum"
        ];
    }

    public function map($row): array{
        return [
            Date::dateTimeToExcel($row->datum),
        ];
    }

    public function columnFormats(): array {
        return [
            'A' => NumberFormat::FORMAT_DATE_DDMMYYYY
        ];
    }

    public function styles(Worksheet $sheet) {
        return [ 1 => ['font' => ['bold' => true ]]];
    }


}

@alcinos I'm guessing its because your datum is a string representation of a date. You could try converting it to a Carbon instance, which I think will fix your problem:

Date::dateTimeToExcel(Carbon::parse($row->datum)),

If you were using Eloquent, it would probably already be available as a Carbon instance on the model.

Using Carbon or Models work neither.

My model:

protected $dates= ['datum'];

@alcinos Without using WithMapping you will be passing an ISO 8601 datetime to the sheet, and it looks like it's having a hard time formatting it the way you want.

That is why I suggested mapping over the data and converting it to a simple date format, which should allow your column formatting to work.

Since you have declared datum as a date on your model this should work:

public function map($row): array{
        return [
            Date::dateTimeToExcel($row->datum->format('Y-m-d'));  // datum should be a carbon instance
        ];
    }

If for some reason, datum is a string, then try this:

public function map($row): array{
        return [
            Date::dateTimeToExcel(Carbon::parse($row->datum)->format('Y-m-d')); 
        ];
    }

and remember to implement WithMapping or else the map() method won't be called.

class TestExport implements FromQuery, WithHeadings, ShouldAutoSize, WithStyles, WithColumnFormatting, WithMapping

If all else fails, although I don't recommend it, you can always just force a date string to be populated on your sheet. This will work, but will likely have the unfortunate consequence of not being able to change its cell formatting by the end user within Excel:

public function map($row): array{
        return [
            	$row->datum->format('Y/m/d'); // again, I don't recommend this approach
        ];
    }