c# execl 导入数据库

1. default.aspx.cs

 

using System.Data.OleDb;
using System.mySQLConnection;
using System.Data.SqlClient;

 

public DataSet ExecleDs(string filenameurl,string table)
    {
        
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" +filenameurl+ ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
          
            OleDbConnection conn = new OleDbConnection(strConn);
     
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [外地$]",conn);

            DataSet ds = new DataSet();

            odda.Fill(ds,table);
     
            return ds;

   }
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_ServerClick(object sender, EventArgs e)
    {

     
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName.ToString().ToLower());
        if (IsXls != ".xls")
        {
          Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script type='text/javascript' language='javascript'>alert('只可以选择Excel文件')</script>");
          return;//当选择的不是Excel文件时,返回
        }
       string error = null;
      
       SqlConnections cn = new SqlConnections();

       string strpath = FileUpload1.PostedFile.FileName.ToString();
       
        //获取Execle文件路径
       string filename = FileUpload1.FileName;
        //获取Execle文件名
       DataSet ds = ExecleDs(strpath,filename);

       DataRow[] dr = ds.Tables[0].Select(); 
        //定义一个DataRow数组
      int rowsnum = ds.Tables[0].Rows.Count;
      if (rowsnum == 0)                                
      {
           Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script type='text/javascript' language='javascript'>alert('Excel表为空表,无数据!')</script>");
          
            return;
       }
     else
      {
         for (int i = 0; i < dr.Length; i++)
           {
               string YHMC = dr[i]["通知书号"].ToString();
               string YHMM = dr[i]["姓名"].ToString();
               string DQRQ = dr[i]["身份证号"].ToString();
               string ZT = dr[i]["性别"].ToString();
               string TJSJ = dr[i]["专业"].ToString();
               string JXDM = dr[i]["住址"].ToString();
               string youbian = dr[i]["邮编"].ToString();
               string dianhua = dr[i]["联系电话"].ToString();
               string shouji = dr[i]["手机"].ToString();
               string tuijianren = dr[i]["推荐人"].ToString();

           
               //还缺少，学生ID号，学生报名号

               string sqlcheck = "select f_StudentID  from t_studentInfo  where f_Name='" + YHMM + "'And f_Identify='" + DQRQ + "'";  //检查用户是否存在

               DataTable CH = System.mySQLConnection.SqlConnections.GetDataTable(sqlcheck, "aa");

               //得到专业ID

               string sql = "select o_id from t_setclass where o_outname='" + TJSJ + "'";

               string zhuanyeid = System.mySQLConnection.SqlConnections.GetNumOneField(sql);

               if (CH != null && CH.Rows.Count > 0)
               {
                   error += "<em style='color:red;font-sixe:25px'>" + YHMM + "</em>用户基本信息已存在,此行记录无法插入!<br>";   //若用户存在,将已存在用户信息打出,并提示此用户无法插入

                   string baomingId = System.mySQLConnection.SqlConnections.GetNumOneField("select dbo.myFun_StudentInfo_Flowid(getDate())");

                   string sqltext = "insert into t_StudentAdmit(f_FlowID,f_StudentID,o_id,t_tuijianname,f_zhengshu) values('" + baomingId + "','" + CH.Rows[i]["f_StudentID"].ToString() + "','" + zhuanyeid + "','" + tuijianren + "','" + YHMC + "')";

                   System.mySQLConnection.SqlConnections.UpdateOrDelete(sqltext);

                   continue;
               }
               else
               {
                  string  studentId = System.mySQLConnection.SqlConnections.GetNumOneField("select dbo.myFun_StudentInfo_XueHao(getDate())");

                  string sqltest = "insert into t_studentInfo(f_StudentID,f_Name,f_Gender,f_Identify,f_detail,f_youbian,f_zuoji,f_shouji,f_tuijianren) values('" + studentId + "','" + YHMM + "','" + ZT + "','" + DQRQ + "','" + JXDM + "','" + youbian + "','" + dianhua + "','" + shouji + "','" + tuijianren + "')";

                  System.mySQLConnection.SqlConnections.UpdateOrDelete(sqltest);

                  string baomingId = System.mySQLConnection.SqlConnections.GetNumOneField("select dbo.myFun_StudentInfo_Flowid(getDate())");

                  string sqltext1 = "insert into t_StudentAdmit(f_FlowID,f_StudentID,o_id,t_tuijianname,f_zhengshu) values('" + baomingId + "','" + studentId + "','" + zhuanyeid + "','" + tuijianren + "','" + YHMC + "')";

                  System.mySQLConnection.SqlConnections.UpdateOrDelete(sqltext1);
      
               }   
           }
           Response.Write("<script>alert('Excle表导入成功!')</script>");

           Label1.Text = error;
         }
    }

 

2.default.aspx

 <form id="form1" runat="server">
    <div>
        <br />
        <asp:FileUpload ID="FileUpload1" runat="server" /><br />
        <br />
        <input id="Button1" type="button" value="导入数据" runat="server" onserverclick="Button1_ServerClick" /><br />
        <br />
         <asp:Label ID="Label1" runat="server" Height="31px"  Width="297px"></asp:Label><br />
        <br />
        &nbsp;</div>
    </form>