OA项目之导入

内容显示页:

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>                         &nbsp;选择文件:                         <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;">             &nbsp;<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 中内容个数相同,且内容类型相同

上一篇:自搭的一个系统框架,使用Spring boot+Vue+Element


下一篇:C# 中size_t的暗坑