Adding date and time to excel export in Laravel

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

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 PhpOfficePhpSpreadsheetStyleNumberFormat and use PhpOfficePhpSpreadsheetSharedDate.

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 AppExports;

use MaatwebsiteExcelConcernsFromCollection;
use MaatwebsiteExcelConcernsWithHeadings;
use MaatwebsiteExcelConcernsWithMapping;
use MaatwebsiteExcelConcernsWithColumnFormatting;

use PhpOfficePhpSpreadsheetSharedDate;
use PhpOfficePhpSpreadsheetStyleNumberFormat;

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

    }

    /**
    * @return IlluminateSupportCollection
    */
    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)
        ];
    }
}