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

如何把Excel数据导入到SQL2008数据库的实例方法

程序员文章站 2022-05-03 07:55:28
复制代码 代码如下:private void addmanydata_click(object sender, routedeventargs e)  &nb...

如何把Excel数据导入到SQL2008数据库的实例方法

复制代码 代码如下:

private void addmanydata_click(object sender, routedeventargs e)
       {
           openfiledialog openfiledialog = new openfiledialog();
           openfiledialog.filter = "excel文件|*.xls";

           if ((bool)openfiledialog.showdialog())  
           {  
                fileinfo fileinfo = new fileinfo(openfiledialog.filename);  
                string filepath = fileinfo.fullname;  
                string connexcel = "provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";extended properties=excel 8.0";

                using (oledbconnection oledbconn = new oledbconnection(connexcel))
                {
                    oledbconn.open();

                    //获取excel表  
                    datatable dt = oledbconn.getoledbschematable(oledbschemaguid.tables, null);
                    //获取excel表的表名 
                    string tablename = dt.rows[0][2].tostring().trim();
                    //去掉空格
                    tablename = "[" + tablename.replace("'", "") + "]";

                    //利用sql语句从excel文件里获取数据  
                     string query = @"select 学号,姓名,公益劳动,电子工艺实习,操作系统 ,计算机组成,数值分析,网络设备与集成,动态网站开发实验周,动态网站开发,均分,排名 from ";                  + tablename;
                     dataset dataset = new dataset();

                     using (oledbcommand oledbcomm = oledbconn.createcommand())
                     {
                         oledbcomm.commandtext = query;
                         oledbdataadapter oleadapter = new oledbdataadapter(oledbcomm);
                         oleadapter.fill(dataset);
                     }
                     string connstr = "data source=heshuhua-pc;initial catalog=rsmsystem;integrated security=true";
                     //利用sqlbulkcopy批量插入数据
                    using (sqlbulkcopy sqlbc = new sqlbulkcopy(connstr))
                    {
                        sqlbc.destinationtablename = "t_stuscore";
                        // sqlbc.columnmappings.add("学号", "stunum"),第一个参数对应数据库中的列名,
                        //第二个参数对应数据库中相应表的列名
                        sqlbc.columnmappings.add("学号", "stunum");
                        sqlbc.columnmappings.add("姓名", "stuname");
                        sqlbc.columnmappings.add("公益劳动", "activity");
                        sqlbc.columnmappings.add("电子工艺实习", "elecact");
                        sqlbc.columnmappings.add("操作系统", "oprationsystem");
                        sqlbc.columnmappings.add("计算机组成", "computermaded");
                        sqlbc.columnmappings.add("数值分析", "dataanalyze");
                        sqlbc.columnmappings.add("网络设备与集成", "network");
                        sqlbc.columnmappings.add("动态网站开发实验周", "webweek");
                        sqlbc.columnmappings.add("动态网站开发", "webmake");
                        sqlbc.columnmappings.add("均分", "avscore");
                        sqlbc.columnmappings.add("排名", "stupaiming");
                        sqlbc.writetoserver(dataset.tables[0]);
                        messagebox.show("数据导入成功!");

                    }
                }  

           }  

       }