C#將Excel資料表匯入SQL資料庫的兩種方法

標籤：string   ide   oid   raw   write   man   目標   匯入資料庫   generic   

最近用寫個winform程式想用excel 檔案匯入資料庫中，網上尋求辦法，找到了這個經過嘗試可以使用。

方法一:

實現在c#中可高效的將excel資料匯入到sqlserver資料庫中,很多人通過迴圈來拼接sql，這樣做不但容易出錯而且效率低下，最好的辦法是使用bcp，也就是System.Data.SqlClient.SqlBulkCopy 類來實現。

    using System;        using System.Collections.Generic;        using System.ComponentModel;        using System.Data;        using System.Drawing;        using System.Linq;        using System.Text;        using System.Windows.Forms;        using System.Data.OleDb;                namespace ExcelToSQL        {            public partial class Form1 : Form            {                public Form1()                {                    InitializeComponent();                }                        private void button1_Click(object sender, EventArgs e)                {                    //測試，將excel中的student匯入到sqlserver的db_test中，如果sql中的資料表不存在則建立                        string connString = "server = (local); uid = sa; pwd = sa; database = db_test";                    System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();                       if (fd.ShowDialog() == DialogResult.OK)                       {                        TransferData(fd.FileName, "student", connString);                    }                   }                           public void TransferData(string excelFile, string sheetName, string connectionString)                   {                       DataSet ds = new DataSet();                    try                      {                        //擷取全部資料                         string strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties = Excel 8.0;";                           OleDbConnection conn = new OleDbConnection(strConn);                        conn.Open();                        string strExcel = "";                        OleDbDataAdapter myCommand = null;                           strExcel = string.Format("select * from [{0}$]", sheetName);                        myCommand = new OleDbDataAdapter(strExcel, strConn);                        myCommand.Fill(ds, sheetName);                                  //如果目標表不存在則建立,excel檔案的第一行為欄位標題,從第二行開始全部都是資料記錄                         string strSql = string.Format("if not exists(select * from sysobjects where name = ‘{0}‘) create table {0}(", sheetName);   //以sheetName為表名                                 foreach (System.Data.DataColumn c in ds.Tables[0].Columns)                        {                               strSql += string.Format("[{0}] varchar(255),", c.ColumnName);                           }                           strSql = strSql.Trim(‘,‘) + ")";                                     using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))                           {                            sqlconn.Open();                               System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();                               command.CommandText = strSql;                               command.ExecuteNonQuery();                               sqlconn.Close();                        }                           //用bcp匯入資料                            //excel檔案中列的順序必須和資料表的列順序一致，因為資料匯入時，是從excel檔案的第二行資料開始，不管資料表的結構是什麼樣的，反正就是第一列的資料會插入到資料表的第一欄欄位中，第二列的資料插入到資料表的第二欄欄位中，以此類推，它本身不會去判斷要插入的資料是對應資料表中哪一個欄位的                         using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))                           {                               bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);                               bcp.BatchSize = 100;//每次傳輸的行數                                bcp.NotifyAfter = 100;//進度提示的行數                                bcp.DestinationTableName = sheetName;//目標表                                bcp.WriteToServer(ds.Tables[0]);                        }                       }                       catch (Exception ex)                       {                           System.Windows.Forms.MessageBox.Show(ex.Message);                       }                 }                             //進度顯示                    void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)                   {                       this.Text = e.RowsCopied.ToString();                       this.Update();                   }              }           }    

方法二：

先將Excel檔案轉換成DataTable，然後再迴圈將記錄插入到資料庫表中，這種方式可以任由程式員來選擇將哪列資料匯入到資料表的哪個欄位中

    using System;      using System.Collections.Generic;      using System.ComponentModel;      using System.Data;      using System.Drawing;      using System.Linq;      using System.Text;      using System.Windows.Forms;      using System.Data.OleDb;      using System.Data.SqlClient;           namespace ExcelToSQL      {          public partial class Form2 : Form          {              public Form2()              {                  InitializeComponent();              }                   DataTable dt = new DataTable();              string connString = "server = (local); uid = sa; pwd = sa; database = db_test";              SqlConnection conn;                   private void button1_Click(object sender, EventArgs e)              {                  System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();                  if (fd.ShowDialog() == DialogResult.OK)                  {                      string fileName = fd.FileName;                      bind(fileName);                  }              }              private void bind(string fileName)              {                  string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +                       "Data Source=" + fileName + ";" +                       "Extended Properties=‘Excel 8.0; HDR=Yes; IMEX=1‘";                  OleDbDataAdapter da = new OleDbDataAdapter("SELECT *  FROM [student$]", strConn);                  DataSet ds = new DataSet();                  try                 {                      da.Fill(ds);                      dt = ds.Tables[0];                      this.dataGridView1.DataSource = dt;                  }                  catch (Exception err)                  {                      MessageBox.Show("操作失敗！" + err.ToString());                  }              }                   //將Datagridview1的記錄插入到資料庫                private void button2_Click(object sender, EventArgs e)              {                  conn = new SqlConnection(connString);                  conn.Open();                  if (dataGridView1.Rows.Count > 0)                  {                      DataRow dr = null;                      for (int i = 0; i < dt.Rows.Count; i++)                      {                          dr = dt.Rows[i];                          insertToSql(dr);                      }                      conn.Close();                      MessageBox.Show("匯入成功！");                  }                  else                 {                      MessageBox.Show("沒有資料！");                  }              }              private void insertToSql(DataRow dr)              {                  //excel表中的列名和資料庫中的列名一定要對應                  string name = dr["StudentName"].ToString();                  string sex = dr["Sex"].ToString();                  string no = dr["StudentIDNO"].ToString();                  string major = dr["Major"].ToString();                  string sql = "insert into student values(‘" + name + "‘,‘" + sex + "‘,‘" + no + "‘,‘" + major +"‘)";                           SqlCommand cmd = new SqlCommand(sql, conn);                             cmd.ExecuteNonQuery();              }          }      } 

C#將Excel資料表匯入SQL資料庫的兩種方法