PHP 匯入 Excel/CSV 到 MySQL 完整實戰：PhpSpreadsheet、fgetcsv、大檔優化與錯誤處理

前言

將 Excel/CSV 匯入 MySQL 是後台管理常見功能。PHP 提供兩條路徑：CSV 用內建 fgetcsv（零依賴、效能最佳）；.xls/.xlsx 用 PhpSpreadsheet（支援多工作表、公式、樣式）。本文提供兩種完整實作，含大檔分塊、交易批次、編碼轉換、驗證、錯誤收集、進度回報，可直接整合到專案。

方案一：CSV 匯入（推薦大檔、高效能）

完整匯入腳本

<?php
// import_csv.php
require __DIR__ . '/config/db.php'; // 回傳 $pdo

$csvFile = 'members_import.csv';
$batchSize = 500;            // 每批 commit 筆數
$encoding  = 'big5';         // CSV 檔案編碼
$delimiter = ',';            // 分隔符

$handle = fopen($csvFile, 'r');
if (!$handle) die('無法開啟檔案：' . $csvFile);

$stmt = $pdo->prepare('INSERT INTO `members` (`name`, `email`, `phone`) VALUES (?, ?, ?)');
$header = null;
$count = 0;
$success = 0;
$errors = [];

$db->beginTransaction();
try {
    while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
        $count++;
        // 跳過空行
        if (count($row) === 1 && $row[0] === '') continue;

        // 編碼轉 UTF-8
        if (strtolower($encoding) !== 'utf-8') {
            $row = array_map(fn($v) => iconv($encoding, 'utf-8//IGNORE', $v), $row);
        }

        if ($header === null) {
            $header = $row; // 第一列標題
            continue;
        }

        $record = array_combine($header, $row);

        // 驗證
        $err = [];
        if (empty($record['name'])) $err[] = '姓名必填';
        if (empty($record['email']) || !filter_var($record['email'], FILTER_VALIDATE_EMAIL)) $err[] = 'Email 格式錯誤';
        if (!empty($record['phone']) && !preg_match('/^09\d{8}$/', $record['phone'])) $err[] = '手機格式錯誤';

        if ($err) {
            $errors[] = "第 $count 列：" . implode('、', $err);
            continue;
        }

        $stmt->execute([
            $record['name'],
            $record['email'],
            $record['phone'] ?? null
        ]);
        $success++;

        // 批次 commit
        if ($success % $batchSize === 0) {
            $pdo->commit();
            $pdo->beginTransaction();
            echo "已匯入 $success 筆...
";
            if (php_sapi_name() !== 'cli') { ob_flush(); flush(); }
        }
    }
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    die('匯入失敗：' . $e->getMessage());
}
fclose($handle);

echo "完成！共讀取 $count 列，成功 $success 筆。";
if ($errors) {
    echo "
錯誤列表（前 50 筆）：
" . implode("\n", array_slice($errors, 0, 50)) . "
";
}

大檔優化：Generator 逐行處理（記憶體恆定）

function csvRows($file, $encoding = 'big5', $delimiter = ',') {
    $h = fopen($file, 'r');
    $header = null;
    while (($row = fgetcsv($h, 0, $delimiter)) !== false) {
        if (count($row) === 1 && $row[0] === '') continue;
        if (strtolower($encoding) !== 'utf-8') {
            $row = array_map(fn($v) => iconv($encoding, 'utf-8//IGNORE', $v), $row);
        }
        if ($header === null) { $header = $row; continue; }
        yield array_combine($header, $row);
    }
    fclose($h);
}

// 使用：記憶體不隨檔案大小增長
$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO `members` (`name`, `email`, `phone`) VALUES (?, ?, ?)');
$i = 0;
foreach (csvRows('huge.csv') as $rec) {
    $stmt->execute([$rec['name'], $rec['email'], $rec['phone'] ?? null]);
    if (++$i % 500 === 0) { $pdo->commit(); $pdo->beginTransaction(); }
}
$pdo->commit();

方案二：Excel (.xlsx/.xls) 匯入 — PhpSpreadsheet

安裝：composer require phpoffice/phpspreadsheet

基本匯入範例

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;

$file = 'members.xlsx';
try {
    $reader = IOFactory::createReader('Xlsx');
    $reader->setReadDataOnly(true); // 僅讀資料，不讀樣式/公式，大幅加速
    $spreadsheet = $reader->load($file);
} catch (ReaderException $e) {
    die('讀取失敗：' . $e->getMessage());
}

$sheet = $spreadsheet->getActiveSheet(); // 或 getSheetByName('Sheet2')
$rows = $sheet->toArray(null, true, true, true); // 索引從 A1 開始

// 第一列為標題
$header = array_values(array_shift($rows));

$pdo = require __DIR__ . '/config/db.php';
$stmt = $pdo->prepare('INSERT INTO `members` (`name`, `email`, `phone`) VALUES (?, ?, ?)');
$success = 0;
$errors = [];

$pdo->beginTransaction();
foreach ($rows as $i => $row) {
    $rowNum = $i + 2; // 實際列號
    $record = array_combine($header, array_values($row));

    // 驗證（同 CSV）
    if (empty($record['name']) || empty($record['email'])) {
        $errors[] = "第 $rowNum 列：必填欄位缺漏";
        continue;
    }

    $stmt->execute([$record['name'], $record['email'], $record['phone'] ?? null]);
    $success++;

    if ($success % 500 === 0) {
        $pdo->commit();
        $pdo->beginTransaction();
    }
}
$pdo->commit();

echo "完成：成功 $success 筆";
if ($errors) echo "
錯誤：" . implode('
', array_slice($errors, 0, 20));

大檔優化：ReadFilter 只讀必要範圍

use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

class MyFilter implements IReadFilter {
    public function readCell($column, $row, $worksheetName = '') {
        // 只讀 A~C 欄，第 1~10000 列
        return in_array($column, ['A', 'B', 'C']) && $row >= 1 && $row <= 10000;
    }
}

$reader = IOFactory::createReader('Xlsx');
$reader->setReadFilter(new MyFilter());
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load('huge.xlsx');

方案三：HTML 表單上傳 + 即時匯入（完整流程）

上傳頁面

<!-- upload.html -->
<form action="import_action.php" method="post" enctype="multipart/form-data">
  <input type="file" name="file" accept=".csv,.xlsx,.xls" required>
  <select name="encoding">
    <option value="big5">Big5 (繁體 Excel) </option>
    <option value="gbk">GBK (簡體 Excel) </option>
    <option value="utf-8">UTF-8 </option>
  </select>
  <button type="submit">匯入資料庫</button>
</form>

處理動作

<?php
// import_action.php
if ($_SERVER['REQUEST_METHOD'] !== 'POST' || empty($_FILES['file'])) {
    die('請上傳檔案');
}

$file = $_FILES['file'];
if ($file['error'] !== UPLOAD_ERR_OK) {
    die('上傳錯誤：' . $file['error']);
}

$ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
$encoding = $_POST['encoding'] ?? 'big5';

// 移至暫存目錄
$tmp = sys_get_temp_dir() . '/import_' . uniqid() . '.' . $ext;
move_uploaded_file($file['tmp_name'], $tmp);

try {
    if ($ext === 'csv') {
        require 'import_csv_logic.php'; // 封裝上方 CSV 匯入邏輯
        import_csv($tmp, $encoding);
    } elseif (in_array($ext, ['xlsx', 'xls'])) {
        require 'import_xlsx_logic.php'; // 封裝 PhpSpreadsheet 邏輯
        import_xlsx($tmp);
    } else {
        throw new Exception('不支援的檔案格式');
    }
    echo "匯入成功！";
} catch (Exception $e) {
    echo "匯入失敗：" . $e->getMessage();
} finally {
    @unlink($tmp); // 清理暫存檔
}

進階技巧與最佳實務

常見問題對照表

總結選型指南

掌握 fgetcsv 串流處理與 PhpSpreadsheet 精準讀取，即能應付 99% 的 Excel/CSV 匯入需求。