导入Excel文件上传数据到SQL服务器的通用方法

         实现原理，通过读取上传Excel文件的标题行，创建对应的DataTable标题列（DataColumn），然后遍历Excel表格的数据行，添加到DataTable的数据行（DataRow），生成DataTable后，用SQL的BulkCopy方法，一次性上传到数据服务器，效率比生成逐条Insert into语句和利用DataTable生成List实体后用EF框架更新要高。

1.读取Excel，需要引用NPOI库

        public static DataTable getDataTableFromExcel(string excelFilePath)
        {
            IWorkbook workbook;
            DataTable dt=new DataTable();
            try
            {
                using (FileStream fs = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
                {
                    string fileExt = Path.GetExtension(excelFilePath).ToLower(); //获得Excel文件的扩展名，再判断用哪种方式打开
                    if (fileExt == ".xls") workbook = new HSSFWorkbook(fs);
                    else if (fileExt == ".xlsx") workbook = new XSSFWorkbook(fs);
                    else workbook = null;
                }
            }
            catch (Exception)
            {
                //这里可以抛出异常纪录
                throw;
            }
            ISheet sheet = workbook.GetSheetAt(0);//获得Excel的第一个工作表
            IRow headerRow = sheet.GetRow(0);//获得Excel的第一行
            int cellCount = headerRow.LastCellNum;//从第一行获取字段（列）数
            int rowCount = sheet.LastRowNum+1;  //获得行数
            //循环添加DataTabel对应的标题列
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn col = new DataColumn(headerRow.GetCell(i).StringCellValue);
                dt.Columns.Add(col);
            }
            //循环添加行数据到DataTable中
            for (int i = (sheet.FirstRowNum+1); i < rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow drow = dt.NewRow();
                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            drow[j] = getCellValue(cell);
                        }
                    }
                }
                dt.Rows.Add(drow);
            }
            return dt;
        }

2.使用SQL的BulkCopy，需要数据库中原表名和DataTable的表名保持一致，可以在上传Excel表时先做个逻辑判断，创建一个事务，用于上传失败时的回滚

public static bool sqlBulkcopyToServer(string ServerTableName, DataTable dt)
        {
            string connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            bool isSucess = false;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction();
                using (SqlBulkCopy sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints,trans))
                {
                    try
                    {
                        sbc.DestinationTableName = ServerTableName;
                        sbc.BatchSize = 20000;       //每一批次执行的最大行数
                        sbc.BulkCopyTimeout = 0;     //不限制执行时间
                        int cols = dt.Columns.Count;
                        for (int i = 0; i < cols; i++)
                        {
                            string colName = dt.Columns[i].ColumnName;
                            sbc.ColumnMappings.Add(colName, colName);//要求目标数据库表字段名与DataTable字段名一致，可用下载模板限制
                        }
                        sbc.WriteToServer(dt);
                        trans.Commit();
                        isSucess = true;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            return isSucess;
        }