如何高效的將excel匯入sqlserver

 大部分人都知道用oledb來讀取資料到dataset，但是讀取之後怎麼處理dataset就千奇百怪了。很多人通過迴圈來拼接sql，這樣做不但容易出錯而且效率低下，System.Data.SqlClient.SqlBulkCopy 對於新手來說還是比較陌生的，這個就是傳說中效率極高的bcp，6萬多資料從excel匯入到sql只需要4.5秒。
using System;<br />using System.Data;<br />using System.Windows.Forms;<br />using System.Data.OleDb;<br />namespace WindowsApplication2<br />{<br /> public partial class Form1 : Form<br /> {<br /> public Form1()<br /> {<br /> InitializeComponent();<br /> }</p><p> private void button1_Click(object sender, EventArgs e)<br /> {<br /> //測試，將excel中的sheet1匯入到sqlserver中<br /> string connString = "server=localhost;uid=sa;pwd=sqlgis;database=master";<br /> System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();<br /> if (fd.ShowDialog() == DialogResult.OK)<br /> {<br /> TransferData(fd.FileName, "sheet1", connString);<br /> }<br /> }</p><p> public void TransferData(string excelFile, string sheetName, string connectionString)<br /> {<br /> DataSet ds = new DataSet();<br /> try<br /> {<br /> //擷取全部資料<br /> string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";<br /> OleDbConnection conn = new OleDbConnection(strConn);<br /> conn.Open();<br /> string strExcel = "";<br /> OleDbDataAdapter myCommand = null;<br /> strExcel = string.Format("select * from [{0}$]", sheetName);<br /> myCommand = new OleDbDataAdapter(strExcel, strConn);<br /> myCommand.Fill(ds, sheetName);</p><p> //如果目標表不存在則建立<br /> string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);<br /> foreach (System.Data.DataColumn c in ds.Tables[0].Columns)<br /> {<br /> strSql += string.Format("[{0}] varchar(255),", c.ColumnName);<br /> }<br /> strSql = strSql.Trim(',') + ")";</p><p> using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))<br /> {<br /> sqlconn.Open();<br /> System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();<br /> command.CommandText = strSql;<br /> command.ExecuteNonQuery();<br /> sqlconn.Close();<br /> }<br /> //用bcp匯入資料<br /> using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))<br /> {<br /> bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);<br /> bcp.BatchSize = 100;//每次傳輸的行數<br /> bcp.NotifyAfter = 100;//進度提示的行數<br /> bcp.DestinationTableName = sheetName;//目標表<br /> bcp.WriteToServer(ds.Tables[0]);<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /> System.Windows.Forms.MessageBox.Show(ex.Message);<br /> }</p><p> }</p><p> //進度顯示<br /> void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)<br /> {<br /> this.Text = e.RowsCopied.ToString();<br /> this.Update();<br /> }<br /> }<br />}<br />

上面的TransferData基本可以直接使用，如果要考慮周全的話，可以用oledb來擷取excel的表結構，並且加入ColumnMappings來設定對照欄位，這樣效果就完全可以做到和sqlserver的dts相同的效果了。

本文資料整理來自：CSDN