早期常見用 header('Content-type:application/vnd.ms-excel') 輸出 HTML 表格偽裝成 .xls,雖然簡單但問題眾多:無法支援公式、樣式易碎、大數據記憶體爆炸、Excel 2007+ 會彈出格式不符警告。現代專案應根據場景選擇正規庫。
| 方案 | 適用場景 | 優點 | 缺點 | 記憶體 |
|---|---|---|---|---|
| fputcsv 輸出 CSV | 純資料匯出、大數據、無樣式需求 | 極速、記憶體恆定、原生函數 | 無樣式/公式/多工作表、編碼易亂碼 | O(1) |
| Box/Spout | 中大型資料、需基本樣式、.xlsx/.ods | 流式寫入、記憶體低、無依賴 | 功能較簡單、不支援複雜圖表 | O(1) |
| PhpSpreadsheet | 完整 Excel 功能、樣式/公式/圖表/多表 | 功能最全、社群活躍、格式相容佳 | 記憶體高、大檔案易 OOM | O(N) |
<?php
function exportCsvStream(array $headers, iterable $data, string $filename = 'export.csv'): void
{
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
header('Expires: 0');
$out = fopen('php://output', 'w');
// 寫入 BOM 防止 Excel 開啟 UTF-8 亂碼
fwrite($out, "\xEF\xBB\xBF");
fputcsv($out, $headers);
foreach ($data as $row) {
fputcsv($out, $row);
}
fclose($out);
}
// 使用範例:搭配 Generator 逐行查詢資料庫
function getUserGenerator(PDO $pdo): Generator
{
$stmt = $pdo->query('SELECT id, name, email, created_at FROM users');
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
yield $row;
}
}
exportCsvStream(['ID', '姓名', 'Email', '註冊時間'], getUserGenerator($pdo), 'users.csv');
?>
<?php
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Writer\XLSX\Writer;
require 'vendor/autoload.php';
$writer = Writer::create(WriterEntityFactory::XLSX);
$writer->openToBrowser('users.xlsx');
$writer->addRow(WriterEntityFactory::createRow(['ID', '姓名', 'Email', '註冊時間']));
$stmt = $pdo->query('SELECT id, name, email, created_at FROM users');
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$writer->addRow(WriterEntityFactory::createRow($row));
}
$writer->close();
?>
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'ID')->setCellValue('B1', '姓名')
->setCellValue('C1', 'Email')->setCellValue('D1', '註冊時間');
// 樣式範例
$sheet->getStyle('A1:D1')->getFont()->setBold(true);
$sheet->getColumnDimension('C')->setWidth(30);
$row = 2;
$stmt = $pdo->query('SELECT id, name, email, created_at FROM users LIMIT 1000');
while ($data = $stmt->fetch(PDO::FETCH_NUM)) {
$sheet->fromArray($data, null, "A{$row}");
$row++;
}
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
?>
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = false + fetch() 逐行ini_set('memory_limit', '512M') 視情況調整$spreadsheet->setPreCalculateFormulas(false) 關閉公式預計算fwrite($out, "\xEF\xBB\xBF")charset=utf8mb4fputcsv 會自動加雙引號包裹並轉義