PHP 讀取 Excel/CSV 並匯入 MySQL 完整實作

前置：安裝 PhpSpreadsheet

composer require phpoffice/phpspreadsheet

Excel 匯入 MySQL

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$spreadsheet = IOFactory::load('data.xlsx');
$sheet = $spreadsheet->getActiveSheet();

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

$stmt = $pdo->prepare('INSERT INTO kehu (userid, sortid, danwei, xingming, zuncheng, tel, mobile, fax, email, website, qq, address, postcode, sex, beizhu, addtime)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');

foreach ($sheet->getRowIterator(2) as $row) { // 跳過標題列
    $cells = [];
    foreach ($row->getCellIterator() as $cell) $cells[] = $cell->getValue();
    // $cells[0]~[14] 對應欄位，addtime 由 PHP 產生
    $cells[] = date('Y-m-d H:i:s');
    $stmt->execute($cells);
}
echo '匯入完成';

CSV 匯入

$fp = fopen('data.csv', 'r');
$stmt = $pdo->prepare('INSERT INTO mantis_bug_table (id, project_id, summary, bug_text_id) VALUES (?, ?, ?, ?)');
while (($data = fgetcsv($fp, 1000, ',')) !== false) {
    $stmt->execute([$data[0], $data[1], $data[2], $data[0]]); // 依實際欄位調整
}
fclose($fp);

效能優化

• 關閉自動提交：$pdo->beginTransaction() ... 批次執行 ... $pdo->commit()。
• 使用 LOAD DATA LOCAL INFILE（需 MySQL 啟用 local_infile）大量匯入 CSV 最快。
• 分批次 (chunk) 處理大檔案，避免記憶體耗盡。