EXCEL中的数据转到数据库中

///<summary>

        ///加载Excel到DataTable

        ///</summary>

        ///<param name="filePath">文件路径</param>

        ///<param name="sheetName">读取Excel中的sheet名字</param>

        ///<param name="where">查询条件</param>

        ///<returns></returns>

        public static DataTable LoadDataFromExcel(string filePath, string sheetName, string where)

        {

            DataTable dt = new DataTable();

            try

            {

                string strConn;

                if (Path.GetExtension(filePath) == ".xls")

                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

                else

                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'";

                OleDbConnection OleConn = new OleDbConnection(strConn);

                OleConn.Open();

                String sql = string.Format("SELECT * FROM  [{0}] {1}", sheetName, where);//可更改Sheet名称，比如sheet2，等等  

                OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);

                DataSet OleDsExcle = new DataSet();

                OleDaExcel.Fill(OleDsExcle, "Sheet1");

                OleConn.Close();

                if (OleDsExcle.Tables.Count > 0)

                    dt = OleDsExcle.Tables[0];

                return dt;

            }

            catch (Exception err)

            {

                return null;

            }

        }

 

DataTable dt = LoadDataFromExcel(filePath, "Sheet1$A:L", "where [工单号] is not null");//调用

using (SqlBulkCopy sqlBc = new SqlBulkCopy(conn))

{

    sqlBc.BatchSize = 1000;

    sqlBc.BulkCopyTimeout = 120;

    sqlBc.NotifyAfter = 10000;

    //sqlBc.SqlRowsCopied += sqlBc_SqlRowsCopied;

    sqlBc.DestinationTableName = "[MTNWLineBiz].[dbo].[SFGS_signedcontract]";

    sqlBc.ColumnMappings.Add(0, 1);

    sqlBc.ColumnMappings.Add(1, 2);

    sqlBc.ColumnMappings.Add(2, 3);

    sqlBc.ColumnMappings.Add(3, 4);

    sqlBc.ColumnMappings.Add(4, 5);

    sqlBc.ColumnMappings.Add(5, 6);

    sqlBc.ColumnMappings.Add(6, 7);

    sqlBc.ColumnMappings.Add(7, 8);

    sqlBc.ColumnMappings.Add(8, 9);

    sqlBc.ColumnMappings.Add(9, 10);

    sqlBc.ColumnMappings.Add(10, 11);

    sqlBc.ColumnMappings.Add(11, 12);

    sqlBc.WriteToServer(dt);

}