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('.')[0]; var table = ExcelHelper.ExcelRead("temp", "Sheet1", preStr + fileName, out uploadPath); if (table == null) { return "{\"error\":\"模版格式不正确!\",\"msg\":\"gzz\"}"; } if (table.Rows.Count == 0) { return "{\"error\":\"此模版数据为空!\",\"msg\":\"gzz\"}"; } if (table.Columns.Count != 10) { 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(), 0); xueyuanId = WDCBS_Common.GetSchoolId(item["院系"].ToString().Trim(), 1); classId = WDCBS_Common.GetSchoolId(item["班级"].ToString().Trim(), 2); 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 = 1; model.CodeFlag = "0"; int e_id = bll.Add(model); dataRow["ZhuanYeID"] = e_id; bll.MenuDropList("1", "0", "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 = "3"; model.ShowIf = "1"; model.PingCeIf = "1"; 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"] = "1"; } else { dataRow["PayMoneyIf"] = "0"; } if (WDCBS_Common.GetStudentIsExist(item["身份证号码"].ToString().Trim())) { dataRow["IDCard"] = item["身份证号码"].ToString().Trim(); } else { count++; continue; } dataRow["Password"] = comm.MD5("123"); //密码 dataRow["BiYeIf"] =0; dataRow["Role"] = ConfigurationManager.AppSettings["StudengRole"].ToString(); dataRow["LoginIf"] = "1"; 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 != 0) { 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>
转载于:https://www.cnblogs.com/yuefengkai/p/3449196.html