Excel Spreadsheet with Data Filters. PhpSpreadsheet Laravel

Question

I am currently generating an Excel spreadsheet with the result of a Query:

    if($request->get('excel')){
      return Excel::download(new RsestagiosExcel($dados,$campos,$nomeCampos), 'relatorio_estagios.xlsx');
    }else{
      return view('relatorios.rsestagios_relatorio_geral', ['dados' => $dados,'filtros' =>$filtros, 'nomes' => $nomeCampos, 'campos' => $campos]);
    }

I want to generate this worksheet with filters in some columns. I'm already able to add the filters but they don't work as they should. I want date columns to filter correctly as dates, however Excel initially recognizes it as text, only after clicking and editing, without changing the content, the filter recognizes as date. I'm converting the columns correctly as date:

<?php

namespace App\Exports;


use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
class RsestagiosExcel implements FromCollection, WithHeadings, WithColumnFormatting, WithEvents, ShouldAutoSize
{
  public function __construct($dados,$campos,$nomeCampos){
      $this->dados = $dados;
      $this->campos = $campos;
      $this->nomeCampos = $nomeCampos;
  }
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {   
        $relatorio = array();
        
        foreach($this->dados as $key => $value){
            foreach($this->campos as $campo){
                $relatorio[$key][$campo] = $this->dados[$key][$campo];
            };
        }
        return collect($relatorio);
    }

    public function columnFormats(): array  
    {
        return [  
            'E' => NumberFormat::FORMAT_DATE_DDMMYYYY,  
            'F' => NumberFormat::FORMAT_DATE_DDMMYYYY,  
            'G' => NumberFormat::FORMAT_DATE_DDMMYYYY,
            'L' => NumberFormat::FORMAT_DATE_DDMMYYYY,     
        ];  
    }
    public function headings(): array
    {   
        return [$this->nomeCampos];
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
                $styleArrayHeader = [
                    'font' => [
                        'bold' => true,
                    ],
                    'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    ],
                    'borders' => [
                        'top' => [
                            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        ],
                    ],
                    'fill' => [
                        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
                        'rotation' => 90,
                        'startColor' => [
                            'argb' =>'FFA0A0A0',
                        ],
                        'endColor' => [
                            'argb' => 'FFFFFFFF',
                        ],
                    ],
                ];


                $cellRangeHeader = 'A1:W1'; // All headers
                $event->sheet->setAutoFilter($event->sheet->calculateWorksheetDimension());
                $event->sheet->getStyle($cellRangeHeader)->applyFromArray($styleArrayHeader);

            },
        ];
    }
}

Follow the link with the examples of the result obtained: https://docs.google.com/document/d/18H1OpkRnEZf4iRlwltGBCDBtXoMlrvQjUZ5I2OiRThA/edit?usp=sharing

Answer 1

I managed to solve the problem as follows:

foreach($this->dados as $key => $value){
            foreach($this->campos as $campo){
                if(in_array($campo,$camposVerificar) && !empty($this->dados[$key][$campo])){
                    $relatorio[$key][$campo] ='=DATEVALUE("'. $this->dados[$key][$campo].'")';    //transforma o valor da celula em um valor valido de data do excel
                }else{
                    $relatorio[$key][$campo] = $this->dados[$key][$campo];
                }
            };
        }
$event->sheet->setAutoFilter($event->sheet->calculateWorksheetDimension());//Adiciona filtros automaticos, somente em colunas validas

public function columnFormats(): array  
    {
        return [  
            'E' => NumberFormat::FORMAT_DATE_DDMMYYYY,  
            'F' => NumberFormat::FORMAT_DATE_DDMMYYYY,  
            'G' => NumberFormat::FORMAT_DATE_DDMMYYYY,
            'L' => NumberFormat::FORMAT_DATE_DDMMYYYY   
        ];  //Formata a estrutura das colunas para que o filtro as reconheca corretamente como data
    }