C#将Excel数据表导入SQL数据库的两种方法（高效率）

C#将Excel数据表导⼊SQL数据库的两种⽅法（⾼效率）⽅法⼀:使⽤bcp，也就是System.Data.SqlClient.SqlBulkCopy类来实现。usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingSystem.Windows.Forms;usingSystem.Data.OleDb;namespaceTest1{publicpartialclassForm4:Form{publicForm4(){InitializeComponent();}privatevoidbutton1_Click(objectsender,EventArgse){//connStr="Server=USER-20180315FD;initialcatalog=AliDataTech;userid=sa;password=wxd123456;ConnectTimeout=5";//测试，将excel中的student导⼊到sqlserver的db_test中，如果sql中的数据表不存在则创建//stringconnString="server=(local);uid=sa;pwd=wxd123456;database=db_test";stringconnString="Server=USER-20180315FD;initialcatalog=db_test;userid=sa;password=wxd123456;ConnectTimeout=5";System.Windows.Forms.OpenFileDialogfd=newOpenFileDialog();if(fd.ShowDialog()==DialogResult.OK){TransferData(fd.FileName,"student",connString);}}publicvoidTransferData(stringexcelFile,stringsheetName,stringconnectionString){DataSetds=newDataSet();try{//获取全部数据stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+excelFile+";"+"ExtendedProperties=Excel8.0;";OleDbConnectionconn=newOleDbConnection(strConn);conn.Open();stringstrExcel=OleDbDataAdaptermyCommand=null;strExcel=string.Format("select*from[{0}$]",sheetName);myCommand=newOleDbDataAdapter(strExcel,strConn);myCommand.Fill(ds,sheetName);//如果⽬标表不存在则创建,excel⽂件的第⼀⾏为列标题,从第⼆⾏开始全部都是数据记录stringstrSql=string.Format("ifnotexists(select*fromsysobjectswherename='{0}')createtable{0}(",sheetName);//以sheetName为表名foreach(System.Data.DataColumncinds.Tables[0].Columns){strSql+=string.Format("[{0}]varchar(255),",c.ColumnName);}strSql=strSql.Trim(',')+")";using(System.Data.SqlClient.SqlConnectionsqlconn=newSystem.Data.SqlClient.SqlConnection(connectionString)){sqlconn.Open();System.Data.SqlClient.SqlCommandcommand=sqlconn.CreateCommand();command.CommandText=strSql;command.ExecuteNonQuery();sqlconn.Close();}//⽤bcp导⼊数据//excel⽂件中列的顺序必须和数据表的列顺序⼀致，因为数据导⼊时，是从excel⽂件的第⼆⾏数据开始，不管数据表的结构是什么样的，反正就using(System.Data.SqlClient.SqlBulkCopybcp=newSystem.Data.SqlClient.SqlBulkCopy(connectionString)){bcp.SqlRowsCopied+=newSystem.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);bcp.BatchSize=1;//每次传输的⾏数bcp.NotifyAfter1;//进度提⽰的⾏数bcp.DestinationTableName=sheetName;//⽬标表bcp.WriteToServer(ds.Tables[0]);}}catch(Exceptionex){System.Windows.Forms.MessageBox.Show(ex.Message);}}//进度显⽰voidbcp_SqlRowsCopied(objectsender,System.Data.SqlClient.SqlRowsCopiedEventArgse){this.Text=e.RowsCopied.ToString();textBox1.Text=e.RowsCopied.ToString();textBox1.Update();this.Update();}privatevoidprogressBar1_Click(objectsender,EventArgse){}}}⽅法⼆：usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel;usingSystem.Data;usingSystem.Drawing;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;usingSystem.Windows.Forms;usingSystem.Data.OleDb;usingSystem.Data.SqlClient;namespaceTest1{publicpartialclassForm5:Form{publicForm5(){InitializeComponent();}DataTabledt=newDataTable();//stringconnString="Server=USER-20180315FD;initialcatalog=db_test;userid=sa;password=wxd123456;ConnectTimeout=5";stringconnString="Server=USER-20180315FD;initialcatalog=db_test;userid=sa;password=wxd123456;ConnectTimeout=5";SqlConnectionconn;privatevoidbutton1_Click(objectsender,EventArgse){System.Windows.Forms.OpenFileDialogfd=newOpenFileDialog();if(fd.ShowDialog()==DialogResult.OK){stringfileName=fd.FileName;bind(fileName);}}privatevoidbind(stringfileName){stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;"+"DataSource="+fileName+";"+"ExtendedProperties='Excel8.0;HDR=Yes;IMEX=1'";OleDbDataAdapterda=newOleDbDataAdapter("SELECT*FROM[student$]",strConn);DataSetds=newDataSet();try{da.Fill(ds);dt=ds.Tables[0];this.dataGridView1.DataSource=dt;}catch(Exceptionerr){MessageBox.Show("操作失败！"+err.ToString());}}//将Datagridview1的记录插⼊到数据库privatevoidbutton2_Click(objectsender,EventArgse){conn=newSqlConnection(connString);conn.Open();if(dataGridView1.Rows.Count>0){DataRowdr=null;for(inti=0;i<dt.Rows.Count;i++){dr=dt.Rows[i];insertToSql(dr);}conn.Close();MessageBox.Show("导⼊成功！");}else{MessageBox.Show("没有数据！");}}privatevoidinsertToSql(DataRowdr){//excel表中的列名和数据库中的列名⼀定要对应stringname=dr["StudentName"].ToString();stringsex=dr["Sex"].ToString();stringno=dr["StudentIDNO"].ToString();stringmajor=dr["Major"].ToString();stringsql="insertintostudentvalues('"+name+"','"+sex+"','"+no+"','"+major+"')";SqlCommandcmd=newSqlCommand(sql,conn);cmd.ExecuteNonQuery();}}}.9Form5 l=1恤button! butt泣宝贝名称宝贝名称搜索类型流里来谏展现里＾►曰期18月芽儿粗踉短2019/1/12 18DYV28匕CI？ICIC(|"