# 注：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']='不能上传空文件！';
return json($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_all as $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;
return json($msg);
die();
}
$error_idcards=[];
for ($i = 1; $i < $highestRow; $i++) {
// $name = (string)$objPHPExcel->getActiveSheet()->getCell("A" . ($i + 1))->getValue(); //证件号
foreach($letter_all as $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();
return json($msg);
}else{
$msg['code']=1;
$msg['msg']=$file->getError();
// 上传失败获取错误信息
}
return json($msg);studentModel
<?php
/**
* Created by PhpStorm.
* User: eq
* Date: 2021/7/17
* Time: 11:18
*/
namespace app\index\model;
use think\Model;
use think\Db;
class Student extends Model
{
protected $table = 'zy_student';
// 字段标志
const FIELD_FLAG_YES=1;//可以修改
const FIELD_FLAG_NO=2; //禁止修改
const FIELD_FLAG_KEY=3;//主键
const FIELD_TYPE_TEXT=1; //文字输入
const FIELD_TYPE_SELECT=2; //下拉类型
public static $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],
];
/**
* 获取字段
*/
public function getFields($flag=0,$type=0)
{
$stu_fields=[];
foreach(self::$FIELD as $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
*/
public function getFieldByTitle($title=''){
$field='';
foreach(self::$FIELD as $k => $v){
if($v['title']==$title){
$field=$v['field'];
}
}
return $field;
}
/**
* 通过名称获取下拉的key
* */
public function getSelectKeyByName($field,$name)
{
$fleid=strtoupper($field);
$key='';
foreach(self::$$field as $k => $v){
if($v==$name){
$key=$k;
break;
}
}
return $key;
}
public static $SEX=[
1=>'男',
2=>'女'
] ;
const SEX_NAN=1;
const SEX_NV=2;
public function getSex($value){
if($value==-1){
$arr=[];
foreach(self::$SEX as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$SEX[$value];
}
}
// 在校情况
public static $ZXQK=[
22=>'正常在校',
23=>'实习',
24=>'退学',
25=>'休学',
];
const ZXQK_ZC=22;
const ZXQK_SX=23;
const ZXQK_TX=24;
const ZXQK_XX=25;
public function getZxqk($value){
if($value==-1){
$arr=[];
foreach(self::$ZXQK as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$ZXQK[$value];
}
}
// 政治面貌
public static $ZZMM=[
11=>'中国共产主义青年团团员',
12=>'群众',
];
const ZZMM_TY=11;
const ZZMM_QZ=12;
public function getZzmm($value){
if($value==-1){
$arr=[];
foreach(self::$ZZMM as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$ZZMM[$value];
}
}
// 健康状况
public static $JKZK=[
13=>'健康或良好',
14=>'一般或较弱',
15=>'有慢性病',
16=>'残疾',
];
const JKZK_LH=13;
const JKZK_YB=14;
const JKZK_MXB=15;
const JKZK_CJ=16;
public function getJkzk($value){
if($value==-1){
$arr=[];
foreach(self::$JKZK as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$JKZK[$value];
}
}
// 学生来源
public static $XSLY=[
17=>'应届',
18=>'非应届',
];
const XSLY_YJ=17;
const XSLY_FYJ=18;
public function getXsly($value){
if($value==-1){
$arr=[];
foreach(self::$XSLY as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$XSLY[$value];
}
}
// 招生对象
public static $ZSDX=[
19=>'应届初中毕业生',
20=>'应届高中毕业生',
21=>'往届初中毕业生',
];
const ZSDX_YJC=19;
const ZSDX_YJG=20;
const ZSDX_FYJC=21;
public function getZsdx($value){
if($value==-1){
$arr=[];
foreach(self::$ZSDX as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$ZSDX[$value];
}
}
// 证件类型
public static $IDTYPE=[
3=>'居民证件号',
4=>'香特区护照/信息证明',
5=>'澳特区护照/信息证明',
6=>'台湾居民来往大陆通行证',
7=>'境外永久居住证',
8=>'护照',
9=>'户口薄',
10=>'其他',
];
const IDTYPE_SFZ=3;
const IDTYPE_XG=4;
const IDTYPE_AM=5;
const IDTYPE_TW=6;
const IDTYPE_JW=7;
const IDTYPE_HZ=8;
const IDTYPE_HKP=9;
const IDTYPE_QT=10;
public function getIdtype($value){
if($value==-1){
$arr=[];
foreach(self::$IDTYPE as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$IDTYPE[$value];
}
}
// 血型
public static $XX=[
26=>'未知血型',
27=>'A血型',
28=>'B血型',
29=>'AB血型',
30=>'O血型',
31=>'RH阳性血型',
32=>'RH阴性血型',
33=>'HLA血型',
// 34=>'未定血型',
];
const XX_UN=26;
const XX_A=27;
const XX_B=28;
const XX_AB=29;
const XX_O=30;
const XX_RHY=31;
const XX_RHN=32;
const XX_HLA=33;
// const XX_WD=34;
public function getXx($value){
if($value==-1){
$arr=[];
foreach(self::$XX as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$XX[$value];
}
}
// 就读方式
public static $JDFS=[
713=>'住校',
714=>'走读',
];
const JDFS_ZX=713;
const JDFS_ZD=714;
public function getJdfs($value){
if($value==-1){
$arr=[];
foreach(self::$JDFS as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$JDFS[$value];
}
}
// 个人荣誉等级
public static $GRRY=[
715=>'国家级',
716=>'省级',
717=>'市级',
718=>'区县级',
719=>'校级',
];
const GRRY_GUO=715;
const GRRY_SHEN=716;
const GRRY_SHI=717;
const GRRY_QU=718;
const GRRY_XIAO=719;
public function getGrry($value){
if($value==-1){
$arr=[];
foreach(self::$GRRY as $k => $v){
$item=[];
$item['id']=$k;
$item['name']=$v;
array_push($arr,$item);
}
return $arr;
}else{
return self::$GRRY[$value];
}
}
// public function getYsTypeAttr($value)
// {
// $type = ['01' => '中午', '02' => '下午', '03' => '晚上', '04' => '早上', '05' => '其他'];
// $vArr = explode(',', $value);
// $attr = [];
// if (count($vArr) > 0) {
// foreach ($vArr as $item) {
// $attr[] = $type['0' . $item];
// }
// }
// return count($attr) > 0 ? implode(',', $attr) : '';
// }
/**
* 查询学生信息
* */
public function getInfo($where)
{
$info=self::where($where)->find();
return $info;
}
// 查询学生信息
public function getStudentInfoById($id)
{
$where['id']=$id;
$info=self::where($where)->find();
return $info;
}
}