您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHPExcel 讀寫 Excel (XLS/XLSX) 完整操作指南

編輯：PHP 相關知識

庫選擇說明

重要遷移提示： PHPExcel 已於 2017 年停止維護，正式被 PhpSpreadsheet 取代。 本文代碼基於 PHPExcel 歷史版本語法，新專案請直接使用 PhpSpreadsheet，API 基本兼容，只需將命名空間從 PHPExcel 改為 PhpOffice\PhpSpreadsheet。

環境要求

PHP >= 5.6 (PhpSpreadsheet 需 7.2+)
擴展：php_xml, php_gd2, php_zip, php_xmlreader
Composer 安裝：composer require phpoffice/phpspreadsheet

核心操作：讀取 Excel 文件

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// 1. 自動識別格式並加載 (支援 .xls, .xlsx, .csv, .ods)
$filePath = 'data/products.xlsx';
$spreadsheet = IOFactory::load($filePath);

// 2. 獲取工作表 (預設第一張)
$sheet = $spreadsheet->getActiveSheet();
// 或指定名稱: $sheet = $spreadsheet->getSheetByName('Sheet1');

// 3. 獲取尺寸
$highestRow = $sheet->getHighestRow();       // 如 100
$highestCol = $sheet->getHighestColumn();    // 如 'F'
$highestColIdx = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestCol); // 6

// 4. 遍歷讀取 (兩種方式)

// 方式 A: 迭代器 (記憶體友好，適合大文件)
foreach ($sheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); // 含空單元格
    $rowData = [];
    foreach ($cellIterator as $cell) {
        $rowData[] = $cell->getValue();
    }
    // 處理 $rowData...
    if ($row->getRowIndex() == 1) continue; // 跳過表頭
    processRow($rowData);
}

// 方式 B: 索引訪問 (適合小文件/隨機讀取)
for ($row = 2; $row <= $highestRow; $row++) {
    $id = $sheet->getCellByColumnAndRow(1, $row)->getValue();
    $name = $sheet->getCellByColumnAndRow(2, $row)->getValue();
    $price = $sheet->getCellByColumnAndRow(3, $row)->getCalculatedValue(); // 獲取公式計算結果
    // ...
}

核心操作：寫入/生成 Excel 文件

<?php
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('產品列表');

// 1. 設置文檔屬性
$spreadsheet->getProperties()
    ->setCreator('Admin System')
    ->setLastModifiedBy('Cron Job')
    ->setTitle('產品導出報表')
    ->setSubject('月度銷售數據')
    ->setDescription('自動生成')
    ->setKeywords('excel export')
    ->setCategory('Report');

// 2. 寫入表頭 (樣式化)
$headerStyle = [
    'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF'], 'size' => 12],
    'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
    'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
    'borders' => ['allBorders' => ['borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN]]
];

$headers = ['ID', '產品名稱', '價格', '庫存', '創建時間', '狀態'];
$sheet->fromArray($headers, null, 'A1');
$sheet->getStyle('A1:F1')->applyFromArray($headerStyle);

// 3. 寫入數據 (批量高效)
$data = [
    [1001, '筆記本電腦', 5999.00, 50, '2024-01-15', '在售'],
    [1002, '無線滑鼠', 129.00, 200, '2024-01-16', '在售'],
    // ... 更多數據
];
$sheet->fromArray($data, null, 'A2'); // 從 A2 開始填充

// 4. 設置列寬 (自適應或固定)
$sheet->getColumnDimension('A')->setWidth(8);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setWidth(12);
$sheet->getColumnDimension('D')->setWidth(10);
$sheet->getColumnDimension('E')->setWidth(20);
$sheet->getColumnDimension('F')->setWidth(12);

// 5. 數據格式化 (貨幣、日期)
$sheet->getStyle('C2:C' . (count($data)+1))
    ->getNumberFormat()->setFormatCode('#,##0.00'); // 貨幣格式
$sheet->getStyle('E2:E' . (count($data)+1))
    ->getNumberFormat()->setFormatCode('YYYY-MM-DD'); // 日期格式

// 6. 寫入公式
$lastRow = count($data) + 1;
$sheet->setCellValue("C{$lastRow}", "=SUM(C2:C" . ($lastRow-1) . ")");
$sheet->getStyle("C{$lastRow}")->applyFromArray(['font' => ['bold' => true]]);

// 7. 凍結首行
$sheet->freezePane('A2');

// 8. 導出輸出
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 或 'Xls' (舊版)
// 直接下載
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="products_' . date('Ymd') . '.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output');
exit;

// 或保存文件
// $writer->save('/path/to/products.xlsx');

進階技巧：複製模板並填充數據

適用於固定格式報表（如發票、證書）：

$templatePath = 'templates/invoice_template.xlsx';
$spreadsheet = IOFactory::load($templatePath);
$sheet = $spreadsheet->getActiveSheet();

// 填充動態字段 (假設模板中預留了佔位單元格)
$sheet->setCellValue('B3', 'INV-2024001'); // 發票號
$sheet->setCellValue('B4', '張三公司');   // 客戶名
$sheet->setCellValue('B5', '2024-01-20');  // 日期

// 填充明細表 (從第 8 行開始)
$items = [
    ['服務費', 1, 5000.00, 5000.00],
    ['維護費', 12, 200.00, 2400.00],
];
$sheet->fromArray($items, null, 'A8');

// 總計公式已在模板中預設，無需重寫

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('invoices/INV-2024001.xlsx');

大文件處理優化策略

場景	問題	解決方案
讀取 10萬+ 行	記憶體溢出 (PHPExcel 全量載入)	使用 `IOFactory::createReader('Xlsx')->setReadDataOnly(true)` + `getRowIterator()` 流式讀取
寫入 5萬+ 行	生成緩慢、內存峰值高	分批寫入、關閉不必要樣式、使用 `setPreCalculateFormulas(false)`
超大導出	超時/內存限制	異步隊列生成 + 分片下載 / CSV 替代

常見問題排查

中文亂碼： 確保文件編碼 UTF-8，PHP 腳本 header('Content-Type: ...; charset=utf-8')，數據庫連接 SET NAMES utf8mb4。
日期讀出為數字 (如 45231)： Excel 內部存儲為天數偏移量。使用 \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value) 轉換。
公式不計算： 讀取時設置 setReadDataOnly(false) (默認)，或寫入後調用 $writer->setPreCalculateFormulas(true)。