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>
                &nbsp;&nbsp;
                <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>&nbsp;&nbsp;&nbsp;&nbsp;</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" />&nbsp;
            <asp:Image id="img_modify" runat="server" ImageUrl="../images/icon_edit.gif" ToolTip="修改" CssClass="cursorPointer" />&nbsp;

            <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"> &nbsp;
                <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">&nbsp;
                <asp:Button ID="btn_import" runat="server" Text="导入" CssClass="btnStyle mr10" onclick="btn_import_Click" />&nbsp;&nbsp;&nbsp; 
                <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;
           }
       }

 

转载于:https://www.cnblogs.com/linyongqin/articles/3929058.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值