```php use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; use PhpOffice\PhpSpreadsheet\Reader\Xls; use PhpOffice\PhpSpreadsheet\Reader\Csv; ``` 導入 ```php /** * 導入 */ protected function import() { $this->model="調用數據表模型"; $file = $this->request->request('file'); if (!$file) { $this->error(__('Parameter %s can not be empty', 'file')); } $filePath = ROOT_PATH . DS . 'public' . DS . $file; if (!is_file($filePath)) { $this->error(__('No results were found')); } //實例化reader $ext = pathinfo($filePath, PATHINFO_EXTENSION); if (!in_array($ext, ['csv', 'xls', 'xlsx'])) { $this->error(__('Unknown data format')); } if ($ext === 'csv') { $file = fopen($filePath, 'r'); $filePath = tempnam(sys_get_temp_dir(), 'import_csv'); $fp = fopen($filePath, "w"); $n = 0; while ($line = fgets($file)) { $line = rtrim($line, "\n\r\0"); $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']); if ($encoding != 'utf-8') { $line = mb_convert_encoding($line, 'utf-8', $encoding); } if ($n == 0 || preg_match('/^".*"$/', $line)) { fwrite($fp, $line . "\n"); } else { fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n"); } $n++; } fclose($file) || fclose($fp); $reader = new Csv(); } elseif ($ext === 'xls') { $reader = new Xls(); } else { $reader = new Xlsx(); } //判斷是根據注釋導入還是字段導入、導入文件首行類型,默認是注釋,如果需要使用字段名稱請使用name $importHeadType = isset($this->importHeadType) ? $this->importHeadType : 'comment'; //獲取數據表的字段和注釋信息組成新的數組 開始 $table = $this->model->getQuery()->getTable(); $database = \think\Config::get('database.database'); $fieldArr = []; $list = db()->query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [$table, $database]); foreach ($list as $k => $v) { if ($importHeadType == 'comment') { $fieldArr[$v['COLUMN_COMMENT']] = $v['COLUMN_NAME']; } else { $fieldArr[$v['COLUMN_NAME']] = $v['COLUMN_NAME']; } } //獲取數據表的字段和注釋信息組成新的數組 結束 //加載文件 $insert = []; try { if (!$PHPExcel = $reader->load($filePath)) { $this->error(__('Unknown data format')); } $currentSheet = $PHPExcel->getSheet(0); //讀取文件中的第一個工作表 $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列號 $allRow = $currentSheet->getHighestRow(); //取得一共有多少行 $maxColumnNumber = Coordinate::columnIndexFromString($allColumn); $fields = []; //讀取表頭信息 for ($currentRow = 1; $currentRow <= 1; $currentRow++) { for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { //逐行逐列讀取 $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $fields[] = $val; } } //讀取表頭信息結束 //讀取表內容 for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) { $values = []; for ($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn++) { //逐行逐列讀取 $val = $currentSheet->getCellByColumnAndRow($currentColumn, $currentRow)->getValue(); $values[] = is_null($val) ? '' : $val; } $row = []; //通過合并兩個數組來創建一個新數組，其中的一個數組元素為鍵名，另一個數組元素為鍵值： $temp = array_combine($fields, $values); //判斷數據表是否有此字段并篩選 foreach ($temp as $k => $v) { if (isset($fieldArr[$k]) && $k !== '') { $row[$fieldArr[$k]] = $v; } } //得出需要添加的數據 if ($row) { $insert[] = $row; } } } catch (Exception $exception) { $this->error($exception->getMessage()); } if (!$insert) { $this->error(__('No rows were updated')); } try { //導入數據 $this->model->saveAll($insert); } catch (PDOException $exception) { $msg = $exception->getMessage(); if (preg_match("/.+Integrity constraint violation: 1062 Duplicate entry '(.+)' for key '(.+)'/is", $msg, $matches)) { $msg = "導入失敗，包含【{$matches[1]}】的記錄已存在"; }; $this->error($msg); } catch (Exception $e) { $this->error($e->getMessage()); } $this->success(); } ```