Excel批量导入SQL Server

使用技术栈：NPOI+Sql Bulk Copy+C#

使用场景：

        用户导入Excel，一般数据比较大为了效率用Sql Bulk Copy进行批量写入。

public static bool SqlBulkCopyByDataTable(DataTable sourceDataTable, int batchSize = 10000)
    {
        try
        {
            string connectionString = ConfigurationManager.AppSettings["DBConnectString"];
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                #region 注释Code：这样使用事务代码执行无问题，但是会出现WriteToServer写不进SQL
                //SqlTransaction tran = connection.BeginTransaction();
                //using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran))
                #endregion
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)) 
                {

                    sqlBulkCopy.DestinationTableName = sourceDataTable.TableName;
                    sqlBulkCopy.BatchSize = batchSize;
                    for (int i = 0; i < sourceDataTable.Columns.Count; i++)
                    {
                        sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);   //Table与sql Table字段一致
                    }
                    sqlBulkCopy.WriteToServer(sourceDataTable);

                }
            }
            return true;
        }
        catch (Exception ex)
        {
            return false;
            throw ex;
        }
    }

/// <summary>
    /// 解析来访登记Excel的数据
    /// </summary>
    /// <param name="filePath"></param>
    /// <returns></returns>
    public static DataTable Get_Visit_Registration_Excel(string filePath)
    {
        IWorkbook wk = null;
        string extension = System.IO.Path.GetExtension(filePath);
        DataTable dt = new DataTable();
        dt = GetRegistrationDataTable();
        try
        {
            using (FileStream fs = File.OpenRead(filePath))
            {
                if (extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }

                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                for (int i = sheet.FirstRowNum + 2; i <= sheet.LastRowNum; i++) //  下标从0开始,行数加2   模版从第三行开始
                {
                    DataRow row = dt.NewRow();
                    row["Company"] = sheet.GetRow(i).GetCell(1).ToString();//此处为Excel中的数据，按实际坐标来获取
                    dt.Rows.Add(row);
                }
            }
            return dt;
        }
        catch (Exception ex)
        {
            throw new Exception("Get_Visit_Registration_Excel：" + ex.Message);

        }
    }

    /// <summary>
    /// 初始化Visit_Registration DataTable结构
    /// </summary>
    /// <returns></returns>
    private static DataTable GetRegistrationDataTable()
    {
        DataTable dt = new DataTable();
        dt.TableName = "Visit_Registration";
        dt.Columns.Add("Company");
        return dt;
    }