protected void btn_importEvent_Click(object sender, EventArgs e)
{
string fileExtension = null;
string file = import_upload.FileName.Replace(" ", "");
string filename = System.IO.Path.GetFileNameWithoutExtension(file) + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss").Replace(" ", "").Replace("/", "").Replace(":", "").Replace("-", "");
bool fileOK = false;
string savepath = Server.MapPath("~/Upload/zdts_import/");
//判断是否已选择上传文件
if (import_upload.HasFile)
{
//获得文件后缀
fileExtension = System.IO.Path.GetExtension(import_upload.FileName).ToLower();
//允许上传文件后缀
String[] allowedExtensions = { ".xls", ".xlsx" };
//判断文件后缀是否被允许上传
for (int i = 0; i < allowedExtensions.Length; i++)
{
if (fileExtension == allowedExtensions[i])
{
fileOK = true;
filename += fileExtension;
}
}
//文件类型合法
if (fileOK)
{
try
{
if (!Directory.Exists(savepath))
{
Directory.CreateDirectory(savepath);
}
import_upload.PostedFile.SaveAs(savepath + filename);
//DataTable dt = ReadExcelToTable(savepath + filename); //读取Excel文件（.xls和.xlsx格式）
DataTable dt = ExcelToDataTable(GetNewDataTable(), null, savepath + filename, false); //读取Excel文件（.xls和.xlsx格式）
int success = 0;
int fail = 0;
int repeat = 0;
StringBuilder sb_failIndex = new StringBuilder();
StringBuilder sb_repeatIndex = new StringBuilder();
string errorInfo = "。";
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//判重，通过姓名，证件类型，证件号码进行唯一性判别别。
if (Student.FindOne(CK.K["Name"] == dr["Name"].ToString() && CK.K["IdentityNo"] == dr["IdentityNo"].ToString() && CK.K["IsDelete"] == false) != null)
{
repeat++;
sb_repeatIndex.Append((i + 3) + "." + dr["Name"].ToString() + "、");
continue;
}
StringBuilder sb_DateLostInfo = new StringBuilder();//日期无法确定，填写为1900/01/01，需添加备注
//基本信息
bool sex = false;
DateTime birthday = new DateTime();
int studyTypeId = 0;
DateTime joinSchoolDate = new DateTime();
//家庭主要成员
bool fa_sex_1 = false;
DateTime fa_birthday_1 = new DateTime();
bool fa_sex_2 = false;
DateTime fa_birthday_2 = new DateTime();
bool fa_sex_3 = false;
DateTime fa_birthday_3 = new DateTime();
bool fa_sex_4 = false;
DateTime fa_birthday_4 = new DateTime();
bool fa_sex_5 = false;
DateTime fa_birthday_5 = new DateTime();
//学习经历
DateTime Exp_start_1 = new DateTime();
DateTime Exp_End_1 = new DateTime();
DateTime Exp_start_2 = new DateTime();
DateTime Exp_End_2 = new DateTime();
DateTime Exp_start_3 = new DateTime();
DateTime Exp_End_3 = new DateTime();
DateTime Exp_start_4 = new DateTime();
DateTime Exp_End_4 = new DateTime();
DateTime Exp_start_5 = new DateTime();
DateTime Exp_End_5 = new DateTime();
try//检验输入是否规范
{
if (dr["Sex"].Equals("男") || dr["Sex"].Equals("女"))
{
sex = dr["Sex"].Equals("女");//女true 男false
}
else
{
throw new Exception("性别输入错误!");
}
birthday = Convert.ToDateTime(dr["Birthday"].ToString());
if (birthday.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Name"].ToString() + "'出生日期无法查实，");
}
studyTypeId = (int)(StudyType.FindOne(CK.K["StudyTypeText"] == dr["StudyTypeText"]).StudyTypeId);
joinSchoolDate = Convert.ToDateTime(dr["JoinSchoolDate"]);
if (joinSchoolDate.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Name"].ToString() + "'入学日期无法查实，");
}
//if (tw_address.Length <= 3 || !tw_address.Substring(0, 3).Equals("台湾省") || (TaiWanCity.FindOne(CK.K["CityName"] == tw_address.Substring(3, tw_address.Length - 3)) == null))
//{
// throw new Exception("台湾户籍地输入错误!");
//}
if (!dr["Fa_Name_1"].ToString().IsNullOrEmpty())
{
if (dr["Fa_Sex_1"].Equals("男") || dr["Fa_Sex_1"].Equals("女"))
{
fa_sex_1 = dr["Fa_Sex_1"].Equals("女");//女true 男false
}
else
{
throw new Exception("性别输入错误!");
}
fa_birthday_1 = Convert.ToDateTime(dr["Fa_Birthday_1"].ToString());
if (fa_birthday_1.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Fa_Name_1"].ToString() + "'出生日期无法查实，");
}
}
if (!dr["Fa_Name_2"].ToString().IsNullOrEmpty())
{
if (dr["Fa_Sex_2"].Equals("男") || dr["Fa_Sex_2"].Equals("女"))
{
fa_sex_2 = dr["Fa_Sex_2"].Equals("女");//女true 男false
}
else
{
throw new Exception("性别输入错误!");
}
fa_birthday_2 = Convert.ToDateTime(dr["Fa_Birthday_2"].ToString());
if (fa_birthday_2.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Fa_Name_2"].ToString() + "'出生日期无法查实，");
}
}
if (!dr["Fa_Name_3"].ToString().IsNullOrEmpty())
{
if (dr["Fa_Sex_3"].Equals("男") || dr["Fa_Sex_3"].Equals("女"))
{
fa_sex_3 = dr["Fa_Sex_3"].Equals("女");//女true 男false
}
else
{
throw new Exception("性别输入错误!");
}
fa_birthday_3 = Convert.ToDateTime(dr["Fa_Birthday_3"].ToString());
if (fa_birthday_3.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Fa_Name_3"].ToString() + "'出生日期无法查实，");
}
}
if (!dr["Fa_Name_4"].ToString().IsNullOrEmpty())
{
if (dr["Fa_Sex_4"].Equals("男") || dr["Fa_Sex_4"].Equals("女"))
{
fa_sex_4 = dr["Fa_Sex_4"].Equals("女");//女true 男false
}
else
{
throw new Exception("性别输入错误!");
}
fa_birthday_4 = Convert.ToDateTime(dr["Fa_Birthday_4"].ToString());
if (fa_birthday_4.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Fa_Name_4"].ToString() + "'出生日期无法查实，");
}
}
if (!dr["Fa_Name_5"].ToString().IsNullOrEmpty())
{
if (dr["Fa_Sex_5"].Equals("男") || dr["Fa_Sex_5"].Equals("女"))
{
fa_sex_5 = dr["Fa_Sex_5"].Equals("女");//女true 男false
}
else
{
throw new Exception("性别输入错误!");
}
fa_birthday_5 = Convert.ToDateTime(dr["Fa_Birthday_5"].ToString());
if (fa_birthday_5.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Fa_Name_5"].ToString() + "'出生日期无法查实，");
}
}
if (!dr["Exp_School_1"].ToString().IsNullOrEmpty())
{
Exp_start_1 = Convert.ToDateTime(dr["Exp_StartTime_1"].ToString());
if (Exp_start_1.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_1"].ToString() + "'起始日期无法查实，");
}
Exp_End_1 = Convert.ToDateTime(dr["Exp_EndTime_1"].ToString());
if (Exp_End_1.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_1"].ToString() + "'截止日期无法查实，");
}
}
if (!dr["Exp_School_2"].ToString().IsNullOrEmpty())
{
Exp_start_2 = Convert.ToDateTime(dr["Exp_StartTime_2"].ToString());
if (Exp_start_2.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_2"].ToString() + "'起始日期无法查实，");
}
Exp_End_2 = Convert.ToDateTime(dr["Exp_EndTime_2"].ToString());
if (Exp_End_2.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_2"].ToString() + "'截止日期无法查实，");
}
}
if (!dr["Exp_School_3"].ToString().IsNullOrEmpty())
{
Exp_start_3 = Convert.ToDateTime(dr["Exp_StartTime_3"].ToString());
if (Exp_start_3.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_3"].ToString() + "'起始日期无法查实，");
}
Exp_End_3 = Convert.ToDateTime(dr["Exp_EndTime_3"].ToString());
if (Exp_End_3.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_3"].ToString() + "'截止日期无法查实，");
}
}
if (!dr["Exp_School_4"].ToString().IsNullOrEmpty())
{
Exp_start_4 = Convert.ToDateTime(dr["Exp_StartTime_4"].ToString());
if (Exp_start_4.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_4"].ToString() + "'起始日期无法查实，");
}
Exp_End_4 = Convert.ToDateTime(dr["Exp_EndTime_4"].ToString());
if (Exp_End_4.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_4"].ToString() + "'截止日期无法查实，");
}
}
if (!dr["Exp_School_5"].ToString().IsNullOrEmpty())
{
Exp_start_5 = Convert.ToDateTime(dr["Exp_StartTime_5"].ToString());
if (Exp_start_5.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_5"].ToString() + "'起始日期无法查实，");
}
Exp_End_5 = Convert.ToDateTime(dr["Exp_EndTime_5"].ToString());
if (Exp_End_5.Equals(new DateTime(1900, 1, 1)))
{
sb_DateLostInfo.Append("'" + dr["Exp_School_5"].ToString() + "'截止日期无法查实，");
}
}
if (sb_DateLostInfo.Length > 0)
{
sb_DateLostInfo.Remove(sb_DateLostInfo.Length - 1, 1);
sb_DateLostInfo.Append("。");
}
}
catch (Exception ex)
{
fail++;
sb_failIndex.Append((i + 3) + "." + dr["Name"].ToString() + "、");
text_importLog("第" + (i + 3) + "行（" + dr["Name"].ToString() + "）添加失败：" + ex.Message);
continue;
}
try
{
DbEntry.UsingTransaction(delegate
{
int stuId = StudentBusiness.AddStudentForImport(dr["Name"].ToString(), sex, dr["IdentityNo"].ToString(), birthday, dr["School"].ToString(), dr["Major"].ToString(), studyTypeId, joinSchoolDate, dr["SystemOfEdu"].ToString(), dr["PhoneNo"].ToString(), dr["AddressOfH"].ToString(), sb_DateLostInfo + dr["Tip"].ToString());
if (!dr["Fa_Name_1"].ToString().IsNullOrEmpty())
{
bool result = FamilyOfStudentBusiness.AddFamilyOfStudent(stuId, dr["Fa_Name_1"].ToString(), fa_sex_1, fa_birthday_1, dr["Fa_Relationship_1"].ToString(), dr["Fa_Company_1"].ToString(), dr["Fa_Tip_1"].ToString());
if (!result)
{
throw new Exception("家庭主要成员添加失败！");
}
}
if (!dr["Fa_Name_2"].ToString().IsNullOrEmpty())
{
bool result = FamilyOfStudentBusiness.AddFamilyOfStudent(stuId, dr["Fa_Name_2"].ToString(), fa_sex_2, fa_birthday_2, dr["Fa_Relationship_2"].ToString(), dr["Fa_Company_2"].ToString(), dr["Fa_Tip_2"].ToString());
if (!result)
{
throw new Exception("家庭主要成员添加失败！");
}
}
if (!dr["Fa_Name_3"].ToString().IsNullOrEmpty())
{
bool result = FamilyOfStudentBusiness.AddFamilyOfStudent(stuId, dr["Fa_Name_3"].ToString(), fa_sex_3, fa_birthday_3, dr["Fa_Relationship_3"].ToString(), dr["Fa_Company_3"].ToString(), dr["Fa_Tip_3"].ToString());
if (!result)
{
throw new Exception("家庭主要成员添加失败！");
}
}
if (!dr["Fa_Name_4"].ToString().IsNullOrEmpty())
{
bool result = FamilyOfStudentBusiness.AddFamilyOfStudent(stuId, dr["Fa_Name_4"].ToString(), fa_sex_4, fa_birthday_4, dr["Fa_Relationship_4"].ToString(), dr["Fa_Company_4"].ToString(), dr["Fa_Tip_4"].ToString());
if (!result)
{
throw new Exception("家庭主要成员添加失败！");
}
}
if (!dr["Fa_Name_5"].ToString().IsNullOrEmpty())
{
bool result = FamilyOfStudentBusiness.AddFamilyOfStudent(stuId, dr["Fa_Name_5"].ToString(), fa_sex_5, fa_birthday_5, dr["Fa_Relationship_5"].ToString(), dr["Fa_Company_5"].ToString(), dr["Fa_Tip_5"].ToString());
if (!result)
{
throw new Exception("家庭主要成员添加失败！");
}
}
if (!dr["Exp_School_1"].ToString().IsNullOrEmpty())
{
bool result = ExperienceBusiness.AddExperience(stuId, Exp_start_1, Exp_End_1, dr["Exp_School_1"].ToString());
if (!result)
{
throw new Exception("学习经历添加失败！");
}
}
if (!dr["Exp_School_2"].ToString().IsNullOrEmpty())
{
bool result = ExperienceBusiness.AddExperience(stuId, Exp_start_2, Exp_End_2, dr["Exp_School_2"].ToString());
if (!result)
{
throw new Exception("学习经历添加失败！");
}
}
if (!dr["Exp_School_3"].ToString().IsNullOrEmpty())
{
bool result = ExperienceBusiness.AddExperience(stuId, Exp_start_3, Exp_End_3, dr["Exp_School_3"].ToString());
if (!result)
{
throw new Exception("学习经历添加失败！");
}
}
if (!dr["Exp_School_4"].ToString().IsNullOrEmpty())
{
bool result = ExperienceBusiness.AddExperience(stuId, Exp_start_4, Exp_End_4, dr["Exp_School_4"].ToString());
if (!result)
{
throw new Exception("学习经历添加失败！");
}
}
if (!dr["Exp_School_5"].ToString().IsNullOrEmpty())
{
bool result = ExperienceBusiness.AddExperience(stuId, Exp_start_5, Exp_End_5, dr["Exp_School_5"].ToString());
if (!result)
{
throw new Exception("学习经历添加失败！");
}
}
});
success++;
continue;
}
catch (Exception ex)
{
fail++;
sb_failIndex.Append((i + 3) + "." + dr["Name"].ToString() + "、");
text_importLog("第" + (i + 3) + "行（" + dr["Name"].ToString() + "）添加失败：" + ex.Message);
continue;
}
}
string txt_log = "";
if (sb_failIndex.Length > 0)
{
sb_failIndex.Remove(sb_failIndex.Length - 1, 1);
errorInfo = "，数据错误导致失败excel序号（包含标题行）为：" + sb_failIndex.ToString();
}
if (sb_repeatIndex.Length > 0)
{
sb_repeatIndex.Remove(sb_repeatIndex.Length - 1, 1);
errorInfo = "，数据重复导致失败excel序号（包含标题行）为：" + sb_repeatIndex.ToString();
}
RefreshView(0);
string showMsg = "导入完成！总共" + (success + fail + repeat) + "条，其中成功导入" + success + "条，数据错误导致失败" + fail + "条，数据重复导致失败" + repeat + "条" + errorInfo;
text_importLog("导入完成！总共" + (success + fail + repeat) + "条，其中成功导入" + success + "条，数据错误导致失败" + fail + "条，数据重复导致失败" + repeat + "条。" + txt_log);
ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "提示", "layer.alert('导入详情已存入日志文件：" + @Server.MapPath("~/import_log.txt") + "。" + showMsg + "')", true);
//ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "提示", "layer.alert('" + "导入完成！总共" + (success + fail) + "条，其中成功导入" + success + "条，失败导入" + fail + "条" + errorInfo + "')", true);
return;
}
catch (Exception ex)
{
text(ex.ToString());
ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "提示", "layer.alert('无法上传！')", true);
return;
}
}
else
{
ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "提示", "layer.alert('未识别的文件类型！请使用.xls或.xlsx文件进行导入！')", true);
return;
}
}
else
{
ScriptManager.RegisterStartupScript(UpdatePanel1, this.GetType(), "提示", "layer.alert('请选择文件！')", true);
return;
}
}