使用PhpSpreadsheet写入万级XLSX数据过慢及内存问题求助

问题场景 #

需要生成包含1万-100万条交易记录的XLSX报表，每条记录含50列。使用PhpSpreadsheet时遇到以下问题：

• 循环逐条读写文件时速度极慢：写入1万条数据耗时近24小时仅完成2300条
• 内存溢出：未启用Redis缓存时写入不到400条就触发内存错误；尝试一次性写入全量数据也会中途内存报错
• 客户仅接受XLSX格式，拒绝CSV

原代码的致命问题 #

当前代码的核心错误是每次循环都重新读取整个XLSX文件、修改后再完整保存，这会导致：

1. 重复IO爆炸：每写一行都要读整个文件+写整个文件，随着文件体积增大，IO耗时呈指数级上升
2. 内存反复过载：每次循环都加载整个表格到内存，加上重复创建Reader/Spreadsheet/Writer对象，内存占用持续飙升，最终触发溢出

优化方案 #

针对1万条数据的快速修复版 #

不需要每次循环读写文件，只初始化一次Spreadsheet，批量构建数据后一次性保存，配合缓存减少内存占用：

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// 启用Redis缓存降低内存占用
$client = new \Redis();
$client->connect('192.168.7.147', 6379);
$pool = new \Cache\Adapter\Redis\RedisCachePool($client);
$simpleCache = new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool);
\PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);

$process_time = microtime(true);

// 只初始化一次Spreadsheet和工作表
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 批量构建所有数据行
for ($r = 1; $r <= 10000; $r++) {
    $rowArray = [];
    for ($c = 1; $c <= 50; $c++) {
        $rowArray[] = $r . ".Content " . $c;
    }
    // 直接写入对应行，无需每次保存
    $sheet->fromArray($rowArray, NULL, 'A' . $r);
}

// 仅执行一次保存操作
$writer = new Xlsx($spreadsheet);
// 关闭公式预计算，提升写入速度
$writer->setPreCalculateFormulas(false);
$writer->save("test.xlsx");

// 手动清理资源释放内存
$spreadsheet->disconnectWorksheets();
unset($spreadsheet, $writer);

$process_time = microtime(true) - $process_time;
echo "执行耗时：" . $process_time . "秒\n";

针对100万条超大数据量的进阶优化 #

如果要处理100万条数据，普通模式仍会内存溢出，需要用**分批写入（Chunk Writing）**模式，把数据拆分成批次写入，避免全量加载到内存：

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// 启用Redis缓存
$client = new \Redis();
$client->connect('192.168.7.147', 6379);
$pool = new \Cache\Adapter\Redis\RedisCachePool($client);
$simpleCache = new \Cache\Bridge\SimpleCache\SimpleCacheBridge($pool);
\PhpOffice\PhpSpreadsheet\Settings::setCache($simpleCache);

$process_time = microtime(true);

// 创建空工作表
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// 定义每批次写入行数（根据服务器内存调整，比如1000行一批）
$batchSize = 1000;
$totalRows = 1000000;

$writer = new Xlsx($spreadsheet);
// 开启磁盘缓存，将临时数据写入磁盘而非内存
$writer->setUseDiskCaching(true, sys_get_temp_dir());
// 关闭公式预计算
$writer->setPreCalculateFormulas(false);

// 分批写入数据
for ($batchStart = 1; $batchStart <= $totalRows; $batchStart += $batchSize) {
    $batchEnd = min($batchStart + $batchSize - 1, $totalRows);
    
    // 构建当前批次的所有行
    for ($r = $batchStart; $r <= $batchEnd; $r++) {
        $rowArray = [];
        for ($c = 1; $c <= 50; $c++) {
            $rowArray[] = $r . ".Content " . $c;
        }
        $sheet->fromArray($rowArray, NULL, 'A' . ($r - $batchStart + 1));
    }
    
    // 将当前批次写入临时文件
    $writer->saveBatch();
    // 清空工作表数据，释放内存准备下一批
    $sheet->removeRow(1, $batchSize);
}

// 合并所有批次并生成最终文件
$writer->save("large_test.xlsx");

// 清理资源
$spreadsheet->disconnectWorksheets();
unset($spreadsheet, $writer);

$process_time = microtime(true) - $process_time;
echo "执行耗时：" . $process_time . "秒\n";

关键优化点说明 #

• 彻底消除重复IO：只初始化一次核心对象，一次性/分批写入后再保存，避免循环读写文件的巨大开销
• 磁盘缓存降级：通过setUseDiskCaching把临时数据写入磁盘，大幅降低PHP进程内存占用
• 关闭无用计算：setPreCalculateFormulas(false)跳过不必要的公式预计算，提升写入速度
• 分批拆分大数据：对于超大量数据，拆分批次处理，每批次后清空工作表，避免内存累积溢出
• Redis缓存辅助：利用PhpSpreadsheet的缓存机制，将单元格数据缓存到Redis，进一步减少内存压力

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