萬盛學電腦網

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

PHPExcel 操作 Excel 完整實戰指南:讀寫、樣式、公式、大數據匯出優化

重要提示:PHPExcel 已停止維護

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

讀取 Excel 檔案

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

大數據匯出優化策略 (超過 5000 行)

策略原理適用場景
分塊寫入 + 清理記憶體每 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

遷移至 PhpSpreadsheet 指南

<?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+ 支援、效能優化、安全性修復與持續維護。

copyright © 萬盛學電腦網 all rights reserved