C#使用SqlBulkCopy快速导入Excel文件(xls/xlsx)

     public virtual void DoImport(IButtonInfo sender)
{
    var count = new frmKeywordImporter().Execute();
    Msg.ShowInformation($"成功导入{count}条记录！");
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSFrameworkV5.Library.CommonClass
{
    public class ImportExcel
    {
        protected DbConnection _DbConnection = null;

        private string _FileName = "";

        private bool _IsConnected = false;

        public string FileName { get { return _FileName; } }

        public ImportExcel(string fileName)
        {
            _FileName = fileName;

            if (File.Exists(fileName))
            {
                _DbConnection = CreateConnection();
                TestConnection();
            }
            else
                throw new Exception("文件不存在！" + fileName);
        }

        public int Import(string tableName, Action<DataRow> row)
        {
            var dt = GetData(tableName);
            foreach (DataRow R in dt.Rows) row(R);
            return dt.Rows.Count;
        }

        public bool TestConnection()
        {
            try
            {
                _IsConnected = this.GetTables().Count > 0;

                //excel 格式

                return _IsConnected;
            }
            catch
            {
                return false;
            }
        }

        public DbConnection CreateConnection()
        {
            string conn = "";
            string fileExt = Path.GetExtension(_FileName);

            if (fileExt == ".xls")
            {
                // IMEX=1 可把混合型作为文本型读取，避免null值         
                //xls
                conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _FileName + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
            }

            if (fileExt == ".xlsx")
            {
                //xlsx格式的OLEDB链接
                conn = "Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + _FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
            }

            if (String.IsNullOrWhiteSpace(conn)) throw new Exception("不能识别的文件格式！");

            return new OleDbConnection(conn);
        }


        /// <summary>
        /// 获取Sheet数据
        /// </summary>
        /// <returns></returns>
        public virtual DataTable GetData(string tableName)
        {
            string sql = "SELECT * FROM [" + tableName + "] ";
            return this.GetDataBySQL(sql);
        }

        public DataTable GetDataBySQL(string sql)
        {
            DataTable dt = null;
            try
            {
                if (_DbConnection.State == ConnectionState.Closed) _DbConnection.Open();
                OleDbCommand cmd = _DbConnection.CreateCommand() as OleDbCommand;
                cmd.CommandText = sql;
                dt = new DataTable();
                DbDataAdapter adp = new OleDbDataAdapter(cmd);
                adp.Fill(dt);
            }
            finally
            {
                if (_DbConnection.State != ConnectionState.Closed) _DbConnection.Close();
            }
            return dt;
        }

        public void CloseConnection()
        {
            //
        }


        /// <summary> 
        /// 取XLS文件所有表名(SHEET)
        /// </summary> 
        /// <returns></returns>       
        public List<string> GetTables()
        {
            List<string> list = new List<string>();
            try
            {
                if (_DbConnection.State == ConnectionState.Closed) _DbConnection.Open();
                DataTable dt = _DbConnection.GetSchema("Tables");
                foreach (DataRow row in dt.Rows)
                {
                    if ((row[3].ToString() == "TABLE") || (row[3].ToString() == "BASE TABLE"))
                        list.Add(row[2].ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_DbConnection.State != ConnectionState.Closed) _DbConnection.Close();
            }
            return list;
        }


    }
}

private void btnImport_Click(object sender, EventArgs e)
{
    if (!File.Exists(txtExcel.Text)) return;
    Assertion.AssertEditorEmpty(txtTableName, "请选择表名！", true);

    try
    {
        btnImport.Enabled = false;
        Application.DoEvents();

        ImportExcel import = new ImportExcel(txtExcel.Text);

        DataTable target = GetTargetTable();

        string keyword = "";

        import.Import(txtTableName.Text, (row) =>
        {
            keyword = row[1].ToStringEx().Trim();

            //关键词不为空，导入记录
            if (keyword != "")
            {
                DataRow R = target.Rows.Add();
                R["CustomerCode"] = txtCustomerCode.EditValue;
                R["Domain"] = txtDomain.EditValue;
                R["Keyword"] = keyword;
                R["IndexNo"] = ConvertEx.ToInt(row[5]);//数字类型
                R["IndexType"] = "百度";
                R["FlagSEO"] = row[6].ToStringEx() == "1" ? "Y" : "N";//FlagSEO=1/0/空
                R["InUse"] = "Y";
                R["CreationDate"] = DateTime.Now;
                R["CreatedBy"] = "admin";
                R["LastUpdateDate"] = DateTime.Now;
                R["LastUpdatedBy"] = "admin";
            }
        });

        long count = new bllCustomerKeywords().BulkImport(target);

        _ImportCount = count;

        if (count > 0)
            this.Close();
        else
            btnImport.Enabled = true;
    }
    catch (Exception ex)
    {
        btnImport.Enabled = true;
        Msg.Warning(ex.Message);
    }
}

public long SyncRows { get; set; } = 0;

public long BulkImport(DataTable dt)
{
    if (dt.Rows.Count == 0) return 0;
    int batchSize = 10;//每个批次提交的数据(单个事务)

    try
    {
        //目标数据库，连接字符串
        string conn = _Database.ConnectionString;
        SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default);

        bulk.BatchSize = batchSize;
        bulk.BulkCopyTimeout = 5 * 60;
        bulk.DestinationTableName = dt.TableName;
        bulk.SqlRowsCopied += Bulk_SqlRowsCopied;
        bulk.NotifyAfter = 1;
        bulk.WriteToServer(dt);

        return this.SyncRows;
    }
    catch (Exception ex)
    {

        //计算导入成功的记录数
        if (this.SyncRows > 0 && batchSize > 0)
        {
            if (this.SyncRows % batchSize > 0)
                this.SyncRows = this.SyncRows - SyncRows % batchSize;
            if (this.SyncRows == batchSize)
                this.SyncRows = 0;
        }

        return this.SyncRows;
    }
}

private void Bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
    SyncRows = e.RowsCopied;
}