合规国际互联网加速 OSASE为企业客户提供高速稳定SD-WAN国际加速解决方案。 广告
## 注意事项 * 导入的excel不能是从页面导出的excel,要新建一个 * 覆盖backend里面的import方法,需修改为public,以及引入相关类 * excel第一列为数据库字段名 * 导入excel日期列时需转换时间戳$v = date("Y-m-d H:i", ($v-25569) * 86400); ## 数据库增加导入权限 ``` INSERT INTO `yibao`.`fa_auth_rule` (`id`, `type`, `pid`, `name`, `title`, `icon`, `url`, `condition`, `remark`, `ismenu`, `menutype`, `extend`, `py`, `pinyin`, `createtime`, `updatetime`, `weigh`, `status`) VALUES ('156', 'file', '93', 'yibao_user/import', '导入', 'fa fa-circle-o', '', '', '', '0', NULL, '', 'dr', 'daoru', '1684381267', '1684381267', '0', 'normal'); ``` ## html页面 ``` <a href="javascript:;" data-url="ajax/upload" id="btn-import-file" class="btn btn-info btn-import{:$auth->check('yibao_user/import')?'':'hide'}" title="{:__('Import')}" data-mimetype="csv,xls,xlsx" data-multiple="false" ><i class="fa fa-upload"></i> {:__('Import')}</a> <a href="/template/yibaouser.xls" class="btn btn-primary">下载导入模板</a> ``` ## 控制器 ``` use Exception; use PhpOffice\PhpSpreadsheet\Cell\Coordinate; use PhpOffice\PhpSpreadsheet\Reader\Xlsx; use PhpOffice\PhpSpreadsheet\Reader\Xls; use PhpOffice\PhpSpreadsheet\Reader\Csv; use think\Db; use think\db\exception\BindParamException; use think\db\exception\DataNotFoundException; use think\db\exception\ModelNotFoundException; use think\exception\DbException; use think\exception\PDOException; use think\exception\ValidateException; use think\response\Json; /** * 导入 * * @return void * @throws PDOException * @throws BindParamException */ public function import() { $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(); } 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 = []; //加载文件 $insert = []; 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($k=='begindate' || $k=='enddate'){ if(empty($v)){ $v = '1970-01-01'; } } $row[$k] = $v; } if ($row) { $insert[] = $row; } } if (!$insert) { $this->error(__('No rows were updated')); } Db::startTrans(); try { //是否包含admin_id字段 /* $has_admin_id = false; foreach ($fieldArr as $name => $key) { if ($key == 'admin_id') { $has_admin_id = true; break; } } if ($has_admin_id) { $auth = Auth::instance(); foreach ($insert as &$val) { if (!isset($val['admin_id']) || empty($val['admin_id'])) { $val['admin_id'] = $auth->isLogin() ? $auth->id : 0; } } } */ $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()); } } ```