將excel大量新增資料匯入sql

           //測試，將excel中的sheet1匯入到sqlserver中        private void run_Click(object sender, EventArgs e)        {            System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();            if (fd.ShowDialog() == DialogResult.OK)            {               DataSet ds=  TransferData(fd.FileName, "sheet1");                     AddBatch(ds.Tables[0]);            }        }        public DataSet TransferData(string excelFile, string sheetName)        {            DataSet ds = new DataSet();            try            {                //擷取全部資料                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";                if (excelFile.ToLower().IndexOf(".xlsx") > 0 && excelFile.ToLower().EndsWith("xlsx"))                {                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelFile + "';Extended Properties='Excel 12.0;HDR=YES'";                }                else if (excelFile.ToLower().IndexOf(".xls") > 0 && excelFile.ToLower().EndsWith("xls"))                {                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelFile + "';Extended Properties='Excel 8.0;HDR=YES;'";                }                else                {                    return null;                }                OleDbConnection conn = new OleDbConnection(strConn);                conn.Open();                string strExcel = "";                OleDbDataAdapter myCommand = null;                strExcel = string.Format("select * from [{0}$]", sheetName);                myCommand = new OleDbDataAdapter(strExcel, strConn);                myCommand.Fill(ds, sheetName);                return ds;            }            catch (Exception ex)            {                System.Windows.Forms.MessageBox.Show(ex.Message);            }            return null;        }            /// <summary>        /// 大量新增        /// </summary>        /// <param name="dt"></param>        public void AddBatch(DataTable dt)        {            SqlBulkCopyColumnMapping[] sc = new SqlBulkCopyColumnMapping[] {                 new SqlBulkCopyColumnMapping(0, "site"),            new SqlBulkCopyColumnMapping(1, "goodslocationid"),            new SqlBulkCopyColumnMapping(2, "area")};            DbHelperSQL.TransferData(dt, "wms_goodsLocation", sc);        }    public static void TransferData(DataTable dtb, string tableName, SqlBulkCopyColumnMapping[]  sbccm)        {            using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))            {                bcp.BatchSize = 100;//每次傳輸的行數                bcp.DestinationTableName = tableName;// 目標表                foreach(SqlBulkCopyColumnMapping s in sbccm)                bcp.ColumnMappings.Add(s);                bcp.WriteToServer(dtb);            }        }