public ActionResult Import(Models.StudentChange.Import vm) { using (var db = new XkSystem.Models.DbContext()) { #region excel文件,并转为DataTable var file = Request.Files[nameof(vm.UploadFile)]; var fileSave = System.IO.Path.GetTempFileName(); file.SaveAs(fileSave); 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)); } #endregion #region 将DataTable转为List foreach (System.Data.DataRow dr in dt.Rows) { var dto = new Dto.StudentChange.Import() { #region StudentCode = dr["学号"].ConvertToString(), StudentName = dr["姓名"].ConvertToString(), ChangeTypeName = dr["调动类型"].ConvertToString(), Remark = dr["备注"].ConvertToString(), #endregion }; if (vm.ImportList.Where(d => d.StudentName == dto.StudentName && d.StudentCode == dto.StudentCode && d.ChangeTypeName == dto.ChangeTypeName).Count() == 0) { vm.ImportList.Add(dto); } } if (vm.ImportList.Count == 0) { ModelState.AddModelError("", "未读取到任何有效数据!"); return(View(vm)); } #endregion var studentList = db.Table <Student.Entity.tbStudent>().ToList(); var changeTypeList = db.Table <Student.Entity.tbStudentChangeType>().ToList(); var studentChangeList = db.Table <Student.Entity.tbStudentChange>() .Include(d => d.tbStudentChangeType) .Include(d => d.tbStudent).ToList(); #region 验证数据格式是否正确 foreach (var v in vm.ImportList) { if (string.IsNullOrEmpty(v.ChangeTypeName)) { v.Error += "调动类型不能为空;"; } if (changeTypeList.Where(d => d.StudentChangeTypeName == v.ChangeTypeName).Count() == 0) { v.Error += "调动类型不存在;"; } if (string.IsNullOrEmpty(v.StudentName)) { v.Error += "学生姓名不能为空;"; } if (string.IsNullOrEmpty(v.StudentCode)) { v.Error += "学生学号不能为空;"; } if (studentList.Where(d => d.StudentCode == v.StudentCode && d.StudentName == v.StudentName).Count() == 0) { v.Error += "学生不存在;"; } if (!vm.IsUpdate && studentChangeList.Where(d => d.tbStudent.StudentCode == v.StudentCode && d.tbStudentChangeType.StudentChangeTypeName == v.ChangeTypeName).Count() > 0) { v.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 数据导入,判断关键字,存在的数据做更新,不存在的做新增 var tbStudentChangeList = new List <Student.Entity.tbStudentChange>(); foreach (var v in vm.ImportList) { var tb = new Student.Entity.tbStudentChange(); if (studentChangeList.Where(d => d.tbStudent.StudentCode == v.StudentCode && d.tbStudentChangeType.StudentChangeTypeName == v.ChangeTypeName).Count() > 0) { #region 修改 if (vm.IsUpdate) { tb = studentChangeList.Where(d => d.tbStudent.StudentCode == v.StudentCode && d.tbStudentChangeType.StudentChangeTypeName == v.ChangeTypeName).FirstOrDefault(); tb.tbStudent = studentList.Where(d => d.StudentCode == v.StudentCode && d.StudentName == v.StudentName).FirstOrDefault(); tb.Remark = v.Remark; tb.tbSysUser = db.Set <Sys.Entity.tbSysUser>().Find(Code.Common.UserId); if (!string.IsNullOrEmpty(v.ChangeTypeName)) { tb.tbStudentChangeType = changeTypeList.Where(d => d.StudentChangeTypeName == v.ChangeTypeName).FirstOrDefault(); } } #endregion } else { #region 新增 tb = new Student.Entity.tbStudentChange(); tb.InputDate = DateTime.Now; tb.Remark = v.Remark; tb.tbSysUser = db.Set <Sys.Entity.tbSysUser>().Find(Code.Common.UserId); tb.tbStudent = studentList.Where(d => d.StudentCode == v.StudentCode && d.StudentName == v.StudentName).FirstOrDefault(); if (!string.IsNullOrEmpty(v.ChangeTypeName)) { tb.tbStudentChangeType = changeTypeList.Where(d => d.StudentChangeTypeName == v.ChangeTypeName).FirstOrDefault(); } tbStudentChangeList.Add(tb); #endregion } } db.Set <Student.Entity.tbStudentChange>().AddRange(tbStudentChangeList); if (db.SaveChanges() > 0) { vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error)); XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("导入学生异动"); vm.Status = true; } #endregion } return(View(vm)); }
public ActionResult Import() { var vm = new Models.StudentChange.Import(); return(View(vm)); }