public ActionResult Import(Models.StudyRoomStudent.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 dtoStudyRoomStudent = new Dto.StudyRoomStudent.Import()
{
StudyName = Convert.ToString(dr["晚自习名称"]),
RoomName = Convert.ToString(dr["教室"]),
StudentCode = Convert.ToString(dr["学号"]),
StudentName = Convert.ToString(dr["姓名"])
};
if (vm.ImportList.Where(d => d.StudyName == dtoStudyRoomStudent.StudyName &&
d.RoomName == dtoStudyRoomStudent.RoomName &&
d.StudentCode == dtoStudyRoomStudent.StudentCode &&
d.StudentName == dtoStudyRoomStudent.StudentName).Count() == 0)
{
vm.ImportList.Add(dtoStudyRoomStudent);
}
}
vm.ImportList.RemoveAll(d =>
string.IsNullOrEmpty(d.StudyName) &&
string.IsNullOrEmpty(d.RoomName) &&
string.IsNullOrEmpty(d.StudentCode) &&
string.IsNullOrEmpty(d.StudentName)
);
if (vm.ImportList.Count == 0)
{
ModelState.AddModelError("", "Excel未读取到任何有效数据!");
return(View(vm));
}
#endregion
#region 3、Excel格式校验
//学生列表
var StudyRoomStudentList = (from p in db.Table <Study.Entity.tbStudyRoomStudent>()
.Include(d => d.tbStudent)
.Include(d => d.tbStudy)
.Include(d => d.tbRoom)
select p).ToList();
//学生列表
var StudentList = (from p in db.Table <Student.Entity.tbStudent>() select p).ToList();
foreach (var item in vm.ImportList)
{
if (string.IsNullOrEmpty(item.StudyName))
{
item.Error = item.Error + "晚自习名称不能为空!";
continue;
}
if (string.IsNullOrEmpty(item.RoomName))
{
item.Error = item.Error + "教室不能为空!";
continue;
}
if (string.IsNullOrEmpty(item.StudentCode))
{
item.Error = item.Error + "学号不能为空!";
continue;
}
else
{
if (StudentList.Where(d => d.StudentCode == item.StudentCode).Count() == decimal.Zero)
{
item.Error = item.Error + "学生不存在数据库!";
continue;
}
}
if (string.IsNullOrEmpty(item.StudentName))
{
item.Error = item.Error + "姓名不能为空!";
continue;
}
else
{
if (StudentList.Where(d => d.StudentName == item.StudentName).Count() == decimal.Zero)
{
item.Error = item.Error + "学生不存在数据库!";
continue;
}
}
if (StudyRoomStudentList.Where(d => d.tbStudy.Id == vm.StudyId && d.tbRoom.Id == vm.RoomId && d.tbStudent.StudentCode == item.StudentCode).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 4、Excel执行导入
var addStudyRoomStudentList = new List <Study.Entity.tbStudyRoomStudent>();
foreach (var item in vm.ImportList)
{
Study.Entity.tbStudyRoomStudent tb = null;
if (StudyRoomStudentList.Where(d => d.tbStudy.Id == vm.StudyId && d.tbRoom.Id == vm.RoomId && d.tbStudent.StudentCode == item.StudentCode).Count() == decimal.Zero)
{
tb = new Study.Entity.tbStudyRoomStudent();
tb.tbStudy = db.Set <Study.Entity.tbStudy>().Find(vm.StudyId);
tb.tbRoom = db.Set <Basis.Entity.tbRoom>().Find(vm.RoomId);
tb.tbStudent = db.Table <Student.Entity.tbStudent>().Where(d => d.StudentCode == item.StudentCode).FirstOrDefault();
addStudyRoomStudentList.Add(tb);
}
}
db.Set <Study.Entity.tbStudyRoomStudent>().AddRange(addStudyRoomStudentList);
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.StudyRoomStudent.Import();
return(View(vm));
}
