Drupal 專案常需匯入 Excel 資料(會員、商品、訂單等)。本文完整示範如何在 Drupal 7/8/9+ 中整合 PHPExcel(現為 PhpSpreadsheet),實現 Excel 讀取、驗證、批次寫入資料庫,含大檔案記憶體優化與錯誤處理。
# 1. 下載 PHPExcel 至 sites/all/libraries/PHPExcel
# 2. 啟用 Libraries 模組
# 3. 程式碼載入
libraries_load('PHPExcel');
require_once libraries_get_path('PHPExcel') . '/PHPExcel/IOFactory.php';
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
}
| 問題 | 原因 | 解決方案 |
|---|---|---|
| 日期欄位變成數字 | Excel 日期儲存為序數 | 用 `Shared\Date::excelToDateTimeObject()` 轉換 |
| 中文亂碼 | 編碼不一致 | 確保資料庫 `utf8mb4`、連線 `SET NAMES utf8mb4`、檔案 UTF-8 |
| 大檔案 OOM | 一次載入整檔 | 啟用 ChunkReadFilter、增加 memory_limit、分批處理 |
| 公式未計算 | PhpSpreadsheet 不自動計算公式 | 使用 `$writer->setPreCalculateFormulas(true)` 或手動計算 |