高效將 Excel 資料導入 SQL Server：使用 SqlBulkCopy 實現極速匯入

前言

許多開發者習慣使用 OLEDB 將 Excel 讀入 DataSet，但後續處理往往採用迴圈拼接 SQL，不僅容易出錯，效率也極低。其實 .NET Framework 內建的 System.Data.SqlClient.SqlBulkCopy 就是傳說中的 BCP 工具，能以極高效能將大量資料批次寫入 SQL Server。實測 6 萬筆資料從 Excel 匯入僅需 4.5 秒，效能遠超傳統逐筆寫入。

核心程式碼實作

以下完整範例示範如何讀取 Excel 工作表，自動建立目標資料表，並透過 SqlBulkCopy 批次寫入：

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace ExcelImportDemo
{
    public partial class Form1 : Form
    {
        public Form1() { InitializeComponent(); }

        private void button1_Click(object sender, EventArgs e)
        {
            string connString = "server=localhost;uid=sa;pwd=sqlgis;database=master";
            OpenFileDialog fd = new OpenFileDialog();
            if (fd.ShowDialog() == DialogResult.OK)
            {
                TransferData(fd.FileName, "Sheet1", connString);
            }
        }

        public void TransferData(string excelFile, string sheetName, string connectionString)
        {
            DataSet ds = new DataSet();
            try
            {
                // 1. 讀取 Excel 資料
                string strConn = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={excelFile};Extended Properties=Excel 8.0;";
                using (OleDbConnection conn = new OleDbConnection(strConn))
                {
                    conn.Open();
                    string strExcel = $"SELECT * FROM [{sheetName}$]";
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    adapter.Fill(ds, sheetName);
                }

                // 2. 若目標表不存在則自動建立（全部欄位預設 varchar(255)）
                string createSql = $"IF OBJECT_ID('{sheetName}') IS NULL CREATE TABLE {sheetName} (";
                foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    createSql += $"[{col.ColumnName}] varchar(255),";
                }
                createSql = createSql.TrimEnd(',') + ")";

                using (SqlConnection sqlConn = new SqlConnection(connectionString))
                {
                    sqlConn.Open();
                    using (SqlCommand cmd = sqlConn.CreateCommand())
                    {
                        cmd.CommandText = createSql;
                        cmd.ExecuteNonQuery();
                    }
                }

                // 3. 使用 SqlBulkCopy 批次寫入
                using (SqlBulkCopy bcp = new SqlBulkCopy(connectionString))
                {
                    bcp.SqlRowsCopied += (s, args) => 
                    {
                        this.Text = args.RowsCopied.ToString();
                        this.Update();
                    };
                    bcp.BatchSize = 100;
                    bcp.NotifyAfter = 100;
                    bcp.DestinationTableName = sheetName;
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

進階優化建議

• 欄位對應：使用 ColumnMappings 明確指定來源與目標欄位對應，避免欄位順序不一致導致錯誤。
• 資料型別推斷：透過 OLEDB 取得 Excel 結構描述，依實際資料型別建立資料表，而非通用 varchar(255)。
• 交易控制：大量匯入時可包裝在 SqlTransaction 中，確保全有或全無。
• 效能調校：依資料量調整 BatchSize 與 NotifyAfter，一般建議 1000~5000 筆為一批次。

總結

SqlBulkCopy 是 .NET 進行大量資料載入的最佳選擇，配合 OleDb 讀取 Excel，即可構建高效、穩定的匯入管線。若需更完整功能，可參考 SSIS 或開源庫（如 EPPlus、NPOI）處理複雜 Excel 格式。