PHP 產出 Excel/CSV 時,中文亂碼主因是 **Excel 預期特定編碼**(舊版偏好 GBK/Big5,新版支援 UTF-8 BOM),而 PHP 內部通常使用 UTF-8。解決關鍵在於:**正確設定 Header + 編碼轉換 + BOM 標記**。
<?php
// 關鍵 Header 設定
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: attachment; filename=\"export_".date('YmdHis').".xls\"");
header("Pragma: public");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Expires: 0");
// 輸出 UTF-8 BOM,讓 Excel 正確識別編碼
echo "\xEF\xBB\xBF";
$filename = "報表_" . date('Ymd');
$filename = iconv('utf-8', 'gb2312', $filename); // 舊版 Excel 相容
echo $filename;
?>
<table border="1">
<tr><th>姓名</th><th>年齡</th><th>城市</th></tr>
<tr><td>張三</td><td>25</td><td>北京</td></tr>
<tr><td>李四</td><td>30</td><td>上海</td></tr>
</table>
<?php
function export_csv(array $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');
// UTF-8 BOM 關鍵:解決 Excel 開啟 CSV 亂碼
echo "\xEF\xBB\xBF";
$output = fopen('php://output', 'w');
foreach ($data as $row) {
fputcsv($output, $row);
}
fclose($output);
}
// 使用範例
$data = [
['姓名', '年齡', '城市', '備註'],
['張三', 25, '北京', '工程師'],
['李四', 30, '上海', '設計師,含逗號'], // fputcsv 自動處理逗號轉義
['王五', 28, '廣州', "換行\n測試"], // 自動處理換行
];
export_csv($data, 'users_' . date('Ymd') . '.csv');
?>
<?php
class CsvExporter {
private $handle;
private $filename;
private $bomWritten = false;
public function __construct(string $filename = 'export.csv') {
$this->filename = $filename;
}
public function addRow(array $row): self {
if (!$this->handle) {
$this->initOutput();
}
fputcsv($this->handle, $row);
return $this;
}
public function addRows(iterable $rows): self {
foreach ($rows as $row) {
$this->addRow($row);
}
return $this;
}
private function initOutput(): void {
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $this->filename . '"');
header('X-Accel-Buffering: no'); // 關閉 Nginx 緩衝,支援流式輸出
$this->handle = fopen('php://output', 'w');
// 寫入 UTF-8 BOM
fwrite($this->handle, "\xEF\xBB\xBF");
$this->bomWritten = true;
}
public function __destruct() {
if ($this->handle) {
fclose($this->handle);
}
}
}
// 使用:記憶體友善,適合大量資料
$exporter = new CsvExporter('big_data_' . date('YmdHis') . '.csv');
$exporter->addRow(['ID', 'Name', 'Email']);
// 模擬大量資料分批輸出
foreach (range(1, 100000) as $i) {
$exporter->addRow([$i, "User{$i}", "user{$i}@example.com"]);
// 每 1000 筆可選擇 flush()
if ($i % 1000 === 0) flush();
}
?>
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Fill;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('用戶資料');
// 標題列樣式
$sheet->fromArray(['ID', '姓名', '郵箱', '註冊時間'], null, 'A1');
$sheet->getStyle('A1:D1')->applyFromArray([
'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 12],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
]);
// 資料
$data = [
[1, '張三', 'zhang@example.com', '2024-01-15'],
[2, '李四', 'li@example.com', '2024-02-20'],
[3, '王五', 'wang@example.com', '2024-03-10'],
];
$sheet->fromArray($data, null, 'A2');
// 自動欄寬
foreach (range('A', 'D') as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
// 輸出
$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="users_' . date('YmdHis') . '.xlsx"');
$writer->save('php://output');
?>
| 方案 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| HTML Table | 零依賴、支援合併儲存格、樣式豐富 | 非真標準、大檔案記憶體壓力大 | 簡單報表、內部工具、快速原型 |
| CSV (fputcsv) | 標準格式、記憶體效率高、通用性最強 | 無樣式、無多工作表、無公式 | 資料匯出、系統整合、大量資料 |
| PhpSpreadsheet | 完整 Excel 功能、樣式/圖表/公式/多工作表 | 記憶體佔用大、效能較慢、需 Composer | 專業報表、客戶交付、複雜格式需求 |
echo "\xEF\xBB\xBF"; 或 fwrite($fp, "\xEF\xBB\xBF");iconv('utf-8', 'gb2312', $filename)fputcsv() 自動處理逗號、引號、換行轉義