//首先要使用composer 安裝擴(kuò)展庫PHPexcel (首先要確定電腦上安裝了composer)
命令:
composer require phpoffice/phpexcel
//引入Excel公共類
vendor("PHPExcel.Classes.PHPExcel");
vendor('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
vendor('PHPExcel.Classes.PHPExcel.Reader.Excel5');
$objPHPExcel = new \PHPExcel();
$file = request()->file('filename');
$info = $file->validate(['ext' => 'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'uploads/import');//上傳驗(yàn)證后綴名,以及上傳之后移動的地址
if($info){
$exclePath = $info->getSaveName();? //獲取文件名
$file_name = ROOT_PATH . 'public' . DS . 'uploads/import' . DS . $exclePath;? //上傳文件的地址
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
$obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');? //加載文件內(nèi)容,編碼utf-8
$excel_array=$obj_PHPExcel->getsheet(0)->toArray();? //轉(zhuǎn)換為數(shù)組格式
array_shift($excel_array);? //刪除第一個數(shù)組(標(biāo)題);
$datas = [];
$data_errors = [];
//處理Excel導(dǎo)入時數(shù)據(jù)為空的情況
foreach($excel_array as $k=>$v) {
if(!empty($v[0] || $v[1] || $v[2])){
$excel_list[] = $v;
}
}
$cn=count($excel_list);
//循環(huán)遍歷，組裝數(shù)據(jù)進(jìn)行入庫
foreach ($excel_list as $k=>$v){
if(!empty($v[0]) && !empty($v[1])){
$wh['job_number'] = $v[0];
$wh['is_deleted'] = '1';
$res_info=db('member')->where($wh)->count();
if($res_info ==0){
$data=array(
'job_number'=>$v[0],
'user_name'=>$v[1],
'department'=>$v[2],
'create_id' =>$admin_id,
'create_time'=>date('Y-m-d H:i:s'),
);
$datas[] = $data;
}
? ? ? }else{
$error_data=array(
'job_number'=>$v[0],
'user_name'=>$v[1],
'department'=>$v[2],
'create_id' =>$admin_id,
'create_time'=>date('Y-m-d H:i:s'),
);
$data_errors[] = $error_data;
}
}
$errors_data = count($data_errors);
$repetition = $cn-$errors_data;
$success=db('member')->insertAll($datas); //批量插入數(shù)據(jù)
if($success){
$cn_fail=count($data_errors);
$content = "批量新增抽獎參與者名單記錄：".json_encode($data);
$this->writelog($admin_id,$content,'4');//4出席成員
$error=$cn-$success;
$error_all = $error-$cn_fail;
$data = array(
'code'=>1,
'message'=>"總{$cn}條，導(dǎo)入成功{$success}條，其中失敗{$cn_fail}條,重復(fù){$error_all}條",
'result'=>$data_errors,
);
return json($data);
}
return json(['code'=>1,'message'=>"總{$cn}條，導(dǎo)入成功0條，其中重復(fù){$repetition}條,失敗{$errors_data}條",'result'=>$data_errors]);
}else{
// 上傳失敗獲取錯誤信息
return json(['code'=>2,'message'=>'導(dǎo)入失敗','result'=>null]);
}
