使用PhpSpreadsheet读写Excel单元格出现性能问题如何优化

1 核心性能瓶颈：数据库N+1查询 #

你代码中最大的性能损耗来自循环内单次查询数据库：100条ID就会发起100次独立SQL请求，网络IO+数据库查询的开销累积后会直接导致速度骤降。
优化方案：

• 先批量收集所有ID，去重、过滤空值后一次性查询所有符合条件的记录
• 将查询结果转为「ID为键、车辆信息为值」的映射数组，后续循环直接从数组取值，完全避免重复查库
• 提前给数据库ID字段加唯一索引，进一步提升批量查询效率

2 PhpSpreadsheet读写优化 #

原有逐单元格写入、全量加载文件的方式也会拖慢速度，可做以下调整：

• 读取源文件时开启只读模式，忽略不必要的格式解析，降低内存占用和加载耗时
• 写入结果时用fromArray批量写入，代替逐单元格循环调用setCellValue
• 无公式计算需求时关闭公式预计算，减少导出耗时

3 优化后代码示例 #

public static function export($date, $file)
{
    // 只读模式加载源文件，大幅降低读取开销
    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($file);
    $reader->setReadDataOnly(true);
    $loadFile = $reader->load($file);
    $worksheet = $loadFile->getSheet(0);

    // 读取A列所有ID
    $highestRow = $worksheet->getHighestDataRow();
    $firstRow = $worksheet->rangeToArray('A1:A'.$highestRow);

    // 新建导出文件，写入ID列
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->fromArray($firstRow);

    // 批量收集所有ID、去重过滤
    $ids = [];
    foreach ($firstRow as $value) {
        $id = current($value);
        if (!empty($id)) $ids[] = $id;
    }
    $ids = array_unique($ids);

    // 新增CarService批量查询方法，一次性查所有ID对应数据，返回格式：['id1'=>'车辆信息1','id2'=>'车辆信息2']
    // 底层SQL为 SELECT id,xxx FROM car WHERE id IN (ids)
    $carMap = CarService::getCarsByIds($ids);

    // 直接从映射数组取值组装结果，无额外数据库查询
    $result = [];
    foreach ($firstRow as $value) {
        $id = current($value);
        $result[] = $carMap[$id] ?? '无效ID';
    }

    // 批量写入C列，无需循环逐单元格赋值
    $writeData = array_merge([['Car']], array_map(fn($item) => [$item], $result));
    $sheet->fromArray($writeData, null, 'C1');

    // 导出文件，关闭公式预计算
    $writer = new Xlsx($spreadsheet);
    $writer->setPreCalculateFormulas(false);
    $filename = "filename.xlsx";
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    return $writer->save("php://output");
}

4 极端大数据量进阶优化 #

如果后续ID量达到几千甚至上万条，可额外做以下调整：

• 替换读写库为更轻量的Spout，内存占用仅为PhpSpreadsheet的几十分之一，读写速度快3~5倍
• 数据库批量查询时分批处理，避免whereIn参数过多导致SQL性能下降

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