public ActionResult Import(Models.PerformItem.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 dtoTest = new Dto.PerformItem.Import() { No = Convert.ToString(dr["排序"]), PerformItemName = Convert.ToString(dr["评价项目"]), PerformGroupName = Convert.ToString(dr["评价分组"]), ScoreMax = Convert.ToString(dr["满分值"]), Rate = Convert.ToString(dr["折算比例"]), IsMany = Convert.ToString(dr["是否多次"]), IsSelect = Convert.ToString(dr["是否选项"]), DefaultValue = Convert.ToString(dr["默认分数"]), OptionNo = Convert.ToString(dr["选项排序"]), OptionName = Convert.ToString(dr["选项名称"]), OptionValue = Convert.ToString(dr["选项分值"]) }; if (vm.ImportList.Where(d => d.No == dtoTest.No && d.No == dtoTest.No && d.PerformItemName == dtoTest.PerformItemName && d.PerformGroupName == dtoTest.PerformGroupName && d.ScoreMax == dtoTest.ScoreMax && d.Rate == dtoTest.Rate && d.IsMany == dtoTest.IsMany && d.IsSelect == dtoTest.IsSelect && d.DefaultValue == dtoTest.DefaultValue && d.OptionNo == dtoTest.OptionNo && d.OptionName == dtoTest.OptionName && d.OptionValue == dtoTest.OptionValue).Count() == 0) { vm.ImportList.Add(dtoTest); } } vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.No) && string.IsNullOrEmpty(d.PerformItemName) && string.IsNullOrEmpty(d.PerformGroupName) && string.IsNullOrEmpty(d.ScoreMax) && string.IsNullOrEmpty(d.Rate) && string.IsNullOrEmpty(d.IsMany) && string.IsNullOrEmpty(d.IsSelect) && string.IsNullOrEmpty(d.DefaultValue) && string.IsNullOrEmpty(d.OptionNo) && string.IsNullOrEmpty(d.OptionName) && string.IsNullOrEmpty(d.OptionValue) ); if (vm.ImportList.Count == 0) { ModelState.AddModelError("", "Excel未读取到任何有效数据!"); return(View(vm)); } #endregion #region 3、Excel格式校验 //评价分组 var PerformGroup = (from p in db.Table <Perform.Entity.tbPerformGroup>() .Include(d => d.tbPerform) where p.tbPerform.Id == vm.PerformId && p.tbPerform.IsDeleted == false select p).ToList(); //评价内容 var PerformItemList = (from p in db.Table <Perform.Entity.tbPerformItem>() .Include(d => d.tbPerformGroup) where p.tbPerformGroup.tbPerform.Id == vm.PerformId && p.tbPerformGroup.IsDeleted == false && p.tbPerformGroup.tbPerform.IsDeleted == false select p).ToList(); //评价选项 var PerformOptionList = (from p in db.Table <Entity.tbPerformOption>() .Include(d => d.tbPerformItem) where p.tbPerformItem.tbPerformGroup.tbPerform.Id == vm.PerformId && p.tbPerformItem.IsDeleted == false && p.tbPerformItem.tbPerformGroup.IsDeleted == false && p.tbPerformItem.tbPerformGroup.tbPerform.IsDeleted == false select p).ToList(); //是否多次打分 var IsManyList = new List <string>() { "是", "否" }; //是否可选 var IsSelectList = new List <string>() { "文本框", "下拉框" }; foreach (var item in vm.ImportList) { int No = 0; if (int.TryParse(item.No, out No) == false || No <= 0) { item.Error = item.Error + "【排序】必须是正整数!"; continue; } if (string.IsNullOrEmpty(item.PerformItemName)) { item.Error = item.Error + "【评价项目】不能为空!"; continue; } if (string.IsNullOrEmpty(item.PerformGroupName)) { item.Error = item.Error + "【评价分组】不能为空!"; continue; } else { if (PerformGroup.Where(d => d.PerformGroupName == item.PerformGroupName).Count() == decimal.Zero) { item.Error = item.Error + "【评价分组】不存在数据库!"; continue; } } if (string.IsNullOrEmpty(item.ScoreMax)) { item.ScoreMax = "100"; } else { try { var ScoreMax = Convert.ToDecimal(item.ScoreMax); } catch { item.Error = item.Error + "【满分值】必须是正确的数字格式!"; continue; } } if (string.IsNullOrEmpty(item.Rate)) { item.Rate = "100"; } else { try { var rate = Convert.ToDecimal(item.Rate); } catch { item.Error = item.Error + "【折算比例】必须是正确的数字格式!"; continue; } } if (string.IsNullOrEmpty(item.DefaultValue)) { item.DefaultValue = "0"; } else { try { var DefaultValue = Convert.ToDecimal(item.DefaultValue); } catch { item.Error = item.Error + "【默认分数】必须是正确的数字格式!"; continue; } } #region 是否多次 if (string.IsNullOrEmpty(item.IsMany)) { item.Error = item.Error + "【是否多次】不能为空!"; continue; } else { if (IsManyList.Where(d => d == item.IsMany).Count() == decimal.Zero) { item.Error = item.Error + "【是否多次】必须是:是、否!"; continue; } else { if (item.IsMany == "是") { #region 是否选项 if (string.IsNullOrEmpty(item.IsSelect)) { item.Error = item.Error + "【是否选项】不能为空!"; continue; } else { if (IsSelectList.Where(d => d == item.IsSelect).Count() == decimal.Zero) { item.Error = item.Error + "【是否选项】必须是:文本框、下拉框!"; continue; } else { if (item.IsSelect == "下拉框") { #region 项排序 int KpiNo = 0; if (int.TryParse(item.OptionNo, out KpiNo) == false || KpiNo <= 0) { item.Error = item.Error + "【选项排序】必须是正整数!"; continue; } #endregion #region 项名称 if (string.IsNullOrEmpty(item.OptionName)) { item.Error = item.Error + "【选项名称】不能为空!"; continue; } #endregion #region 项分值 if (string.IsNullOrEmpty(item.OptionValue)) { item.Error = item.Error + "【选项分值】不能为空!"; continue; } else { try { var KpiOptionValue = Convert.ToDecimal(item.OptionValue); } catch { item.Error = item.Error + "【选项分值】必须是正确的数字格式!"; continue; } } #endregion } } } #endregion } else { #region 是否选项 if (string.IsNullOrEmpty(item.IsSelect)) { item.Error = item.Error + "【是否选项】不能为空!"; continue; } else { if (item.IsSelect != "文本框") { item.Error = item.Error + "【是否选项】必须是:文本框!"; continue; } } #endregion } } } #endregion if (vm.IsUpdate) { if (PerformItemList.Where(d => d.PerformItemName == item.PerformItemName && d.tbPerformGroup.PerformGroupName == item.PerformGroupName).Count() > 1) { item.Error += "系统中该评价内容数据存在重复,无法确认需要更新的记录!"; continue; } } else { if (PerformItemList.Where(d => d.PerformItemName == item.PerformItemName && d.tbPerformGroup.PerformGroupName == item.PerformGroupName).Count() > 0) { 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 5、Excel执行导入 var addPerformItemList = new List <Perform.Entity.tbPerformItem>(); var addPerformOptionList = new List <Entity.tbPerformOption>(); foreach (var item in vm.ImportList) { Perform.Entity.tbPerformItem tb = null; if (PerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).Count() > 0) { if (vm.IsUpdate) { tb = PerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).FirstOrDefault(); tb.No = item.No.ConvertToInt(); tb.PerformItemName = item.PerformItemName; tb.IsMany = item.IsMany == "是" ? true : false; tb.IsSelect = item.IsSelect == "文本框" ? false : true; tb.DefaultValue = item.DefaultValue.ConvertToDecimal(); if (!string.IsNullOrEmpty(item.PerformGroupName)) { tb.tbPerformGroup = PerformGroup.Where(d => d.PerformGroupName == item.PerformGroupName).FirstOrDefault(); } tb.ScoreMax = item.ScoreMax; tb.Rate = Convert.ToDecimal(item.Rate); } } else { if (addPerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).Count() == decimal.Zero) { tb = new Perform.Entity.tbPerformItem(); tb.No = item.No.ConvertToInt(); tb.PerformItemName = item.PerformItemName; tb.IsMany = item.IsMany == "是" ? true : false; tb.IsSelect = item.IsSelect == "文本框" ? false : true; tb.DefaultValue = item.DefaultValue.ConvertToDecimal(); if (!string.IsNullOrEmpty(item.PerformGroupName)) { tb.tbPerformGroup = PerformGroup.Where(d => d.PerformGroupName == item.PerformGroupName).FirstOrDefault(); } tb.ScoreMax = item.ScoreMax; tb.Rate = Convert.ToDecimal(item.Rate); addPerformItemList.Add(tb); } else { tb = addPerformItemList.Where(d => d.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.PerformItemName == item.PerformItemName).FirstOrDefault(); } } if (string.IsNullOrEmpty(item.OptionName) == false && item.IsMany == "是" && item.IsSelect == "下拉框") { if (PerformOptionList.Where(d => d.tbPerformItem.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.tbPerformItem.PerformItemName == item.PerformItemName && d.OptionName == item.OptionName).Count() == decimal.Zero) { if (addPerformOptionList.Where(d => d.tbPerformItem.tbPerformGroup.PerformGroupName == item.PerformGroupName && d.tbPerformItem.PerformItemName == item.PerformItemName && d.OptionName == item.OptionName).Count() == decimal.Zero) { var tbOption = new Entity.tbPerformOption(); tbOption.No = item.OptionNo.ConvertToInt(); tbOption.OptionName = item.OptionName; tbOption.OptionValue = item.OptionValue.ConvertToDecimal(); tbOption.tbPerformItem = tb; addPerformOptionList.Add(tbOption); } } } } db.Set <Entity.tbPerformItem>().AddRange(addPerformItemList); db.Set <Entity.tbPerformOption>().AddRange(addPerformOptionList); 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 Import() { var vm = new Models.PerformItem.Import(); return(View(vm)); }