public ActionResult Import() { var vm = new Models.Course.Import(); vm.ImportList = new List <Dto.Course.Import>(); return(View(vm)); }
public ActionResult Import(Models.Course.Import vm) { vm.ImportList = new List <Dto.Course.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()) { 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)) { Text += a + ","; } } if (!string.IsNullOrEmpty(Text)) { ModelState.AddModelError("", "上传的EXCEL内容与预期不一致,缺少对应的字段:" + Text); return(View(vm)); } #region 将DataTable转为List foreach (System.Data.DataRow dr in dt.Rows) { var dto = new Dto.Course.Import() { CourseName = dr["课程名称"].ToString().Trim(), CourseCode = dr["课程编码"].ConvertToString(), CourseDomainName = Convert.ToString(dr["课程领域"]), CourseGroupName = Convert.ToString(dr["课程分组"]), CourseTypeName = dr["课程类型"].ToString().Trim(), SubjectName = dr["科目名称"].ToString().Trim(), CourseNameEn = Convert.ToString(dr["英文名"]), Hour = Convert.ToString(dr["课时"]), Point = Convert.ToString(dr["学分"]), Remark = Convert.ToString(dr["课程说明"]), }; vm.ImportList.Add(dto); } vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.CourseName) && string.IsNullOrEmpty(d.CourseCode) && string.IsNullOrEmpty(d.CourseTypeName) && string.IsNullOrEmpty(d.CourseGroupName) && string.IsNullOrEmpty(d.CourseDomainName) && string.IsNullOrEmpty(d.SubjectName) && string.IsNullOrEmpty(d.CourseNameEn) && string.IsNullOrEmpty(d.Hour) && string.IsNullOrEmpty(d.Point) && string.IsNullOrEmpty(d.Remark) ); if (vm.ImportList.Count == 0) { ModelState.AddModelError("", "未读取到任何有效数据!"); return(View(vm)); } #endregion var subjectList = db.Table <Course.Entity.tbSubject>().ToList(); var courseTypeList = db.Table <Course.Entity.tbCourseType>().ToList(); var courseList = (from p in db.Table <Course.Entity.tbCourse>() .Include(d => d.tbSubject) .Include(d => d.tbCourseType) select p).ToList(); var courseDomainList = db.Table <Course.Entity.tbCourseDomain>().ToList(); var courseGroupList = db.Table <Course.Entity.tbCourseGroup>().ToList(); #region 验证数据格式是否正确 foreach (var item in vm.ImportList) { if (string.IsNullOrEmpty(item.CourseName)) { item.Error = item.Error + "课程名称不能为空!"; } if (string.IsNullOrEmpty(item.CourseTypeName)) { item.Error = item.Error + "课程类型不能为空!"; } else { if (courseTypeList.Where(d => d.CourseTypeName == item.CourseTypeName).Count() <= 0) { item.Error = item.Error + "课程类型不存在;"; } } if (string.IsNullOrEmpty(item.SubjectName)) { item.Error = item.Error + "科目名称不能为空!"; } else { if (subjectList.Where(d => d.SubjectName == item.SubjectName).Count() <= 0) { item.Error = item.Error + "科目不存在;"; } } if (string.IsNullOrEmpty(item.CourseDomainName) == false) { if (courseDomainList.Where(d => d.CourseDomainName == item.CourseDomainName).Any() == false) { item.Error = item.Error + "课程领域不存在;"; } } if (string.IsNullOrEmpty(item.CourseGroupName) == false) { if (courseGroupList.Where(d => d.CourseGroupName == item.CourseGroupName).Any() == false) { item.Error = item.Error + "课程分组不存在;"; } } if (vm.ImportList.Where(d => d.CourseName == item.CourseName).Count() > 1) { item.Error = item.Error + "该条数据重复!"; } int Hour = 0; if ((int.TryParse(item.Hour, out Hour) == false || Hour < 0)) { item.Error = item.Error + "课时必须为正整数!"; } decimal Point = 0; if ((decimal.TryParse(item.Point, out Point) == false || Point < 0)) { item.Error = item.Error + "学分必须为数字!"; } if (vm.IsUpdate == false && courseList.Where(d => d.CourseName == item.CourseName).Count() > 0) { item.Error = 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 foreach (var item in vm.ImportList) { #region 数据导入,判断关键字,存在的数据做更新,不存在的做新增 if (vm.IsUpdate && courseList.Where(d => d.CourseName == item.CourseName).Count() > 0) { if (courseList.Where(d => d.CourseName == item.CourseName).Count() > 1) { item.Error = item.Error + "系统中该数据存在重复,无法确认需要更新的记录!"; } else { var tb = courseList.Where(d => d.CourseName == item.CourseName).FirstOrDefault(); tb.CourseName = item.CourseName; tb.CourseCode = item.CourseCode; tb.CourseNameEn = item.CourseNameEn; tb.tbSubject = subjectList.Where(d => d.SubjectName == item.SubjectName).FirstOrDefault(); tb.tbCourseType = courseTypeList.Where(d => d.CourseTypeName == item.CourseTypeName).FirstOrDefault(); tb.Point = item.Point.ConvertToDecimal(); tb.Hour = item.Hour.ConvertToInt(); tb.Remark = item.Remark; tb.IsLevel = false; if (string.IsNullOrEmpty(item.CourseDomainName) == false) { tb.tbCourseDomain = courseDomainList.Where(d => d.CourseDomainName == item.CourseDomainName).FirstOrDefault(); } if (string.IsNullOrEmpty(item.CourseGroupName) == false) { tb.tbCourseGroup = courseGroupList.Where(d => d.CourseGroupName == item.CourseGroupName).FirstOrDefault(); } } } else { var tb = new Course.Entity.tbCourse(); tb.CourseName = item.CourseName; tb.CourseCode = item.CourseCode; tb.CourseNameEn = item.CourseNameEn; tb.tbSubject = subjectList.Where(d => d.SubjectName == item.SubjectName).FirstOrDefault(); tb.tbCourseType = courseTypeList.Where(d => d.CourseTypeName == item.CourseTypeName).FirstOrDefault(); tb.Point = item.Point.ConvertToDecimal(); tb.Hour = item.Hour.ConvertToInt(); tb.Remark = item.Remark; tb.IsLevel = false; if (string.IsNullOrEmpty(item.CourseDomainName) == false) { tb.tbCourseDomain = courseDomainList.Where(d => d.CourseDomainName == item.CourseDomainName).FirstOrDefault(); } if (string.IsNullOrEmpty(item.CourseGroupName) == false) { tb.tbCourseGroup = courseGroupList.Where(d => d.CourseGroupName == item.CourseGroupName).FirstOrDefault(); } db.Set <Course.Entity.tbCourse>().Add(tb); } #endregion } if (db.SaveChanges() > 0) { vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error)); XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("批量添加了课程"); vm.Status = true; } } } return(View(vm)); }