将Excel数据导入至SqlServer中

/*使用OleDb连接*/

using System.Data.OleDb;

public int TransferData(string excelFile, string sheetName, string connectionString,int WeekId)

{    

　　DataSet ds = new DataSet();    

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

　　 if (excelFile.ToLower().IndexOf(".xlsx") > 0)    

      {

         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelFile + "';Extended Properties='Excel 12.0;HDR=YES'";    

      }    

      if (excelFile.ToLower().IndexOf(".xls") > 0 && excelFile.EndsWith("xls"))    

     {        

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

     }

     OleDbConnection conn = new OleDbConnection(strConn);    

     conn.Open();    

     //获取全部数据    

     try     {

        string strExcel = "";        

        OleDbDataAdapter myCommand = null;        

        strExcel = string.Format("select * from [{0}$]", sheetName);        

        myCommand = new OleDbDataAdapter(strExcel, strConn);        

        myCommand.Fill(ds, sheetName);        

        ds.Tables[0].Columns.Add("WeekId", Type.GetType("System.Int32"));        

        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)        

       {            

           ds.Tables[0].Rows[i]["WeekId"] = WeekId;        

       }        

       //如果目标表不存在则创建        

       string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);        

       foreach (System.Data.DataColumn c in ds.Tables[0].Columns)        

       {            

             strSql += string.Format("[{0}] varchar(255),", c.ColumnName);        

        }

        strSql = strSql.Trim(',') + ")";

 

        using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))        

        {            

             sqlconn.Open();            

             System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();

             command.CommandText = strSql;            

             command.ExecuteNonQuery();            

             sqlconn.Close();        

        }        

        //用bcp导入数据        

         using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))        

        {

            bcp.BatchSize = 100;//每次传输的行数            

            bcp.NotifyAfter = 100;//进度提示的行数            

            bcp.DestinationTableName = sheetName;//目标表            

            bcp.WriteToServer(ds.Tables[0]);        

         }        

         conn.Close();        

         return 1;                    

        }     catch (Exception ex)     {         conn.Close();         return 0;     }            

}