如何使用Laravel Excel实现带分组的数据导出？

1. 构建结构化分组数据 #

不要仅依赖SQL的GROUP BY，先获取全量明细数据，在PHP层面用集合处理成嵌套分组结构：

use Maatwebsite\Excel\Concerns\FromCollection;
use App\Models\Order;

class GroupedReportExport implements FromCollection
{
    public function collection()
    {
        // 获取带关联的原始明细数据
        $records = Order::with('product')->get();
        
        // 按目标字段分组（示例：按产品分类名称）
        $groupedData = $records->groupBy('product.category.name');
        $formattedData = [];

        foreach ($groupedData as $categoryName => $orders) {
            // 添加分组标题行
            $formattedData[] = ['category' => $categoryName, 'product' => '', 'amount' => '', 'total' => ''];
            // 添加分组内的明细行
            foreach ($orders as $order) {
                $formattedData[] = [
                    'category' => '',
                    'product' => $order->product->name,
                    'amount' => $order->quantity,
                    'total' => $order->total_price
                ];
            }
            // 添加分组汇总行
            $formattedData[] = [
                'category' => '合计',
                'product' => '',
                'amount' => $orders->sum('quantity'),
                'total' => $orders->sum('total_price')
            ];
        }

        return collect($formattedData);
    }
}

2. 设置表头与分组样式 #

通过WithHeadings定义表头，WithStyles处理分组行的单元格合并和样式：

use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class GroupedReportExport implements FromCollection, WithHeadings, WithStyles
{
    // ... 上述collection方法 ...

    public function headings(): array
    {
        return ['产品分类', '产品名称', '数量', '总价'];
    }

    public function styles(Worksheet $sheet)
    {
        // 表头样式
        $sheet->getStyle('A1:D1')->applyFromArray([
            'font' => ['bold' => true],
            'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFCCCCCC']]
        ]);

        $rowCount = $sheet->getHighestRow();
        for ($row = 2; $row <= $rowCount; $row++) {
            // 分组标题行：合并单元格+高亮样式
            if (!empty($sheet->getCell("A$row")->getValue()) && empty($sheet->getCell("B$row")->getValue())) {
                $sheet->mergeCells("A$row:D$row");
                $sheet->getStyle("A$row")->applyFromArray([
                    'font' => ['bold' => true, 'size' => 12],
                    'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFE6F2FF']]
                ]);
            }
            // 分组汇总行：合并单元格+强调样式
            if ($sheet->getCell("A$row")->getValue() === '合计') {
                $sheet->mergeCells("A$row:B$row");
                $sheet->getStyle("A$row:D$row")->applyFromArray([
                    'font' => ['bold' => true],
                    'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['argb' => 'FFF9E79F']]
                ]);
            }
        }

        // 设置列宽适配内容
        $sheet->getColumnDimension('A')->setWidth(20);
        $sheet->getColumnDimension('B')->setWidth(25);
        $sheet->getColumnDimension('C')->setWidth(10);
        $sheet->getColumnDimension('D')->setWidth(15);
    }
}

3. 实现Excel原生折叠分组 #

如果需要Excel自带的折叠/展开功能，用WithEvents监听AfterSheet事件设置大纲分组：

use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

class GroupedReportExport implements FromCollection, WithHeadings, WithStyles, WithEvents
{
    // ... 其他方法 ...

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $rowCount = $sheet->getHighestRow();
                $currentGroupStart = 0;

                for ($row = 2; $row <= $rowCount; $row++) {
                    // 定位分组标题行，记录明细起始行
                    if (!empty($sheet->getCell("A$row")->getValue()) && empty($sheet->getCell("B$row")->getValue())) {
                        if ($currentGroupStart !== 0) {
                            // 为上一组的明细行设置分组，默认折叠
                            $sheet->groupRows($currentGroupStart, $row-2, true);
                        }
                        $currentGroupStart = $row+1;
                    }
                }
                // 处理最后一组
                if ($currentGroupStart !== 0 && $currentGroupStart < $rowCount) {
                    $sheet->groupRows($currentGroupStart, $rowCount-1, true);
                }
            },
        ];
    }
}

4. 控制器调用导出 #

和基础导出逻辑一致，直接实例化导出类即可：

use Maatwebsite\Excel\Facades\Excel;

public function exportGroupedReport()
{
    return Excel::download(new GroupedReportExport(), '分组报表.xlsx');
}

核心思路：先通过集合处理出包含分组标题、明细、汇总的完整数据集，再利用Laravel Excel的样式和事件能力，还原Excel的分组视觉效果和交互功能，避免仅靠SQL分组无法实现的复杂报表结构。

内容的提问来源于stack exchange，提问作者Diwani Hime