Laravel-Excel大文件导出内存耗尽且耗时递增问题求助

在Laravel中使用Maatwebsite\Excel包以chunk+队列方式导出大文件时，出现每个AppendQueryToSheet任务耗时逐次增加，最终在执行CloseSheet时触发内存耗尽错误（报错位置为PhpSpreadsheet的Cells.php第395行），导致导出提前终止。

控制器代码 #

$exporter = new ReportExport($this->validatedData, $this->id);
$exporter->queue($file_name,
    'gallery', \Maatwebsite\Excel\Excel::XLSX)->chain([
        new ExportEnrollmentsJobWhenDone($this->id),
    ]);

ReportExport 导出类代码 #

class ReportExport implements FromQuery, WithHeadings, WithEvents, WithCustomChunkSize, ShouldQueue
{
    use Exportable;

    protected $progress;
    protected $progressIncrement;
    public $totalRows;


    protected $validatedData;

    protected $id;

    public function __construct($validatedData, $id)
    {
        $this->validatedData = $validatedData;
        $this->id = $id;
        $this->progress = 0;
    }


    public function headings(): array
    {
        return [
            // 自定义表头
        ];
    }


    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            // 自定义事件
        ];
    }

    public function query()
    {
        // 自定义查询逻辑
        $this->totalRows = $enrollments->count();
        $this->progressIncrement = 100 / ceil($this->totalRows / $this->chunkSize());
        return $enrollments->orderBy('id', 'desc');
    }

    public function chunkSize(): int
    {
        return 5000;
    }
}

终端日志 #

2023-07-09 13:06:49 Maatwebsite\Excel\Jobs\QueueExport ............. RUNNING
  2023-07-09 13:06:49 Maatwebsite\Excel\Jobs\QueueExport ....... 117.01ms DONE
  2023-07-09 13:06:50 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:06:56 Maatwebsite\Excel\Jobs\AppendQueryToSheet  6,822.89ms DONE
  2023-07-09 13:06:57 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:07:08 Maatwebsite\Excel\Jobs\AppendQueryToSheet  10,715.10ms DONE
  2023-07-09 13:07:09 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:07:23 Maatwebsite\Excel\Jobs\AppendQueryToSheet  14,725.47ms DONE
  2023-07-09 13:07:24 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:07:43 Maatwebsite\Excel\Jobs\AppendQueryToSheet  18,818.70ms DONE
  2023-07-09 13:07:44 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:08:07 Maatwebsite\Excel\Jobs\AppendQueryToSheet  22,557.33ms DONE
  2023-07-09 13:08:08 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:08:34 Maatwebsite\Excel\Jobs\AppendQueryToSheet  26,214.35ms DONE
  2023-07-09 13:08:35 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:09:05 Maatwebsite\Excel\Jobs\AppendQueryToSheet  29,926.11ms DONE
  2023-07-09 13:09:06 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:09:40 Maatwebsite\Excel\Jobs\AppendQueryToSheet  33,737.76ms DONE
  2023-07-09 13:09:41 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:10:18 Maatwebsite\Excel\Jobs\AppendQueryToSheet  37,834.62ms DONE
  2023-07-09 13:10:20 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:11:02 Maatwebsite\Excel\Jobs\AppendQueryToSheet  42,433.48ms DONE
  2023-07-09 13:11:03 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:11:49 Maatwebsite\Excel\Jobs\AppendQueryToSheet  45,406.48ms DONE
  2023-07-09 13:11:49 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:12:43 Maatwebsite\Excel\Jobs\AppendQueryToSheet  53,667.69ms DONE
  2023-07-09 13:12:44 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:13:43 Maatwebsite\Excel\Jobs\AppendQueryToSheet  58,882.30ms DONE
  2023-07-09 13:13:44 Maatwebsite\Excel\Jobs\CloseSheet .............. RUNNING

   Symfony\Component\ErrorHandler\Error\FatalError 

  Allowed memory size of 536870912 bytes exhausted (tried to allocate 83886080 bytes)

  at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php:395
    391▕         }
    392▕         $column = 0;
    393▕         $row = '';
    394▕         sscanf($cellCoordinate, '%[A-Z]%d', $column, $row);
  ➜ 395▕         $this->index[$cellCoordinate] = (--$row * self::MAX_COLUMN_ID) + Coordinate::columnIndexFromString((string) $column);
    396▕ 
    397▕         $this->currentCoordinate = $cellCoordinate;
    398▕         $this->currentCell = $cell;
    399▕         $this->currentCellIsDirty = true;


   Whoops\Exception\ErrorException 

  Allowed memory size of 536870912 bytes exhausted (tried to allocate 83886080 bytes)

  at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php:395
    391▕         }
    392▕         $column = 0;
    393▕         $row = '';
    394▕         sscanf($cellCoordinate, '%[A-Z]%d', $column, $row);
  ➜ 395▕         $this->index[$cellCoordinate] = (--$row * self::MAX_COLUMN_ID) + Coordinate::columnIndexFromString((string) $column);
    396▕ 
    397▕         $this->currentCoordinate = $cellCoordinate;
    398▕         $this->currentCell = $cell;
    399▕         $this->currentCellIsDirty = true;

      +1 vendor frames 
  2   [internal]:0
      Whoops\Run::handleShutdown()

解决方案 #

1. 优化chunk大小与Excel配置 #

修改config/excel.php，降低chunk尺寸并关闭不必要的功能：

return [
    'exports' => [
        'chunk' => [
            'size' => 1000, // 从5000降到1000，减少单任务单元格处理量
        ],
        'pre_calculate_formulas' => false, // 关闭公式预计算，节省内存
    ],
    'temporary_files' => [
        'local_path' => storage_path('framework/cache'), // 指定临时文件存储路径
    ],
];

小chunk能分散内存占用，避免单元格索引数组过快膨胀，缓解耗时递增问题。

2. 禁用PhpSpreadsheet单元格索引缓存 #

在导出类的事件中关闭Cells集合的索引缓存，这是内存暴涨的核心原因：

public function registerEvents(): array
{
    return [
        BeforeSheet::class => function(BeforeSheet $event) {
            $event->sheet->getDelegate()->getCellCollection()->setCacheEnabled(false);
        },
    ];
}

Cells.php第395行的错误源于$this->index数组随单元格数量持续膨胀，禁用缓存后直接操作单元格，大幅降低内存占用。

3. 移除query方法中的冗余count查询 #

当前代码在query()中执行$enrollments->count()会额外触发全表查询，消耗资源，改为单独计算总行数：

public function query()
{
    $enrollments = // 你的查询逻辑
    // 移除$this->totalRows = $enrollments->count();
    return $enrollments->orderBy('id', 'desc');
}

如需进度条，可单独创建队列任务先计算总行数，再启动导出任务。

4. 调高队列进程内存限制 #

临时调整队列进程的内存配额：

php artisan queue:work --memory=1024

或修改php.ini中的memory_limit = 1024M，此为临时缓解方案，需配合其他优化使用。

5. 改用流式导出（百万级数据推荐） #

使用FromIterator+cursor()实现流式导出，逐行加载数据不占内存：

class ReportExport implements FromIterator, WithHeadings, ShouldQueue
{
    use Exportable;

    protected $validatedData;
    protected $id;

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

    public function headings(): array
    {
        return [/* 自定义表头 */];
    }

    public function iterator(): Iterator
    {
        return \App\Models\Enrollment::query()
            ->where(/* 你的查询条件 */)
            ->orderBy('id', 'desc')
            ->cursor(); // 迭代器逐行加载数据
    }
}

内容的提问来源于stack exchange，提问作者Mohammad Nizar Arbash