將 Excel(.xls/.xlsx)匯入資料庫是後台管理常見需求。本文以「商品基本資料匯入」為例,示範 PhpSpreadsheet(PHPExcel 繼任者)完整流程:檔案上傳、表頭驗證、必填欄位檢查、重複偵測、分批讀取大檔、錯誤收集回報。
composer require phpoffice/phpspreadsheet
最低需 PHP 7.2+,建議 8.1+ 獲得最佳效能。
<?php
namespace App\Service;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
class ProductImportService
{
// 表頭對應常數
const MAP = [
'商品SAP編碼' => 'sap_code',
'商品名稱' => 'pname',
'商品群組' => 'product_group',
'品牌' => 'product_brand',
'代理標記' => 'product_proxy_flag',
'分箱' => 'product_binning',
'揀貨方式' => 'product_sell_pick',
'屬性' => 'product_attribute',
'供應商編碼' => 'vendor_code',
'供應地址' => 'zzwerk_code',
'批次' => 'zzlgort_code',
];
public function import(array $file): array
{
$errors = [];
$imported = 0;
// 1. 上傳檔案基本檢查
if ($file['error'] === UPLOAD_ERR_INI_SIZE) {
return ['success' => false, 'errors' => ['檔案超過 php.ini upload_max_filesize']];
}
if ($file['error'] === UPLOAD_ERR_NO_FILE) {
return ['success' => false, 'errors' => ['未選擇檔案']];
}
// 2. 自動偵測格式建立 Reader
$reader = IOFactory::createReaderForFile($file['tmp_name']);
$reader->setReadDataOnly(true); // 僅讀數值,不載入樣式/公式,省記憶體
$spreadsheet = $reader->load($file['tmp_name']);
$sheet = $spreadsheet->getActiveSheet();
// 3. 解析表頭
$headerRow = 1;
$highestCol = $sheet->getHighestColumn();
$colMap = [];
foreach ($sheet->getRowIterator($headerRow, $headerRow) as $row) {
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false);
foreach ($cellIterator as $cell) {
$val = trim((string)$cell->getValue());
if (isset(self::MAP[$val])) {
$colMap[$cell->getColumn()] = self::MAP[$val];
}
}
}
if (empty($colMap)) {
return ['success' => false, 'errors' => ['找不到可識別的表頭,請檢查模板']];
}
if (!in_array('sap_code', $colMap, true)) {
return ['success' => false, 'errors' => ['缺少必要欄位:商品SAP編碼']];
}
// 4. 分批讀取資料列(每批 1000 列)
$highestRow = $sheet->getHighestRow();
$batchSize = 1000;
$seenSap = []; // 當批次內重複偵測
for ($startRow = $headerRow + 1; $startRow <= $highestRow; $startRow += $batchSize) {
$endRow = min($startRow + $batchSize - 1, $highestRow);
$batchData = [];
for ($row = $startRow; $row <= $endRow; $row++) {
$sap = $this->getCell($sheet, $colMap, $row, 'sap_code');
if (!$sap) continue; // 空列略過
if (isset($seenSap[$sap])) {
$errors[] = "第 $row 列:SAP 編碼 $sap 重複";
continue;
}
$seenSap[$sap] = $row;
$record = ['sap_code' => $sap];
foreach (self::MAP as $header => $field) {
if ($field === 'sap_code') continue;
$record[$field] = $this->getCell($sheet, $colMap, $row, $field);
}
$batchData[] = $record;
}
// 5. 批次寫入資料庫(這裡用 PDO 交易示範)
if ($batchData) {
$imported += $this->batchInsert($batchData);
}
}
return ['success' => empty($errors), 'imported' => $imported, 'errors' => $errors];
}
private function getCell($sheet, array $colMap, int $row, string $field): ?string
{
$col = array_search($field, $colMap, true);
if ($col === false) return null;
$cell = $sheet->getCell($col . $row);
$val = $cell->getValue();
return $val !== null && $val !== '' ? trim((string)$val) : null;
}
private function batchInsert(array $records): int
{
// 實際專案請使用 PDO prepare + execute 批次插入或 ORM bulkInsert
// 這裡僅示意回傳筆數
return count($records);
}
}
| 環節 | 建議 | 原因 |
|---|---|---|
| Reader 建立 | IOFactory::createReaderForFile() 自動偵測 .xls/.xlsx | 避免手動判斷副檔名出錯 |
| 記憶體優化 | setReadDataOnly(true) + 分批讀取 | 大檔(>5 萬列)不會 OOM |
| 表頭驗證 | 建立「顯示名 → 欄位名」映射表,容忍順序調換 | 使用者常自行調整欄順 |
| 必填檢查 | 匯入前驗證關鍵欄位(如 sap_code)存在 | 避免髒資料入庫 |
| 重複偵測 | 陣列 $seenSap 記錄已見鍵值 | O(1) 查重,避免資料庫唯一鍵拋錯 |
| 錯誤收集 | 累積所有錯誤一次回報,不中斷流程 | 使用者可一次修正所有問題 |
\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($val) 轉換。00123 讀成 123,需在樣板設定儲存格格式為「文字」或匯入時補零。getMergeCells() 取得合併區域,邏輯補全空值。