public ActionResult Import(Models.ClassAllotClass.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));
}
#region 将DataTable转为List
foreach (System.Data.DataRow dr in dt.Rows)
{
var dtoClass = new Dto.ClassAllotClass.Import()
{
No = Convert.ToString(dr["排序"]),
ClassName = Convert.ToString(dr["班级名称"]),
GradeName = Convert.ToString(dr["年级"]),
ClassTypeName = Convert.ToString(dr["班级类型"])
};
if (vm.ImportList.Where(d => d.No == dtoClass.No &&
d.ClassName == dtoClass.ClassName &&
d.GradeName == dtoClass.GradeName &&
d.ClassTypeName == dtoClass.ClassTypeName).Count() == 0)
{
vm.ImportList.Add(dtoClass);
}
}
vm.ImportList.RemoveAll(d =>
string.IsNullOrEmpty(d.No) &&
string.IsNullOrEmpty(d.ClassName) &&
string.IsNullOrEmpty(d.GradeName) &&
string.IsNullOrEmpty(d.ClassTypeName)
);
if (vm.ImportList.Count == 0)
{
ModelState.AddModelError("", "未读取到任何有效数据!");
return(View(vm));
}
#endregion
var gradeList = db.Table <Basis.Entity.tbGrade>().ToList();
var classTypeList = db.Table <Basis.Entity.tbClassType>().ToList();
var classAllotClassList = db.Table <Basis.Entity.tbClassAllotClass>().ToList();
var year = db.Table <Basis.Entity.tbYear>().Where(d => d.IsDefault).FirstOrDefault();
#region 验证数据格式是否正确
foreach (var item in vm.ImportList)
{
if (string.IsNullOrEmpty(item.ClassName))
{
item.Error = item.Error + "班级名称不能为空!";
}
int No = 0;
if (int.TryParse(item.No, out No) == false || No <= 0)
{
item.Error = item.Error + "排序必须是正整数!";
}
if (classTypeList.Where(d => d.ClassTypeName == item.ClassTypeName).Count() == 0)
{
item.Error += "班级类型不存在；";
}
if (gradeList.Where(d => d.GradeName == item.GradeName).Count() == 0)
{
item.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 addClassAllotClassList = new List <Basis.Entity.tbClassAllotClass>();
foreach (var item in vm.ImportList)
{
Basis.Entity.tbClassAllotClass tb = null;
if (classAllotClassList.Where(d => d.ClassName == item.ClassName && d.tbClassType.ClassTypeName == item.ClassTypeName && d.tbGrade.GradeName == item.GradeName).Count() > 0)
{
if (vm.IsUpdate)
{
#region 修改
tb = classAllotClassList.Where(d => d.ClassName == item.ClassName && d.tbClassType.ClassTypeName == item.ClassTypeName && d.tbGrade.GradeName == item.GradeName).FirstOrDefault();
tb.No = item.No.ConvertToInt();
tb.ClassName = item.ClassName;
tb.tbYear = year;
tb.tbGrade = gradeList.Where(d => d.GradeName.Equals(item.GradeName)).FirstOrDefault();
tb.tbClassType = classTypeList.Where(d => d.ClassTypeName.Equals(item.ClassTypeName)).FirstOrDefault();
#endregion
}
}
else
{
#region 新增行政班
tb = new Basis.Entity.tbClassAllotClass();
tb.No = item.No.ConvertToInt();
tb.ClassName = item.ClassName;
tb.tbYear = year;
tb.tbClassType = classTypeList.Where(d => d.ClassTypeName.Equals(item.ClassTypeName)).FirstOrDefault();
tb.tbGrade = gradeList.Where(d => d.GradeName.Equals(item.GradeName)).FirstOrDefault();
addClassAllotClassList.Add(tb);
#endregion
}
}
#endregion
db.Set <Basis.Entity.tbClassAllotClass>().AddRange(addClassAllotClassList);
if (db.SaveChanges() > 0)
{
XkSystem.Areas.Sys.Controllers.SysUserLogController.Insert("批量导入了行政班");
vm.Status = true;
}
}
}
vm.ImportList.RemoveAll(d => string.IsNullOrEmpty(d.Error));
return(View(vm));
}
public ActionResult Import()
{
var vm = new Models.ClassAllotClass.Import();
return(View(vm));
}
