- // 确定提交
- protected void Butn_Submit_Click(object sender, EventArgs e)
- {
- //if (this.CheckRefreshed1.isRefreshed)// 如果是刷新页面重复提交
- //{
- // return;
- //}
- ExcelPath = MethodLibrary.GetDateString() + "_" + this.Fild_Excel.FileName.Substring(this.Fild_Excel.FileName.LastIndexOf('.'));// 表格地址
- this.Fild_Excel.SaveAs(Server.MapPath("/kaosheng/shujuheyan/") + ExcelPath);// 添加表格
- DataTranf(Server.MapPath("/kaosheng/shujuheyan/") + ExcelPath);// 访问到数据库
- }
- // 传入 excel 文件的路径.
- protected void DataTranf(string Excelpath)
- {
- DataTable dtExcel = ExcelToDataTable(Excelpath, "Sheet2");
- if (dtExcel.Rows.Count == 0)// 判断行数
- {
- RegisterClientScriptBlock("","<script>alert('您选择的要导入的 xls 数据存在问题, 无法正常导入. 请下载模板然后进行添加数据, 不可以修改表的结构, 如有问题请点击页面上的 QQ 在线咨询.');</script>");
- return;
- }
- if (dtExcel.Columns.Count != 6)// 判断列数
- {
- RegisterClientScriptBlock("","<script>alert('模板的列数不为 6,(当前" + dtExcel.Columns.Count + "列) 格式错误!');</script>");
- return;
- }
- for (int i = 0; i <dtExcel.Rows.Count; i++)
- {
- if (dtExcel.Rows[i][0].ToString().Trim().Length> 0 || dtExcel.Rows[i][2].ToString().Trim().Length> 0)
- {
- int b = 1;
- //InsertToDB(dtExcel.Rows[i][0].ToString().Trim(), dtExcel.Rows[i][b++].ToString().Trim(), dtExcel.Rows[i][b++].ToString().Trim(), dtExcel.Rows[i][b++].ToString().Trim(), dtExcel.Rows[i][b++].ToString().Trim(), dtExcel.Rows[i][b++].ToString().Trim(), dtExcel.Rows[i][b++].ToString().Trim());// 插入数据
- if (My_Sql.Sql2("select R_sn from NF_User_Record where R_Name='" + dtExcel.Rows[i][1].ToString().Trim() + "'and R_IDnumber='" + dtExcel.Rows[i][2].ToString().Trim() + "'and R_BuMen =',1247,'").Rows.Count == 0)
- {
- Response.Write("姓名:" + dtExcel.Rows[i][(1)].ToString().Trim() + "--- 身份证号:" + dtExcel.Rows[i][2].ToString().Trim() + "的人数据库不存在 < br />");
- }
- }
- // 此处根据 excel 中字段, 自己设定要取用的字段.
- if (i == 999)
- {
- RegisterClientScriptBlock("","<script>alert('一次最多导入 1000 人, 最后导入人姓名:" + dtExcel.Rows[i][0].ToString() + "');</script>");
- break;
- }
- }
- //RegisterClientScriptBlock("","<script>alert('导入成功, 共导入" + ChengGongCount + "条数据');</script>");
- }
- ///// <summary>
- ///// 插入记录到 SqlServer 数据库:
- ///// </summary>
- ///// <param name="J_Name"> 考点名称 </param>
- ///// <param name="J_GuanLiYuan_DiQuDaiMa"> 考点所属的上级地区管理代码 </param>
- ///// <param name="J_KaoDianDaiMa"> 考点代码 </param>
- ///// <param name="J_Shi"> 所在城市 </param>
- ///// <param name="J_ZuoWeiMoShi"> 座位模式 (1 是 7887,2 是 66666,9 是其它)</param>
- ///// <param name="J_KaoDianLeiXing"> 考点类型 </param>
- ///// <param name="J_JiHuaKaoChangAmout"> 考场数 </param>
- //protected void InsertToDB(string J_Name, string J_GuanLiYuan_DiQuDaiMa, string J_KaoDianDaiMa, string J_Shi, string J_ZuoWeiMoShi, string J_KaoDianLeiXing, string J_JiHuaKaoChangAmout)
- //{
- // if (J_JiHuaKaoChangAmout.Length == 0)
- // {
- // J_JiHuaKaoChangAmout = "0";
- // }
- // JiZheZheng_KaoDianInfo _JiZheZheng_KaoDianInfo = new JiZheZheng_KaoDianInfo();
- // _JiZheZheng_KaoDianInfo.J_Name = J_Name;
- // _JiZheZheng_KaoDianInfo.J_KaoDianDanWei = _JiZheZheng_KaoDianInfo.J_Name;
- // _JiZheZheng_KaoDianInfo.J_BuMenSN = 1241;
- // _JiZheZheng_KaoDianInfo.J_GuanLiYuan_DiQuDaiMa = J_GuanLiYuan_DiQuDaiMa;
- // _JiZheZheng_KaoDianInfo.J_KaoDianDaiMa = J_KaoDianDaiMa;
- // _JiZheZheng_KaoDianInfo.J_Shi = J_Shi.Replace("市", "");
- // _JiZheZheng_KaoDianInfo.J_ZuoWeiMoShi = J_ZuoWeiMoShi;
- // _JiZheZheng_KaoDianInfo.J_KaoDianLeiXing = J_KaoDianLeiXing;
- // _JiZheZheng_KaoDianInfo.J_JiHuaKaoChangAmout = Convert.ToInt32(J_JiHuaKaoChangAmout);
- // _JiZheZheng_KaoDianInfo.J_Sheng = "湖北";
- // _JiZheZheng_KaoDianInfo.J_AddAdminSN = 81;
- // _JiZheZheng_KaoDianInfo.J_AddAdmin = "张文岐";
- // _JiZheZheng_KaoDianInfo.J_ShenPiZhuangTai = 2;
- // _JiZheZheng_KaoDianInfo.J_ShenPiAdmin = "张文岐";
- // _JiZheZheng_KaoDianInfo.J_NianFen = MyUser.Info.L_KaoShiNianFen;
- // MyJiZheZheng_KaoDian.Add(_JiZheZheng_KaoDianInfo);
- // ChengGongCount++;
- //}
- public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
- {
- try
- {
- string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties=Excel 5.0;";
- string strExcel = string.Format("select * from [{0}$]", strSheetName);
- DataSet ds = new DataSet();
- using (OleDbConnection conn = new OleDbConnection(strConn))
- {
- conn.Open();
- OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
- adapter.Fill(ds, strSheetName);
- conn.Close();
- }
- return ds.Tables[strSheetName];
- }
- catch
- {
- DataTable ds = new DataTable();
- return ds;
- }
- }
来源: http://www.bubuko.com/infodetail-2604581.html