/**
* 导入数据及表格图片
*/
public function excelinsert()
{
if (request()->isPost()) {
vendor("PHPExcel.PHPExcel");
$objPHPExcel = new \PHPExcel();
$file = request()->file('file');
$info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'Excel'); //上传验证后缀名,以及上传之后移动的地址
if ($file) {
if ($info) {
$exclePath = $info->getSaveName();//获取文件名
$file_name = ROOT_PATH . 'public' . DS . 'Excel' . DS . $exclePath;//上传文件的地址
$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
if ($extension == 'xlsx') {
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
} else
if ($extension == 'xls') {
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
}
$obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');//加载文件内容,编码utf-8
$a = $obj_PHPExcel->getsheet(0);
$imageFilePath = ROOT_PATH . 'public/uploads/student/' . date('Ymd') . '/';//图片保存目录
$this->create_folders($imageFilePath);
foreach ($a->getDrawingCollection() as $img) {
list ($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($img->getCoordinates());//获取列与行号
$imageFileName = date('Ymd') . $startRow . mt_rand(100, 99999);
switch ($img->getMimeType()) {
//处理图片格式
case 'image/jpg':
case 'image/jpeg':
$imageFileName .= '.jpg';
imagejpeg($img->getImageResource(), $imageFilePath . $imageFileName);
break;
case 'image/gif':
$imageFileName .= '.gif';
imagegif($img->getImageResource(), $imageFilePath . $imageFileName);
break;
case 'image/png':
$imageFileName .= '.png';
imagepng($img->getImageResource(), $imageFilePath . $imageFileName);
break;
}
$tb_data[$startRow - 2]['to_image'] = $this->request->root() . '/uploads/student/' . date('Ymd') . '/' . $imageFileName;//追加到数组中去
}
$tb_data = array_column($tb_data,'to_image');//照片数组
$excel_array = $obj_PHPExcel->getsheet(0)->toArray();//转换为数组格式
for ($i = 0; $i < count($excel_array[0]); $i++) {
$student_field = Model('student_field')->where('remarks', $excel_array[0][$i])->field('field_name,remarks')->find();
if (!$student_field) {
return ajax_return_error('表头《' . $excel_array[0][$i] . '》不存在,请修改后再试!');
} else {
$excel_array[0][$i] = $student_field['field_name'];
}
}
$arraymatching = $this->arraymatching($excel_array[0]);
if($arraymatching != 10000){
return ajax_return_error("缺少《".$arraymatching."》数据,请修改后再试!");
}else{
$Header = $excel_array[0];
array_shift($excel_array);//删除第一个数组(标题);
$student_data = [];
foreach ($excel_array as $k => $v) {
foreach ($Header as $k1 => $v1) {
if($v1 == "sex"){
if($v[$k1] == '男'){
$student_data[$k][$v1] =1;
}else{
$student_data[$k][$v1] =2;
}
}else if($v1 == "birth_time"){
$student_data[$k][$v1] =strtotime($v[$k1]);
}else if($v1 == "teacher_children"){
if($v[$k1] == '否'){
$student_data[$k][$v1] =0;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "only_child"){
if($v[$k1] == '否'){
$student_data[$k][$v1] =0;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "single_parent"){
if($v[$k1] == '否'){
$student_data[$k][$v1] =0;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "military_force"){
if($v[$k1] == '否'){
$student_data[$k][$v1] =0;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "low_protection"){
if($v[$k1] == '否'){
$student_data[$k][$v1] =0;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "return_chinese"){
if($v[$k1] == '否'){
$student_data[$k][$v1] =0;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "orphan"){
if($v[$k1] == '否'){
$student_data[$k][$v1] =0;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "family_economy"){
if($v[$k1] == '一般困难'){
$student_data[$k][$v1] =2;
}else if($v[$k1] == '困难'){
$student_data[$k][$v1] =3;
}else if($v[$k1] == '非常困难'){
$student_data[$k][$v1] =4;
}else{
$student_data[$k][$v1] =1;
}
}else if($v1 == "photo"){
$student_data[$k][$v1] =$tb_data[$k];
}else if($v1 == "instructor_id"){
$instructor_id = Model('instructor')->where('nickname',$v[$k1])->field('id')->find();
if($instructor_id){
$student_data[$k][$v1] =$instructor_id['id'];
}else{
return ajax_return_error("管理员员《".$v[$k1]."》不存在,请修改后再试!");
}
}else if($v1 == "department_id"){
$department_id = Model('department')->where('department_name',$v[$k1])->field('id')->find();
if($department_id){
$student_data[$k][$v1] =$department_id['id'];
}else{
return ajax_return_error("所属院系《".$v[$k1]."》不存在,请修改后再试!");
}
}else if($v1 == "grade_id"){
$grade_id = Model('grade')->where('grade_name',$v[$k1])->field('id')->find();
if($grade_id){
$student_data[$k][$v1] =$grade_id['id'];
}else{
return ajax_return_error("所属年级《".$v[$k1]."》不存在,请修改后再试!");
}
}else if($v1 == "major_id"){
$major_id = Model('major')->where('major_name',$v[$k1])->field('id')->find();
if($major_id){
$student_data[$k][$v1] =$major_id['id'];
}else{
return ajax_return_error("所属专业《".$v[$k1]."》不存在,请修改后再试!");
}
}else if($v1 == "collective_id"){
$collective_id = Model('collective')->where('collective_name',$v[$k1])->field('id')->find();
if($collective_id){
$student_data[$k][$v1] =$collective_id['id'];
}else{
return ajax_return_error("所属班级《".$v[$k1]."》不存在,请修改后再试!");
}
}else if($v1 == "id_number"){
$student_data[$k]['password'] = md5(substr($v[$k1],-6));
$student_data[$k][$v1] =$v[$k1];
}else{
$student_data[$k][$v1] =$v[$k1];
}
}
}
$student_insert=[];
$student_update=[];
foreach ($student_data as $k=>$vo){
$thisstudent = Model('student')->where('student_id',$vo['student_id'])->field('id')->find();
if($thisstudent){
$student_update[$k] = $vo;
}else{
$student_insert[$k] = $vo;
}
}
$studentadd = Model('student')->insertAll($student_insert);
foreach ($student_update as $vo){
Model('student')->where('student_id',$vo['student_id'])->update($vo);
}
if ($studentadd) {
return ajax_return_adv('导入成功,总共导入'.count($student_insert).'条数据,更新了'.count($student_update).'条数据', 'index');
} else {
return ajax_return_error('导入失败,可能是部分数据相同,导致失败!', 'index');
}
}
} else {
$this->error("文件上传失败");
}
} else {
$this->success("未上传文件");
}
} else {
$this->success("未上传文件");
}
}