MY_DEPART_KKKS.aspx (导入Excel数据)
GSAO/KCBP/MY_DEPART_KKKS.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MY_DEPART_KKKS.aspx.cs" Inherits="GSAO.KCBP.MY_DEPART_KKKS" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>本学院课程编排</title> <link href="../css/style.css" rel="stylesheet" type="text/css" /> </head> <body> <form id="form1" runat="server"> <asp:Label ID="lb_function_desc" runat="server" Visible="false" > 功能说明:课程编排表.增,删除,改!<br/> 操作表为:JX_KKXX ,JX_KKKS </asp:Label> <table width="99%" class="tbDefault mb5 mt5"> <tr> <td class="tdRight bgEFEFEF" width="50">学年:</td> <td class="tdLeft" width="60"> <asp:DropDownList ID="ddl_XNM" runat="server" CssClass="ddl"> </asp:DropDownList> </td> <td class="tdRight bgEFEFEF" width="50">学期:</td> <td class="tdLeft" width="60"> <asp:DropDownList ID="ddl_XQM" runat="server" CssClass="ddl"> </asp:DropDownList> </td> <td class="tdRight bgEFEFEF" width="50">单位:</td> <td class="tdLeft" width="150"> <asp:DropDownList ID="ddl_YXSM" runat="server" CssClass="ddl" AutoPostBack="True" onselectedindexchanged="ddl_YXSM_SelectedIndexChanged"> </asp:DropDownList> </td> <td class="tdRight bgEFEFEF" width="60">授课地点:</td> <td class="tdLeft"> <asp:DropDownList ID="ddl_SKJS" runat="server" CssClass="ddl"> </asp:DropDownList> </td> <td class="tdRight bgEFEFEF" width="50">名称:</td> <td class="tdLeft"> <asp:TextBox ID="tb_KCMC" runat="server" CssClass="txt" Width="100"></asp:TextBox> <asp:Button ID="btn_search" runat="server" Text="查 询" CssClass="btnStyle mr10" onclick="btn_search_Click" /> <asp:Button ID="btnExcel" runat="server" Text="Excel导入" CssClass="btnStyle" onclick="btnExcel_Click"/> <asp:Label ID="lb_total" runat="server"></asp:Label> </td> <td class="tdRight"> <img src="../images/icon_add.gif" /><asp:hyperlink id="btnAdd" runat="server" NavigateUrl="MY_DEPART_KKKS_TJ.aspx">添加课程</asp:hyperlink> </td> </tr> </table> <asp:GridView ID="GridView1" runat="server" CssClass="tbGvw" Width="99%" AllowPaging="true" AutoGenerateColumns="false" BorderStyle="Solid" BorderWidth="1" BorderColor="Silver" PageSize="20" onpageindexchanged="GridView1_PageIndexChanged" onrowdatabound="GridView1_RowDataBound" onpageindexchanging="GridView1_PageIndexChanging" onrowcommand="GridView1_RowCommand"> <PagerSettings PageButtonCount="20" FirstPageText="[ 首页 ]" LastPageText="[ 末页 ]" Mode="NextPreviousFirstLast" NextPageText="[ 下一页 ]" Position="TopAndBottom" PreviousPageText="[ 上一页 ]" /> <PagerTemplate> <table width="100%"> <tr> <td style="text-align:left; color:#996600;"> 第<asp:Label id="lblPageIndex" runat="server" text='<%# ((GridView)Container.Parent.Parent).PageIndex + 1 %>' />页 共<asp:Label id="lblPageCount" runat="server" text='<%# ((GridView)Container.Parent.Parent).PageCount %>' />页 <asp:linkbutton id="btnFirst" runat="server" causesvalidation="False" commandargument="First" ForeColor="#996600" Font-Bold="true" commandname="Page" text="首页" /> <asp:linkbutton id="btnPrev" runat="server" causesvalidation="False" ForeColor="#996600" Font-Bold="true" commandargument="Prev" commandname="Page" text="上一页" /> <asp:linkbutton id="btnNext" runat="server" ForeColor="#996600" Font-Bold="true" causesvalidation="False" commandargument="Next" commandname="Page" text="下一页" /> <asp:linkbutton id="btnLast" runat="server" ForeColor="#996600" Font-Bold="true" causesvalidation="False" commandargument="Last" commandname="Page" text="尾页" /></td> </tr> </table> </PagerTemplate> <HeaderStyle CssClass="tbHeader" /> <RowStyle HorizontalAlign="Center" Wrap="False" Height="25px" ></RowStyle> <Columns> <asp:BoundField DataField="JX_KKKS_KEY" HeaderText="ID不显示" /> <asp:BoundField DataField="KKXX_KEY" HeaderText="ID不显示" /> <asp:BoundField DataField="SKJS_KEY" HeaderText="ID不显示" /> <asp:TemplateField HeaderText="修改/取消" ItemStyle-Width="100" ItemStyle-HorizontalAlign="Center" > <ItemTemplate> <asp:Image id="img_show" runat="server" ImageUrl="../images/icon_view.gif" ToolTip="查看" CssClass="cursorPointer" /> <asp:Image id="img_modify" runat="server" ImageUrl="../images/icon_edit.gif" ToolTip="修改" CssClass="cursorPointer" /> <asp:ImageButton id="btn_delete" CommandArgument ="<%# GridView1.Rows.Count %>" CommandName="delete1" runat="server" ImageUrl="../images/icon_delete.gif" ImageAlign="Baseline" AlternateText="删除"></asp:ImageButton> </ItemTemplate> </asp:TemplateField> <asp:BoundField HeaderText="学年" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center" DataField="XNMC"/> <asp:BoundField HeaderText="学期" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center" DataField="XQMC"/> <asp:BoundField HeaderText="开设学院" ItemStyle-Width="120px" ItemStyle-HorizontalAlign="Center" DataField="YXSMC"/> <%--<asp:BoundField HeaderText="课程号" ItemStyle-Width="80px" ItemStyle-HorizontalAlign="Center" DataField="KCH"/>--%> <asp:BoundField HeaderText="授课地点" ItemStyle-HorizontalAlign="Center" DataField="SKJXMC"/> <asp:BoundField HeaderText="开课班" ItemStyle-Width="150px" ItemStyle-HorizontalAlign="Center" DataField="KCMC"/> <asp:BoundField HeaderText="开始周" ItemStyle-HorizontalAlign="Center" DataField="KSZ"/> <asp:BoundField HeaderText="结束周" ItemStyle-HorizontalAlign="Center" DataField="JSZ"/> <asp:BoundField HeaderText="周一" ItemStyle-HorizontalAlign="Center" DataField="ZC1"/> <asp:BoundField HeaderText="周二" ItemStyle-HorizontalAlign="Center" DataField="ZC2"/> <asp:BoundField HeaderText="周三" ItemStyle-HorizontalAlign="Center" DataField="ZC3"/> <asp:BoundField HeaderText="周四" ItemStyle-HorizontalAlign="Center" DataField="ZC4"/> <asp:BoundField HeaderText="周五" ItemStyle-HorizontalAlign="Center" DataField="ZC5"/> <asp:BoundField HeaderText="周六" ItemStyle-HorizontalAlign="Center" DataField="ZC6"/> <asp:BoundField HeaderText="周日" ItemStyle-HorizontalAlign="Center" DataField="ZC7"/> <asp:BoundField HeaderText="当前状态" ItemStyle-Width="80px" ItemStyle-HorizontalAlign="Center" DataField="YXBZ"/> <asp:BoundField HeaderText="选修人数" ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" /> </Columns> <PagerStyle Font-Size="10pt" BorderColor="White" Font-Bold="False" ForeColor="#996600"></PagerStyle> </asp:GridView> <div class="pt10 pb10"> (备注:<font color="red">红色显示</font>为“同一教室相同周次相同上课节数安排了两个以上的开课班”,可能有冲突,特此提示!) </div> <div id="divExcel" class="div_main" visible="false" style="width:450px; position: absolute; top: 20%;left:30%; z-index:999;" runat="server"> <table id="Table2" width="100%" class="tbDefault"> <tr> <td class="tdRight" width="110">学年:</td> <td class="tdLeft"> <asp:DropDownList ID="ddl_Excel_XNM" runat="server" CssClass="ddl"></asp:DropDownList> </td> </tr> <tr> <td class="tdRight">学期:</td> <td class="tdLeft"> <asp:DropDownList ID="ddl_Excel_XQM" runat="server" CssClass="ddl"></asp:DropDownList> </td> </tr> <tr> <td class="tdRight">单位:</td> <td class="tdLeft"> <asp:DropDownList ID="ddl_Excel_YXSM" runat="server" CssClass="ddl"></asp:DropDownList> </td> </tr> <td class="tdRight"><FONT face="宋体">导入文件:</FONT></td> <td class="tdLeft"> <INPUT onpaste="return false" id="inputFile" onkeydown="return false;" type="file" size="20" name="File1" runat="server" class="button"> <p class="pt10"> <IMG height="16" src="../images/icon_save.gif" width="16" border="0"> <A href="../Templates/本学院课程编排导入数据模板.xls" target="_blank">本学院课程编排导入数据模板文件(点击下载)</A> </p> </td> </tr> <tr class="tdLeft"> <td class="tdRight">模板必填项说明:</td> <td class="tdLeft">开课班(<FONT color="#ff0066">*</FONT>),开始周(<FONT color="#ff0066">*</FONT>),结束周(<FONT color="#ff0066">*</FONT>)</td> </tr> <tr> <td colSpan="2"> <asp:Button ID="btn_import" runat="server" Text="导入" CssClass="btnStyle mr10" onclick="btn_import_Click" /> <asp:Button ID="btn_close" runat="server" Text="关闭" CssClass="btnStyle" onclick="btn_close_Click"/> </td> </tr> </table> </div> </form> </body> </html>
using System; using System.Collections; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.OleDb; namespace GSAO.KCBP { public partial class MY_DEPART_KKKS : System.Web.UI.Page { private static string sheet_name; protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { Bind_ddl_XNM(); Bind_ddl_XQM(); Bind_ddl_YXSM(); Bind_SKJS(); Bind_gv(); } } protected void Bind_ddl_YXSM() { DataTable dt = new DataTable(); string ldap_account = Session["ldap_account"].ToString(); if (ldap_account == "zjcai" || ldap_account == "linyq") dt = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select * from TB_YXS order by YXSMC asc").Tables[0]; else dt = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select * from TB_YXS where YXSM in (select YXSM from XTB_YHDYYXS where vid=" + Session["vid"].ToString() + ") order by YXSMC asc").Tables[0]; this.ddl_YXSM.DataSource = dt; this.ddl_YXSM.DataTextField = "YXSMC"; this.ddl_YXSM.DataValueField = "YXSM"; this.ddl_YXSM.DataBind(); this.ddl_Excel_YXSM.DataSource = dt; this.ddl_Excel_YXSM.DataTextField = "YXSMC"; this.ddl_Excel_YXSM.DataValueField = "YXSM"; this.ddl_Excel_YXSM.DataBind(); } /// <summary> /// 单位切换 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void ddl_YXSM_SelectedIndexChanged(object sender, EventArgs e) { Bind_SKJS(); } protected void Bind_ddl_XNM() { DataTable dt = new DataTable(); dt = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select * from TB_XN order by ZT_FLG desc").Tables[0]; this.ddl_XNM.DataSource = dt; this.ddl_XNM.DataTextField = "XNMC"; this.ddl_XNM.DataValueField = "XNM"; this.ddl_XNM.DataBind(); this.ddl_Excel_XNM.DataSource = dt; this.ddl_Excel_XNM.DataTextField = "XNMC"; this.ddl_Excel_XNM.DataValueField = "XNM"; this.ddl_Excel_XNM.DataBind(); } protected void Bind_ddl_XQM() { DataTable dt = new DataTable(); dt = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select * from TB_XQ order by ZT_FLG desc").Tables[0]; this.ddl_XQM.DataSource = dt; this.ddl_XQM.DataTextField = "XQMC"; this.ddl_XQM.DataValueField = "XQM"; this.ddl_XQM.DataBind(); this.ddl_Excel_XQM.DataSource = dt; this.ddl_Excel_XQM.DataTextField = "XQMC"; this.ddl_Excel_XQM.DataValueField = "XQM"; this.ddl_Excel_XQM.DataBind(); } /// <summary> /// 绑定授课地点(教室) /// </summary> private void Bind_SKJS() { ddl_SKJS.DataSource = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select * from TB_SKJS where SKJS_KEY in (select SKJS_KEY from JW_JXDWKS where YXSM=" + this.ddl_YXSM.SelectedValue + ") order by SKJXMC asc").Tables[0]; ddl_SKJS.DataTextField = "SKJXMC"; ddl_SKJS.DataValueField = "SKJS_KEY"; ddl_SKJS.DataBind(); ddl_SKJS.Items.Insert(0, "全部"); ddl_SKJS.Items[0].Value = "0"; } private void Bind_gv() { string where = " JX_KKXX.YXBZ not in (0) and JX_KKXX.KCMC like '%" + this.tb_KCMC.Text + "%' "; if (this.ddl_YXSM.SelectedValue != "0") where += " and JW_KCXX.YXSM=" + this.ddl_YXSM.SelectedValue; if (this.ddl_XNM.SelectedValue != "0") where += " and JX_KKXX.XNM=" + this.ddl_XNM.SelectedValue; if (this.ddl_XQM.SelectedValue != "0") where += " and JX_KKXX.XQM=" + this.ddl_XQM.SelectedValue; if (this.ddl_SKJS.SelectedValue != "0") where += " and JX_KKKS.SKJS_KEY=" + this.ddl_SKJS.SelectedValue; where += "order by KSZ"; DAL_GSAO_JW.Controller.JX_KKKS C_JX_KKKS = new DAL_GSAO_JW.Controller.JX_KKKS(); DAL_GSAO_JW.Controller.JX_XSXK C_JX_XSXK = new DAL_GSAO_JW.Controller.JX_XSXK(); DataTable dt = C_JX_KKKS.GetList_Join_All(where).Tables[0]; this.GridView1.DataSource = dt; this.GridView1.DataBind(); string KSKCKEY_In = "0"; for (int i = 0; i < dt.Rows.Count; i++) { KSKCKEY_In += "," + dt.Rows[i]["KKXX_KEY"].ToString(); } DataTable dt_JX_XSXK = C_JX_XSXK.GetList(" KSKCKEY in (" + KSKCKEY_In + ")").Tables[0]; this.lb_total.Text = "总计-" + dt.Rows.Count.ToString() + "-项"; for (int i = 0; i < this.GridView1.Rows.Count; i++) { System.Web.UI.WebControls.Image img_show = (System.Web.UI.WebControls.Image)this.GridView1.Rows[i].FindControl("img_show"); img_show.Attributes.Add("onclick", "location.href='MY_DEPART_KKKS_XG.aspx?showonly=true&JX_KKKS_KEY=" + this.GridView1.Rows[i].Cells[0].Text.Trim() + "';"); System.Web.UI.WebControls.Image img_modify = (System.Web.UI.WebControls.Image)this.GridView1.Rows[i].FindControl("img_modify"); img_modify.Attributes.Add("onclick", "location.href='MY_DEPART_KKKS_XG.aspx?JX_KKKS_KEY=" + this.GridView1.Rows[i].Cells[0].Text.Trim() + "';"); System.Web.UI.WebControls.ImageButton btn_delete = (System.Web.UI.WebControls.ImageButton)this.GridView1.Rows[i].FindControl("btn_delete"); btn_delete.Attributes.Add("onclick", "return confirm('确定要取消-" + this.GridView1.Rows[i].Cells[7].Text.Trim() + "-吗?');"); if (this.GridView1.Rows[i].Cells[18].Text == "1") this.GridView1.Rows[i].Cells[18].Text = "已保存"; if (this.GridView1.Rows[i].Cells[18].Text == "2") this.GridView1.Rows[i].Cells[18].Text = "开始选课"; if (this.GridView1.Rows[i].Cells[18].Text == "3") this.GridView1.Rows[i].Cells[17].Text = "结束选课"; DataRow[] row = dt_JX_XSXK.Select("KSKCKEY=" + this.GridView1.Rows[i].Cells[0].Text); this.GridView1.Rows[i].Cells[19].Text = "<a href='MY_DEPART_KKKS_XG.aspx?showonly=true&JX_KKKS_KEY=" + this.GridView1.Rows[i].Cells[0].Text.Trim() + "'>" + row.Length.ToString() + "</a>"; //同一周次同一时间点同个地点有相冲突的编排,显示红色 DataTable dt_select_ct = Common_GSAO.Tools.GetNewDataTable(dt, "KKXX_KEY not in ('" + GridView1.Rows[i].Cells[1].Text + "') and SKJS_KEY='" + GridView1.Rows[i].Cells[2].Text + "' and (ZC1='" + GridView1.Rows[i].Cells[11].Text + "' or ZC2='" + GridView1.Rows[i].Cells[12].Text + "' or ZC3='" + GridView1.Rows[i].Cells[13].Text + "' or ZC4='" + GridView1.Rows[i].Cells[14].Text + "' or ZC5='" + GridView1.Rows[i].Cells[15].Text + "' or ZC6='" + GridView1.Rows[i].Cells[16].Text + "' or ZC7='" + GridView1.Rows[i].Cells[17].Text + "')"); //有冲突的排课 if (dt_select_ct.Rows.Count > 0) { bool is_qudian_nei = (int.Parse(dt_select_ct.Rows[0]["KSZ"].ToString()) >= int.Parse(GridView1.Rows[i].Cells[9].Text) && int.Parse(dt_select_ct.Rows[0]["KSZ"].ToString()) <= int.Parse(GridView1.Rows[i].Cells[10].Text)) || (int.Parse(dt_select_ct.Rows[0]["JSZ"].ToString()) >= int.Parse(GridView1.Rows[i].Cells[9].Text) && int.Parse(dt_select_ct.Rows[0]["JSZ"].ToString()) <= int.Parse(GridView1.Rows[i].Cells[10].Text)); if (is_qudian_nei) { GridView1.Rows[i].Cells[7].CssClass = "colorRed"; GridView1.Rows[i].Cells[8].CssClass = "colorRed"; GridView1.Rows[i].Cells[9].CssClass = "colorRed"; GridView1.Rows[i].Cells[10].CssClass = "colorRed"; GridView1.Rows[i].Cells[11].CssClass = "colorRed"; GridView1.Rows[i].Cells[12].CssClass = "colorRed"; GridView1.Rows[i].Cells[13].CssClass = "colorRed"; GridView1.Rows[i].Cells[14].CssClass = "colorRed"; GridView1.Rows[i].Cells[15].CssClass = "colorRed"; GridView1.Rows[i].Cells[16].CssClass = "colorRed"; GridView1.Rows[i].Cells[17].CssClass = "colorRed"; } else { GridView1.Rows[i].Cells[7].CssClass = "colorBlack"; GridView1.Rows[i].Cells[8].CssClass = "colorBlack"; GridView1.Rows[i].Cells[9].CssClass = "colorBlack"; GridView1.Rows[i].Cells[10].CssClass = "colorBlack"; GridView1.Rows[i].Cells[11].CssClass = "colorBlack"; GridView1.Rows[i].Cells[12].CssClass = "colorBlack"; GridView1.Rows[i].Cells[13].CssClass = "colorBlack"; GridView1.Rows[i].Cells[14].CssClass = "colorBlack"; GridView1.Rows[i].Cells[15].CssClass = "colorBlack"; GridView1.Rows[i].Cells[16].CssClass = "colorBlack"; GridView1.Rows[i].Cells[17].CssClass = "colorBlack"; } } } } protected void GridView1_PageIndexChanged(object sender, EventArgs e) { Bind_gv(); } protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { e.Row.Attributes.Add("onmouseover", "if(this.style.backgroundColor!='#ffffd0'){this.oldcolor=this.style.backgroundColor;this.style.backgroundColor='#c4e4ff';}"); e.Row.Attributes.Add("onmouseout", "if(this.style.backgroundColor!='#ffffd0')this.style.backgroundColor=this.oldcolor;"); e.Row.Attributes.Add("onmousedown", "if(this.style.backgroundColor=='#ffffd0')this.style.backgroundColor='white';else this.style.backgroundColor='#ffffd0';"); } if ((e.Row.RowType == DataControlRowType.DataRow) || (e.Row.RowType == DataControlRowType.Header) || (e.Row.RowType == DataControlRowType.Footer)) { e.Row.Cells[0].Visible = false; e.Row.Cells[1].Visible = false; e.Row.Cells[2].Visible = false; } } protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) { if (e.CommandName == "delete1") { int nIdx = Convert.ToInt32(e.CommandArgument.ToString()); GridViewRow row = this.GridView1.Rows[nIdx]; string JX_KKKS_KEY = row.Cells[0].Text; DAL_GSAO_JW.Controller.JX_KKKS C_JX_KKXX = new DAL_GSAO_JW.Controller.JX_KKKS(); C_JX_KKXX.Delete(int.Parse(JX_KKKS_KEY)); Bind_gv(); } } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { try { GridView1.PageIndex = e.NewPageIndex; } catch { GridView1.PageIndex = 0; } } protected void btn_search_Click(object sender, EventArgs e) { Bind_gv(); } public override void VerifyRenderingInServerForm(Control control) { //base.VerifyRenderingInServerForm(control); } /// <summary> /// Excel导入 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnExcel_Click(object sender, EventArgs e) { divExcel.Visible = true; } protected void btn_import_Click(object sender, EventArgs e) { string strFileName = ""; DataTable dtLoadedTable = null; DataTable dtPutOutDetailList = null; //保存文件 if (!SaveFile(out strFileName)) return; //加载数据 if (!LoadData(strFileName, out dtLoadedTable)) return; //分析数据 if (!AnalyseData(dtLoadedTable, out dtPutOutDetailList)) return; //插入数据 InsertData(dtPutOutDetailList); } private bool SaveFile(out string strFileName) { bool bRet = true; strFileName = ""; string strMsg = ""; string strUploadDir = "Temp"; strUploadDir = "../" + strUploadDir + "/"; if (!Common_GSAO.Tools.FileSave(this, inputFile, strUploadDir, out strFileName, out strMsg)) { bRet = false; Common_GSAO.Tools.ShowAlert(strMsg); } return bRet; } private bool LoadData(string strFileName, out DataTable dtLoadedTable) { bool bRet = true; dtLoadedTable = null; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("../Temp/" + strFileName) + ";" + "Extended Properties=Excel 8.0;"; DataTable FileDt = new DataTable(); OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); try { //取得EXCEL的第一个工作薄名(表名) DataTable T = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, null }); sheet_name = T.Rows[0][2].ToString(); OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + sheet_name + "] where rtrim(开课班)<>'' or rtrim(开始周)<>'' or rtrim(结束周)<>'' ", conn); DataSet ds = new DataSet(); da.Fill(ds); FileDt = ds.Tables[0]; da.Dispose(); conn.Close(); GC.Collect(); dtLoadedTable = FileDt; } catch (Exception ex) { Page.Response.Write(ex); GC.Collect(); Common_GSAO.Tools.ShowAlert("您上传的文件格式不正确!"); bRet = false; conn.Close(); } return bRet; } private DataTable InitDtPutOutDetailList() { DataTable dtRet = new DataTable(); dtRet.Columns.Add("SKJS_KEY", typeof(int)); dtRet.Columns.Add("KSZ", typeof(int)); dtRet.Columns.Add("JSZ", typeof(int)); dtRet.Columns.Add("KKXX_KEY", typeof(int)); dtRet.Columns.Add("ZC1", typeof(string)); dtRet.Columns.Add("ZC2", typeof(string)); dtRet.Columns.Add("ZC3", typeof(string)); dtRet.Columns.Add("ZC4", typeof(string)); dtRet.Columns.Add("ZC5", typeof(string)); dtRet.Columns.Add("ZC6", typeof(string)); dtRet.Columns.Add("ZC7", typeof(string)); dtRet.Columns.Add("SKZC", typeof(string)); return dtRet; } private bool AnalyseData(DataTable dtLoadedTable, out DataTable dtPutOutDetailList) { bool bRet = true; string strMsg = ""; dtPutOutDetailList = InitDtPutOutDetailList(); int iErrorCount = 0; int iRowCount = 0; int error = 0; string err_no = ""; if (dtLoadedTable.Rows.Count < 1) { Common_GSAO.Tools.ShowAlert("没有任何数据!"); return false; } DAL_GSAO_JW.Controller.JX_KKXX C_JX_KKXX = new DAL_GSAO_JW.Controller.JX_KKXX(); //Common_GSAO.Tools.GetNewDataTable DataTable dtKKXX = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select KKXX_KEY,XNM,XQM,JXDWH,KCMC from JX_KKXX").Tables[0]; DataTable dtSKJS = DAL_GSAO_SZGW.DbHelperSQL_GSAO.Query("select SKJS_KEY,SKJXMC from TB_SKJS where SKJS_KEY in (select SKJS_KEY from JW_JXDWKS where YXSM=" + ddl_Excel_YXSM.SelectedValue + ")").Tables[0]; foreach (DataRow dr in dtLoadedTable.Rows) { iRowCount++; if (iErrorCount > 10) { strMsg += "错误行数已经达到10行以上,程序不再分析该文件的数据格式;"; break; } try { DataRow drNew = dtPutOutDetailList.NewRow(); DataTable dt_select_KKXX = Common_GSAO.Tools.GetNewDataTable(dtKKXX, "XNM=" + ddl_Excel_XNM.SelectedValue + " and XQM=" + ddl_Excel_XQM.SelectedValue + " and JXDWH='" + ddl_Excel_YXSM.SelectedValue + "' and KCMC='" + dr["开课班"].ToString() + "'"); if (dt_select_KKXX.Rows.Count > 0) { drNew["KKXX_KEY"] = int.Parse(dt_select_KKXX.Rows[0]["KKXX_KEY"].ToString()); } else { Common_GSAO.Tools.ShowAlert("无法分析第" + iRowCount + "行的[开课班]数据;"); return false; } DataTable dt_select_SKJS = Common_GSAO.Tools.GetNewDataTable(dtSKJS, "SKJXMC='" + dr["授课地点"].ToString() + "'"); if (dt_select_SKJS.Rows.Count > 0) { drNew["SKJS_KEY"] = int.Parse(dt_select_SKJS.Rows[0]["SKJS_KEY"].ToString()); } else { Common_GSAO.Tools.ShowAlert("无法分析第" + iRowCount + "行的[授课地点]数据;"); return false; } try { drNew["KSZ"] = int.Parse(dr["开始周"].ToString()); } catch { Common_GSAO.Tools.ShowAlert("第" + iRowCount + "行的[开始周]数据必须是数字类型;"); return false; } try { drNew["JSZ"] = int.Parse(dr["结束周"].ToString()); } catch { Common_GSAO.Tools.ShowAlert("第" + iRowCount + "行的[结束周]数据必须是数字类型;"); return false; } drNew["ZC1"] = dr["周一"].ToString(); drNew["ZC2"] = dr["周二"].ToString(); drNew["ZC3"] = dr["周三"].ToString(); drNew["ZC4"] = dr["周四"].ToString(); drNew["ZC5"] = dr["周五"].ToString(); drNew["ZC6"] = dr["周六"].ToString(); drNew["ZC7"] = dr["周日"].ToString(); if (dr["授课周次"].ToString() == "") { drNew["SKZC"] = drNew["KSZ"] + "-" + drNew["JSZ"] + "周"; } else { drNew["SKZC"] = dr["授课周次"].ToString(); } //分析代码 dtPutOutDetailList.Rows.Add(drNew); } catch { bRet = false; strMsg += string.Format("无法分析第{0}行的数据;\n", iRowCount); iErrorCount++; } } if (strMsg != "") { Common_GSAO.Tools.ShowAlert(strMsg); } return bRet; } private void InsertData(DataTable dtPutOutDetailList) { DAL_GSAO_JW.Entities.JX_KKKS E_JX_KKKS = new DAL_GSAO_JW.Entities.JX_KKKS(); DAL_GSAO_JW.Controller.JX_KKKS C_JX_KKKS = new DAL_GSAO_JW.Controller.JX_KKKS(); //插入 for (int i = 0; i < dtPutOutDetailList.Rows.Count; i++) { E_JX_KKKS.KKXX_KEY = int.Parse(dtPutOutDetailList.Rows[i]["KKXX_KEY"].ToString()); E_JX_KKKS.SKJS_KEY = int.Parse(dtPutOutDetailList.Rows[i]["SKJS_KEY"].ToString()); E_JX_KKKS.KSZ = int.Parse(dtPutOutDetailList.Rows[i]["KSZ"].ToString()); E_JX_KKKS.JSZ = int.Parse(dtPutOutDetailList.Rows[i]["JSZ"].ToString()); E_JX_KKKS.ZC1 = dtPutOutDetailList.Rows[i]["ZC1"].ToString(); E_JX_KKKS.ZC2 = dtPutOutDetailList.Rows[i]["ZC2"].ToString(); E_JX_KKKS.ZC3 = dtPutOutDetailList.Rows[i]["ZC3"].ToString(); E_JX_KKKS.ZC4 = dtPutOutDetailList.Rows[i]["ZC4"].ToString(); E_JX_KKKS.ZC5 = dtPutOutDetailList.Rows[i]["ZC5"].ToString(); E_JX_KKKS.ZC6 = dtPutOutDetailList.Rows[i]["ZC6"].ToString(); E_JX_KKKS.ZC7 = dtPutOutDetailList.Rows[i]["ZC7"].ToString(); E_JX_KKKS.SKZC = dtPutOutDetailList.Rows[i]["SKZC"].ToString(); C_JX_KKKS.Add(E_JX_KKKS); } Common_GSAO.Tools.ShowAlertAndRedirect("成功导入-" + dtPutOutDetailList.Rows.Count.ToString() + "笔-数据!", "MY_DEPART_KKKS.aspx"); } /// <summary> /// 关闭Excel导入窗口 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btn_close_Click(object sender, EventArgs e) { divExcel.Visible = false; } } }
相关方法:
public static DataTable GetNewDataTable(DataTable dt, string condition) { DataTable newdt = new DataTable(); newdt = dt.Clone(); DataRow[] dr = dt.Select(condition); for (int i = 0; i < dr.Length; i++) { newdt.ImportRow((DataRow)dr[i]); } return newdt;//返回的查询结果 }
public static bool FileSave(System.Web.UI.Page page, System.Web.UI.HtmlControls.HtmlInputFile inputFile, string strSaveDir, out string filename, out string strMsg) { filename = ""; strMsg = ""; //检查上传文件不为空 if (inputFile.PostedFile.ContentLength > 0) { string strName = inputFile.PostedFile.FileName; //取得文件名(抱括路径)里最后一个"."的索引 int intExt = strName.LastIndexOf("."); //取得文件扩展名 string strExt = strName.Substring(intExt).ToLower(); string strNewName = Guid.NewGuid().ToString() + strExt; filename = strNewName; //注意: 我这里用Server.MapPath()取当前文件的绝对目录.在asp.net里"\"必须用"\\"代替 inputFile.PostedFile.SaveAs(page.Server.MapPath(strSaveDir + strNewName)); return true; } else { strMsg = "请选择你要上传的文件!"; return false; } }