欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

xls表格导入数据库功能实例代码

程序员文章站 2024-02-29 20:07:28
复制代码 代码如下:protected void button1_click(object sender, eventargs e)   &n...

复制代码 代码如下:

protected void button1_click(object sender, eventargs e)
        {

            if (fileupload1.hasfile == false)
            {
                page.clientscript.registerstartupscript(this.gettype(), "shifou", "<script>alert('请您选择   excel文件')</script> ");
                return;
            }
            string name = fileupload1.postedfile.filename; //获取初始文件名
            int j = name.lastindexof("."); //取得文件名中最后一个"."的索引
            string newext1 = name.substring(j); //获取文件扩展名
            if (newext1 != ".xls" && newext1 != ".xlsx")
            {
                page.clientscript.registerstartupscript(this.gettype(), "wenjian", "<script>alert('只可以选择  excel文件')</script>");
                return;//当选择的不是excel文件时,返回
            }
            sqlconnection cn = new sqlconnection(configurationmanager.appsettings["connectionstring"]);
            cn.open();
            //savepath = path.getfullpath(fileupload1.postedfile.filename);//    fileupload1.postedfile.filename.tostring();
            if (fileupload1.hasfile == false)//hasfile用来检查fileupload是否有指定文件
            {
                page.clientscript.registerstartupscript(this.gettype(), "wenjian", "<script>alert('excel路径有  问题')</script>");
                return;//当无文件时,返回
            }

            filename = fileupload1.filename;  //path.getfilenamewithoutextension  (fileupload1.postedfile.filename);
            savepath = server.mappath(("../../uploadfiles/xls/") + filename);
            fileupload1.saveas(savepath); //保存xls文件

            dataset ds = execleds(savepath, filename);          //调用自定义方法
            datarow[] dr = ds.tables[0].select();            //定义一个datarow数组
            int rowsnum = ds.tables[0].rows.count;
            if (rowsnum == 0)
            {
                //当excel表为空时,对用户进行提示
                page.clientscript.registerstartupscript(this.gettype(), "kong", "<script>alert('excel表为空表,   无数据!')</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]["学号"].tostring();
                            student.username = dr[i]["姓名"].tostring();
                            student.userpassword = dr[i]["学号"].tostring();
                        }
                        catch(exception ex) {
                            response.write("<script>alert('"+ex.message+"')</script>");
                            return;
                        }
                        dataset ds2 = studentbll.getlist("stuid='" + dr[i]["学号"].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表导入成功!')</script>");
                            }
                            catch (membershipcreateuserexception ex)       //捕捉异常
                            {
                                page.clientscript.registerstartupscript(this.gettype(), "chongfu",      "<script>alert('导入内容:" + ex.message + "')</script> ");
                            }
                        }
                        else
                        {
                            page.clientscript.registerstartupscript(this.gettype(), "chongfu", "<script>alert    ('内容重复!禁止导入')</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;'";//这段字符串注意空格别写错否则会报错
            oledbconnection conn = new oledbconnection(strconn);
            oledbdataadapter odda = new oledbdataadapter("select * from [sheet18$]", conn);//【sheet18$】是表格名

            dataset ds = new dataset();
            odda.fill(ds, table);
            return ds;
        }