PHP 匯出 Excel/CSV 完整指南：編碼處理、多種方案與記憶體優化

PHP 匯出試算表是常見需求，本文涵蓋 CSV/Excel 多種方案、UTF-8/BIG5 編碼轉換、大數據量記憶體優化與實務封裝類別。

方案一：CSV 純文字輸出 (最輕量、相容性最好)

基本輸出

<?php
header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="export_' . date('YmdHis') . '.csv"');
header('Pragma: no-cache');
header('Expires: 0');

$output = fopen('php://output', 'w');

// 寫入 BOM (Excel 開啟 UTF-8 CSV 必備)
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// 標題列
fputcsv($output, ['ID', '姓名', '郵箱', '註冊時間']);

// 資料列
$data = [
    [1, '張三', 'zhang@example.com', '2024-01-15 10:30:00'],
    [2, '李四', 'li@example.com', '2024-01-16 14:22:00'],
];
foreach ($data as $row) {
    fputcsv($output, $row);
}
fclose($output);

封裝類別：CSV_Writer

<?php
class CSV_Writer {
    private $data = [];
    private $delimiter = ',';
    private $enclosure = '"';
    private $escapeChar = '\\';
    
    public function __construct(array $data = [], $delimiter = ',') {
        $this->data = $data;
        $this->delimiter = $delimiter;
    }
    
    public function addRow(array $row) {
        $this->data[] = $row;
    }
    
    public function setData(array $data) {
        $this->data = $data;
    }
    
    public function output($filename = 'export.csv') {
        header('Content-Type: text/csv; charset=UTF-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Pragma: no-cache');
        
        $out = fopen('php://output', 'w');
        fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF)); // UTF-8 BOM
        
        foreach ($this->data as $row) {
            fputcsv($out, $row, $this->delimiter, $this->enclosure, $this->escapeChar);
        }
        fclose($out);
    }
    
    public function getString(): string {
        $out = fopen('php://temp', 'r+');
        fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF));
        foreach ($this->data as $row) {
            fputcsv($out, $row, $this->delimiter, $this->enclosure, $this->escapeChar);
        }
        rewind($out);
        $str = stream_get_contents($out);
        fclose($out);
        return $str;
    }
}

// 使用
$writer = new CSV_Writer([
    ['ID', '姓名', '郵箱'],
    [1, '張三', 'zhang@example.com'],
]);
$writer->addRow([2, '李四', 'li@example.com']);
$writer->output('users_' . date('Ymd') . '.csv');

方案二：HTML 表格偽裝 Excel (簡單、支援樣式)

<?php
header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
header('Content-Disposition: attachment; filename="report_' . date('Ymd') . '.xls"');
header('Pragma: no-cache');
header('Expires: 0');

echo '<table border="1">';
echo '<tr><th>ID</th><th>姓名</th><th>郵箱</th></tr>';
foreach ($data as $row) {
    echo '<tr>';
    foreach ($row as $cell) {
        echo '<td>' . htmlspecialchars($cell) . '</td>';
    }
    echo '</tr>';
}
echo '</table>';

⚠️ 實為 HTML，Excel 會警告「格式不符」，但可正常開啟。不支援公式、多工作表。

方案三：PhpSpreadsheet (專業、功能完整、現代標準)

繼承自 PHPExcel (已停止維護)，支援 .xlsx/.xls/.ods/.csv、樣式、公式、圖表、多工作表。

<?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;

$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]],
];

$dataStyle = [
    'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
];

// 寫入標題
$headers = ['ID', '姓名', '郵箱', '註冊時間'];
$sheet->fromArray($headers, null, 'A1');
$sheet->getStyle('A1:D1')->applyFromArray($headerStyle);

// 寫入資料
$data = [
    [1, '張三', 'zhang@example.com', '2024-01-15 10:30:00'],
    [2, '李四', 'li@example.com', '2024-01-16 14:22:00'],
];
$sheet->fromArray($data, null, 'A2');
$sheet->getStyle('A2:D' . (count($data) + 1))->applyFromArray($dataStyle);

// 自動欄寬
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"');
header('Cache-Control: max-age=0');
$writer->save('php://output');

方案四：大數據量記憶體優化 (Chunk/Stream 輸出)

<?php
function exportLargeCsv($query, $filename, $batchSize = 1000) {
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    
    $out = fopen('php://output', 'w');
    fprintf($out, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // 寫入標題
    fputcsv($out, ['ID', '姓名', '郵箱', '建立時間']);
    
    $offset = 0;
    $total = 0;
    
    while (true) {
        // 使用 PDO 分頁查詢 (避免一次性載入所有資料)
        $stmt = $pdo->prepare("SELECT id, name, email, created_at FROM users LIMIT ?, ?");
        $stmt->bindValue(1, $offset, PDO::PARAM_INT);
        $stmt->bindValue(2, $batchSize, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_NUM);
        
        if (empty($rows)) break;
        
        foreach ($rows as $row) {
            fputcsv($out, $row);
        }
        
        $total += count($rows);
        $offset += $batchSize;
        
        // 釋放記憶體
        unset($rows);
        $stmt = null;
        
        // 可選：定期 flush 避免緩衝區過大
        if ($total % 5000 === 0) {
            flush();
        }
    }
    
    fclose($out);
    return $total;
}

編碼處理核心原則

常見編碼轉換場景

<?php
// 資料庫為 GBK，匯出 UTF-8 CSV
$value = iconv('GBK', 'UTF-8//IGNORE', $gbkValue);

// 匯出 BIG5 (繁體中文環境舊系統)
$value = iconv('UTF-8', 'BIG5//TRANSLIT', $utf8Value);

// 移除非法字元
$clean = preg_replace('/[^\x09\x0A\x0D\x20-\x7E\xA0-\xFF]/u', '', $str);

方案選擇決策表