萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 匯出 Excel 完整指南:從原生 Header 到 PhpSpreadsheet 專業級方案

專案需要將資料庫查詢結果匯出為 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 "";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "";
    foreach ($row as $cell) {
        echo "";
    }
    echo "";
}
echo "
TitleKeywordsDescriptionURL
" . htmlspecialchars($cell) . "
";
⚠️ 缺陷: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 服務類,統一處理標頭、樣式、下載回應、錯誤處理,讓業務代碼專注於資料準備。

copyright © 萬盛學電腦網 all rights reserved