ThinkPHP 整合 PHPExcel 實現 Excel 匯入資料庫

環境準備

1. 下載 PHPExcel（GitHub: PHPOffice/PHPExcel）
2. 解壓至 ThinkPHP/Extend/Vendor/PHPExcel/Classes/
3. 確認 PHPExcel.php 可被自動載入

前端上傳表單

<form action="{:U('Import/excel')}" method="post" enctype="multipart/form-data">
    <input type="file" name="excel" accept=".xls,.xlsx">
    <input type="hidden" name="table" value="target_table">
    <button type="submit">匯入</button>
</form>

控制器匯入邏輯

public function excel() {
    if (empty($_FILES)) { $this->error('請選擇檔案'); }

    import('ORG.Net.UploadFile');
    $upload = new UploadFile([
        'allowExts' => ['xls', 'xlsx'],
        'savePath'  => './Public/Uploads/Excel/',
        'saveRule'  => 'time',
    ]);

    if (!$upload->upload()) { $this->error($upload->getErrorMsg()); }
    $info = $upload->getUploadFileInfo();
    $path = $info[0]['savepath'] . $info[0]['savename'];

    vendor('PHPExcel.PHPExcel');
    $objReader = PHPExcel_IOFactory::createReader('Excel2007'); // .xlsx
    // $objReader = PHPExcel_IOFactory::createReader('Excel5'); // .xls
    $objPHPExcel = $objReader->load($path);
    $sheet = $objPHPExcel->getSheet(0);

    $highestRow = $sheet->getHighestRow();
    $highestCol = $sheet->getHighestColumn();

    $model = M('TargetTable');
    $model->startTrans();

    try {
        for ($row = 2; $row <= $highestRow; $row++) { // 第 1 列為標題
            $data = [];
            $data['field1'] = $sheet->getCell("A{$row}")->getValue();
            $data['field2'] = $sheet->getCell("B{$row}")->getValue();
            $data['field3'] = $sheet->getCell("C{$row}")->getValue();
            // ... 對應更多欄位

            if (!$model->add($data)) throw new Exception("第 {$row} 列寫入失敗");
        }
        $model->commit();
        $this->success('匯入成功，共 ' . ($highestRow - 1) . ' 筆');
    } catch (Exception $e) {
        $model->rollback();
        $this->error('匯入失敗: ' . $e->getMessage());
    }
}

關鍵提醒

• getValue() 回傳原始值，日期型別為 Excel 序列數，需 PHPExcel_Shared_Date::ExcelToPHP() 轉換
• 大量資料（>5000 列）建議分批 commit 或使用 LOAD DATA INFILE
• 嚴格驗證每欄資料型別、長度、必填，避免髒資料入庫
• 匯入前備份目標表，支援一鍵回滾