Laravel中如何导出带合并单元格表格格式的CSV数据

API 探路者

2026-6-19

Laravel导出带合并单元格的Excel表格解决方案

问题背景

我目前在Laravel中使用Maatwebsite Excel包导出数据，但只能导出普通行结构的文件，现在需要导出带合并单元格的表格格式。以下是我当前使用的代码：

导出类文件：`App\Exports\ExportCertificate.php`

<?php

namespace App\Exports;

use App\Models\Certificates;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use DB;

class ExportCertificate implements FromCollection, WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */

    public function __construct($id)
    {
        $this->id = $id;
    }

    public function headings(): array
    {
        // 定义表头字段名
        return [
            'Sr.No.',
            'Batch Number',
            'Batch Number Manual',
            'Exporter Name',
            'Certificate Number',
            'Certificate Date',
            'Item Name',
            'Treated QTY',
            'Invoice Number',
            'Invoice Date',
            'Country Of Export',
            'Moisture Before Treat',
            'Moisture After Treat',
            'Total Time',
            'Warmup Time',
            'Dwell Time',
            'Company Name',
            // 按需添加更多列
        ];
    }
    public function collection()
    {
        $added_by           = session('login_type');
        $added_by_id        = session('user_id');

        $Records            = Certificates::where('added_by',$added_by)
                                ->where('added_by_id',$added_by_id)
                                ->where('id',$this->id)
                                //->orderBy('id','DESC')
                                ->get();

        return $Records->map(function ($Record, $index) {

                $BatchDetails   = \App\Models\Batches::find($Record->batch_id);
                $exporters      = \App\Models\Exporterlist::find($Record->exporter_id);
                $Item       = DB::table('items')->where('id',$Record->item_id)->first();
                
                $manual_batch_number = $BatchDetails?->manual_batch_number ?? 'N/A';
                $exporterName = $exporters?->party_name ?? 'N/A';
                $ItemName = $Item?->name ?? 'N/A';

                return [
                    'Sr.No.'                => $index + 1,
                    'Batch Number'          => $Record->batch_id ?? 'N/A',
                    'Batch Number Manual'   => $manual_batch_number, 
                    'Exporter Name'         => $exporterName, 
                    'Certificate Number'    => $Record->certificate_number ?? 'N/A',
                    'Certificate Date'      => $Record->certificate_date ?? 'N/A',
                    'Item Name'             => $ItemName,
                    'Treated QTY'           => $Record->treated_quantity,
                    'Invoice Number'        => $Record->invoice_number,
                    'Invoice Date'          => $Record->invoice_date,
                    'Country Of Export'     => $Record->country_of_export,
                    'Moisture Before Treat' => $Record->before_moisture,
                    'Moisture After Treat'  => $Record->after_moisture,
                    'Total Time'            => $Record->total_time,
                    'Warmup Time'           => $Record->warmup_time,
                    'Dwell Time'            => $Record->dwell_time,
                    'Company Name'          => $Record->company_name,
                    // 按需添加更多列
                ];
            });
    }
}

控制器方法：`App\Http\Controllers\admin\CertificateController.php`

public function export_certificates_excel ($id)
{
    return Excel::download(new ExportCertificate($id), 'certificates.xlsx');
}

路由配置

Route::any('/export_certificates_excel/{id}', 'CertificateController@export_certificates_excel')->name('certificate.export_certificates_excel');

实现合并单元格的解决方案

要实现合并单元格，需要使用Maatwebsite Excel的WithEvents和AfterSheet事件，通过PhpSpreadsheet原生API设置单元格合并和样式。

修改导出类代码

更新ExportCertificate.php，实现WithEvents接口并添加事件处理逻辑：

<?php

namespace App\Exports;

use App\Models\Certificates;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use DB;

class ExportCertificate implements FromCollection, WithHeadings, WithEvents
{
    protected $id;

    public function __construct($id)
    {
        $this->id = $id;
    }

    // 保留原headings方法不变
    public function headings(): array
    {
        return [
            'Sr.No.',
            'Batch Number',
            'Batch Number Manual',
            'Exporter Name',
            'Certificate Number',
            'Certificate Date',
            'Item Name',
            'Treated QTY',
            'Invoice Number',
            'Invoice Date',
            'Country Of Export',
            'Moisture Before Treat',
            'Moisture After Treat',
            'Total Time',
            'Warmup Time',
            'Dwell Time',
            'Company Name',
        ];
    }

    // 保留原collection方法不变
    public function collection()
    {
        $added_by           = session('login_type');
        $added_by_id        = session('user_id');

        $Records            = Certificates::where('added_by',$added_by)
                                ->where('added_by_id',$added_by_id)
                                ->where('id',$this->id)
                                ->get();

        return $Records->map(function ($Record, $index) {

                $BatchDetails   = \App\Models\Batches::find($Record->batch_id);
                $exporters      = \App\Models\Exporterlist::find($Record->exporter_id);
                $Item       = DB::table('items')->where('id',$Record->item_id)->first();
                
                $manual_batch_number = $BatchDetails?->manual_batch_number ?? 'N/A';
                $exporterName = $exporters?->party_name ?? 'N/A';
                $ItemName = $Item?->name ?? 'N/A';

                return [
                    'Sr.No.'                => $index + 1,
                    'Batch Number'          => $Record->batch_id ?? 'N/A',
                    'Batch Number Manual'   => $manual_batch_number, 
                    'Exporter Name'         => $exporterName, 
                    'Certificate Number'    => $Record->certificate_number ?? 'N/A',
                    'Certificate Date'      => $Record->certificate_date ?? 'N/A',
                    'Item Name'             => $ItemName,
                    'Treated QTY'           => $Record->treated_quantity,
                    'Invoice Number'        => $Record->invoice_number,
                    'Invoice Date'          => $Record->invoice_date,
                    'Country Of Export'     => $Record->country_of_export,
                    'Moisture Before Treat' => $Record->before_moisture,
                    'Moisture After Treat'  => $Record->after_moisture,
                    'Total Time'            => $Record->total_time,
                    'Warmup Time'           => $Record->warmup_time,
                    'Dwell Time'            => $Record->dwell_time,
                    'Company Name'          => $Record->company_name,
                ];
            });
    }

    // 添加事件处理，设置合并单元格和样式
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                
                // 示例：合并表头上方的大标题行（A1到Q1）
                $sheet->mergeCells('A1:Q1');
                $sheet->setCellValue('A1', '证书导出详情');
                // 设置标题样式
                $sheet->getStyle('A1')->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'size' => 16,
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => [
                            'rgb' => 'FFD966',
                        ],
                    ],
                ]);

                // 动态合并内容行中相同值的单元格（示例：合并Certificate Number列）
                $dataCount = $this->collection()->count();
                $endRow = 2 + $dataCount; // 表头在第2行，数据从第3行开始

                // 遍历Certificate Number列（E列），合并相同值的连续行
                $currentVal = $sheet->getCell('E3')->getValue();
                $startMergeRow = 3;
                for ($row = 4; $row <= $endRow; $row++) {
                    $cellVal = $sheet->getCell('E'.$row)->getValue();
                    if ($cellVal !== $currentVal) {
                        if ($startMergeRow < $row - 1) {
                            $sheet->mergeCells('E'.$startMergeRow.':E'.($row - 1));
                            $sheet->getStyle('E'.$startMergeRow.':E'.($row - 1))->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
                        }
                        $currentVal = $cellVal;
                        $startMergeRow = $row;
                    }
                }
                // 处理最后一组相同值
                if ($startMergeRow < $endRow) {
                    $sheet->mergeCells('E'.$startMergeRow.':E'.$endRow);
                    $sheet->getStyle('E'.$startMergeRow.':E'.$endRow)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
                }

                // 设置表头样式
                $sheet->getStyle('A2:Q2')->applyFromArray([
                    'font' => [
                        'bold' => true,
                    ],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => [
                            'rgb' => 'CCCCCC',
                        ],
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                        ],
                    ],
                ]);

                // 设置数据区域边框
                $sheet->getStyle('A3:Q'.$endRow)->applyFromArray([
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                        ],
                    ],
                ]);

                // 自动调整列宽
                foreach(range('A', 'Q') as $column) {
                    $sheet->getColumnDimension($column)->setAutoSize(true);
                }
            },
        ];
    }
}

关键说明

接口实现：通过WithEvents接口监听AfterSheet事件，在表格生成后执行样式和合并操作。
单元格合并：使用mergeCells()方法指定合并范围，格式如A1:Q1。
动态合并逻辑：遍历数据行，判断相邻单元格值是否一致，批量合并相同值的连续单元格。
样式优化：可自定义字体、对齐方式、背景色、边框等样式，提升表格可读性。

注意事项

根据实际需求调整合并列和判断逻辑（比如合并Exporter Name、Batch Number等列）。
CSV格式不支持单元格合并，必须导出为XLSX格式才能保留合并样式。

内容的提问来源于stack exchange，提问作者Manoj Jangid

Laravel中如何导出带合并单元格表格格式的CSV数据

问题背景 #

导出类文件：App\Exports\ExportCertificate.php #

控制器方法：App\Http\Controllers\admin\CertificateController.php #

路由配置 #

实现合并单元格的解决方案 #

修改导出类代码 #

关键说明 #

注意事项 #