Excel 数据导入到SQL SERVER

     #region Import data from excel to SQL.

        /// <summary>
        /// Import data from excel to SQL Server, but the difference struct of table . 
        /// </summary>
        public static void ImportDataFromExcelToSQLDemo(string filename)
        {
           
            // Load Excel data into DataTable
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
            Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'";

            string strSQL = "SELECT * FROM [Sh1$]";
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open(); // This code will open excel file.

            OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

            // create data table
            DataTable dTable = new DataTable();
            //fill table with echel data
            dataAdapter.Fill(dTable);

            //step 2 
            //connect to server
            using (SqlConnection destinationConnection = new SqlConnection(DestinationConnStr))
            {
                destinationConnection.Open();

                using (SqlBulkCopy bulkCopy =
                        new SqlBulkCopy(destinationConnection))
                {
                    // Destination Table must match columns in Excel sheet
                    string tableName = "SH_1";
                    bulkCopy.DestinationTableName = tableName;

                    try
                    {
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(dTable);
                    }
                    catch (Exception ex)
                    {
                        CommonDo.DoException.DealException(ex);
                    }
                    destinationConnection.Close();
                }
            }

            // dispose used objects
            dTable.Dispose();
            dataAdapter.Dispose();
            dbCommand.Dispose();

            excelConnection.Close();
            excelConnection.Dispose();
        }

        #endregion

        #region Import data from excel to SQL Server using map.
        /// <summary>
        /// Import data from excel to SQL Server using map.
        /// </summary>
        /// <param name="filename"></param>
        public static void ImportDataFromExcelToSQLUsingMap(string filename)
        {
            // Load Excel data into DataTable
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
            Data Source=" + filename + ";Extended Properties='Excel 12.0;HDR=YES;'";
            
           using(  OleDbConnection excelConnection = new OleDbConnection(connectionString))
           {
                string strSQL = "SELECT * FROM [WL_ProductDetail$]";
                OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);

                excelConnection.Open(); // This code will open excel file.
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

                // create data table
                DataTable dTable = new DataTable();
                //fill table with echel data
                dataAdapter.Fill(dTable);

                // step 2 
                // string serverConnectionString = "Data Source=DELL390-2;Initial Catalog=TestData;Persist Security Info=True;User ID=sa;Password=427427";
                string serverConnectionString = ConfigurationManager.ConnectionStrings["WL_DemoConn"].ConnectionString;

                using (SqlConnection destinationConnection =
                       new SqlConnection(serverConnectionString))
                {
                    destinationConnection.Open();

                    SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("RefNo", "RefNo");
                    SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("FOR CHINA REPAIR CENTER", "OriginCountry");
                    SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("品名", "品名");
                    SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("商品名称", "商品名称");
                    SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("原产国", "原产国");
                    SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("数量", "数量");
                    SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("单价", "单价");
                    SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("净重", "净重");

                    SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("总价CNY", "总价CNY");
                    SqlBulkCopyColumnMapping mapping10 = new SqlBulkCopyColumnMapping("HS", "HS");
                    SqlBulkCopyColumnMapping mapping11 = new SqlBulkCopyColumnMapping("品名2", "品名2");


                    using (SqlBulkCopy bulkCopy =
                            new SqlBulkCopy(destinationConnection))
                    {
                        //Destination Table must match columns in Excel sheet
                        string tableName = "WL_ProductDetail";
                        bulkCopy.DestinationTableName = tableName;

                        #region  Test bulkCopy eventhandler

                        bulkCopy.BatchSize = 100;
                        bulkCopy.BulkCopyTimeout = 5;

                        #endregion
                        bulkCopy.ColumnMappings.Add(mapping1);
                        bulkCopy.ColumnMappings.Add(mapping2);

                        bulkCopy.ColumnMappings.Add(mapping3);
                        bulkCopy.ColumnMappings.Add(mapping4);

                        bulkCopy.ColumnMappings.Add(mapping5);
                        bulkCopy.ColumnMappings.Add(mapping6);

                        bulkCopy.ColumnMappings.Add(mapping7);
                        bulkCopy.ColumnMappings.Add(mapping8);

                        bulkCopy.ColumnMappings.Add(mapping9);
                        bulkCopy.ColumnMappings.Add(mapping10);

                        bulkCopy.ColumnMappings.Add(mapping11);

                        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                        bulkCopy.NotifyAfter = 200;

                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(dTable);

                        }
                        catch (Exception ex)
                        {
                            CommonDo.DoException.DealException(ex);
                        }
                        destinationConnection.Close();
                    }
                    excelConnection.Close();
                }
            }


        }

        #endregion