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));
}
