```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(); } ```