PHP导入Excel

发布于：2022-05-10 10:35:28 栏目：技术文档

#注：Excel首行标题名称要和$FIELD的title字段一致。表字段名要和$FIELD的field一致。

set_time_limit(0);
ini_set('memory_limit','1024M');

include_once(ROOT_PATH.'extend/lib/PHPExcel.php');
$params=$this->request->param();

$file=$this->request->file('file');
$save_path='../uploads/student';
$info=$file->move($save_path);

if($info){
////成功上传后获取上传信息
////输出jpg
//echo$info->getExtension();
////输出/uploads/student/20160820/42a79759f284b767dfcb2a0197904287.jpg
//echo$info->getSaveName();
////输出42a79759f284b767dfcb2a0197904287.jpg
//echo$info->getFilename();

$file_name=$save_path.'/'.$info->getSaveName();
$file_name=str_replace('\\','/',$file_name);
$objReader=\PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel=$objReader->load($file_name,$encode='utf-8');
$sheet=$objPHPExcel->getSheet(0);
$highestRow=$sheet->getHighestRow();//取得总行数
$highestColumn=$sheet->getHighestColumn();//取得总列数

if($highestRow<=1){
$msg['code']=0;
$msg['msg']='不能上传空文件！';
returnjson($msg);
die();
}

//计算列表头英文字母
$letter_all=[];
$letter=['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
$item=0;
$k=0;
$column='';
while($column!=$highestColumn){
$item_arr=[];
if($item==26){
$item=0;
}
$n=ceil(($k+1)/26);
if($n==1){
$column=$letter[$item];
$item_arr['column']=$column;
//array_push($letter_all,$letter[$item]);
}else{
$n=$n-2;
$column=$letter[$n].$letter[$item];
$item_arr['column']=$column;
//array_push($letter_all,$letter[$n].$letter[$item]);
}
$letter_all[]=$item_arr;
$k++;
$item++;
}



#学生主键
$key_fileds=$StudentModel->getFields(StudentModel::FIELD_FLAG_KEY);
$key_field_arr=array_column($key_fileds,'field');
$key_field_titles=array_column($key_fileds,'title');

#学生可修改字段
$yes_fileds=$StudentModel->getFields(StudentModel::FIELD_FLAG_YES);
$yes_field_arr=array_column($yes_fileds,'field');

#下拉选项
$field_type_select=$StudentModel->getFields(0,StudentModel::FIELD_TYPE_SELECT);
$field_type_select_fields=array_column($field_type_select,'field');

//判断表头是否存在主键
$key_num=0;
foreach($letter_allas$k=>$v){
$value=(string)$objPHPExcel->getActiveSheet()->getCell($v['column'].'1')->getValue();
$v['title']=$value;
$v['field']=$StudentModel->getFieldByTitle($value);
if(in_array($value,$key_field_titles)){
$key_num++;
}
$letter_all[$k]=$v;
}

//主键数量是否一致
if($key_num!=count($key_field_titles)){
$msg['code']=0;
$msg['msg']=implode('、',$key_field_titles).'必须存在！';
$msg['data']=$letter_all;
returnjson($msg);
die();
}

$error_idcards=[];

for($i=1;$i<$highestRow;$i++){
//$name=(string)$objPHPExcel->getActiveSheet()->getCell("A".($i+1))->getValue();//证件号

foreach($letter_allas$k=>$v){
//获取excel内容
$value=(string)$objPHPExcel->getActiveSheet()->getCell($v['column'].($i+1))->getValue();
$value=trim($value);

//判断是否主键
if(!empty($v['field'])&&in_array($v['field'],$key_field_arr)){
$where[$v['field']]=$value;
}

//判断是否可修改字段
if(!empty($v['field'])&&in_array($v['field'],$yes_field_arr)){

//判断是否下拉选择的字段
if(in_array($v['field'],$field_type_select_fields)){
if(in_array($v['field'],['is_zzpks'])){
$value=$BaseModel->getWhetherKeyByName($value);
}else{
$value=$StudentModel->getSelectKeyByName(strtoupper($v['field']),$value);

}
}
$save_data[$v['field']]=$value;
}

}

$save_data['update_time']=time();
$save_data['opt_time']=time();
$save_data['opt_userid']=session('LOGIN_UID');
$save_data['opt_idcard']=session('user_idcard');
$save_data['opt_name']=session('username');
$save_data['is_excel']=BaseModel::WHETHER_YES;
$save_data['is_detail']=BaseModel::WHETHER_YES;

$where['delete_time']=0;
$where['class_id']=['in',session('charge_class_id')];
$where['graduation']=BaseModel::WHETHER_NO;//未毕业学生可以更新
$save_res=$StudentModel->where($where)->update($save_data);
if(!$save_res){
$error_idcards[]=$where['idcard'];
}
}

$StudentExcelLogModel->addData(['filepath'=>$file_name,'content'=>implode(',',$error_idcards)]);
$msg['code']=0;
$msg['msg']='上传成功';
//$msg['data']=$info->getSaveName();

returnjson($msg);
}else{
$msg['code']=1;
$msg['msg']=$file->getError();
//上传失败获取错误信息
}


returnjson($msg);

studentModel

<?php
/**
*CreatedbyPhpStorm.
*User:eq
*Date:2021/7/17
*Time:11:18
*/

namespaceapp\index\model;

usethink\Model;
usethink\Db;

classStudentextendsModel
{

protected$table='zy_student';
//字段标志
constFIELD_FLAG_YES=1;//可以修改
constFIELD_FLAG_NO=2;//禁止修改
constFIELD_FLAG_KEY=3;//主键

constFIELD_TYPE_TEXT=1;//文字输入
constFIELD_TYPE_SELECT=2;//下拉类型
publicstatic$FIELD=[
['title'=>'姓名','field'=>'name','flag'=>self::FIELD_FLAG_NO,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'性别','field'=>'sex','flag'=>self::FIELD_FLAG_NO,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'出生日期','field'=>'birth_date','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'学部','field'=>'grade_name','flag'=>self::FIELD_FLAG_NO,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'班级','field'=>'class_name','flag'=>self::FIELD_FLAG_NO,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'年级','field'=>'school_year','flag'=>self::FIELD_FLAG_NO,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'证件类型','field'=>'idtype','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'证件号','field'=>'idcard','flag'=>self::FIELD_FLAG_KEY,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'学籍号','field'=>'qgxjh','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'学号','field'=>'stu_no','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'民族','field'=>'nation','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'户籍详细地址','field'=>'hjxxdz','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'户籍所属派出所','field'=>'hjsspcs','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'家庭现地址','field'=>'jtxdz','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'政治面貌','field'=>'zzmm','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'健康状况','field'=>'jkzk','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'身心特殊体质','field'=>'sxtstz','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'禁忌症','field'=>'jjz','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'学生电话','field'=>'phone','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'学生来源','field'=>'xsly','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'招生对象','field'=>'zsdx','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'监护人姓名','field'=>'jhrxm','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'与监护人关系','field'=>'yjhrgx','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'监护人mob','field'=>'jhrlxdh','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'初中毕业学校','field'=>'czbyxx','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'初中毕业学校所在地','field'=>'czbyxxszd','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'中考准考证号','field'=>'zkzkzh','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'中考考生号','field'=>'zkksh','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'中考考试总分','field'=>'zkkszf','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'就读方式','field'=>'jdfs','flag'=>self::FIELD_FLAG_NO,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'宿舍号','field'=>'ssh','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'在校情况','field'=>'zxqk','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'血型','field'=>'xx','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'军训成绩','field'=>'jx','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'是否享受国家中职贫困生资助','field'=>'is_zzpks','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_SELECT],
['title'=>'毕业后升学院校','field'=>'byhsxyx','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'就业单位','field'=>'jydw','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'技能测试分','field'=>'jncsf','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'学考总分','field'=>'xkzf','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'实习单位','field'=>'sxdw','flag'=>self::FIELD_FLAG_YES,'type'=>self::FIELD_TYPE_TEXT],
['title'=>'是否毕业','field'=>'graduation','flag'=>self::FIELD_FLAG_NO,'type'=>self::FIELD_TYPE_SELECT],
];

/**
*获取字段
*/
publicfunctiongetFields($flag=0,$type=0)
{
$stu_fields=[];
foreach(self::$FIELDas$k=>$v){
if($flag>0&&$type>0){
if($v['flag']==$flag&&$v['type']==$type){
$stu_fields[]=$v;
}
}elseif($flag){
if($v['flag']==$flag){
$stu_fields[]=$v;
}
}elseif($type){
if($v['type']==$type){
$stu_fields[]=$v;
}
}


}
return$stu_fields;
}


/**
*通过title获取字段field
*/
publicfunctiongetFieldByTitle($title=''){
$field='';
foreach(self::$FIELDas$k=>$v){
if($v['title']==$title){
$field=$v['field'];
}
}
return$field;
}

/**
*通过名称获取下拉的key
**/
publicfunctiongetSelectKeyByName($field,$name)
{
$fleid=strtoupper($field);
$key='';
foreach(self::$$fieldas$k=>$v){
if($v==$name){
$key=$k;
break;
}
}
return$key;
}

publicstatic$SEX=[
1=>'男',
2=>'女'
];

constSEX_NAN=1;
constSEX_NV=2;

publicfunctiongetSex($value){
if($value==-1){
$arr=[];
foreach(self::$SEXas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$SEX[$value];
}
}

//在校情况
publicstatic$ZXQK=[
22=>'正常在校',
23=>'实习',
24=>'退学',
25=>'休学',
];

constZXQK_ZC=22;
constZXQK_SX=23;
constZXQK_TX=24;
constZXQK_XX=25;

publicfunctiongetZxqk($value){
if($value==-1){
$arr=[];
foreach(self::$ZXQKas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$ZXQK[$value];
}
}


//政治面貌
publicstatic$ZZMM=[
11=>'中国共产主义青年团团员',
12=>'群众',
];

constZZMM_TY=11;
constZZMM_QZ=12;

publicfunctiongetZzmm($value){
if($value==-1){
$arr=[];
foreach(self::$ZZMMas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$ZZMM[$value];
}
}

//健康状况
publicstatic$JKZK=[
13=>'健康或良好',
14=>'一般或较弱',
15=>'有慢性病',
16=>'残疾',
];

constJKZK_LH=13;
constJKZK_YB=14;
constJKZK_MXB=15;
constJKZK_CJ=16;

publicfunctiongetJkzk($value){
if($value==-1){
$arr=[];
foreach(self::$JKZKas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$JKZK[$value];
}
}

//学生来源
publicstatic$XSLY=[
17=>'应届',
18=>'非应届',
];

constXSLY_YJ=17;
constXSLY_FYJ=18;

publicfunctiongetXsly($value){
if($value==-1){
$arr=[];
foreach(self::$XSLYas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$XSLY[$value];
}
}

//招生对象
publicstatic$ZSDX=[
19=>'应届初中毕业生',
20=>'应届高中毕业生',
21=>'往届初中毕业生',
];

constZSDX_YJC=19;
constZSDX_YJG=20;
constZSDX_FYJC=21;

publicfunctiongetZsdx($value){
if($value==-1){
$arr=[];
foreach(self::$ZSDXas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$ZSDX[$value];
}
}

//证件类型
publicstatic$IDTYPE=[
3=>'居民证件号',
4=>'香特区护照/信息证明',
5=>'澳特区护照/信息证明',
6=>'台湾居民来往大陆通行证',
7=>'境外永久居住证',
8=>'护照',
9=>'户口薄',
10=>'其他',
];



constIDTYPE_SFZ=3;
constIDTYPE_XG=4;
constIDTYPE_AM=5;
constIDTYPE_TW=6;
constIDTYPE_JW=7;
constIDTYPE_HZ=8;
constIDTYPE_HKP=9;
constIDTYPE_QT=10;

publicfunctiongetIdtype($value){
if($value==-1){
$arr=[];
foreach(self::$IDTYPEas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$IDTYPE[$value];
}
}

//血型
publicstatic$XX=[
26=>'未知血型',
27=>'A血型',
28=>'B血型',
29=>'AB血型',
30=>'O血型',
31=>'RH阳性血型',
32=>'RH阴性血型',
33=>'HLA血型',
//34=>'未定血型',
];

constXX_UN=26;
constXX_A=27;
constXX_B=28;
constXX_AB=29;
constXX_O=30;
constXX_RHY=31;
constXX_RHN=32;
constXX_HLA=33;
//constXX_WD=34;

publicfunctiongetXx($value){
if($value==-1){
$arr=[];
foreach(self::$XXas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$XX[$value];
}
}


//就读方式
publicstatic$JDFS=[
713=>'住校',
714=>'走读',
];

constJDFS_ZX=713;
constJDFS_ZD=714;


publicfunctiongetJdfs($value){
if($value==-1){
$arr=[];
foreach(self::$JDFSas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$JDFS[$value];
}
}


//个人荣誉等级
publicstatic$GRRY=[
715=>'国家级',
716=>'省级',
717=>'市级',
718=>'区县级',
719=>'校级',
];

constGRRY_GUO=715;
constGRRY_SHEN=716;
constGRRY_SHI=717;
constGRRY_QU=718;
constGRRY_XIAO=719;


publicfunctiongetGrry($value){
if($value==-1){
$arr=[];
foreach(self::$GRRYas$k=>$v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return$arr;
}else{
returnself::$GRRY[$value];
}
}

//publicfunctiongetYsTypeAttr($value)
//{
//$type=['01'=>'中午','02'=>'下午','03'=>'晚上','04'=>'早上','05'=>'其他'];
//$vArr=explode(',',$value);
//$attr=[];
//if(count($vArr)>0){
//foreach($vArras$item){
//$attr[]=$type['0'.$item];
//}
//}
//returncount($attr)>0?implode(',',$attr):'';
//}

/**
*查询学生信息
**/
publicfunctiongetInfo($where)
{
$info=self::where($where)->find();
return$info;
}

//查询学生信息
publicfunctiongetStudentInfoById($id)
{

$where['id']=$id;
$info=self::where($where)->find();
return$info;
}
}

相关推荐

SaaS的续费率为什么那么低？
中国SaaS产业表面风光无限为什么说很难盈利
Code Review有什么好处？
什么是Istio? Istio究竟是干嘛的？
linux mv命令怎么用

阅读 +