private string ExcelToStudent()
{
/*---*/
var preStr = DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_";//文件前缀
var httpFile = Request.Files["fileUpload"];
bool flag = !(httpFile.ContentType != "application/vnd.ms-excel");
if (httpFile.ContentType != "application/octet-stream")
{
flag = false;
}
if (flag)
{
return "{\"error\":\"文件格式不正确！请先下载模板再导入数据！\",\"msg\":\"gzz\"}";
}
if (httpFile != null)
{
var fileName = httpFile.FileName;
const string fileUrl = "../../../UploadFile/temp/";
var uploadPath = Server.MapPath(fileUrl) + preStr + fileName;//上传路径
if (!Directory.Exists(Server.MapPath(fileUrl))) //文件夹不存在
{
Directory.CreateDirectory(Server.MapPath(fileUrl));
}
httpFile.SaveAs(uploadPath);//文件上传
fileName = fileName.Split('.')[];
var table = ExcelHelper.ExcelRead("temp", "Sheet1", preStr + fileName, out uploadPath);
if (table == null)
{
return "{\"error\":\"模版格式不正确！\",\"msg\":\"gzz\"}";
}
if (table.Rows.Count == )
{
return "{\"error\":\"此模版数据为空！\",\"msg\":\"gzz\"}";
}
if (table.Columns.Count != )
{
return "{\"error\":\"模版格式不正确！\",\"msg\":\"gzz\"}";
}
DataTable dataTable = GetTableSchema();
long countTime = SqlBulkCopyInsert(table, dataTable);
YFY.Web.App_Code.JLCommonBind.InsertActionLog(Session["master_name"].ToString(), "老师信息导入", "导入数据！合计" + (table.Rows.Count - count) + "条！");
if (!string.IsNullOrEmpty(schoolName))
{
return "{\"error\":\"\",\"msg\":\"" + schoolName.TrimEnd(',') + "不存在!成功导入" + (table.Rows.Count - count) + "条数据！\"}";
}
if (!string.IsNullOrEmpty(xueyuanName))
{
return "{\"error\":\"\",\"msg\":\"" + xueyuanName.TrimEnd(',') + "不存在!成功导入" + (table.Rows.Count - count) + "条数据！\"}";
}
return "{\"error\":\"\",\"msg\":\"成功导入" + (table.Rows.Count - count) + "条数据!\"}";
}
return "{\"error\":\"\",\"msg\":\"数据导入失败!\"}";
}
/// <summary>
/// 使用SqlBulkCopy方式插入数据
/// </summary>
/// <param name="dt">源数据</param>
/// <param name="dataTable">目标table</param>
/// <returns></returns>
private long SqlBulkCopyInsert(DataTable dt, DataTable dataTable)
{
Commens.Commens comm = new YFY.Commens.Commens();
string schoolId,xueyuanId,classId,zhuanyeId = "";
foreach (DataRow item in dt.Rows)
{
DataRow dataRow = dataTable.NewRow();
dataRow["StudentName"] = item["学生姓名"].ToString().Trim();
schoolId = WDCBS_Common.GetSchoolId(item["学校名称"].ToString().Trim(), );
xueyuanId = WDCBS_Common.GetSchoolId(item["院系"].ToString().Trim(), );
classId = WDCBS_Common.GetSchoolId(item["班级"].ToString().Trim(), );
zhuanyeId = WDCBS_Common.GetZhuanYeId(item["专业"].ToString().Trim());
if (schoolId != "")//根据学校名称判断学校是否存在
{
dataRow["SchoolID"] = schoolId;
}
else
{
schoolName += item["学校名称"].ToString().Trim() + ",";
count++;
continue;
}
if (xueyuanId != "")//根据学院名称判断学院是否存在
{
dataRow["XueYuanID"] = xueyuanId;
}
else
{
schoolName += item["院系"].ToString().Trim() + ",";
count++;
continue;
}
if (zhuanyeId != "")//根据学院名称判断学院是否存在
{
dataRow["ZhuanYeID"] = zhuanyeId;
}
else
{
var bll = new BLLDao.JL_UnlimitCategory();
var model = new ModelDao.JL_UnlimitCategory();
model.CodeName = item["专业"].ToString().Trim();
model.CodePid = ;
model.CodeFlag = "";
int e_id = bll.Add(model);
dataRow["ZhuanYeID"] = e_id;
bll.MenuDropList("", "", "JL_UnlimitCategory", "CodeCode", "CodeId", e_id);
}
if (classId != "")//根据学院名称判断学院是否存在
{
dataRow["ClassID"] = classId;
}
else
{
var bll = new WDCBS_SchoolMessage();
var model = new Model.WDCBS_SchoolMessage();
model.TopId =schoolId;
model.UpId = xueyuanId;
model.Type = "";
model.ShowIf = "";
model.PingCeIf = "";
model.AddTime = DateTime.Now;
model.Name = item["班级"].ToString().Trim();
dataRow["ClassID"] = bll.Add(model);
}
dataRow["Sex"] = item["性别"].ToString().Trim();
dataRow["Paymoney"] = item["支付额度"].ToString().Trim();
if (item["是否支付"].ToString().Trim()=="是")
{
dataRow["PayMoneyIf"] = "";
}
else
{
dataRow["PayMoneyIf"] = "";
}
if (WDCBS_Common.GetStudentIsExist(item["身份证号码"].ToString().Trim())) {
dataRow["IDCard"] = item["身份证号码"].ToString().Trim();
}
else
{
count++;
continue;
}
dataRow["Password"] = comm.MD5(""); //密码
dataRow["BiYeIf"] =;
dataRow["Role"] = ConfigurationManager.AppSettings["StudengRole"].ToString();
dataRow["LoginIf"] = "";
dataTable.Rows.Add(dataRow);
}
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
string connectionString = Maticsoft.DBUtility.PubConstant.ConnectionString;
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
sqlBulkCopy.DestinationTableName = "WDCBS_Student";
sqlBulkCopy.BatchSize = dataTable.Rows.Count;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
if (dataTable != null && dataTable.Rows.Count != )
{
try
{
sqlBulkCopy.WriteToServer(dataTable);
}
catch (Exception ex)
{
YFY.Web.App_Code.JLCommonBind.InsertActionLog("", "学生信息导入", ex.ToString());
}
}
sqlBulkCopy.Close();
sqlConnection.Close();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
//return dataTable.Rows.Count;
}
#region
private static DataTable GetTableSchema()
{
DataTable dataTable = new DataTable();
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("StudentID") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("StudentNo") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("StudentName") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Sex") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("SchoolID") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("XueYuanID") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("ClassID") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("ZhuanYeID") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("StudentProvince") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("StudentCity") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("StudentAdress") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("FamilyPersonName") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("FamilyPersonRelation") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("FamilyPersonTelephone") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("BaoXianIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Mail") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("IDCard") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Telephone") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("QQ") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Password") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Studenttype") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("BanZhuRenTeacher") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("SchoolZhiDaoTeacher") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("EnterpriseZhiDaoTeacher") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("BiYeSheJiTeacher") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("JiuYeZhiDaoTeacher") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("XueJie") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("BiYeIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("BiYeTime") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Statue") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PingCeTeacherIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PingCeEnterpriseIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("TeacherPingCeIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("EnterprisePingCeIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("JiaoWuPingCeIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Attachment") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Remark") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Role") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("Paymoney") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("PayMoneyIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("HavePaymoney") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("RegistTag") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("RegistTagIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("LoginIf") });
dataTable.Columns.AddRange(new DataColumn[] { new DataColumn("DeleteIf") });
return dataTable;
}
#endregion
/// <summary>
