Excel 匯入資料庫是後台管理常見需求。本文以 PHPExcel(現併入 PhpSpreadsheet)為例,示範從上傳、解析、驗證到批次寫入 MySQL 的完整流程。
composer require phpoffice/phpspreadsheet
# 或舊專案:require_once 'PHPExcel/IOFactory.php';
CREATE TABLE `execl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['excel'])) {
$file = $_FILES['excel']['tmp_name'];
$ext = strtolower(pathinfo($_FILES['excel']['name'], PATHINFO_EXTENSION));
try {
// 自動偵測格式 (xls, xlsx, csv...)
$spreadsheet = IOFactory::load($file);
$sheet = $spreadsheet->getActiveSheet();
$rows = $sheet->toArray(null, true, true, true); // 索引從 A 欄開始
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare('INSERT INTO `execl` (`name`) VALUES (?)');
$pdo->beginTransaction();
$success = 0;
foreach ($rows as $rowNum => $row) {
if ($rowNum === 1) continue; // 跳過標題列
$name = trim($row['A'] ?? '');
if ($name === '') continue;
$stmt->execute([$name]);
$success++;
}
$pdo->commit();
echo "成功匯入 $success 筆資料";
} catch (Exception $e) {
if (isset($pdo)) $pdo->rollBack();
echo "匯入失敗: " . $e->getMessage();
}
}
?>
<form method="post" enctype="multipart/form-data">
<input type="file" name="excel" accept=".xls,.xlsx,.csv" required>
<button type="submit">匯入資料</button>
</form>
| 環節 | 說明 |
|---|---|
| 大檔案處理 | 使用 ChunkReadFilter 分塊讀取,避免記憶體耗盡 |
| 資料驗證 | 匯入前檢查必填欄位、格式、長度、唯一性 |
| 批次寫入 | PDO 事務 + Prepared Statement,效能與安全並重 |
| 編碼處理 | 資料庫、連線、PHP 檔案統一 UTF-8 |
| 錯誤回報 | 記錄失敗行號與原因,回饋用戶修正 |
<?php
// 使用 LOAD DATA INFILE(需檔案在資料庫伺服器本機)
$sql = "LOAD DATA INFILE '/path/file.csv'
INTO TABLE `execl`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(`name`)";
$pdo->exec($sql); // 速度比 PHP 解析快 10-100 倍
?>