项目场景：大概有一百来份excel表格数据需要整理入库，基础字段一样，如果按照传统的处理方法，需要先整理好数据（数据清洗、合并等），并且按照系统导入模板整理出来，费时费力。
需要解决的问题：
1、每份表的字段位置都不一样；
2、从字符串字段中匹配出手机号；
3、数据去重；
前台录入界面：
录入方法：
/*** 复制导入** @return string* @throws \think\Exception*/public function import(){$prefix = Config::get('database.prefix');$source = $this->request->param('source');$table = $prefix.'miniform_'.$source;// $project_id = $this->request->param('project_id');if (false === $this->request->isPost()) {$sql = "SELECT COLUMN_NAME AS 'name', COLUMN_COMMENT AS 'title' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'hr_gde_cc' AND TABLE_NAME = 'fa_call_loupan';";$fieldinfo = Db::query($sql);$field = [];foreach ($fieldinfo as $val){if(in_array($val['name'],['id','building','phone'])) continue;$field[$val['name']] = $val['title'];}$this->view->assign('field',$field);// $this->view->assign('tableinfo',$tableinfo);return $this->view->fetch();}$params = $this->request->post('row/a');$field = $this->request->post('field');// Session::set('fieldlast',$field);$data = $this->request->post('data');if (empty($params)) {$this->error(__('Parameter %s can not be empty', ''));}if($data == '' || !strpos($data, "\t")){ // \t是 tab 换行$this->error('导入数据格式不正确！');}$list = explode("\n",$data); // 导入的内容$insertAll = array(); // 存数据库的数组$field = explode(',',$field);$count = count($list);$exists = $success = $fail = 0;$mustArr['building'] = $params['building'];// 初次处理：排除原始号码phone2为空的数据foreach ($list as $k => $v) {$arr = explode("\t",trim($v));for ($i = 0; $i < count($field); $i++) {$a[$field[$i]] = isset($arr[$i]) ? $arr[$i] : '';}// 判断手机号是否为空if(!$a['phone2']){$fail ++;}else{$a = array_merge($mustArr,$a);$insertAll[] = $a;$success ++;}}// print_r($insertAll);exit;// 根据phone2推导出phoneforeach ($insertAll as &$val){if(preg_match('/^1[3-9]\d{9}$/', $val['phone2'])){$val['phone'] = $val['phone2'];continue;}else{$text = $val['phone2'];// 正则表达式匹配中国大陆手机号preg_match_all('/1\d{10}/', $text, $matches);// print_r($matches); if(isset($matches[0][0])){$val['phone'] = $matches[0][0];}else{$val['phone'] = '';$fail ++;}}}$msg = '共计：'.$count.'条';//号码查重开关if($params['unique_mobile']){// 导入数据本身的号码查重$insertAll = $this->array_unique_by_value($insertAll,'phone');// 查找数据库已存在的手机号$phonelist = $this->model->column('phone');foreach ($insertAll as $key=>$val){if(in_array($val['phone'],$phonelist) || !preg_match('/^1[3-9]\d{9}$/', $val['phone'])){unset($insertAll[$key]);$exists ++;}}$msg .= '共'.$count.'条，重复号码'.$exists.'条，成功导入'.$succes.'条，失败'.$fail.'条';}$result = false;Db::startTrans();try {$result = $this->model->saveAll($insertAll);Db::commit();} catch (ValidateException|PDOException|Exception $e) {Db::rollback();$this->error($e->getMessage());}if ($result === false) {$this->error($msg);}$this->success($msg,null,['ok'=>333]);}// php 数组根据某个值去重protected function array_unique_by_value($array, $key) {$unique = array();foreach ($array as $value) {if (!array_key_exists($key, $value)) {throw new InvalidArgumentException('The provided array does not have the specified key');}if (!array_key_exists($value[$key], $unique)) {$unique[$value[$key]] = $value;}}return array_values($unique);}
