excel导入

 public DataSet ExecleDs(string filenameurl, string table)     {         string strConn = "";         string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();         if (IsXls == ".xlsx")    //excel2007的文件         {             strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filenameurl + "';Extended Properties='Excel 12.0;HDR=YES'";         }         if (IsXls == ".xls")   //excel2003的文件         {             strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filenameurl + "';Extended Properties='Excel 8.0;HDR=YES;'";         }         OleDbConnection conn = new OleDbConnection(strConn);         conn.Open();         DataSet ds = new DataSet();         OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);         odda.Fill(ds, table);         return ds;     }   protected void btnPass_Click(object sender, EventArgs e)     {

        if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件         {             Response.Write("<script>alert('请您选择Excel文件')</script> ");             return;//当无文件时,返回         }         string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名         if (IsXls != ".xls" && IsXls != ".xlsx")         {             Response.Write("<script>alert('只可以选择Excel文件')</script>");             return;//当选择的不是Excel文件时,返回         }                string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数         string savePath = Server.MapPath(("~\\upfiles\\") + filename);//Server.MapPath 获得虚拟服务器相对路径         FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上         DataSet ds = ExecleDs(savePath, filename);           //调用自定义方法         DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组         DataTable dt1;         DataTable dt2;         int rowsnum = ds.Tables[0].Rows.Count;         if (rowsnum == 0)         {             Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示         }         else         {             for (int i = 0; i < dr.Length; i++)             {                                 string banzu = dr[i]["班组名称"].ToString();//编号 列名 以下类似                 string danwei = dr[i]["所在单位"].ToString();                 string leixing = dr[i]["班组类型"].ToString();                 string dengji = dr[i]["班组等级"].ToString();                 string fenshu = Convert.ToDouble((dr[i]["分数"])).ToString("0.00");//Convert.((dr[i]["分数"]),"0.00");                 string riqi = Convert.ToDateTime(dr[i]["日期"].ToString()).ToString("yyyy-MM").Substring(0,7);                 string sqlcheck = "select count(*)  as a from phb where riqi='" + riqi + "'And adminname='" + lblAdminName.Text + "'";  //检查用户是否存在                 string sqlcheck_rq="select * from phb_rq where phb_rq ='"+riqi+"'";                 int count_rq = -1;

                if (cls.GetDataTable(sqlcheck_rq).Rows.Count > 0)                 {                     count_rq = 1;                 }                 else                 {                     count_rq = 0;                 }              //   int count = Convert.ToInt32(cls.GetDataTable(sqlcheck).Rows[0]["a"].ToString());                 int count = -1;                 if (count < 1)                 {                     string insertstr = "insert into phb (banzu,danwei,leixing,dengji,fenshu,riqi,adminname) values('" + banzu + "','" + danwei + "','" + leixing + "','" + dengji + "','" + fenshu + "','" + riqi + "','" + lblAdminName.Text + "')";                                         if (count_rq == 0)                     {                         string insertstr1 = "insert into phb_rq (phb_rq) values ('" + riqi + "')";                         dt2 = cls.GetDataTable(insertstr1);                         dt2.Dispose();                      }                     try                     {                         dt1 = cls.GetDataTable(insertstr);                         dt1.Dispose();                                            }                     catch (MembershipCreateUserException ex)       //捕捉异常                     {                         Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");                     }                 }                 else                 {                     Response.Write("<script>alert('内容重复！禁止导入');location='OrtherList2.aspx'</script></script> ");                     continue;                 }             }             Response.Write("<script>alert('Excle表导入成功!');location='OrtherList2.aspx'</script>");         }

转载于:https://www.cnblogs.com/tenghaibo/p/5035496.html