复制代码 代码如下:
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(“数据导入成功！”);
}
}
}
}