PHP 使用 Spreadsheet_Excel_Writer 產生 Excel 檔案並輸出至瀏覽器

PHP 匯出 Excel 完整實作指南

本文詳細介紹如何使用 PEAR Spreadsheet_Excel_Writer 套件在 PHP 中建立 Excel 檔案 (.xls 格式)，並提供下載給瀏覽器或儲存至伺服器的完整範例。適用於報表匯出、資料備份等常見業務場景。

環境安裝與準備

套件相依 OLE 套件，需先安裝：

pear install OLE-0.5
pear install Spreadsheet_Excel_Writer-0.9.1

或透過 Composer (現代專案建議)：

composer require phpoffice/phpspreadsheet

注意：Spreadsheet_Excel_Writer 僅支援舊版 .xls (BIFF 格式)，新專案強烈建議使用 PhpSpreadsheet 支援 .xlsx、.ods 等現代格式。

範例一：建立檔案並儲存至伺服器

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// 建立活頁簿
$workbook = new Spreadsheet_Excel_Writer('myfile.xls');

// 新增工作表
$worksheet =& $workbook->addWorksheet('students');

// 寫入資料
$worksheet->write(0, 0, 'name');
$worksheet->write(0, 1, 'grade');
$worksheet->write(1, 0, 'ivancho');
$worksheet->write(1, 1, 7);
$worksheet->write(2, 0, 'mariika');
$worksheet->write(2, 1, 7);
$worksheet->write(3, 0, 'stoyancho');
$worksheet->write(3, 1, 8);

// 儲存檔案
$workbook->close();
?>

範例二：直接輸出至瀏覽器供下載

<?php
require_once 'Spreadsheet/Excel/Writer.php';

// 建立活頁簿 (不指定檔名則輸出至瀏覽器)
$workbook = new Spreadsheet_Excel_Writer();

// 發送 HTTP 標頭觸發下載
$workbook->send('students.xls');

// 新增工作表
$worksheet =& $workbook->addWorksheet('students');

// 寫入資料 (同上)
$worksheet->write(0, 0, 'name');
$worksheet->write(0, 1, 'grade');
$worksheet->write(1, 0, 'ivancho');
$worksheet->write(1, 1, 7);
$worksheet->write(2, 0, 'mariika');
$worksheet->write(2, 1, 7);
$worksheet->write(3, 0, 'stoyancho');
$worksheet->write(3, 1, 8);

// 關閉並輸出
$workbook->close();
?>

進階功能與常用方法

完整報表匯出實作範例

<?php
require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$workbook->send('sales_report_' . date('Ymd') . '.xls');

$worksheet =& $workbook->addWorksheet('銷售報表');

// 標題列樣式
$title_format =& $workbook->addFormat();
$title_format->setBold();
$title_format->setSize(12);
$title_format->setAlign('center');
$title_format->setBgColor('gray');
$title_format->setColor('white');

// 資料列樣式
$data_format =& $workbook->addFormat();
$data_format->setAlign('center');
$data_format->setNumFormat('#,##0.00');

// 寫入標題
$headers = ['日期', '商品', '數量', '單價', '小計'];
foreach ($headers as $col => $header) {
  $worksheet->write(0, $col, $header, $title_format);
}

// 模擬資料來源
$data = [
  ['2024-01-15', '筆記型電腦', 2, 25000],
  ['2024-01-16', '滑鼠', 10, 500],
  ['2024-01-17', '鍵盤', 5, 1200],
];

// 寫入資料並計算小計
foreach ($data as $row => $record) {
  $worksheet->write($row + 1, 0, $record[0], $data_format);
  $worksheet->write($row + 1, 1, $record[1], $data_format);
  $worksheet->write($row + 1, 2, $record[2], $data_format);
  $worksheet->write($row + 1, 3, $record[3], $data_format);
  $worksheet->writeFormula($row + 1, 4, "=C" . ($row+2) . "*D" . ($row+2), $data_format);
}

// 合併儲存格加總計
$total_row = count($data) + 1;
$worksheet->write($total_row, 3, '總計', $title_format);
$worksheet->writeFormula($total_row, 4, "=SUM(E2:E" . ($total_row) . ")", $title_format);

$workbook->close();
?>

常見問題與解決方案

• 中文亂碼：確保 PHP 檔案編碼為 UTF-8，資料寫入前使用 iconv('UTF-8', 'GB2312//IGNORE', $str) 轉碼 (舊版 .xls 限制)
• 記憶體不足：大量資料分批寫入，或改用 PhpSpreadsheet 的串流寫入模式
• 檔案損壞：確保 close() 前無任何輸出 (含空白字元、BOM)