public ActionResult Import(Models.DormStudent.Import vm) { vm.ImportList = new List <Dto.DormStudent.Import>(); 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 数据 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()) == false) { Text += a + ","; } } if (string.IsNullOrEmpty(Text) == false) { ModelState.AddModelError("", "上传的EXCEL内容与预期不一致,缺少对应的字段:" + Text); return(View(vm)); } foreach (System.Data.DataRow dr in dt.Rows) { var dto = new Dto.DormStudent.Import() { DormName = dr["住宿名称"].ConvertToString(), RoomName = dr["宿舍名称"].ConvertToString(), BuildName = dr["宿舍楼"].ConvertToString(), StudentCode = dr["学生学号"].ConvertToString(), StudentName = dr["学生姓名"].ConvertToString() }; if (vm.ImportList.Where(d => d.DormName == dto.DormName && d.RoomName == dto.RoomName && d.BuildName == dto.BuildName && d.StudentCode == dto.StudentCode && d.StudentName == dto.StudentName).Any() == false) { vm.ImportList.Add(dto); } } vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.RoomName) && string.IsNullOrEmpty(d.DormName) && string.IsNullOrEmpty(d.BuildName) && string.IsNullOrEmpty(d.StudentName) && string.IsNullOrEmpty(d.StudentCode)); if (vm.ImportList.Count == 0) { ModelState.AddModelError("", "未读取到任何有效数据!"); return(View(vm)); } #endregion var RoomList = db.Table <Basis.Entity.tbRoom>() .Include(d => d.tbBuild).ToList(); var BuildList = db.Table <Basis.Entity.tbBuild>().ToList(); var dormStudentList = db.Table <Dorm.Entity.tbDormStudent>() .Include(d => d.tbStudent) .Include(d => d.tbRoom) .Include(d => d.tbRoom.tbBuild).ToList(); var studentList = db.Table <Student.Entity.tbStudent>().ToList(); var dormList = db.Table <Dorm.Entity.tbDorm>().ToList(); #region 验证数据格式是否正确 foreach (var item in vm.ImportList) { if (string.IsNullOrEmpty(item.RoomName) || string.IsNullOrEmpty(item.DormName) || string.IsNullOrEmpty(item.BuildName) || string.IsNullOrEmpty(item.StudentName) || string.IsNullOrEmpty(item.StudentCode)) { item.Error += "字段不能为空!"; } if (studentList.Where(d => d.StudentCode == item.StudentCode && d.StudentName == item.StudentName).Any() == false) { item.Error += "学生不存在;"; } if (RoomList.Where(d => d.RoomName == item.RoomName && d.tbBuild.BuildName == item.BuildName).Any() == false) { item.Error += "宿舍不存在;"; } if (!vm.IsUpdate && dormStudentList.Where(d => d.tbStudent.StudentCode == item.StudentCode).Count() > 0) { item.Error += "系统中已存在该记录!"; } } 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 数据导入 List <Dorm.Entity.tbDormStudent> tbDormStudentList = new List <Dorm.Entity.tbDormStudent>(); foreach (var v in vm.ImportList) { if (dormStudentList.Where(d => d.tbStudent.StudentCode == v.StudentCode && d.tbStudent.StudentName == v.StudentName).Count() > 0) { if (vm.IsUpdate) { var tb = dormStudentList.Where(d => d.tbStudent.StudentCode == v.StudentCode && d.tbStudent.StudentName == v.StudentName).FirstOrDefault(); tb.tbRoom = RoomList.Where(d => d.RoomName == v.RoomName && d.tbBuild.BuildName == v.BuildName).FirstOrDefault(); tb.tbDorm = dormList.Where(d => d.DormName == v.DormName).FirstOrDefault(); } } else { var tb = new Dorm.Entity.tbDormStudent(); tb.tbDorm = dormList.Where(d => d.DormName == v.DormName).FirstOrDefault(); tb.tbStudent = studentList.Where(d => d.StudentCode == v.StudentCode && d.StudentName == v.StudentName).FirstOrDefault(); tb.tbRoom = RoomList.Where(d => d.RoomName == v.RoomName && d.tbBuild.BuildName == v.BuildName).FirstOrDefault(); tbDormStudentList.Add(tb); } } #endregion db.Set <Dorm.Entity.tbDormStudent>().AddRange(tbDormStudentList); if (db.SaveChanges() > 0) { vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error)); XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("导入宿舍信息"); vm.Status = true; } } } return(View(vm)); }
public ActionResult Import() { var vm = new Models.DormStudent.Import(); return(View(vm)); }