萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHPExcel/PhpSpreadsheet 導入 Excel 數據完整實戰:驗證、分批讀取、錯誤收集

將 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) 查重,避免資料庫唯一鍵拋錯
錯誤收集累積所有錯誤一次回報,不中斷流程使用者可一次修正所有問題

常見坑點

  • 日期儲存為數值:Excel 日期為序數(如 45292),需用 \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($val) 轉換。
  • 前導零遺失:如條碼 00123 讀成 123,需在樣板設定儲存格格式為「文字」或匯入時補零。
  • 合併儲存格:getMergeCells() 取得合併區域,邏輯補全空值。
copyright © 萬盛學電腦網 all rights reserved