PHP 導入 Excel 到 MySQL：PhpSpreadsheet 現代化完整教學

為什麼選 PhpSpreadsheet？

舊專案常用 PHPExcel，但已於 2017 年停止維護。PhpSpreadsheet 是其官方繼任者，支援 PHP 7.1+、嚴格遵循 PSR-4、記憶體優化、支援 .xlsx/.xls/.csv/.ods 等格式，是目前最佳選擇。

安裝與環境需求

composer require phpoffice/phpspreadsheet

• PHP ≥ 7.1（建議 8.0+）
• 擴展：gd、zip、xml、mbstring
• 大檔案建議啟用 Cell Caching 或使用 Read Filter 串流讀取

資料表準備

CREATE TABLE `import_demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

完整導入腳本（支援驗證、去重、錯誤收集）

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

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel'])) {
    $file = $_FILES['excel']['tmp_name'];
    $ext  = strtolower(pathinfo($_FILES['excel']['name'], PATHINFO_EXTENSION));

    try {
        $spreadsheet = IOFactory::load($file);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray(null, true, true, true); // 索引從 A=1 開始

        // 假設第一行為標題，資料從第 2 行開始
        $header = array_shift($rows);
        // 期望欄位對應：A=name, B=email, C=age
        $expected = ['A' => 'name', 'B' => 'email', 'C' => 'age'];

        $success = 0;
        $errors = [];
        $data = [];

        foreach ($rows as $rowNum => $row) {
            $line = $rowNum + 1; // 實際 Excel 行號
            $name  = trim($row['A'] ?? '');
            $email = trim(strtolower($row['B'] ?? ''));
            $age   = isset($row['C']) ? (int)$row['C'] : null;

            // 基本驗證
            if (!$name) {
                $errors[] = "第 $line 行：姓名不可為空";
                continue;
            }
            if ($email && !filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $errors[] = "第 $line 行：Email 格式錯誤 ($email)";
                continue;
            }
            if ($age !== null && ($age < 1 || $age > 120)) {
                $errors[] = "第 $line 行：年齡異常 ($age)";
                continue;
            }

            $data[] = [$name, $email, $age];
        }

        // 批次寫入（事務 + 忽略重複 Email）
        if ($data) {
            $pdo->beginTransaction();
            $stmt = $pdo->prepare(
                'INSERT IGNORE INTO import_demo (name, email, age) VALUES (?, ?, ?)'
            );
            foreach ($data as $d) {
                $stmt->execute($d);
                $success += $stmt->rowCount();
            }
            $pdo->commit();
        }

        echo json_encode([
            'success' => $success,
            'total' => count($rows),
            'errors' => $errors
        ], JSON_UNESCAPED_UNICODE);

    } catch (Throwable $e) {
        http_response_code(500);
        echo json_encode(['error' => $e->getMessage()], JSON_UNESCAPED_UNICODE);
    }
    exit;
}
?>
<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>Excel 導入示範</title></head><body>
<h2>上傳 Excel (.xlsx/.xls/.csv)</h2>
<form method="post" enctype="multipart/form-data" id="upForm">
    <input type="file" name="excel" accept=".xlsx,.xls,.csv" required>
    <button type="submit">開始導入</button>
</form>
<pre id="result"></pre>
<script>
document.getElementById('upForm').onsubmit = async e => {
    e.preventDefault();
    const fd = new FormData(e.target);
    const res = await fetch('', {method: 'POST', body: fd});
    const data = await res.json();
    document.getElementById('result').textContent = JSON.stringify(data, null, 2);
};
</script>
</body></html>

大檔案優化：Read Filter 串流讀取

避免將整個試算表載入記憶體（>10MB 檔案建議）：

class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {
    private $startRow = 0;
    private $endRow = 0;
    public function setRange(int $start, int $chunkSize) {
        $this->startRow = $start;
        $this->endRow = $start + $chunkSize;
    }
    public function readCell($column, $row, $worksheetName = '') {
        return $row >= $this->startRow && $row < $this->endRow;
    }
}

$reader = new Xlsx();
$reader->setReadFilter(new ChunkReadFilter());
$reader->setReadDataOnly(true); // 僅讀數值，忽略樣式

$chunkSize = 1000;
for ($start = 2; ; $start += $chunkSize) { // 從第 2 行開始（跳過標題）
    $filter = $reader->getReadFilter();
    $filter->setRange($start, $chunkSize);
    $spreadsheet = $reader->load($file);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray(null, true, true, true);
    if (empty($rows)) break;

    // 處理 $rows 批次寫入...
    unset($spreadsheet); // 釋放記憶體
}

常見問題與解決

進階：對應欄位映射設定

實際專案中欄位順序不固定，建議讀取標題行建立映射：

$headerRow = $sheet->rangeToArray('A1:' . $sheet->getHighestColumn() . '1', null, true, false)[0];
$map = array_flip($headerRow); // ['姓名'=>'A', 'Email'=>'B', '年齡'=>'C']
// 之後用 $row[$map['姓名']] 取值

採用 PhpSpreadsheet + 批次預處理 + 分塊讀取，打造穩健、高效、可維護的 Excel 導入功能。