public ActionResult ImportStudent(Models.ExamRoom.Import vm) { if (ModelState.IsValid) { var file = Request.Files[nameof(vm.UploadFile)]; var fileSave = System.IO.Path.GetTempFileName(); file.SaveAs(fileSave); using (var db = new XkSystem.Models.DbContext()) { #region 1、Excel模版校验 if (Code.Common.GetFileType(file.FileName) != Code.FileType.Excel) { ModelState.AddModelError("", "上传的文件不是正确的EXCLE文件!"); return(View(vm)); } var dt = Code.NpoiHelper.ExcelToDataTable(fileSave, System.IO.Path.GetExtension(file.FileName), string.Empty); if (dt == null) { ModelState.AddModelError("", "无法读取上传的文件,请检查文件格式是否正确!"); return(View(vm)); } var tbList = new List <string>() { "考场名称", "学生学号", "学生姓名" }; var Text = string.Empty; foreach (var a in tbList) { if (!dt.Columns.Contains(a.ToString())) { Text += a + ","; } } if (!string.IsNullOrEmpty(Text)) { ModelState.AddModelError("", "上传的EXCEL内容与预期不一致,缺少对应的字段:" + Text); return(View(vm)); } #endregion #region 2、Excel数据读取 foreach (System.Data.DataRow dr in dt.Rows) { var dto = new Dto.ExamRoom.Import() { ExamRoomName = Convert.ToString(dr["考场名称"]), StudentCode = Convert.ToString(dr["学号"]), StudentName = Convert.ToString(dr["学生姓名"]), }; if (vm.ImportList.Where(d => d.ExamRoomName == dto.ExamRoomName && d.StudentCode == dto.StudentCode && d.StudentName == dto.StudentName ).Count() == 0) { vm.ImportList.Add(dto); } } vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.ExamRoomName) && string.IsNullOrEmpty(d.StudentCode) && string.IsNullOrEmpty(d.StudentName) ); if (vm.ImportList.Count == 0) { ModelState.AddModelError("", "Excel未读取到任何有效数据!"); return(View(vm)); } #endregion #region 3、Excel格式校验 var studentList = (from p in db.Table <Student.Entity.tbStudent>() select p).ToList(); var examRoomList = (from p in db.Table <Exam.Entity.tbExamRoom>() where p.tbExamCourse.Id == vm.ExamCourseId select p).ToList(); foreach (var item in vm.ImportList) { if (string.IsNullOrEmpty(item.ExamRoomName)) { item.Error = item.Error + "考场名称不能为空!"; continue; } else { if (examRoomList.Where(d => d.ExamRoomName == item.ExamRoomName).Count() == decimal.Zero) { item.Error = item.Error + "该考试课程的考场不存在数据库!"; continue; } } if (string.IsNullOrEmpty(item.StudentName)) { item.Error = item.Error + "学生姓名不能为空!"; continue; } if (studentList.Where(d => d.StudentCode == item.StudentCode && d.StudentName == item.StudentName).Count() == 0) { item.Error = item.Error + "学生姓名和学生学号不匹配!"; } if (string.IsNullOrEmpty(item.StudentCode)) { item.Error = item.Error + "学生学号不能为空!"; continue; } else { if (studentList.Where(d => d.StudentCode == item.StudentCode).Count() == decimal.Zero) { item.Error = item.Error + "该学号不存在数据库!"; continue; } } } if (vm.ImportList.Where(d => string.IsNullOrEmpty(d.Error) == false).Count() > 0) { vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error)); return(View(vm)); } #endregion #region 4、Excel执行导入 var examStudentList = (from p in db.Table <Exam.Entity.tbExamStudent>() where p.tbStudent.IsDeleted == false && p.tbExamRoom.IsDeleted == false select p).ToList(); var addList = new List <Exam.Entity.tbExamStudent>(); foreach (var item in vm.ImportList) { Exam.Entity.tbExamStudent tb = null; tb = examStudentList.Where(d => d.tbStudent.StudentCode == item.StudentCode && d.tbExamRoom.ExamRoomName == item.ExamRoomName).Select(d => d).FirstOrDefault(); if (tb == null) { tb = new Exam.Entity.tbExamStudent(); tb.tbExamRoom = (from p in db.Table <Exam.Entity.tbExamRoom>() where p.ExamRoomName == item.ExamRoomName && p.tbExamCourse.Id == vm.ExamCourseId select p).FirstOrDefault(); tb.tbStudent = (from p in db.Table <Student.Entity.tbStudent>() where p.StudentCode == item.StudentCode select p).FirstOrDefault(); addList.Add(tb); } } db.Set <Exam.Entity.tbExamStudent>().AddRange(addList); if (db.SaveChanges() > 0) { XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("批量导入考场学生"); vm.Status = true; } #endregion } } vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error)); return(View(vm)); }
public ActionResult ImportStudent() { var vm = new Models.ExamRoom.Import(); return(View(vm)); }