php实现excel表格数据快速入库

项目场景：大概有一百来份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推导出phone
        foreach ($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);
    }