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

发布时间：2023-07-29 15:03:58 所属栏目：MsSql教程来源：

导读：代码如下:

private void AddManyData_Click(object sender,RoutedEventArgs e)

{

OpenFileDialog openFileDialog = new OpenFileDialog();

openFileDialog.Filter = "Excel文件|*.xls"; if ((bool)o

代码如下:

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

} } }

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!