C# 使用 SqlBulkCopy 將 Excel 資料高效匯入 SQL Server 2008 完整範例

整體流程

1. 使用 OpenFileDialog 選取 Excel 檔 (.xls)。
2. 透過 OleDbConnection (Microsoft.Jet.OLEDB.4.0) 讀取 Excel 工作表資料到 DataSet。
3. 使用 SqlBulkCopy 批量寫入 SQL Server 目標資料表。
4. 透過 ColumnMappings 對應 Excel 欄位與資料表欄位。

完整程式碼 (WPF / WinForms 通用)

private void AddManyData_Click(object sender, RoutedEventArgs e)
{
    var openFileDialog = new Microsoft.Win32.OpenFileDialog
    {
        Filter = "Excel 檔案|*.xls"
    };

    if (openFileDialog.ShowDialog() == true)
    {
        string filePath = openFileDialog.FileName;
        string connExcel = $@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES;'";

        using (var oleDbConn = new System.Data.OleDb.OleDbConnection(connExcel))
        {
            oleDbConn.Open();
            // 取得第一個工作表名稱
            DataTable schema = oleDbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string sheetName = schema.Rows[0]["TABLE_NAME"].ToString().Trim();
            sheetName = "[" + sheetName.Replace("'", "") + "]";

            string query = $@"SELECT 學號, 姓名, 公益勞動, 電子工藝實習, 作業系統, 計算機組成, 數值分析, 網絡設備與集成, 動態網站開發實驗周, 動態網站開發, 均分, 排名 FROM {sheetName}";

            var dataSet = new DataSet();
            using (var cmd = new System.Data.OleDb.OleDbCommand(query, oleDbConn))
            using (var adapter = new System.Data.OleDb.OleDbDataAdapter(cmd))
            {
                adapter.Fill(dataSet);
            }

            string connStr = @"Data Source=HESHUHUA-PC;Initial Catalog=RSMSystem;Integrated Security=True";

            using (var sqlBulk = new System.Data.SqlClient.SqlBulkCopy(connStr))
            {
                sqlBulk.DestinationTableName = "T_StuScore";
                sqlBulk.BatchSize = 5000;
                sqlBulk.BulkCopyTimeout = 300;

                // 欄位對應：Excel欄位名 -> 資料表欄位名
                sqlBulk.ColumnMappings.Add("學號", "StuNum");
                sqlBulk.ColumnMappings.Add("姓名", "StuName");
                sqlBulk.ColumnMappings.Add("公益勞動", "Activity");
                sqlBulk.ColumnMappings.Add("電子工藝實習", "ElecAct");
                sqlBulk.ColumnMappings.Add("作業系統", "OprationSystem");
                sqlBulk.ColumnMappings.Add("計算機組成", "ComputerMaded");
                sqlBulk.ColumnMappings.Add("數值分析", "DataAnalyze");
                sqlBulk.ColumnMappings.Add("網絡設備與集成", "NetWork");
                sqlBulk.ColumnMappings.Add("動態網站開發實驗周", "WebWeek");
                sqlBulk.ColumnMappings.Add("動態網站開發", "WebMake");
                sqlBulk.ColumnMappings.Add("均分", "AvScore");
                sqlBulk.ColumnMappings.Add("排名", "StuPaiMing");

                sqlBulk.WriteToServer(dataSet.Tables[0]);
                MessageBox.Show("資料匯入成功！");
            }
        }
    }
}

關鍵設定說明

• Extended Properties：HDR=YES 表示第一列為標題；IMEX=1 可強制混合型態欄位視為文字，避免型別推斷錯誤。
• SqlBulkCopy.BatchSize：分批寫入，減少記錄檔膨脹與鎖定爭用，建議 1000-5000。
• ColumnMappings：必須明確指定，否則依順序對應極易出錯。
• 目標資料表：需預先建立，欄位型別與長度需相容 Excel 資料。

常見問題與解決

進階：使用 IDataReader 避免記憶體暴增

using (var reader = cmd.ExecuteReader())
using (var bulk = new SqlBulkCopy(connStr))
{
    bulk.DestinationTableName = "T_StuScore";
    // ... 設定 ColumnMappings ...
    bulk.WriteToServer(reader); // 串流式寫入，不佔用大量記憶體
}