asp.net Excel2007导入

 

protected void Button1_Click(object sender, EventArgs e)
        {
            string Commect = "server=.;user id=sa;password=sa;database=db_MRHard";
            SqlConnection conn = new SqlConnection(Commect);
            conn.Open();

            string path = FileUpload1.PostedFile.FileName;

            path = path.Substring(path.LastIndexOf("//") + 1);

            path = path.Substring(path.LastIndexOf(".")); 

            if (path.ToLower() != ".xls" && path.ToLower() != ".xlsx")
            {
                this.ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('请选择excel文件')</script>");
                return;
            }
            path = DateTime.Now.ToString("yyyyMMddmmss") + path;

            string serverPath = Server.MapPath("//ExcelFiles//");
            if (!Directory.Exists(serverPath))
            {
                Directory.CreateDirectory(serverPath);
            }
            //保存路径   
            path = serverPath + path;
            //保存   
            FileUpload1.PostedFile.SaveAs(path);

            DataTable dt = Impoet(path);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string Name = dt.Rows[i][0].ToString(); 
                string Pwd = dt.Rows[i][1].ToString();
                string sql = "insert into tb_Login(Name,Pwd)values('" + Name + "','" + Pwd + "')";
                SqlCommand Comm = new SqlCommand(sql, conn);
                Comm.ExecuteNonQuery();
            }
            conn.Close();
            File.Delete(path);
            Response.Write("<script>alert('导入完成')</script>");
        }
        public DataTable Impoet(string filePath)
        {
            string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';"; 
            System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon);
            myConn.Open();
            string strCom = string.Format("select * from [{0}$]", "Sheet1"); 
            System.Data.OleDb.OleDbDataAdapter comm = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);
               
            DataSet ds = new DataSet();
           
            comm.Fill(ds);
                
            Conn.Close();
            return ds.Tables[0];
        }