如何編寫xls表格導(dǎo)入數(shù)據(jù)庫(kù)功能代碼

這篇文章主要介紹“如何編寫xls表格導(dǎo)入數(shù)據(jù)庫(kù)功能代碼”，在日常操作中，相信很多人在如何編寫xls表格導(dǎo)入數(shù)據(jù)庫(kù)功能代碼問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”如何編寫xls表格導(dǎo)入數(shù)據(jù)庫(kù)功能代碼”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

復(fù)制代碼 代碼如下:

protected void Button1_Click(object sender, EventArgs e)
        {

            if (FileUpload1.HasFile == false)
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "shifou", "<script>alert('請(qǐng)您選擇   Excel文件')</script> ");
                return;
            }
            string Name = FileUpload1.PostedFile.FileName; //獲取初始文件名
            int j = Name.LastIndexOf("."); //取得文件名中最后一個(gè)"."的索引
            string newext1 = Name.Substring(j); //獲取文件擴(kuò)展名
            if (newext1 != ".xls" && newext1 != ".xlsx")
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert('只可以選擇  Excel文件')</script>");
                return;//當(dāng)選擇的不是Excel文件時(shí),返回
            }
            SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
            cn.Open();
            //savePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);//    FileUpload1.PostedFile.FileName.ToString();
            if (FileUpload1.HasFile == false)//HasFile用來(lái)檢查FileUpload是否有指定文件
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "wenjian", "<script>alert('Excel路徑有  問(wèn)題')</script>");
                return;//當(dāng)無(wú)文件時(shí),返回
            }

            filename = FileUpload1.FileName;  //Path.GetFileNameWithoutExtension  (FileUpload1.PostedFile.FileName);
            savePath = Server.MapPath(("../../UploadFiles/xls/") + filename);
            FileUpload1.SaveAs(savePath); //保存XLS文件

            DataSet ds = ExecleDs(savePath, filename);          //調(diào)用自定義方法
            DataRow[] dr = ds.Tables[0].Select();            //定義一個(gè)DataRow數(shù)組
            int rowsnum = ds.Tables[0].Rows.Count;
            if (rowsnum == 0)
            {
                //當(dāng)Excel表為空時(shí),對(duì)用戶進(jìn)行提示
                Page.ClientScript.RegisterStartupScript(this.GetType(), "kong", "<script>alert('Excel表為空表,   無(wú)數(shù)據(jù)!')</script>");
            }
            else
            {
                    for (int i = 0; i < dr.Length; i++)
                    {

　　　　　　　　//自定義的model-student
                        student.belong = "";
                        student.birthday = Convert.ToDateTime("1990-1-1");
                        student.classid = 1;
                        student.id = 1;
                        student.inschool = Convert.ToDateTime("1990-1-1");
                        student.phone = "";
                        student.sex = "";
                        student.type = "";
                        student.isaudite = "否";
                        student.roleid = Convert.ToInt32("22");
                        try
                        {
                            student.stuname = dr[i]["姓名"].ToString();
                            student.stuid = dr[i]["學(xué)號(hào)"].ToString();
                            student.username = dr[i]["姓名"].ToString();
                            student.userpassword = dr[i]["學(xué)號(hào)"].ToString();
                        }
                        catch(Exception ex) {
                            Response.Write("<script>alert('"+ex.Message+"')</script>");
                            return;
                        }
                        DataSet ds2 = studentbll.GetList("stuid='" + dr[i]["學(xué)號(hào)"].ToString() + "'");
                        if (ds2.Tables[0].Rows.Count == 0)
                        {
                            try
                            {
                                bool addstudent = Convert.ToBoolean(studentbll.Add(student));
                                if (addstudent)
    Page.ClientScript.RegisterStartupScript(this.GetType(), "daochu",     "<script>alert('Excle表導(dǎo)入成功!')</script>");
                            }
                            catch (MembershipCreateUserException ex)       //捕捉異常
                            {
                                Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu",      "<script>alert('導(dǎo)入內(nèi)容:" + ex.Message + "')</script> ");
                            }
                        }
                        else
                        {
                            Page.ClientScript.RegisterStartupScript(this.GetType(), "chongfu", "<script>alert    ('內(nèi)容重復(fù)！禁止導(dǎo)入')</script> ");
                            continue;
                        }
                    }
            }
        }

  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;'";//這段字符串注意空格別寫錯(cuò)否則會(huì)報(bào)錯(cuò)
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet18$]", conn);//【sheet18$】是表格名

            DataSet ds = new DataSet();
            odda.Fill(ds, table);
            return ds;
        }

到此，關(guān)于“如何編寫xls表格導(dǎo)入數(shù)據(jù)庫(kù)功能代碼”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！