public ActionResult Import(Models.ClassStudent.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())
{
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));
}
//将DataTable转为List
foreach (System.Data.DataRow dr in dt.Rows)
{
var dto = new Dto.ClassStudent.Import()
{
ClassName = dr["行政班名称"].ConvertToString(),
StudentCode = dr["学生学号"].ConvertToString(),
StudentName = dr["学生姓名"].ConvertToString(),
ClassGroupName = dr["小组名称"].ConvertToString()
};
vm.ImportEdit.Add(dto);
}
vm.ImportEdit.RemoveAll(d =>
string.IsNullOrEmpty(d.ClassName) &&
string.IsNullOrEmpty(d.StudentCode) &&
string.IsNullOrEmpty(d.StudentName));
if (vm.ImportEdit.Count == 0)
{
ModelState.AddModelError("", "未读取到任何有效数据!");
return(View(vm));
}
var year = (from p in db.Table <Basis.Entity.tbYear>()
where p.IsDisable == true
select p).FirstOrDefault();
var classList = (from p in db.Table <Basis.Entity.tbClass>()
//where p.tbYear.Id == year.Id
select p).ToList();
var studentList = db.Table <Student.Entity.tbStudent>().ToList();
var classStudentList = db.Table <Basis.Entity.tbClassStudent>()
.Include(d => d.tbClass).Include(d => d.tbStudent).ToList();
var classGroupList = db.Table <Basis.Entity.tbClassGroup>().ToList();
foreach (var item in vm.ImportEdit)
{
if (string.IsNullOrEmpty(item.ClassName))
{
item.Error = item.Error + "行政班名称不能为空!";
}
if (string.IsNullOrEmpty(item.StudentCode))
{
item.Error = item.Error + "学生学号不能为空!";
}
if (string.IsNullOrEmpty(item.StudentName))
{
item.Error = item.Error + "学生姓名不能为空!";
}
if (studentList.Where(d => d.StudentCode == item.StudentCode && d.StudentName == item.StudentName).Count() == 0)
{
item.Error = item.Error + "学生姓名和学生学号不匹配!";
}
if (classList.Where(d => d.ClassName == item.ClassName).Count() == 0)
{
item.Error = item.Error + "行政班不存在!";
}
}
if (vm.ImportEdit.Where(d => string.IsNullOrEmpty(d.Error) == false).Count() > 0)
{
vm.ImportEdit.RemoveAll(d => string.IsNullOrEmpty(d.Error));
return(View(vm));
}
//数据导入，判断关键字，存在的数据做更新，不存在的做新增
foreach (var item in vm.ImportEdit)
{
if (classStudentList.Where(d => d.tbClass.ClassName == item.ClassName && d.tbStudent.StudentCode == item.StudentCode).Count() > 0)
{
item.Error = "数据已存在!";
vm.ImportEdit.RemoveAll(d => string.IsNullOrEmpty(d.Error));
return(View(vm));
}
else
{
var tb = new Basis.Entity.tbClassStudent();
tb.tbClass = classList.Where(d => d.ClassName == item.ClassName).FirstOrDefault();
tb.tbStudent = studentList.Where(d => d.StudentName == item.StudentName && d.StudentCode == item.StudentCode).FirstOrDefault();
db.Set <Basis.Entity.tbClassStudent>().Add(tb);
}
}
if (db.SaveChanges() > 0)
{
vm.ImportEdit.RemoveAll(d => string.IsNullOrEmpty(d.Error));
XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("批量导入了行政班学生");
vm.Status = true;
}
}
}
return(View(vm));
}
public ActionResult Import()
{
var vm = new Models.ClassStudent.Import();
return(View(vm));
}
