Laravel中PhpSpreadsheet生成Excel日期列筛选异常问题

问题说明 #

通过Maatwebsite Excel生成报表时，已成功添加自动筛选，但日期列被Excel识别为文本类型，仅在双击单元格（无需修改内容）后才会被识别为日期，导致日期筛选功能无法正常使用。已尝试通过WithColumnFormatting设置列格式，但未解决根本问题。

现有代码 #

控制器导出逻辑 #

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

导出类代码 #

<?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);
          },
      ];
  }
}

解决方案 #

核心原因 #

设置列格式仅改变单元格的显示样式，但数据库返回的日期是字符串类型，PhpSpreadsheet无法将字符串自动转换为Excel可识别的日期数据类型，因此Excel仍将其视为文本。

修复步骤 #

修改导出类的collection方法，将日期字段转换为Carbon日期对象（PhpSpreadsheet会自动识别为日期类型）：

public function collection()
{   
    $relatorio = array();
    // 替换为你实际的日期字段名
    $dateFields = ['data_inicio', 'data_fim', 'data_matricula', 'data_aprovacao'];
    
    foreach($this->dados as $key => $value){
        foreach($this->campos as $campo){
            // 检测字段是否为日期字段，且值不为空
            if(in_array($campo, $dateFields) && !empty($value[$campo])){
                // 将字符串日期转换为Carbon对象
                $relatorio[$key][$campo] = \Carbon\Carbon::parse($value[$campo]);
            } else {
                $relatorio[$key][$campo] = $value[$campo];
            }
        };
    }
    return collect($relatorio);
}

备选方案（无Carbon场景） #

如果项目未使用Carbon，可以手动转换为Excel日期序列号：

use PhpOffice\PhpSpreadsheet\Shared\Date;

public function collection()
{   
    $relatorio = array();
    $dateFields = ['data_inicio', 'data_fim', 'data_matricula', 'data_aprovacao'];
    
    foreach($this->dados as $key => $value){
        foreach($this->campos as $campo){
            if(in_array($campo, $dateFields) && !empty($value[$campo])){
                // 解析日期为时间戳，转换为Excel日期序列号
                $timestamp = strtotime($value[$campo]);
                $relatorio[$key][$campo] = Date::PHPToExcel($timestamp);
            } else {
                $relatorio[$key][$campo] = $value[$campo];
            }
        };
    }
    return collect($relatorio);
}

验证要点 #

1. 确保$dateFields中的字段名与数据库返回的字段名完全匹配
2. 确认日期字符串格式为标准格式（如Y-m-d、d/m/Y），确保解析成功
3. 转换后，Excel会直接识别为日期类型，无需手动编辑单元格即可使用日期筛选功能

内容的提问来源于stack exchange，提问作者Michael S. Bitencourt