萬盛學電腦網

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

PHP 使用 PHPExcel 操作 Excel (xls/xlsx) 檔案完整教學

前言

PHPExcel 是早期 PHP 操作試算表最廣泛使用的函式庫,支援讀寫 xls (BIFF)、xlsx (OOXML)、CSV、ODS 等格式。雖然官方已於 2017 年停止維護,轉由 PhpSpreadsheet 繼承,但許多舊專案仍在使用。本文整理 PHPExcel 核心操作範例,並標註遷移建議。

環境需求

  • PHP 5.2+ (PHPExcel 1.8 最後支援 PHP 5.6,PhpSpreadsheet 需 PHP 7.1+)
  • 擴充:php_zip, php_xml, php_gd2 (圖表/圖片支援)
  • Composer 安裝:composer require phpoffice/phpexcel (已棄用,建議改 phpoffice/phpspreadsheet)

核心類別架構

  • PHPExcel:主物件,代表整個試算表檔案
  • PHPExcel_Worksheet:單一工作表
  • PHPExcel_Cell:單一儲存格
  • PHPExcel_IOFactory:統一讀寫入口,自動偵測格式
  • PHPExcel_Style_*:樣式相關類別 (字型、對齊、邊框、填滿等)

範例一:建立並寫入 Excel 檔案 (xlsx)

<?php
require_once 'vendor/autoload.php'; // Composer 自動載入

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('產品清單');

// 設定文件屬性
$spreadsheet->getProperties()
    ->setCreator('系統管理員')
    ->setLastModifiedBy('系統管理員')
    ->setTitle('產品匯出報表')
    ->setSubject('產品匯出報表')
    ->setDescription('自動產生的產品資料匯出')
    ->setKeywords('產品 匯出 Excel');

// 標題列資料
$titles = ['ID', '產品名稱', '分類', '單價', '庫存', '建立時間'];
foreach ($titles as $colIndex => $title) {
    $col = $colIndex + 1; // 1-based
    $sheet->setCellValueByColumnAndRow($col, 1, $title);
}

// 標題列樣式
$titleRange = 'A1:' . $sheet->getCellByColumnAndRow(count($titles), 1)->getCoordinate();
$sheet->getStyle($titleRange)->applyFromArray([
    'font' => [
        'bold' => true,
        'color' => ['argb' => 'FFFFFFFF'],
        'size' => 12,
    ],
    'alignment' => [
        'horizontal' => Alignment::HORIZONTAL_CENTER,
        'vertical' => Alignment::VERTICAL_CENTER,
    ],
    'borders' => [
        'allBorders' => [
            'borderStyle' => Border::BORDER_THIN,
            'color' => ['argb' => 'FF000000'],
        ],
    ],
    'fill' => [
        'fillType' => Fill::FILL_SOLID,
        'startColor' => ['argb' => 'FF4472C4'], // 藍色標題列
    ],
]);

// 模擬資料
$data = [
    [1, '筆記型電腦', '3C電子', 25000, 50, '2024-01-15'],
    [2, '無線滑鼠', '3C電子', 800, 200, '2024-02-20'],
    [3, '機械鍵盤', '3C電子', 3200, 80, '2024-03-10'],
    [4, '27吋螢幕', '3C電子', 12000, 30, '2024-01-28'],
];

// 寫入資料列
foreach ($data as $rowIndex => $row) {
    $rowNum = $rowIndex + 2; // 從第 2 列開始
    foreach ($row as $colIndex => $value) {
        $col = $colIndex + 1;
        $sheet->setCellValueByColumnAndRow($col, $rowNum, $value);
    }
    
    // 偶數列淡色背景
    if ($rowIndex % 2 === 0) {
        $range = 'A' . $rowNum . ':' . $sheet->getCellByColumnAndRow(count($titles), $rowNum)->getCoordinate();
        $sheet->getStyle($range)->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()->setARGB('FFF2F2F2');
    }
}

// 自動調整欄寬
foreach (range(1, count($titles)) as $col) {
    $sheet->getColumnDimensionByColumn($col)->setAutoSize(true);
}

// 輸出檔案
$filename = 'products_' . date('Ymd_His') . '.xlsx';
$writer = new Xlsx($spreadsheet);
$writer->save($filename);

// 若為 Web 下載,改用:
// header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
// header('Content-Disposition: attachment;filename="' . $filename . '"');
// header('Cache-Control: max-age=0');
// $writer->save('php://output');

echo "檔案已產生:$filename";

範例二:讀取 Excel 檔案內容

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

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

$inputFile = 'products_20240115.xlsx';

try {
    // 自動偵測格式並載入
    $spreadsheet = IOFactory::load($inputFile);
    $sheet = $spreadsheet->getActiveSheet();
    
    echo "工作表標題: " . $sheet->getTitle() . PHP_EOL;
    echo "尺寸: " . $sheet->calculateWorksheetDimension() . PHP_EOL;
    
    // 迭代所有儲存格 (含空白)
    foreach ($sheet->getRowIterator() as $row) {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false); // 包含空白儲存格
        
        $rowData = [];
        foreach ($cellIterator as $cell) {
            $rowData[] = $cell->getValue();
        }
        echo implode(' | ', $rowData) . PHP_EOL;
    }
    
    // 或僅讀取有資料的區塊 (高效能)
    $highestRow = $sheet->getHighestRow();
    $highestCol = $sheet->getHighestColumn();
    $highestColIdx = Coordinate::columnIndexFromString($highestCol);
    
    for ($row = 1; $row <= $highestRow; $row++) {
        $rowData = [];
        for ($col = 1; $col <= $highestColIdx; $col++) {
            $cell = $sheet->getCellByColumnAndRow($col, $row);
            $rowData[] = $cell->getValue();
        }
        // 處理資料...
    }
    
} catch (Exception $e) {
    echo "讀取失敗: " . $e->getMessage();
}

常見問題與解決方案

1. 中文亂碼

  • 確保 PHP 檔案編碼為 UTF-8 無 BOM
  • 資料庫連線設定 SET NAMES utf8mb4
  • PHPExcel 內部使用 UTF-8,直接寫入中文字串即可

2. 記憶體不足 (大檔案)

// 增加記憶體限制 (臨時)
ini_set('memory_limit', '512M');

// 或使用 cell caching 減少記憶體
$cacheMethod = \PhpOffice\PhpSpreadsheet\CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = ['memoryCacheSize' => '16MB'];
\PhpOffice\PhpSpreadsheet\Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

// 大量資料建議分批寫入,或改用 CSV 格式

3. 日期/數值格式錯誤

// 明確設定儲存格格式
$sheet->getStyle('D2:D100')->getNumberFormat()
    ->setFormatCode('#,##0'); // 數值千分位

$sheet->getStyle('F2:F100')->getNumberFormat()
    ->setFormatCode('YYYY-MM-DD'); // 日期格式

// 或使用內建常數
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
$sheet->getStyle('F2:F100')->getNumberFormat()
    ->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDD);

遷移至 PhpSpreadsheet 指南

PHPExcel 已不再維護,新專案強烈建議直接使用 PhpSpreadsheet:

  • 命名空間:PHPExcel → PhpOffice\PhpSpreadsheet
  • 類別前綴:PHPExcel_ → 移除,改用命名空間
  • IOFactory 用法相同
  • 樣式陣列鍵名部分變更 (如 'font' => ['bold' => true] 維持相容)
  • 支援 PHP 7.1+、更嚴格的型別提示、效能優化、新增功能 (如圖表、樞紐分析表)

結語

PHPExcel/PhpSpreadsheet 提供豐富的試算表操作功能,適合報表匯出、資料匯入、樣板產生等場景。掌握核心 API (建立工作表、寫入儲存格、套用樣式、讀取迭代) 即可應付大多數需求。處理大量資料時務必注意記憶體管理,善用快取機制或改用串流式讀寫。

copyright © 萬盛學電腦網 all rights reserved