PHPExcel 讀取 Excel 匯入資料庫完整實作：ThinkPHP 整合與 PDO 批次寫入

前言

PHPExcel (現已併入 PhpSpreadsheet) 是 PHP 處理 Excel 最主流的庫。本文示範完整流程：上傳 → 讀取 → 驗證 → 批次寫入資料庫，含 ThinkPHP 整合與純 PDO 兩種寫法。

一、環境準備

Composer 安裝 (推薦)

composer require phpoffice/phpspreadsheet

手動引入 (無 Composer 環境)

require_once 'PHPExcel.php';
require_once 'PHPExcel/IOFactory.php';
require_once 'PHPExcel/Reader/Excel5.php';    // .xls
require_once 'PHPExcel/Reader/Excel2007.php'; // .xlsx

二、ThinkPHP 3.x/5.x 整合範例

<?php
namespace Home\Controller;
use Think\Controller;

class ImportController extends Controller {
    public function importExcel() {
        // 1. 取得上傳檔案路徑
        $file = $_FILES['excel_file'];
        if ($file['error'] !== UPLOAD_ERR_OK) $this->error('上傳失效');
        
        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if (!in_array($ext, ['xls', 'xlsx'])) $this->error('僅支援 .xls/.xlsx');
        
        // 2. 自動偵測格式並讀取
        $reader = \PHPExcel_IOFactory::createReader($ext === 'xls' ? 'Excel5' : 'Excel2007');
        $reader->setReadDataOnly(true); // 僅讀資料，不載入格式，省記憶體
        $objPHPExcel = $reader->load($file['tmp_name']);
        
        // 3. 選擇工作表 (索引 0 = 第一張)
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestCol = $sheet->getHighestColumn();
        
        // 4. 批次準備資料
        $data = [];
        for ($row = 2; $row <= $highestRow; $row++) { // 假設第 1 列是標題
            $rowData = [
                'student_no' => $sheet->getCell("A$row")->getValue(),
                'name'       => $sheet->getCell("B$row")->getValue(),
                'gid'        => $sheet->getCell("C$row")->getValue(),
            ];
            // 簡單驗證
            if (empty($rowData['student_no'])) continue;
            $data[] = $rowData;
        }
        
        // 5. PDO 批次寫入 (效能最佳)
        $dsn = "mysql:host=localhost;dbname=alumni;charset=utf8mb4";
        $pdo = new PDO($dsn, 'root', '', [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ]);
        
        $sql = "INSERT INTO alumni (student_no, name, gid) VALUES (:student_no, :name, :gid)";
        $stmt = $pdo->prepare($sql);
        
        $pdo->beginTransaction();
        try {
            foreach ($data as $row) {
                $stmt->execute($row);
            }
            $pdo->commit();
            $this->success("匯入成功，共 " . count($data) . " 筆");
        } catch (Exception $e) {
            $pdo->rollBack();
            $this->error("匯入失敗: " . $e->getMessage());
        }
    }
}
?>

三、純 PHP + PDO 完整腳本 (獨立執行)

<?php
set_time_limit(0);
ini_set('memory_limit', '512M');

require_once 'vendor/autoload.php'; // Composer

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;

$file = 'bks.xlsx'; // 或 .xls
$ext = pathinfo($file, PATHINFO_EXTENSION);
$readerClass = $ext === 'xls' ? Xls::class : Xlsx::class;

$reader = new $readerClass();
$reader->setReadDataOnly(true);
$spreadsheet = $reader->load($file);
$sheet = $spreadsheet->getActiveSheet();
$highestRow = $sheet->getHighestRow();

// PDO 連線
$dsn = "mysql:host=localhost;dbname=alumni;charset=utf8mb4";
$pdo = new PDO($dsn, "root", "", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$stmt = $pdo->prepare("INSERT INTO alumni (student_no, name, gid) VALUES (?, ?, ?)");

$pdo->beginTransaction();
$success = 0; $errors = [];
for ($row = 2; $row <= $highestRow; $row++) {
    try {
        $student_no = $sheet->getCell("A$row")->getValue();
        $name       = $sheet->getCell("B$row")->getValue();
        $gid        = $sheet->getCell("C$row")->getValue();
        
        if (empty($student_no)) continue;
        
        $stmt->execute([$student_no, $name, $gid]);
        $success++;
    } catch (Exception $e) {
        $errors[] = "第 $row 行: " . $e->getMessage();
    }
}
$pdo->commit();

echo "成功: $success 筆\n";
if ($errors) echo "失敗: " . implode("\n", $errors) . "\n";
?>

四、大量資料優化策略

ChunkReadFilter 範例 (PhpSpreadsheet)

<?php
class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter {
    private $startRow = 0; private $endRow = 0;
    public function setRows($start, $chunkSize) {
        $this->startRow = $start; $this->endRow = $start + $chunkSize;
    }
    public function readCell($column, $row, $worksheetName = '') {
        return ($row >= $this->startRow && $row < $this->endRow);
    }
}

$filter = new ChunkReadFilter();
$reader->setReadFilter($filter);
$chunkSize = 1000;

for ($startRow = 2; $startRow <= $highestRow; $startRow += $chunkSize) {
    $filter->setRows($startRow, $chunkSize);
    $spreadsheet = $reader->load($file);
    $sheet = $spreadsheet->getActiveSheet();
    // 處理這一塊資料...
    unset($spreadsheet); // 釋放記憶體
}
?>

五、資料結構參考 (php-excel-reader 格式)

Array
(
    [0] => Array
        (
            [numRows] => 5
            [numCols] => 4
            [cells] => Array
                (
                    [1] => Array ( [1] => 編號, [2] => 姓名, [3] => 年齡, [4] => 學號 )
                    [2] => Array ( [1] => 1, [2] => 小紅, [3] => 22, [4] => a1000 )
                    [3] => Array ( [1] => 2, [2] => 小王, [3] => 33, [4] => a1001 )
                )
            [cellsInfo] => Array ( ... 格式資訊 ... )
        )
)

六、常見問題排查

七、匯出 Excel 反向操作補充

<?php
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', '學號')->setCellValue('B1', '姓名')->setCellValue('C1', 'GID');

$data = $pdo->query("SELECT student_no, name, gid FROM alumni")->fetchAll();
$row = 2;
foreach ($data as $item) {
    $sheet->setCellValue("A$row", $item['student_no'])
          ->setCellValue("B$row", $item['name'])
          ->setCellValue("C$row", $item['gid']);
    $row++;
}

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('export.xlsx');
?>

掌握 PhpSpreadsheet 讀寫核心 API，搭配 PDO 批次操作與分塊讀取策略，即能穩健處理從千筆到百萬筆的 Excel 匯入匯出需求。