当前位置： 移动技术网 > IT编程>数据库>MSSQL > 如何把Excel数据导入到SQL2008数据库的实例方法

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

2017年12月08日  | 移动技术网IT编程  | 我要评论

懒音哥,全屋优品,又名宜都站长网

复制代码 代码如下:

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("数据导入成功！");

                    }
                }  

           }  

       }

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网