多種方法實(shí)現(xiàn)Excel批量導(dǎo)入數(shù)據(jù)庫(kù)

 Excel批量導(dǎo)入數(shù)據(jù)庫(kù)是用到批量導(dǎo)入系統(tǒng)的一個(gè)難題，特別是需要批量導(dǎo)入的Excel表比較復(fù)雜，或者這張Excel表需要多表插入的時(shí)候，批量導(dǎo)入就變得復(fù)雜起來(lái)。其實(shí)了解了批量導(dǎo)入的原理之后，批量導(dǎo)入也就不再?gòu)?fù)雜。
    批量導(dǎo)入的原理其實(shí)很簡(jiǎn)單，首先下載模板，填入信息后進(jìn)行導(dǎo)入；然后讀取Excel文件的路徑，上傳Excel文件，如果需要保存的話；其次進(jìn)行數(shù)據(jù)轉(zhuǎn)化，例如將Excel表的信息轉(zhuǎn)化成DataTable；最后將DataTable導(dǎo)入到數(shù)據(jù)庫(kù)中。知道了批量導(dǎo)入的原理之后，就應(yīng)該一步一步的解決問(wèn)題，剩下的就是代碼了。

    一中考評(píng)系統(tǒng)中，后臺(tái)管理需要導(dǎo)入比較多，最主要的就是導(dǎo)入教職工。因?yàn)檫@個(gè)考評(píng)系統(tǒng)本來(lái)就是用于教師和教師之間測(cè)評(píng)，所以教職工信息肯定需要后臺(tái)管理員導(dǎo)入，而不是一條一條添加。一個(gè)學(xué)校兩百多個(gè)教師，一個(gè)一個(gè)添加就太不為用戶考慮了。
    一中考評(píng)的導(dǎo)入首先用的是SqlBulkCopy的批量導(dǎo)入，因?yàn)檫@種方式是性能比較不錯(cuò)的一種方式，有人進(jìn)行測(cè)試，導(dǎo)入68萬(wàn)條數(shù)據(jù)大概需要53秒，所以就果斷直接采用的這種方式。我們是用它導(dǎo)入DataTable，就是先把Excel轉(zhuǎn)化成DataTable，然后直接用SqlBulkCopy向數(shù)據(jù)庫(kù)中寫(xiě)入DataTable。代碼如下：
[csharp] view plain copy

    <span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">        /// <summary>  
            /// 批量導(dǎo)入DataTable  
            /// </summary>  
            /// <param name="strDatabaseName">配置文件key</param>  
            /// <param name="dt">datatable名稱</param>  
            /// <param name="tableName">表名稱</param>  
            /// <param name="dtColum">所有列（dt.columns)</param>  
            /// <returns>返回true，or false</returns>  
            public Boolean InsertTable(string strDatabaseName, DataTable dt, string tableName, DataColumnCollection dtColum)  
            {  
                using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required))  
                {  
                    using (SqlBulkCopy sqlBC = new SqlBulkCopy(  
                               GetConnection(strDatabaseName).ConnectionString, SqlBulkCopyOptions.KeepIdentity))  
                    {  
                        sqlBC.BatchSize = 1000;  
                        sqlBC.DestinationTableName = tableName;  
      
                        // Write from the source to the destination.  
                        // This should fail with a duplicate key error.  
                        for (int i = 0; i < dtColum.Count; i++)  
                        {  
                            sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString());  
                        }  
                        try  
                        {  
                            //批量寫(xiě)入  
                            sqlBC.WriteToServer(dt);  
                            scope1.Complete();  
                            return true;  
                        }  
                        catch  
                        {  
                            throw new Exception("導(dǎo)入數(shù)據(jù)失??！");  
                        }  
                    }  
                }  
            }</span></span> 
    但是這種導(dǎo)入方法有兩個(gè)缺陷，一個(gè)是數(shù)據(jù)類型轉(zhuǎn)化為Guid不成功，一個(gè)是導(dǎo)入數(shù)據(jù)庫(kù)時(shí)列亂序。而且SqlBulkCopy做批量導(dǎo)入的時(shí)候，需要保證導(dǎo)入的DataTable的順序和數(shù)據(jù)庫(kù)表是一樣的，這樣就給復(fù)雜的Excel表的插入造成了一定的困難。簡(jiǎn)單的導(dǎo)入Excel文件，可以在設(shè)置模板的時(shí)候，就把順序和數(shù)據(jù)庫(kù)表對(duì)應(yīng)好。
    然后我們就采取了另外一種方式，那就是拼接Sql語(yǔ)句，直接用sql語(yǔ)句導(dǎo)入。其實(shí)對(duì)于大批量導(dǎo)入數(shù)據(jù)，sql語(yǔ)句執(zhí)行起來(lái)比較慢，特別是數(shù)據(jù)量在十萬(wàn)條以上的。但是對(duì)于小數(shù)據(jù)量，就比如我們系統(tǒng)，需要導(dǎo)入幾百個(gè)教師信息，改動(dòng)數(shù)據(jù)庫(kù)字段類型對(duì)系統(tǒng)其它地方改動(dòng)太大，除非在設(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候就能注意到這個(gè)問(wèn)題。對(duì)于我們這種情況，還是改導(dǎo)入方式比較方便，于是就采用了拼接sql語(yǔ)句。
    因?yàn)镈ataTable已經(jīng)轉(zhuǎn)化完成，所以我們可以直接循環(huán)DataTable的行去拼接sql語(yǔ)句：
[csharp] view plain copy

    <span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;"> /// <summary>  
            /// 導(dǎo)入Excel數(shù)據(jù)至DB的方法  
            /// </summary>  
            /// <param name="strPath">導(dǎo)入Excel文件全路徑</param>  
            /// <param name="strXMLName">相關(guān)XML名稱</param>  
            /// <param name="dicDefaultColumn">默認(rèn)列數(shù)據(jù)</param>  
            /// <param name="strDBKey">數(shù)據(jù)庫(kù)連接WebConfig配置鍵值</param>  
            /// <returns>過(guò)程中出現(xiàn)的問(wèn)題數(shù)據(jù)</returns>  
            public Dictionary<int, DataTable> ImportExcel(string strPath, string strXMLName, Dictionary<string, string> dicDefaultColumn, string strDBKey)  
            {  
                //得到導(dǎo)入目標(biāo)表的DataTable  
                Dictionary<int, DataTable> dicTargetTable = this.GetImportTable(strPath, strXMLName, dicDefaultColumn, strDBKey);  
                //得到導(dǎo)入第三張表的DataTable  
                Dictionary<int, DataTable> dicThirdTable = this.GetThirdTable();  
                //得到過(guò)程中出現(xiàn)的問(wèn)題表  
                Dictionary<int, DataTable> dicErrorTable = this.GetErrorTable();  
                //數(shù)據(jù)庫(kù)連接字符串,讀配置文件  
                SQLHelper sqlHelper = new SQLHelper("YzEvaluationSystemEntities", true);  
                //執(zhí)行隱式事務(wù)  
                try  
                {  
                    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))  
                    {  
                        for (int intTableIndex = 0; intTableIndex < dicTargetTable.Count; intTableIndex++)  
                        {  
                            if (dicTargetTable[intTableIndex].Rows.Count > 0)  
                            {  
                                DataTable dtTarget = dicTargetTable[intTableIndex];  
                                StringBuilder sbSql = new StringBuilder();  
                                  
                                for (int i = 0; i < dtTarget.Rows.Count; i++)  
                                {  
                                    //sql語(yǔ)句拼接  
                                    sbSql.Append("insert into ").Append(dtTarget.TableName.ToString()).Append("(ID,StaffName,StaffPassword,StaffID,Sex,IdentityCard,Subject,WorkDate,EngageDate,jobQualification,DivisionID,SeriesID,IsUsed) values (");  
                                    sbSql.Append("'" + dtTarget.Rows[i]["ID"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["StaffName"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["StaffPassword"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["StaffID"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["Sex"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["IdentityCard"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["Subject"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["WorkDate"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["EngageDate"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["jobQualification"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["DivisionID"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["SeriesID"] + "',")  
                                        .Append("'" + dtTarget.Rows[i]["IsUsed"] + "' ")  
                                        .Append(")");  
                                }  
      
                                //往SQLHelper里面提交數(shù)據(jù)  
                                int flag = sqlHelper.ExecuteNonQuery(sbSql.ToString(), CommandType.Text);  
                            }  
                              
                        }  
                        scope.Complete();  
                    }  
                }  
                catch (Exception e)  
                {  
                    throw new Exception(e.Message);  
                }  
                Boolean bolIsExistErrorData = false;  
                foreach (int intErrorTableIndex in dicErrorTable.Keys)  
                {  
                    if (dicErrorTable[intErrorTableIndex].Rows.Count > 1)  
                    {  
                        bolIsExistErrorData = true;  
                    }  
                }  
                if (bolIsExistErrorData)  
                {  
                    return dicErrorTable;  
                }  
                return null;  
            }</span></span> 
    這樣循環(huán)拼接其實(shí)是拼接了多條insert語(yǔ)句，DataTable每一行數(shù)據(jù)都是一個(gè)insert語(yǔ)句，多條一起執(zhí)行，就實(shí)現(xiàn)了Excel的批量導(dǎo)入。

總結(jié)
    什么是好的系統(tǒng)，并不是說(shuō)技術(shù)用最新的，架構(gòu)用最好的，最后系統(tǒng)一定是好的。就像貪心算法一樣，每一個(gè)子問(wèn)題都用最優(yōu)，最后結(jié)果不一定最優(yōu)，做系統(tǒng)也是一樣。只有做出最適合客戶需求，系統(tǒng)最適合客戶需求就好。還有一點(diǎn)，就是一切要以數(shù)據(jù)說(shuō)話，做系統(tǒng)需要真實(shí)數(shù)據(jù)去測(cè)試，測(cè)試不同方法的反應(yīng)時(shí)間，最后選擇一個(gè)最合適的方法。