專案需要將資料庫查詢結果匯出為 Excel 檔案供下載。早期常用 header() 輸出 HTML 表格偽裝 .xls,但此法相容性差、無法支援 .xlsx、格式控制極弱。本文對比三種主流方案,推薦以 PhpSpreadsheet 為標準實作,並提供大資料量記憶體優化技巧。
方案對比總覽
| 方案 | 優點 | 缺點 | 適用場景 |
| HTML Table + Header 偽裝 .xls | 零依賴、代碼極少 | 非真實 Excel、格式失真、大檔案記憶體爆炸、.xlsx 不支援 | 極簡內部工具、資料量 < 1000 列 |
| PEAR Spreadsheet_Excel_Writer | 產生真實 .xls | 已停止維護、僅支援 .xls (BIFF)、記憶體佔用大、無 .xlsx 支援 | 維護舊專案 |
| PhpSpreadsheet (推薦) | 支援 .xlsx/.xls/.csv/.ods、豐富格式/圖表/公式、串流寫入省記憶體、活躍維護 | 學習曲線稍陡、大檔案仍需串流模式 | 所有現代專案 |
方案一:快速原型 - HTML 偽裝法 (僅供參考)
<?php
// 設定下載標頭
header('Content-Type: application/vnd.ms-excel; charset=utf-8');
header('Content-Disposition: attachment; filename="export_' . date('Ymd_His') . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$stmt = $pdo->query('SELECT title, keywords, url, description FROM cms_content');
// 輸出 HTML 表格 (Excel 可開啟)
echo "";
echo "| Title | Keywords | Description | URL |
";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "";
foreach ($row as $cell) {
echo "| " . htmlspecialchars($cell) . " | ";
}
echo "
";
}
echo "
";
⚠️ 缺陷:Excel 開啟會彈出「檔案格式與副檔名不符」警告、無法儲存為 .xlsx、中文編碼易亂、無法設定欄寬/樣式/凍結窗格。
方案二:專業標準 - PhpSpreadsheet 完整實作
安裝
composer require phpoffice/phpspreadsheet
基礎匯出範例 (記憶體模式,適合 < 5 萬列)
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('文章資料');
// 表頭樣式
$headerStyle = [
'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 12],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
];
$headers = ['標題', '關鍵字', '描述', 'URL'];
$sheet->fromArray($headers, null, 'A1');
$sheet->getStyle('A1:D1')->applyFromArray($headerStyle);
// 資料寫入
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass');
$stmt = $pdo->query('SELECT title, keywords, description, url FROM cms_content');
$rowNum = 2;
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$sheet->fromArray($row, null, "A$rowNum");
$rowNum++;
}
// 自動調整欄寬
foreach (range('A', 'D') as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
// 凍結首行
$sheet->freezePane('A2');
// 輸出下載
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="export_' . date('Ymd_His') . '.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;
大資料量優化:串流寫入模式 (支援百萬列,記憶體恆定)
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 設定串流寫入器
$writer = new XlsxWriter($spreadsheet);
$writer->setPreCalculateFormulas(false); // 禁用公式預計算
$writer->setUseDiskCaching(true); // 使用磁碟暫存 (可選)
// 開啟輸出流
$callback = function() use ($writer) {
$writer->save('php://output');
};
// 發送標頭 (必須在任何輸出前)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="huge_export_' . date('Ymd_His') . '.xlsx"');
// 邊查詢邊寫入 (使用 PDO 游標或分批 LIMIT)
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL // 游標模式
]);
$stmt = $pdo->prepare('SELECT title, keywords, description, url FROM cms_content');
$stmt->execute();
// 寫入表頭
$sheet->fromArray(['標題', '關鍵字', '描述', 'URL'], null, 'A1');
$sheet->getStyle('A1:D1')->applyFromArray($headerStyle);
// 批次寫入資料
$batchSize = 1000;
$rowNum = 2;
while (true) {
$rows = [];
for ($i = 0; $i < $batchSize; $i++) {
$row = $stmt->fetch(PDO::FETCH_NUM);
if (!$row) break 2;
$rows[] = $row;
}
if (empty($rows)) break;
// 批次寫入 (fromArray 支援二維陣列)
$sheet->fromArray($rows, null, "A$rowNum");
$rowNum += count($rows);
// 定期釋放記憶體
if ($rowNum % 10000 === 0) {
gc_collect_cycles();
}
}
// 最後儲存 (觸發實際輸出)
$writer->save('php://output');
進階功能速查
| 需求 | 代碼片段 |
| 設定儲存格格式 (日期/貨幣/百分比) | $sheet->getStyle('C2:C100')->getNumberFormat()->setFormatCode('YYYY-MM-DD'); |
| 合併儲存格 | $sheet->mergeCells('A1:D1'); |
| 插入圖片 | $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing(); $drawing->setPath('/path/logo.png')->setCoordinates('A1')->setWorksheet($sheet); |
| 資料驗證 (下拉選單) | $validation = $sheet->getCell('E2')->getDataValidation(); $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)->setFormula1('"選項1,選項2,選項3"')->setAllowBlank(true); |
| 保護工作表/鎖定儲存格 | $sheet->getProtection()->setSheet(true)->setPassword('secret'); $sheet->getStyle('A1')->getProtection()->setLocked(false); |
| 匯出 CSV (大檔案最快) | $writer = new \PhpOffice\PhpSpreadsheet\Writer\Csv($spreadsheet); $writer->setDelimiter(','); $writer->setEnclosure('"'); |
常見問題排查
- 中文檔名下載亂碼:
header('Content-Disposition: attachment; filename*=UTF-8\'\'' . rawurlencode($filename) . '.xlsx'); (RFC 5987 標準)
- 記憶體耗盡:啟用串流模式、增加
memory_limit、使用 ChunkReadFilter 分塊讀取現有檔案
- 公式不計算:Excel 開啟需手動重新計算,或使用
$writer->setPreCalculateFormulas(true) (耗記憶體)
- 日期顯示為數字:必須設定儲存格格式碼
setFormatCode('YYYY-MM-DD')
結語
放棄 HTML 偽裝法,PhpSpreadsheet 是 PHP 匯出 Excel 的唯一專業選擇。掌握串流寫入模式,即可輕鬆應對百萬級資料匯出。建議封裝為專案通用的 ExcelExporter 服務類,統一處理標頭、樣式、下載回應、錯誤處理,讓業務代碼專注於資料準備。