使用C#將Excel文件中數(shù)據(jù)導(dǎo)入SQL Server數(shù)據(jù)庫

由于項(xiàng)目中加入了新的功能，可以使管理員向數(shù)據(jù)庫中導(dǎo)入Excel數(shù)據(jù)。因此，在商品管理這塊需要對Excel進(jìn)行操作，在網(wǎng)上查了些資料，根據(jù)項(xiàng)目的實(shí)際情況進(jìn)行了一定的優(yōu)化，這里簡單的介紹下。
C#代碼

[csharp] view plain copy

    <span style="font-family:'Microsoft YaHei';font-size:18px;">/// <summary>  
    /// 上傳Excel文件，并將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫  
    /// </summary>  
    /// <param name="sender"></param>  
    /// <param name="e"></param>  
    protected void lbtnSure_Click(object sender, EventArgs e)  
    {  
           // 定義變量，并賦初值  
           string url = this.fileUpLoad.PostedFile.FileName;  
           string urlLocation = "";  
      
            // 判斷傳輸?shù)刂肥欠駷榭? 
            if (url == "")  
            {  
                  // 提示“請選擇Excel文件”  
                  Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script defer>alert('請選擇97~2003版Excel文件！');</script>");  
                  return;  
             }  
      
             // 判斷獲取的是否為地址，而非文件名  
             if (url.IndexOf("\\") > -1)  
             {  
                 // 獲取文件名  
                 urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//獲取文件名  
      
             }  
             else  
             {  
                 // url為文件名時，直接獲取文件名  
                 urlLocation = url;  
              }  
      
              // 判斷指定目錄下是否存在文件夾，如果不存在，則創(chuàng)建  
              if (!Directory.Exists(Server.MapPath("~\\up")))  
              {  
                    // 創(chuàng)建up文件夾  
                    Directory.CreateDirectory(Server.MapPath("~\\up"));  
              }  
      
              //在系統(tǒng)中建文件夾up,并將excel文件另存  
              this.fileUpLoad.SaveAs(Server.MapPath("~\\up") + "\\" + urlLocation);//記錄文件名到服務(wù)器相對應(yīng)的文件夾中  
      
              // Response.Write(urlLocation);  
      
              // 取得保存到服務(wù)器端的文件路徑  
              string strpath = Server.MapPath("~\\up") + "\\" + urlLocation;  
      
              // 取得config中的字段  
              string connectionString = ConfigurationManager.AppSettings["Connect"].ToString();  
      
              string strCon = ConfigurationManager.AppSettings["strUpLoad"].ToString();  
      
              // 替換變量  
              strCon = strCon.Replace("$Con$", strpath);  
      
              // 初始化導(dǎo)入Excel對象  
              ImportExcel excel = new ImportExcel();  
                  
              // 調(diào)用方法，將Excel文件導(dǎo)入數(shù)據(jù)庫  
              excel.TransferData(strCon, "t_Goods", connectionString);  
      
    }</span>  

TransferData類

[csharp] view plain copy

    <span style="font-family:'Microsoft YaHei';font-size:18px;">public void TransferData(string strCon, string sheetName, string connectionString)         
            {         
                DataSet ds = new DataSet();      
                try        
                {      
                    //獲取全部數(shù)據(jù)              
                    OleDbConnection conn = new OleDbConnection(strCon);      
                    conn.Open();      
                    string strExcel = "";      
                    OleDbDataAdapter myCommand = null;         
                    strExcel = string.Format("select * from [{0}$]", sheetName);      
                    myCommand = new OleDbDataAdapter(strExcel, strConn);      
                    myCommand.Fill(ds, sheetName);      
            
                    //如果目標(biāo)表不存在則創(chuàng)建,excel文件的第一行為列標(biāo)題,從第二行開始全部都是數(shù)據(jù)記錄       
                    string strSql = string.Format("if not exists(select * from sysobjects where name = '{0}') create table {0}(", sheetName);   //以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導(dǎo)入數(shù)據(jù)          
                    //excel文件中列的順序必須和數(shù)據(jù)表的列順序一致，因?yàn)閿?shù)據(jù)導(dǎo)入時，是從excel文件的第二行數(shù)據(jù)開始，不管數(shù)據(jù)表的結(jié)構(gòu)是什么樣的，反正就是第一列的數(shù)據(jù)會插入到數(shù)據(jù)表的第一列字段中，第二列的數(shù)據(jù)插入到數(shù)據(jù)表的第二列字段中，以此類推，它本身不會去判斷要插入的數(shù)據(jù)是對應(yīng)數(shù)據(jù)表中哪一個字段的       
                    using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))         
                    {         
                        bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);         
                        bcp.BatchSize = 100;//每次傳輸?shù)男袛?shù)          
                        bcp.NotifyAfter = 100;//進(jìn)度提示的行數(shù)          
                        bcp.DestinationTableName = sheetName;//目標(biāo)表          
                        bcp.WriteToServer(ds.Tables[0]);      
                    }         
                }         
                catch (Exception ex)         
                {         
                    throw new Exception(ex);         
                }       
            }         
            
            //進(jìn)度顯示          
            void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)         
            {         
                       
            }        
        }       </span> 
Web界面樣式