内容显示页：
protected void btnIMP_Click(object sender, EventArgs e) { Response.Redirect("导入页.aspx?backurl=" + DESEncrypt.Encrypt(iurl)); }
aspx: <head runat="server"> <title></title> <script type="text/javascript" src="../Page/js/jquery-1.7.2.min.js"></script> <script type="text/javascript" src="../Page/js/hide_show.js"></script> <link href="../Page/css/layout.css" rel="stylesheet" type="text/css" /> <link href="../Page/css/Dialog.css" rel="stylesheet" type="text/css" /> <script type="text/javascript" src="../Page/js/jQuery_Dialog.js"></script> <script type="text/javascript" src="../Page/js/function.js"></script> <script type="text/javascript" src="../Page/js/jquery.easydrag.js"></script> <link href="../Page/css/style01.css" rel="stylesheet" type="text/css" /> </head> <body> <form id="form1" runat="server"> <div class="DivMain"> <div class="m_nav"> <%=siteMap %><span>></span><a href="javascript:void(0);">记录信息导入</a> </div> <!--结束--> <div class="clearDiv"> </div> <div class="Search"> 批量导入记录信息模板下载：<a href="../Resources/Template/批量导入记录信息.xls">【批量导入记录信息模板】</a> </div> <div class="div_right_search">
<table class="table_search_001" width="600"> <tr> <td> 选择文件： <asp:FileUpload ID="fileUpload" runat="server" CssClass="txt_file" Width="300px" /> <asp:Button runat="server" Text="EXCEL导入" ID="btnImp" OnClientClick="return check()" OnClick="btnImp_Click" CssClass="button_sm7" /> <asp:Button runat="server" ID="btnBack" CssClass="button_sm1" Text="返回" OnClick="btnBack_Click" /> </td> </tr> </table> </div> <div class="div_right_search" id="divHandel" runat="server" style="text-align: left;"> <input type="button" class="button_bg7" onclick="showExcelData('good')" value='显示验证通过' /> <input type="button" class="button_bg7" onclick="showExcelData('bad')" value='显示验证没通过' /> <input type="button" class="button_bg7" onclick="showExcelData('all')" value='显示所有' /> <asp:Button ID="btnImpRight" runat="server" CssClass="button_bg7" Text="导入验证通过" OnClick="btnImpRight_Click" OnClientClick="return checkIMP();" /> <asp:Button ID="btnCancel" runat="server" CssClass="button_sm1" Text="取 消" OnClick="btnCancel_Click" /> </div> <div class='clearDiv'> </div> <div id="divBadData"> <%Response.Write(GetBadData()); %> </div> <div id="divGoodData"> <%Response.Write(GetGoodData()); %> </div> </div> <!-- 背景层DIV --> <div class="div_documentbg" id="div_documentbg"> </div> <script language="javascript" type="text/javascript"> function check() { if ($("#<%=fileUpload.ClientID %>").val() == "") { $.dialog.alert("请选择需要上传的EXCEL文件。");
return false; } var doc_name = $("#<%=fileUpload.ClientID %>").val(); var doc_type = doc_name.substring(doc_name.lastIndexOf("\") + 1).split(".")[1]; if (doc_type != "xls") { $.dialog.alert("请选择后缀名为.xls文件。"); return false; } if (confirm("您将要导入记录信息")) { SubmitData2(getLoadingDot("正在操作，请稍后"), 600); setInterval("$('.tips_jinggao').html(getLoadingDot("正在操作，请稍后"))", 1000); } }
function checkIMP() { SubmitData2(getLoadingDot("正在操作，请稍后"), 600); setInterval("$('.tips_jinggao').html(getLoadingDot("正在操作，请稍后"))", 1000); }
function showExcelData(type) { if (type == "good") { $("#divBadData").hide(); $("#divGoodData").show(); } else if (type == "bad") { $("#divBadData").show(); $("#divGoodData").hide(); } else { $("#divBadData").show(); $("#divGoodData").show(); } } </script> </form> </body> </html>
aspx.cs: protected string strTempletTitle1 = string.Empty; protected string strTemplet = string.Empty; static string strGoodData = string.Empty; static string strBadData = string.Empty; static DataTable excelData = new DataTable(); protected string backurl = string.Empty; protected void Page_Load(object sender, EventArgs e) { strTempletTitle1 = "内容一，内容二"; strTemplet = string.Empty; backurl = Request.QueryString["backurl"]; if (!IsPostBack) { strGoodData = string.Empty; strBadData = string.Empty; } ShowHandel(); } protected void btnImp_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); string uploadFileName = string.Empty;
if (fileUpload.HasFile) { try { uploadFileName = fileUpload.FileName; string errorMessage = string.Empty;
if (!CheckFileName(fileUpload.FileName, out errorMessage)) { ClientScript.RegisterStartupScript(this.GetType(), "check", "$.dialog.alert('" + errorMessage + "');", true); return; } DeleteInvoiceFile(); string serverPath = Server.MapPath("../Resources/UpFile/"); string fileName = serverPath + Guid.NewGuid() + ".xls"; if (!Directory.Exists(serverPath)) { Directory.CreateDirectory(serverPath); } fileUpload.SaveAs(fileName); OleDbConnection conn; OleDbDataAdapter da; System.Data.DataTable tblSchema;//存放领域表的结构 IList<string> tblNames;//sheet名称 GetExcelSchema(fileName, out conn, out da, out tblSchema, out tblNames, "YES", 1); if (ds != null) ds.Clear(); ds = GetEachSheetContent(conn, ref da, tblSchema, ref tblNames); if (ds.Tables.Count == 0) { ClientScript.RegisterStartupScript(this.GetType(), "check", "$.dialog.alert('导入失败，你可能选择了错误的模板，请重新上传。');", true); return; } //验证模板结构 strTemplet = strTempletTitle1; if (!CheckUploadDataStructure(ds, strTemplet, out errorMessage)) { ClientScript.RegisterStartupScript(this.GetType(), "check", "$.dialog.alert('" + errorMessage + "');", true); return; } excelData = new DataTable(); excelData = ds.Tables[0]; DataColumn dcErr = new DataColumn(); dcErr.ColumnName = "ErrorDate"; dcErr.DataType = typeof(string); dcErr.Caption = "错误提示"; excelData.Columns.Add(dcErr); CheckUploadData(); } catch (Exception ex) { Response.Output.WriteLine(ex.Message); Response.Output.WriteLine(ex.StackTrace); Response.End(); } } ShowHandel(); } private void ShowHandel() {
if (excelData.Rows.Count == 0) { divHandel.Visible = false; } else { divHandel.Visible = true; }
}
private void InputExcelDataToDB() { int exData = 0; string strSql = string.Empty; List<string> listSql = new List<string>(); try { DataRow[] dr = excelData.Select("ErrorDate='正确'"); if (dr.Count() > 0) { strSql = string.Empty; #region 组SQL语句 for (int i = 0; i < dr.Count(); i++) { strSql = @"insert into table(字段)"; strSql += "VALUES ('" + dr[i]["内容一"].ToString().Trim() + "'"; strSql += ",'" + dr[i]["内容二"].ToString().Trim() + "'"; listSql.Add(strSql); } #endregion
exData = DbHelperSQL.ExecuteSqlTran(listSql); if (exData > 0) { string url = string.IsNullOrEmpty(backurl) ? "内容显示页.aspx" : DESEncrypt.Decrypt(backurl); SYS_LOG.AddCommonLog(pfunction.SysLogQueryMenu(Module_Id), "【批量导入记录管理信息成功】，操作用户【" + CookiesMan.GetCookie("xy_admin_user", "userTrueName") + "】", CookiesMan.GetCookie("xy_admin_user", "userName")); ClientScript.RegisterStartupScript(this.GetType(), "handle", "SubmitDataSuccess('【" + exData + "】记录管理信息被成功导入','" + url + "','1');", true); } else { SYS_LOG.AddErrorLog(Request.Url.AbsolutePath, "【批量导入记录信息失败】，操作用户【" + CookiesMan.GetCookie("xy_admin_user", "userTrueName") + "】", CookiesMan.GetCookie("xy_admin_user", "userName")); ClientScript.RegisterStartupScript(this.GetType(), "handle", "SubmitDataError('操作失败','','3');", true); } } else { SYS_LOG.AddErrorLog(Request.Url.AbsolutePath, "【批量导入记录信息失败】，操作用户【" + CookiesMan.GetCookie("xy_admin_user", "userTrueName") + "】", CookiesMan.GetCookie("xy_admin_user", "userName")); ClientScript.RegisterStartupScript(this.GetType(), "handle", "SubmitDataError('数据为空，导入失败','','3');", true); }
} catch (Exception ex) { SYS_LOG.AddErrorLog(Request.Url.AbsolutePath, "【批量导入记录信息失败】" + ex.Message.ToString().Replace("'", "") + "，操作用户【" + CookiesMan.GetCookie("xy_admin_user", "userTrueName") + "】", CookiesMan.GetCookie("xy_admin_user", "userName")); ClientScript.RegisterStartupScript(this.GetType(), "handle", "SubmitDataError('操作失败:" + ex.Message.ToString() + "','','3');", true); }
excelData.Clear();
} /// <summary> /// 验证导入数据 /// </summary> /// <param name="goodData"></param> /// <param name="badData"></param> /// <param name="dataAll"></param> private void CheckUploadData() { for (int i = 0; i < excelData.Rows.Count; i++) { DataRow dr = excelData.Rows[i]; string strErr = string.Empty; CheckOption(dr, out strErr); excelData.Rows[i]["ErrorDate"] = strErr; } } /// <summary> /// 显示验证通过的数据 /// </summary> /// <returns></returns> protected string GetGoodData() { if (excelData.Rows.Count == 0) { return ""; } DataRow[] dr = excelData.Select("ErrorDate='正确'"); if (dr.Count() == 0) { return ""; } string strAlert = string.Empty;
if (dr.Count() == excelData.Rows.Count) { strAlert = "<font color='green'>全部记录信息都已验证通过</font>"; } else { strAlert = "<font color='yellow'>【" + dr.Count() + "】记录信息验证通过</font>"; } #region strGoodData = string.Empty; strGoodData += "<div class='clearDiv'></div><div class="div_right_listtitle">"; strGoodData += "<div style="margin-left:15px;font-weight:bold;padding-top:7px;">" + strAlert + "</div>"; strGoodData += "</div>"; strGoodData += "<table class='table_list' cellpadding='0' cellspacing='0' >"; strGoodData += "<tr class='tr_title'>"; strGoodData += "<td>内容一</td>"; strGoodData += "<td>内容二</td>"; strGoodData += "</tr>"; for (int i = 0; i < dr.Count(); i++) { strGoodData += "<tr class='tr_con_001'>"; strGoodData += "<td>" + dr[i]["内容一"] + "</td>"; strGoodData += "<td>" + dr[i]["内容二"] + "</td>"; strGoodData += "</tr>"; } strGoodData += "</table>";
#endregion
return strGoodData; } /// <summary> /// 显示验证没有通过的数据 /// </summary> /// <returns></returns> protected string GetBadData() { if (excelData.Rows.Count == 0) { return ""; } DataRow[] dr = excelData.Select("ErrorDate<>'正确'"); if (dr.Count() == 0) { return ""; } string strAlert = string.Empty; if (dr.Count() == excelData.Rows.Count) { strAlert = "<font color='red'><div class='clearDiv'></div>全部记录信息都没有验证通过</font>"; } else { strAlert = "<font color='red'>【" + dr.Count() + "】记录信息验证没通过</font>"; } #region strBadData = string.Empty; strBadData += "<div class="div_right_listtitle">"; strBadData += "<div style="margin-left:15px;font-weight:bold;padding-top:7px;">" + strAlert + "</div>"; strBadData += "</div>"; strBadData += "<table class='table_list' cellpadding='0' cellspacing='0' >"; strBadData += "<tr class='tr_title'>"; strBadData += "<td>内容一</td>"; strBadData += "<td>内容二</td>"; strBadData += "<td>错误提示</td>"; strBadData += "</tr>";
for (int i = 0; i < dr.Count(); i++) { strBadData += "<tr class='tr_con_001'>"; strBadData += "<td>" + dr[i]["内容一"] + "</td>"; strBadData += "<td>" + dr[i]["内容二"] + "</td>"; strBadData += "<td style='text-align:left;'>" + dr[i]["ErrorDate"] + "</td>"; strBadData += "</tr>"; } strBadData += "</table>";
#endregion return strBadData; } /// <summary> /// 核对每一项的值 /// </summary> /// <param name="dr"></param> /// <param name="type"></param> /// <param name="strErr"></param> /// <returns></returns> private bool CheckOption(DataRow dr, out string strErr) { bool bTemp = true; strErr = string.Empty;
if (dr["内容一"].ToString().Trim() == "") { bTemp = false; strErr += "内容一为空值<br>"; } if (dr["内容二"].ToString().Trim() == "") { bTemp = false; strErr += "内容二为空值<br>"; } if (strErr.Length > 4) { strErr = strErr.Remove(strErr.Length - 4); } if (bTemp) { strErr = "正确"; }
return bTemp;
} /// <summary> /// 验证上传数据的模板结构 /// </summary> /// <param name="ds"></param> /// <param name="list"></param> /// <param name="errorMessage"></param> /// <returns></returns> private bool CheckUploadDataStructure(DataSet ds, string strTitle, out string errorMessage) {
if (ds.Tables[0].Columns.Count != strTitle.Split(',').Count()) { errorMessage = "上传文件模板字段的个数不对应,请核对模板后重新上传"; return false; } List<string> listTemp = new List<string>(); for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { if (ds.Tables[0].Columns[i].ColumnName.Trim().ToUpper() != strTitle.Split(',')[i].ToUpper()) { errorMessage = "第【" + (i + 1) + "】个字段的名称与定义模板中的名称不一致，请修改后重新上传"; return false; } } errorMessage = string.Empty; return true; } /// <summary> /// 验证上传的文件 /// </summary> /// <param name="fileName"></param> /// <param name="errorMessage"></param> /// <returns></returns> private bool CheckFileName(string fileName, out string errorMessage) { if (fileName.Substring(fileName.LastIndexOf('.') + 1).ToLower() != "xls") { errorMessage = "请选择后缀名为.xls的EXCEL文件上传"; return false; } errorMessage = string.Empty; return true; } /// <summary> /// 删除上传的文件 /// </summary> private void DeleteInvoiceFile() { string serverPath = Server.MapPath("../Resources/UpFile/"); if (!Directory.Exists(serverPath)) { Directory.CreateDirectory(serverPath); } foreach (string file in Directory.GetFiles(serverPath)) { File.Delete(file); } } #region Excel读取 private void GetExcelSchema(string filename, out OleDbConnection conn, out OleDbDataAdapter da, out System.Data.DataTable tblSchema, out IList<string> tblNames, string ifFirst, int i) { // 读取Excel数据，填充DataSet // 连接字符串 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties="Excel 8.0;HDR=" + ifFirst + ";IMEX=" + i + "";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取 "data source=" + filename; //string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + // "Extended Properties="Excel 12.0 Xml;HDR=" + ifFirst + ";IMEX=" + i + "";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取 // "data source=" + filename; conn = null; da = null; tblSchema = null; tblNames = null; // 初始化连接，并打开 conn = new OleDbConnection(connStr); conn.Open(); //获取数据源的表定义元数据 tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); conn.Close(); } private static DataSet GetEachSheetContent(OleDbConnection conn, ref OleDbDataAdapter da, System.Data.DataTable tblSchema, ref IList<string> tblNames) { tblNames = new List<string>(); foreach (DataRow row in tblSchema.Rows) { string tableName = (string)row["TABLE_NAME"]; if (!tableName.StartsWith("_")) //skip system tables { tblNames.Add((string)row["TABLE_NAME"]); // 读取sheet名 } }
//********************************************************* // 初始化适配器 da = new OleDbDataAdapter(); // 准备数据，导入DataSet DataSet ds = new DataSet(); string sql_F = "SELECT * FROM [{0}]"; string sheetName = "学生违纪记录信息$"; foreach (string tblName in tblNames) { if (tblNames.Count > 1 && !tblName.StartsWith(sheetName)) { continue; } da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn); try { da.Fill(ds, tblName); } catch { // 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); } throw; } }
// 关闭连接 if (conn.State == ConnectionState.Open) { conn.Close(); }
return ds; } #endregion /// <summary> /// 用户事件 写入导入数据 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnImpRight_Click(object sender, EventArgs e) { InputExcelDataToDB(); }
protected void btnCancel_Click(object sender, EventArgs e) { excelData.Clear(); ShowHandel(); }
protected void btnBack_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(backurl)) { Response.Redirect("内容显示页.aspx"); } else { Response.Redirect(DESEncrypt.Decrypt(backurl)); } }
PS：导入的模版要和strTempletTitle1 中内容个数相同，且内容类型相同