如何为maatwebsite/Laravel-Excel导出添加带列求和的动态页脚

下面介绍两种实现方式，分别对应打印页脚和数据末尾总计行的需求：

方式一：设置Excel打印页脚（每页底部显示） #

1. 创建/修改导出类，实现必要接口
   确保导出类继承核心接口的同时，实现WithFooter接口，提前计算好列的总和，再在footer方法中返回页脚内容：

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithFooter;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;

class OrdersExport implements FromCollection, WithHeadings, ShouldAutoSize, WithFooter
{
    protected $data;
    protected $totalQty;
    protected $totalAmount;

    // 构造函数传入数据并计算总和
    public function __construct($data)
    {
        $this->data = $data;
        $this->totalQty = $data->sum('quantity');
        $this->totalAmount = $data->sum('amount');
    }

    // 返回导出数据
    public function collection()
    {
        return $this->data->select('order_no', 'quantity', 'amount');
    }

    // 设置表头
    public function headings(): array
    {
        return ['订单号', '数量', '金额'];
    }

    // 定义页脚内容
    public function footer(): string
    {
        // &C表示居中对齐，&L左对齐，&R右对齐
        return '&C 总计: 数量 ' . $this->totalQty . ' | 金额 ' . $this->totalAmount;
    }
}

2. 控制器中调用导出

use App\Exports\OrdersExport;
use Maatwebsite\Excel\Facades\Excel;

public function export()
{
    $data = \App\Models\Order::get();
    return Excel::download(new OrdersExport($data), '订单汇总.xlsx');
}

方式二：在数据末尾添加总计行（更直观的表格底部总和） #

如果需要在表格最后一行显示总和（而非打印页脚），可以通过WithEvents接口监听AfterSheet事件，直接操作Excel单元格：

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;

class OrdersExport implements FromCollection, WithHeadings, ShouldAutoSize, WithEvents
{
    protected $data;
    protected $totalQty;
    protected $totalAmount;

    public function __construct($data)
    {
        $this->data = $data;
        $this->totalQty = $data->sum('quantity');
        $this->totalAmount = $data->sum('amount');
    }

    public function collection()
    {
        return $this->data->select('order_no', 'quantity', 'amount');
    }

    public function headings(): array
    {
        return ['订单号', '数量', '金额'];
    }

    // 注册事件回调
    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event) {
                // 计算总计行的位置（表头+数据行数+1）
                $totalRow = $this->data->count() + 2;

                // 设置总计行内容
                $event->sheet->setCellValue('A' . $totalRow, '总计');
                $event->sheet->setCellValue('B' . $totalRow, $this->totalQty);
                $event->sheet->setCellValue('C' . $totalRow, $this->totalAmount);

                // 给总计行添加样式：加粗、居中对齐
                $event->sheet->getStyle('A' . $totalRow . ':C' . $totalRow)->applyFromArray([
                    'font' => ['bold' => true],
                    'alignment' => [
                        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                    ]
                ]);
            }
        ];
    }
}

注意事项 #

• 确保已安装Maatwebsite Excel包：执行composer require maatwebsite/excel，并发布配置文件php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
• 可根据实际字段调整总和计算的列名，以及单元格位置

内容的提问来源于stack exchange，提问作者Anurag CK