PHPExcel 導出大量資料記憶體不足解決方案：快取模式與分批處理

PHPExcel / PhpSpreadsheet 大量資料匯出記憶體優化

PHPExcel（已停止維護，建議遷移至 PhpSpreadsheet）將所有儲存格載入記憶體，大量資料易導致 OOM。透過快取模式與分批處理可有效降低記憶體峰值。

核心解法：設定快取儲存模式

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\CachedObjectStorageFactory;
use PhpOffice\PhpSpreadsheet\Settings;

// 方法 1：序列化儲存於記憶體（預設較省記憶體）
Settings::setCacheStorageMethod(CachedObjectStorageFactory::cache_in_memory_serialized);

// 方法 2：Gzip 壓縮後儲存於記憶體（更省記憶體，CPU 較高）
Settings::setCacheStorageMethod(CachedObjectStorageFactory::cache_in_memory_gzip);

// 方法 3：暫存檔（最省記憶體，速度較慢）
Settings::setCacheStorageMethod(CachedObjectStorageFactory::cache_to_discISAM);

// 方法 4：php://temp 串流（小資料記憶體，大資料自動落盤）
$cacheSettings = ['memoryCacheSize' => '8MB'];
Settings::setCacheStorageMethod(CachedObjectStorageFactory::cache_to_phpTemp, $cacheSettings);

// 方法 5：Memcached 分散式快取（叢集環境）
$cacheSettings = [
    'memcacheServer' => '127.0.0.1',
    'memcachePort' => 11211,
    'cacheTime' => 600,
];
Settings::setCacheStorageMethod(CachedObjectStorageFactory::cache_to_memcache, $cacheSettings);

// 之後建立 Spreadsheet 物件即自動使用快取
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// ... 寫入資料 ...

快取模式比較表

輔助方案：分批寫入與多工作表

<?php
// 每 5000 筆建立新工作表，避免單表過大
$batchSize = 5000;
$sheetIndex = 0;

foreach (array_chunk($data, $batchSize) as $chunk) {
    if ($sheetIndex === 0) {
        $sheet = $spreadsheet->getActiveSheet();
    } else {
        $sheet = $spreadsheet->createSheet($sheetIndex);
        $sheet->setTitle("Sheet{$sheetIndex}");
    }
    // 寫入標題列
    $sheet->fromArray($headers, null, 'A1');
    // 寫入資料
    $sheet->fromArray($chunk, null, 'A2');
    $sheetIndex++;
}

終極方案：AJAX 分批匯出（前端輪詢）

<!-- 前端 -->
<button id="exportBtn">匯出 Excel</button>
<div id="progress"></div>

<script>
document.getElementById('exportBtn').onclick = async () => {
    const resp = await fetch('export_start.php', {method: 'POST'});
    const {taskId} = await resp.json();
    
    const poll = setInterval(async () => {
        const r = await fetch(`export_status.php?taskId=${taskId}`);
        const {status, url, progress} = await r.json();
        document.getElementById('progress').textContent = `進度: ${progress}%`;
        if (status === 'done') {
            clearInterval(poll);
            window.location.href = url;
        }
    }, 1000);
};
</script>

<?php
// export_start.php：建立任務、回傳 taskId，背景啟動匯出
// export_status.php：查詢進度、完成後提供下載連結
// 背景匯出可用 cli 腳本 + proc_open、或佇列系統
?>

遷移建議：PHPExcel → PhpSpreadsheet

• PHPExcel 最後版本 2017 年，已知安全性問題，PHP 8 不相容。
• PhpSpreadsheet 為官方繼任者，API 幾乎相同，命名空間改為 PhpOffice\PhpSpreadsheet。
• Composer：composer require phpoffice/phpspreadsheet

綜合使用「快取模式 + 分批工作表 + 背景非同步匯出」三重策略，可穩定支撐百萬行級匯出需求。