yii2 xls自动上传,以及批量导入excel

先自动上传xls导入成功后,执行excel读取获取数据后,批量插入数据库 public function actionAddusers(){ $model=new OrgSearch(); if(!$org=$model->getOrg()){ exit('机构不存在'); } if (Yii::$app->request->isAjax){ $savepath='data/attachment/'.date('Ymd').'xls/'; $data=$this->upload_file('file',$savepath,time().mt_rand(01,10),['xls']); if (isset($data['absolute_path'])){ $info=$this->orgusermanager_import($data['absolute_path']); //输出导入错误信息 if (!empty($info)){ return json_encode($info,JSON_UNESCAPED_UNICODE); }else{ return json_encode('导入成功',JSON_UNESCAPED_UNICODE); } } return json_encode($data,JSON_UNESCAPED_UNICODE); } return '请上传xls文件'; } //上传文件 public function upload_file($file_name,$save_dir,$save_name,$file_type=['jpg','txt','xls','jpeg','png']){ if (!is_dir($dir=Yii::$app->basePath.'/../'.$save_dir)){ mkdir(Yii::$app->basePath.'/../'.$save_dir,0777,true); } $file=UploadedFile::getInstanceByName($file_name); $absolute_path=$dir.$save_name.'.'.$file->extension; $relative_path=$save_dir.$save_name.'.'.$file->extension; if (in_array($file->extension,$file_type)){ if ($file && $file->saveAs($absolute_path)){ $data['absolute_path']=$absolute_path; $data['relative_path']=$relative_path; return $data; }else{ return '上传失败'; } }else{ return '格式不对!请选择正确文件'; } } //批量导入 public function orgusermanager_import($path){ require(Yii::getAlias("@vendor")."/composer/phpexcel/PHPExcel.php"); require(Yii::getAlias("@vendor")."/composer/phpexcel/PHPExcel/Writer/Excel2007.php"); $excelReader = \PHPExcel_IOFactory::createReader('Excel5'); $phpexcel = $excelReader->load($path)->getSheet(0);//载入文件并获取第一个sheet $total_line = $phpexcel->getHighestRow(); //多少行 $total_column = $phpexcel->getHighestColumn(); //多少列 $err = array(); for($row = 2; $row <= $total_line; $row++) { $oneUser = array(); for ($column = 'A'; $column <= $total_column; $column++) { $oneUser[] = trim($phpexcel->getCell($column . $row)->getValue()); } // var_dump($oneUser);//获取到的每一行数据 $transaction=Yii::$app->db->beginTransaction(); $usermodel=new UserSearch(); try { //判断是否达到上限 if (!$this->checkmaxuser((new OrgSearch())->getOrg())){ throw new Exception('达到上限了!'); }; //判断用户是否已存在 $existUser = $usermodel->findOne(['username' => $oneUser[0]]); if ($existUser) { throw new Exception('用户已存在'); } //user表添加用户 $usermodel->username=$oneUser[0]; $usermodel->password=$oneUser[1]; $usermodel->email=$oneUser[2]; $usermodel->is_bind=1; if(!$usermodel->save()){ throw new Exception('user表保存失败'); }; //org_user添加信息 $data['user_id']=$usermodel->id; $data['org_id']=Yii::$app->session->get('org_id'); $data['yh_status']=2; $data['role_id']=1; (new OrguserSearch())->addorguser($data); $transaction->commit(); } catch (Exception $e) { $transaction->rollBack(); $err= $e->getMessage(); break; } //若不存在,就判断是否达到绑定上限 } return ($err); }