laravel+xlswriter实现百万数据导出

前提

原来使用laravel-excel异步队列导出1万多条数据，经常爆内存。参考问答：如何解决laravel-excel异步队列导出内存溢出?

使用xlswriter进行改造

<?php

namespace App\Exports;

use Throwable;
use Carbon\Carbon;
use Vtiful\Kernel\Excel;
use App\Models\ExportTask;
use App\Models\SalesPerformance;
use Illuminate\Bus\Queueable;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Facades\Storage;
use Illuminate\Contracts\Queue\ShouldQueue;

class SalesPerformanceExport implements ShouldQueue
{
    use Queueable;

    protected ExportTask $task;
    protected array $request;

    /**
     * 存储文件系统配置
     * @var string
     */
    protected string $disk = 'public';

    /**
     * 分批处理数据的大小
     * @var int
     */
    protected int $chunkSize = 1000;

    /**
     * 设置任务超时时间
     * @var int
     */
    public int $timeout = 3600;

    public function __construct(array $request, ExportTask $task)
    {
        $this->task    = $task;
        $this->request = $request;
    }

    public function handle(): void
    {
        try {
            // 更新任务状态为处理中
            $this->task->update([
                'status'     => 'processing',
                'started_at' => now(),
            ]);

            // 初始化 xlswriter
            $excel = new Excel([
                'path' => Storage::disk($this->disk)->path(dirname($this->task->file_path))
            ]);

            // 设置导出文件名
            $excelFile = $excel->constMemory(basename($this->task->file_path), 'Sheet1', false);

            // 设置表头
            $headers = [
                '员工',
                '职位',
                '工作日期',
                '业务类型',
                '接诊类型',
                '顾客姓名',
                '项目/产品名称',
                '应收金额',
                '实收金额',
                '欠款金额',
                '余额支付',
                '服务占比',
                '计提金额',
                '备注'
            ];
            $excelFile->header($headers);

            // 设置列宽
            $excelFile->setColumn('A:A', 13);
            $excelFile->setColumn('B:B', 13);
            $excelFile->setColumn('C:C', 15);
            $excelFile->setColumn('D:D', 15);
            $excelFile->setColumn('E:E', 10);
            $excelFile->setColumn('F:F', 20);
            $excelFile->setColumn('G:G', 40);
            $excelFile->setColumn('N:N', 60);

            // 写入数据
            $query      = $this->getQuery();
            $position   = config('setting.sales_performance.position');
            $table_name = config('setting.sales_performance.table_name');

            // 分批处理数据并直接写入
            $query->chunk($this->chunkSize, function ($records) use ($excelFile, $position, $table_name) {
                $batchData = [];
                foreach ($records as $row) {
                    $batchData[] = [
                        $row->user_name,
                        $position[$row->position] ?? '',
                        Carbon::parse($row->created_at)->toDateString(),
                        $table_name[$row->table_name] ?? '',
                        get_reception_type_name($row->reception_type),
                        $row->customer_name,
                        $row->product_name . $row->goods_name,
                        $row->payable,
                        $row->income,
                        $row->arrearage,
                        $row->deposit,
                        $row->rate,
                        $row->amount,
                        $row->remark,
                    ];
                }
                // 每一批数据直接写入文件
                if (!empty($batchData)) {
                    $excelFile->data($batchData);
                }
            });

            // 关闭文件
            $excelFile->output();

            // 更新任务状态为完成
            $this->task->update([
                'status'       => 'completed',
                'completed_at' => now(),
            ]);

        } catch (Throwable $exception) {
            $this->task->update([
                'status'        => 'failed',
                'failed_at'     => now(),
                'error_message' => $exception->getMessage(),
            ]);
        }
    }

    protected function getQuery()
    {
        return SalesPerformance::query()
            ->select([
                'users.name as user_name',
                'sales_performance.position',
                'sales_performance.created_at',
                'sales_performance.table_name',
                'sales_performance.reception_type',
                'customer.name as customer_name',
                'sales_performance.product_name',
                'sales_performance.goods_name',
                'sales_performance.payable',
                'sales_performance.income',
                'sales_performance.arrearage',
                'sales_performance.deposit',
                'sales_performance.rate',
                'sales_performance.amount',
                'sales_performance.remark',
            ])
            ->leftJoin('users', 'users.id', '=', 'sales_performance.user_id')
            ->leftJoin('customer', 'customer.id', '=', 'sales_performance.customer_id')
            ->when($keyword = $this->request['keyword'] ?? null, fn(Builder $query) => $query->where('customer.keyword', 'like', "%{$keyword}%"))
            ->whereBetween('sales_performance.created_at', $this->request['created_at'])
            ->queryConditions('ReportPerformanceSales', $this->request['filters'] ?? [])
            ->orderBy('sales_performance.created_at', 'desc');
    }

    /**
     * 任务失败时调用
     * @param Throwable $exception
     * @return void
     */
    public function failed(Throwable $exception): void
    {
        $this->task->update([
            'status'        => 'failed',
            'failed_at'     => now(),
            'error_message' => '导出任务执行失败: ' . $exception->getMessage(),
        ]);
    }
}