public ActionResult Import(Models.SurveyItem.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 dtOptionColumns = new List <string>();
foreach (var columns in dt.Columns)
{
if (!dtOptionColumns.Contains(columns.ToString()) && !tbList.Contains(columns.ToString()))
{
dtOptionColumns.Add(columns.ToString());
}
}
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.SurveyItem.Import()
{
No = Convert.ToString(dr["排序"]),
SurveyItemName = Convert.ToString(dr["评价内容"]),
SurveyGroupName = Convert.ToString(dr["评价分组"]),
SurveyItemType = Convert.ToString(dr["试题类型"]),
IsVertical = Convert.ToString(dr["是否纵向"]),
ImportOptionList = GetImportOptionList(dr, dtOptionColumns)
};
if (vm.ImportList.Where(d => d.No == dtoTest.No &&
d.SurveyItemName == dtoTest.SurveyItemName &&
d.SurveyGroupName == dtoTest.SurveyGroupName &&
d.SurveyItemType == dtoTest.SurveyItemType &&
d.IsVertical == dtoTest.IsVertical).Count() == 0)
{
vm.ImportList.Add(dtoTest);
}
}
vm.ImportList.RemoveAll(d =>
string.IsNullOrEmpty(d.No) &&
string.IsNullOrEmpty(d.SurveyItemName) &&
string.IsNullOrEmpty(d.SurveyGroupName) &&
string.IsNullOrEmpty(d.SurveyItemType) &&
string.IsNullOrEmpty(d.IsVertical)
);
if (vm.ImportList.Count == 0)
{
ModelState.AddModelError("", "Excel未读取到任何有效数据!");
return(View(vm));
}
#endregion
#region 3、Excel格式校验
//评价分组
var SurveyGroup = (from p in db.Table <Entity.tbSurveyGroup>()
.Include(d => d.tbSurvey)
where p.tbSurvey.Id == vm.SurveyId &&
p.tbSurvey.IsDeleted == false
select p).ToList();
//评价内容
var SurveyItemList = (from p in db.Table <Entity.tbSurveyItem>()
.Include(d => d.tbSurveyGroup)
where p.tbSurveyGroup.tbSurvey.Id == vm.SurveyId &&
p.tbSurveyGroup.IsDeleted == false &&
p.tbSurveyGroup.tbSurvey.IsDeleted == false
select p).ToList();
//评价选项
var SurveyOptionList = (from p in db.Table <Entity.tbSurveyOption>()
.Include(d => d.tbSurveyItem)
.Include(d => d.tbSurveyItem.tbSurveyGroup)
where p.tbSurveyItem.tbSurveyGroup.tbSurvey.Id == vm.SurveyId &&
p.tbSurveyItem.IsDeleted == false &&
p.tbSurveyItem.tbSurveyGroup.IsDeleted == false &&
p.tbSurveyItem.tbSurveyGroup.tbSurvey.IsDeleted == false
select p).ToList();
//试题类型
var SurveyItemTypeList = typeof(Code.EnumHelper.SurveyItemType).ToItemList();
//是否纵向
var IsVerticalList = 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.SurveyItemName))
{
item.Error = item.Error + "【评价内容】不能为空!";
continue;
}
if (string.IsNullOrEmpty(item.SurveyGroupName))
{
item.Error = item.Error + "【评价分组】不能为空!";
continue;
}
else
{
if (SurveyGroup.Where(d => d.SurveyGroupName == item.SurveyGroupName).Count() == decimal.Zero)
{
item.Error = item.Error + "【评价分组】不存在数据库!";
continue;
}
}
if (string.IsNullOrEmpty(item.SurveyItemType))
{
item.SurveyItemType = "单选题";
}
else
{
if (SurveyItemTypeList.Where(d => d.Text == item.SurveyItemType).Count() == decimal.Zero)
{
item.Error += "试题类型必须是：单选题、多选题、问答题!";
continue;
}
}
if (string.IsNullOrEmpty(item.IsVertical))
{
item.IsVertical = "否";
}
else
{
if (IsVerticalList.Where(d => d == item.IsVertical).Count() == decimal.Zero)
{
item.Error += "是否纵向必须是：【是】或者【否】!";
continue;
}
}
if (item.ImportOptionList.Count > decimal.Zero)
{
var errorStr = "";
foreach (var a in item.ImportOptionList)
{
if (string.IsNullOrEmpty(a.SurveyOptionValue))
{
a.SurveyOptionValue = "0";
}
else
{
try
{
var value = Convert.ToDecimal(a.SurveyOptionValue);
}
catch
{
errorStr = errorStr + "【" + a.SurveyColumnName + "】的分数必须是正确的数字格式!";
continue;
}
}
}
if (string.IsNullOrEmpty(errorStr) == false)
{
item.Error += errorStr;
continue;
}
}
if (vm.IsUpdate)
{
if (SurveyItemList.Where(d => d.tbSurveyGroup.SurveyGroupName == item.SurveyGroupName && d.SurveyItemName == item.SurveyItemName).Count() > 1)
{
item.Error += "系统中该评价内容数据存在重复，无法确认需要更新的记录!";
continue;
}
}
else
{
if (SurveyItemList.Where(d => d.tbSurveyGroup.SurveyGroupName == item.SurveyGroupName && d.SurveyItemName == item.SurveyItemName).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 addSurveyItemList = new List <Entity.tbSurveyItem>();
var addSurveyOptionList = new List <Entity.tbSurveyOption>();
foreach (var item in vm.ImportList)
{
Entity.tbSurveyItem tb = null;
if (SurveyItemList.Where(d => d.tbSurveyGroup.SurveyGroupName == item.SurveyGroupName && d.SurveyItemName == item.SurveyItemName).Count() > 0)
{
if (vm.IsUpdate)
{
tb = SurveyItemList.Where(d => d.tbSurveyGroup.SurveyGroupName == item.SurveyGroupName && d.SurveyItemName == item.SurveyItemName).FirstOrDefault();
tb.No = item.No.ConvertToInt();
tb.SurveyItemName = item.SurveyItemName;
#region 添加外键
if (!string.IsNullOrEmpty(item.SurveyGroupName))
{
tb.tbSurveyGroup = SurveyGroup.Where(d => d.SurveyGroupName == item.SurveyGroupName).FirstOrDefault();
}
#endregion
tb.IsVertical = item.IsVertical == "是" ? true : false;
if (item.SurveyItemType == "单选题")
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.Radio;
}
else if (item.SurveyItemType == "多选题")
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.CheckBox;
}
else if (item.SurveyItemType == "问答题")
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.TextBox;
}
else
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.Radio;
}
}
}
else
{
tb = new Entity.tbSurveyItem();
tb.No = item.No.ConvertToInt();
tb.SurveyItemName = item.SurveyItemName;
if (!string.IsNullOrEmpty(item.SurveyGroupName))
{
tb.tbSurveyGroup = SurveyGroup.Where(d => d.SurveyGroupName == item.SurveyGroupName).FirstOrDefault();
}
tb.IsVertical = item.IsVertical == "是" ? true : false;
if (item.SurveyItemType == "单选题")
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.Radio;
}
else if (item.SurveyItemType == "多选题")
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.CheckBox;
}
else if (item.SurveyItemType == "问答题")
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.TextBox;
}
else
{
tb.SurveyItemType = Code.EnumHelper.SurveyItemType.Radio;
}
addSurveyItemList.Add(tb);
}
var index = 0;
foreach (var option in item.ImportOptionList)
{
if (item.SurveyItemType == "问答" && index == 1)
{
break;
}
if (string.IsNullOrEmpty(option.SurveyOptionName) == false)
{
if (SurveyOptionList.Where(d => d.tbSurveyItem.tbSurveyGroup.SurveyGroupName == item.SurveyGroupName && d.tbSurveyItem.SurveyItemName == item.SurveyItemName && d.OptionName == option.SurveyOptionName).Count() == decimal.Zero)
{
index++;
var tbOption = new Entity.tbSurveyOption();
tbOption.OptionName = option.SurveyOptionName;
tbOption.tbSurveyItem = tb;
tbOption.OptionValue = option.SurveyOptionValue.ConvertToDecimal();
tbOption.No = index;
addSurveyOptionList.Add(tbOption);
}
}
}
}
db.Set <Entity.tbSurveyItem>().AddRange(addSurveyItemList);
db.Set <Entity.tbSurveyOption>().AddRange(addSurveyOptionList);
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.SurveyItem.Import();
return(View(vm));
}
