從 Excel 匯入資料,要求全部成功或全部回滾,避免部份寫入造成髒資料。
<?php
header('Content-Type: text/html; charset=utf-8');
error_reporting(E_ALL & ~E_NOTICE);
require_once 'excel_reader2.php'; // 舊版 Spreadsheet_Excel_Reader
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read('example.xls');
$rows = $data->sheets[0]['numRows'];
$cols = $data->sheets[0]['numCols'];
echo "{$rows} 行,{$cols} 列
";
// PDO 連線
$dsn = 'mysql:host=localhost;dbname=biaoge;charset=utf8';
$pdo = new PDO($dsn, 'root', '123456');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 開啟事務
$pdo->beginTransaction();
try {
$stmt = $pdo->prepare('INSERT INTO test SET id1=?, id2=?, id3=?, id4=?, id5=?');
for ($i = 1; $i <= $rows; $i++) {
$id1 = $data->sheets[0]['cells'][$i][1];
$id2 = $data->sheets[0]['cells'][$i][2];
$id3 = $data->sheets[0]['cells'][$i][3];
$id4 = $data->sheets[0]['cells'][$i][4];
$id5 = $data->sheets[0]['cells'][$i][5];
$stmt->bindParam(1, $id1);
$stmt->bindParam(2, $id2);
$stmt->bindParam(3, $id3);
$stmt->bindParam(4, $id4);
$stmt->bindParam(5, $id5);
$stmt->execute();
}
$pdo->commit();
echo '匯入成功';
} catch (PDOException $e) {
$pdo->rollBack();
die('Error: ' . $e->getMessage());
}
try/catch 統一回滾。beginTransaction() 到 commit() 之間的所有寫入要麼全成功、要麼全失敗。ChunkReadFilter 分批讀取、分批提交事務(如每 1000 筆 commit 一次),避免長事務鎖表。use PhpOffice\PhpSpreadsheet\IOFactory;
$spreadsheet = IOFactory::load('example.xlsx');
$sheet = $spreadsheet->getActiveSheet();
foreach ($sheet->getRowIterator() as $row) {
$vals = [];
foreach ($row->getCellIterator() as $cell) { $vals[] = $cell->getValue(); }
$stmt->execute($vals);
}