PHPExcel 最後版本 1.8.1 (2017) 已棄用,官方建議遷移至繼任者 PhpSpreadsheet。本文提供 PHPExcel 維護專案參考,新專案請直接使用 PhpSpreadsheet (API 高度相容)。
# PHPExcel (舊專案維護)
composer require phpoffice/phpexcel
# PhpSpreadsheet (新專案首選)
composer require phpoffice/phpspreadsheet
PHPExcel / Spreadsheet:主物件,代表整個試算表檔案Worksheet:工作表,透過 getActiveSheet()、createSheet() 存取Cell:儲存格,透過座標 'A1' 或 getCellByColumnAndRow() 存取Style:樣式 (字體、對齊、邊框、填充、數字格式)Writer:輸出器 (Xlsx, Xls, Csv, Html, Pdf)<?php
require_once '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\NumberFormat;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Drawing;
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('銷售報表');
// === 1. 文件屬性 ===
$spreadsheet->getProperties()
->setCreator('系統自動生成')
->setLastModifiedBy('排程任務')
->setTitle('月度銷售報表')
->setSubject('2024年1月銷售數據')
->setDescription('自動生成的銷售統計報表')
->setKeywords('銷售 報表 Excel')
->setCategory('報表');
// === 2. 標題列樣式 ===
$headerStyle = [
'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 12],
'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '3498DB']],
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'FFFFFF']]],
];
$headers = ['日期', '商品', '數量', '單價', '金額', '備註'];
$sheet->fromArray($headers, null, 'A1');
$sheet->getStyle('A1:F1')->applyFromArray($headerStyle);
$sheet->getColumnDimension('A')->setWidth(12);
$sheet->getColumnDimension('B')->setWidth(20);
$sheet->getColumnDimension('C')->setWidth(10);
$sheet->getColumnDimension('D')->setWidth(12);
$sheet->getColumnDimension('E')->setWidth(12);
$sheet->getColumnDimension('F')->setWidth(30);
// === 3. 資料寫入 (支援大量資料優化) ===
$salesData = [
['2024-01-01', '商品A', 10, 100, '=C2*D2', '首日熱銷'],
['2024-01-02', '商品B', 5, 200, '=C3*D3', ''],
// ... 大量資料建議使用 fromArray 批次寫入
];
// 批次寫入 (效能最佳)
$dataArray = array_merge([$headers], $salesData);
$sheet->fromArray($dataArray, null, 'A1');
// 公式需單獨設定 (fromArray 會當字串處理)
foreach (range(2, count($dataArray)) as $row) {
$sheet->setCellValue("E{$row}", "=C{$row}*D{$row}");
}
// === 4. 資料列樣式 ===
$dataStyle = [
'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'DDDDDD']]],
];
$sheet->getStyle("A2:F" . (count($dataArray)))->applyFromArray($dataStyle);
// 交替行顏色
for ($row = 2; $row <= count($dataArray); $row++) {
if ($row % 2 === 0) {
$sheet->getStyle("A{$row}:F{$row}")->getFill()
->setFillType(Fill::FILL_SOLID)
->getStartColor()->setRGB('F8F9FA');
}
}
// === 5. 日期格式化 ===
$sheet->getStyle('A2:A' . count($dataArray))
->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD);
// === 6. 貨幣格式 ===
$sheet->getStyle('D2:E' . count($dataArray))
->getNumberFormat()->setFormatCode('#,##0.00');
// === 7. 富文本 (Rich Text) ===
$richText = new RichText();
$richText->createText('此筆訂單 ');
$run = $richText->createTextRun('需優先處理');
$run->getFont()->setBold(true)->setColor(new \PhpOffice\PhpSpreadsheet\Style\Color(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED));
$richText->createText(',請儘快出貨。');
$sheet->setCellValue('F2', $richText);
// === 8. 批註 ===
$sheet->getComment('F2')->setAuthor('系統');
$sheet->getComment('F2')->getText()->createTextRun('自動標記優先單')->getFont()->setBold(true);
// === 9. 插入圖片 (Logo) ===
$drawing = new Drawing();
$drawing->setName('Logo');
$drawing->setDescription('公司Logo');
$drawing->setPath(__DIR__ . '/assets/logo.png');
$drawing->setCoordinates('A1');
$drawing->setOffsetX(10);
$drawing->setOffsetY(5);
$drawing->setWidth(80);
$drawing->setWorksheet($sheet);
// === 10. 凍結窗格 / 列印設定 ===
$sheet->freezePane('A2'); // 凍結標題列
$sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
$sheet->getPageSetup()->setFitToWidth(1);
$sheet->getPageSetup()->setFitToHeight(0);
// === 11. 輸出 ===
// 瀏覽器下載
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="sales_report_' . date('Ymd') . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>
<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
$spreadsheet = IOFactory::load('sales_report.xlsx');
$sheet = $spreadsheet->getActiveSheet();
// 1. 迭代讀取 (記憶體效率較好)
foreach ($sheet->getRowIterator() as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // 包含空儲存格
$rowData = [];
foreach ($cellIterator as $cell) {
$rowData[$cell->getColumn()] = $cell->getValue();
// 取得格式化後的值 (含日期、貨幣格式)
$formatted = $cell->getFormattedValue();
}
// 處理 $rowData
}
// 2. 範圍讀取轉陣列
$data = $sheet->rangeToArray('A1:F100', null, true, true, true);
// $data[0] = 第1行陣列 (索引從0開始)
// 3. 取得維度
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
?>
| 策略 | 原理 | 適用場景 |
|---|---|---|
| 分塊寫入 + 清理記憶體 | 每 1000 行 flush、unset 變數、gc_collect_cycles() | 中等資料量 (5K-50K) |
| CSV 替代 XLSX | 無格式、流式寫入、記憶體 O(1) | 純資料匯出、超大檔 (100K+) |
| PhpSpreadsheet 批次寫入 | fromArray() 批次操作比逐儲存格快 100 倍 | 所有場景必用 |
| 關閉不需功能 | $spreadsheet->setPreCalculateFormulas(false) 禁用公式預計算 | 大量公式場景 |
| 使用生成器 + 臨時檔 | 分批生成 XML 片段寫入臨時檔,最後打包 ZIP | 極大檔 (百萬行),需自定義 Writer |
<?php
function exportLargeXlsx(string $filename, iterable $dataGenerator, array $headers): void
{
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$spreadsheet->setPreCalculateFormulas(false); // 關鍵:禁用公式計算
// 寫入標題
$sheet->fromArray($headers, null, 'A1');
$row = 2;
$batchSize = 1000;
$batch = [];
foreach ($dataGenerator as $record) {
$batch[] = $record;
if (count($batch) >= $batchSize) {
$sheet->fromArray($batch, null, "A{$row}");
$row += $batchSize;
$batch = [];
// 釋放記憶體
gc_collect_cycles();
}
}
if ($batch) {
$sheet->fromArray($batch, null, "A{$row}");
}
// 直接輸出瀏覽器 (不佔用伺服器磁碟)
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
}
// 使用:資料庫游標逐行讀取
function dataGenerator(PDO $pdo): Generator
{
$stmt = $pdo->query("SELECT * FROM large_table");
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
yield $row;
}
}
?>
| 問題 | 解決方案 |
|---|---|
| 中文亂碼 | 確保檔案 UTF-8、資料庫連線 utf8mb4、PHP 輸出 header charset |
| 記憶體耗盡 (Allowed memory size) | 啟用分塊寫入、增加 memory_limit、改用 CSV/Stream |
| 日期變成數字 (44927) | Excel 內部儲存為序數,需設定 NumberFormat FORMAT_DATE_* |
| 公式不計算 | Excel 開啟時需啟用「自動計算」;PhpSpreadsheet 不計算公式值 |
| 樣式不生效 | 樣式應用順序:先寫資料再套用樣式,或用 applyFromArray |
| 合併儲存格後資料遺失 | 合併前先寫入左上角儲存格,再 mergeCells |
<?php
// 命名空間對應
// PHPExcel_* → PhpOffice\PhpSpreadsheet\*
// PHPExcel_IOFactory → PhpOffice\PhpSpreadsheet\IOFactory
// PHPExcel_Cell → PhpOffice\PhpSpreadsheet\Cell\Coordinate
// PHPExcel_Style_* → PhpOffice\PhpSpreadsheet\Style\*
// PHPExcel_RichText → PhpOffice\PhpSpreadsheet\RichText\RichText
// PHPExcel_Worksheet_Drawing → PhpOffice\PhpSpreadsheet\Drawing\Drawing
// 程式碼幾乎無需修改即可運行
?>
PHPExcel/PhpSpreadsheet 是 PHP 操作 Excel 的標準庫。核心技巧:批次 fromArray 寫入、樣式陣列套用、數字格式代碼、大檔分塊處理。新專案請直接使用 PhpSpreadsheet,享受 PHP 8.1+ 支援、效能優化、安全性修復與持續維護。