Adding date and time to excel export in Laravel

Question

I am new to Laravel and I am making a new project where I am exporting my schools data to a spreadsheet with maatwebsite, I have given it a title with the following code:

   public function headings(): array
    {
        return [
            ['Staff Report'], [
                'staffid',
                'name',
                'emailaddress',
                'faculty',             
            ]
        ];
    }

What I want to accomplish is to have the date next to the title so it should be Staff Report-04/05/21

I tried to use date time but that relies on a created_at field in the db...Has anyone used this before?

Answer 1

welcome to StackOverflow and Laravel.

So if you want to display the correct format then you have to map the data before export and specify columnFormats function.

For this you also have to use PhpOffice\PhpSpreadsheet\Style\NumberFormat and use PhpOffice\PhpSpreadsheet\Shared\Date.

In my case, I am going to export the user's company's clients. Change the code according to your use case.

Ref. link - https://docs.laravel-excel.com/2.1/export/format.html

Under the columnFormats function, you have to specify column title like (K) in my case and the "NumberFormat::FORMAT_DATE_DDMMYYYY" for the date format.

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;

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

class ClientsExport implements FromCollection, WithHeadings, WithMapping, WithColumnFormatting
{
    public function __construct()
    {

    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        $user = Auth::user();
        return $user->company->clients;
    }

    public function headings(): array
    {
        return [
            'Name',
            'Email',
            'Address',
            'Country',
            'Department',
            'Zip',
            'Fax',
            'Phone',
            'Mobile',
            'Date'
        ];
    }

    public function columnFormats(): array
    {
        return [
            'G' => 0,
            'H' => 0,
            'I' => '@',
            'J' => '@',
            'K' => NumberFormat::FORMAT_DATE_DDMMYYYY,
        ];
    }

    /**
    * @var Client $client
    */
    public function map($client): array
    {
        return [
            $client->name,
            $client->tax_id,
            $client->email,
            // other data
            $client->fax,
            $client->phone,
            $client->mobile,
            Date::dateTimeToExcel($client->created_at)
        ];
    }
}