萬盛學電腦網

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

Drupal 使用 PHPExcel 讀取 Excel 匯入資料庫完整實作

Drupal 專案常需匯入 Excel 資料(會員、商品、訂單等)。本文完整示範如何在 Drupal 7/8/9+ 中整合 PHPExcel(現為 PhpSpreadsheet),實現 Excel 讀取、驗證、批次寫入資料庫,含大檔案記憶體優化與錯誤處理。

環境準備與套件安裝

Drupal 7:Libraries API + PHPExcel(舊專案維護)

# 1. 下載 PHPExcel 至 sites/all/libraries/PHPExcel
# 2. 啟用 Libraries 模組
# 3. 程式碼載入
libraries_load('PHPExcel');
require_once libraries_get_path('PHPExcel') . '/PHPExcel/IOFactory.php';

Drupal 8/9/10:Composer 安裝 PhpSpreadsheet(PHPExcel 繼任者)

composer require phpoffice/phpspreadsheet

# 服務容器註冊(可選,建議建立服務)
# mymodule.services.yml
services:
  mymodule.excel_importer:
    class: Drupal\mymodule\Service\ExcelImporter
    arguments: ['@database']

核心匯入服務類別實作

<?php
namespace Drupal\mymodule\Service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Drupal\Core\Database\Connection;

class ExcelImporter {
    private Connection $database;
    private int $batchSize = 500;
    
    public function __construct(Connection $database) {
        $this->database = $database;
    }
    
    public function import(string $filePath, array $options = []): array {
        $results = ['success' => 0, 'failed' => 0, 'errors' => []];
        
        try {
            $spreadsheet = $this->loadSpreadsheet($filePath);
            $sheet = $spreadsheet->getActiveSheet();
            
            $headerRow = $options['header_row'] ?? 1;
            $dataStartRow = $headerRow + 1;
            $columnMap = $options['column_map'] ?? $this->autoDetectColumns($sheet, $headerRow);
            
            $this->processRows($sheet, $dataStartRow, $columnMap, $options, $results);
            
        } catch (\Exception $e) {
            $results['errors'][] = ['row' => 0, 'message' => $e->getMessage()];
        }
        
        return $results;
    }
    
    private function loadSpreadsheet(string $filePath): Spreadsheet {
        $reader = IOFactory::createReaderForFile($filePath);
        $reader->setReadDataOnly(true); // 僅讀資料,不載入格式,省記憶體
        // 大檔案可啟用快取
        // $reader->setReadFilter(new \PhpOffice\PhpSpreadsheet\Reader\ChunkReadFilter());
        return $reader->load($filePath);
    }
    
    private function autoDetectColumns($sheet, int $headerRow): array {
        $columns = [];
        foreach ($sheet->getRowIterator($headerRow, $headerRow) as $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            foreach ($cellIterator as $cell) {
                $columns[] = $cell->getValue();
            }
        }
        return array_combine($columns, $columns); // 欄位名對應資料庫欄位
    }
    
    private function processRows($sheet, int $startRow, array $columnMap, array $options, array &$results): void {
        $table = $options['table'] ?? 'import_data';
        $batch = [];
        
        for ($rowNum = $startRow; $rowNum <= $sheet->getHighestRow(); $rowNum++) {
            $rowData = [];
            $empty = true;
            
            foreach ($columnMap as $excelCol => $dbCol) {
                $cell = $sheet->getCellByColumnAndRow($excelCol, $rowNum);
                $value = $this->normalizeCellValue($cell);
                if ($value !== null && $value !== '') $empty = false;
                $rowData[$dbCol] = $value;
            }
            
            if ($empty) continue; // 跳過空行
            
            // 資料驗證/轉換鉤子
            if (isset($options['transform'])) {
                $rowData = $options['transform']($rowData, $rowNum);
                if ($rowData === false) {
                    $results['failed']++;
                    $results['errors'][] = ['row' => $rowNum, 'message' => 'Transform failed'];
                    continue;
                }
            }
            
            $batch[] = $rowData;
            
            if (count($batch) >= $this->batchSize) {
                $this->batchInsert($table, $batch, $results);
                $batch = [];
            }
        }
        
        if ($batch) $this->batchInsert($table, $batch, $results);
    }
    
    private function normalizeCellValue($cell): mixed {
        $value = $cell->getValue();
        if ($value instanceof \PhpOffice\PhpSpreadsheet\RichText) {
            $value = $value->__toString();
        }
        if (is_string($value)) $value = trim($value);
        // 日期處理
        if ($cell->getDataType() === \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC) {
            $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
            if (preg_match('/[ymd]/i', $format)) {
                $value = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value)
                    ->format('Y-m-d H:i:s');
            }
        }
        return $value;
    }
    
    private function batchInsert(string $table, array $rows, array &$results): void {
        if (!$rows) return;
        
        $fields = array_keys($rows[0]);
        $placeholders = implode(', ', array_fill(0, count($fields), '?'));
        $sql = "INSERT INTO {{$table}} (" . implode(', ', $fields) . ") VALUES ($placeholders)";
        
        $stmt = $this->database->prepare($sql);
        foreach ($rows as $row) {
            try {
                $stmt->execute(array_values($row));
                $results['success']++;
            } catch (\Exception $e) {
                $results['failed']++;
                $results['errors'][] = ['data' => $row, 'message' => $e->getMessage()];
            }
        }
    }
}

表單整合與大檔案處理

<?php
// Drupal Form API 範例
function mymodule_import_form($form, FormStateInterface $form_state) {
    $form['file'] = [
        '#type' => 'managed_file',
        '#title' => t('Excel 檔案 (.xlsx, .xls, .csv)'),
        '#upload_location' => 'private://excel_import/',
        '#upload_validators' => [
            'file_validate_extensions' => ['xlsx xls csv'],
            'file_validate_size' => [50 * 1024 * 1024], // 50MB
        ],
    ];
    $form['submit'] = ['#type' => 'submit', '#value' => t('匯入')];
    return $form;
}

function mymodule_import_form_submit($form, FormStateInterface $form_state) {
    $fids = $form_state->getValue('file');
    if (empty($fids)) return;
    
    $file = \Drupal\file\Entity\File::load(reset($fids));
    $filepath = $file->getFileUri(); // 如 private://excel_import/xxx.xlsx
    
    // 大檔案建議使用 Batch API 避免逾時
    $batch = [
        'title' => t('匯入 Excel 中...'),
        'operations' => [
            ['\Drupal\mymodule\Service\ExcelImporter::batchImport', [$filepath, $form_state->getValues()]],
        ],
        'finished' => '\Drupal\mymodule\Service\ExcelImporter::batchFinished',
    ];
    batch_set($batch);
}

// 服務類別新增批次方法
public function batchImport(string $filepath, array $options, array &$context): void {
    if (empty($context['sandbox'])) {
        $context['sandbox']['progress'] = 0;
        $context['sandbox']['spreadsheet'] = $this->loadSpreadsheet($filepath);
        $context['sandbox']['sheet'] = $context['sandbox']['spreadsheet']->getActiveSheet();
        $context['sandbox']['max_row'] = $context['sandbox']['sheet']->getHighestRow();
        $context['sandbox']['start_row'] = ($options['header_row'] ?? 1) + 1;
        $context['sandbox']['column_map'] = $options['column_map'] ?? $this->autoDetectColumns($context['sandbox']['sheet'], $options['header_row'] ?? 1);
        $context['sandbox']['results'] = ['success' => 0, 'failed' => 0, 'errors' => []];
    }
    
    $sheet = $context['sandbox']['sheet'];
    $start = $context['sandbox']['start_row'] + $context['sandbox']['progress'];
    $limit = min($start + $this->batchSize - 1, $context['sandbox']['max_row']);
    
    $batchRows = [];
    for ($rowNum = $start; $rowNum <= $limit; $rowNum++) {
        // ... 同 processRows 邏輯收集 $batchRows
    }
    
    if ($batchRows) $this->batchInsert($options['table'] ?? 'import_data', $batchRows, $context['sandbox']['results']);
    
    $context['sandbox']['progress'] += count($batchRows);
    $context['finished'] = $context['sandbox']['progress'] / ($context['sandbox']['max_row'] - $context['sandbox']['start_row'] + 1);
    $context['results'] = $context['sandbox']['results'];
}

public function batchFinished($success, $results, $operations): void {
    if ($success) {
        \Drupal::messenger()->addStatus(t('匯入完成:成功 @success 筆,失敗 @failed 筆', $results));
    } else {
        \Drupal::messenger()->addError(t('匯入中斷'));
    }
    // 記錄錯誤詳情至 watchlog 或匯出 CSV
}

記憶體優化關鍵點

  • ReadDataOnly:`$reader->setReadDataOnly(true)` 忽略樣式、圖表、公式快取
  • ChunkReadFilter:分塊讀取大檔案,每次只載入部分行至記憶體
  • 批次寫入:每 500-1000 筆執行一次 `INSERT`,避免單一交易過大
  • 釋放資源:處理完 `$spreadsheet->disconnectWorksheets(); unset($spreadsheet);`
  • 增加 PHP 記憶體限制:`ini_set('memory_limit', '512M');` 或 `.user.ini` 設定

常見問題排查

copyright © 萬盛學電腦網 all rights reserved
問題原因解決方案
日期欄位變成數字Excel 日期儲存為序數用 `Shared\Date::excelToDateTimeObject()` 轉換
中文亂碼編碼不一致確保資料庫 `utf8mb4`、連線 `SET NAMES utf8mb4`、檔案 UTF-8
大檔案 OOM一次載入整檔啟用 ChunkReadFilter、增加 memory_limit、分批處理
公式未計算PhpSpreadsheet 不自動計算公式使用 `$writer->setPreCalculateFormulas(true)` 或手動計算