Excel批量导数据到SQLServer

         string filepath = @"D:\data.xlsx";
 
          string strConn = "Provider=Microsoft.Ace.OleDb.12.0;Persist Security Info=False;" + "data source=" +
                           filepath + ";Extended Properties=‘Excel 12.0; HDR=yes; IMEX=2‘";
 
          OleDbConnection con = new OleDbConnection(strConn);
 
 
          string excelsql = "select  * from [data$]";
 
 
          OleDbDataAdapter da = new OleDbDataAdapter(excelsql, con);
 
          DataSet ds = new DataSet();
 
          da.Fill(ds);
 
 
          Console.WriteLine(ds.Tables.Count);
 
          Console.WriteLine(ds.Tables[0].Rows.Count);
 
          string conStr =
             "server=.;database=test;uid=sa;pwd=123";
 
 
          Stopwatch wt=new Stopwatch();
          wt.Start();
 
 
 
          using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conStr))
          {
 
              bulkCopy.DestinationTableName = "_Mobile";
 
              //加入只加入一個列的話,那么就會其他數據庫列都默認為空。
 
              bulkCopy.ColumnMappings.Add("mobile", "Mobile"); 

 
              bulkCopy.BulkCopyTimeout = 60 * 20;
 
              bulkCopy.BatchSize = 100;
 
              bulkCopy.NotifyAfter = 100;
 
              bulkCopy.WriteToServer(ds.Tables[0]);
 
          }
          wt.Stop();
 
          Console.WriteLine("Time:>>"+wt.ElapsedMilliseconds/1000+"s");

Excel批量导数据到SQLServer

标签：