多种方法实现Excel批量导入数据库

 Excel批量导入数据库是用到批量导入系统的一个难题，特别是需要批量导入的Excel表比较复杂，或者这张Excel表需要多表插入的时候，批量导入就变得复杂起来。其实了解了批量导入的原理之后，批量导入也就不再复杂。
    批量导入的原理其实很简单，首先下载模板，填入信息后进行导入；然后读取Excel文件的路径，上传Excel文件，如果需要保存的话；其次进行数据转化，例如将Excel表的信息转化成DataTable；最后将DataTable导入到数据库中。知道了批量导入的原理之后，就应该一步一步的解决问题，剩下的就是代码了。

http://www.cda.cn/uploadfile/image/20180101/20180101071006_20792.png

    一中考评系统中，后台管理需要导入比较多，最主要的就是导入教职工。因为这个考评系统本来就是用于教师和教师之间测评，所以教职工信息肯定需要后台管理员导入，而不是一条一条添加。一个学校两百多个教师，一个一个添加就太不为用户考虑了。
    一中考评的导入首先用的是SqlBulkCopy的批量导入，因为这种方式是性能比较不错的一种方式，有人进行测试，导入68万条数据大概需要53秒，所以就果断直接采用的这种方式。我们是用它导入DataTable，就是先把Excel转化成DataTable，然后直接用SqlBulkCopy向数据库中写入DataTable。代码如下：
[csharp] view plain copy

            ///  
            /// 批量导入DataTable  
            ///  
            /// 配置文件key  
            /// datatable名称  
            /// 表名称  
            /// 所有列（dt.columns)  
            /// 返回true，or false  
            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  
                        {  
                            //批量写入  
                            sqlBC.WriteToServer(dt);  
                            scope1.Complete();  
                            return true;  
                        }  
                        catch  
                        {  
                            throw new Exception("导入数据失败！");  
                        }  
                    }  
                }  
            }  
    但是这种导入方法有两个缺陷，一个是数据类型转化为Guid不成功，一个是导入数据库时列乱序。而且SqlBulkCopy做批量导入的时候，需要保证导入的DataTable的顺序和数据库表是一样的，这样就给复杂的Excel表的插入造成了一定的困难。简单的导入Excel文件，可以在设置模板的时候，就把顺序和数据库表对应好。
    然后我们就采取了另外一种方式，那就是拼接Sql语句，直接用sql语句导入。其实对于大批量导入数据，sql语句执行起来比较慢，特别是数据量在十万条以上的。但是对于小数据量，就比如我们系统，需要导入几百个教师信息，改动数据库字段类型对系统其它地方改动太大，除非在设计数据库的时候就能注意到这个问题。对于我们这种情况，还是改导入方式比较方便，于是就采用了拼接sql语句。
    因为DataTable已经转化完成，所以我们可以直接循环DataTable的行去拼接sql语句：
[csharp] view plain copy

    ///  
            /// 导入Excel数据至DB的方法  
            ///  
            /// 导入Excel文件全路径  
            /// 相关XML名称  
            /// 默认列数据  
            /// 数据库连接WebConfig配置键值  
            /// 过程中出现的问题数据  
            public Dictionary ImportExcel(string strPath, string strXMLName, Dictionary dicDefaultColumn, string strDBKey)  
            {  
                //得到导入目标表的DataTable  
                Dictionary dicTargetTable = this.GetImportTable(strPath, strXMLName, dicDefaultColumn, strDBKey);  
                //得到导入第三张表的DataTable  
                Dictionary dicThirdTable = this.GetThirdTable();  
                //得到过程中出现的问题表  
                Dictionary dicErrorTable = this.GetErrorTable();  
                //数据库连接字符串,读配置文件  
                SQLHelper sqlHelper = new SQLHelper("YzEvaluationSystemEntities", true);  
                //执行隐式事务  
                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语句拼接  
                                    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里面提交数据  
                                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;  
            }  
    这样循环拼接其实是拼接了多条insert语句，DataTable每一行数据都是一个insert语句，多条一起执行，就实现了Excel的批量导入。

总结
    什么是好的系统，并不是说技术用最新的，架构用最好的，最后系统一定是好的。就像贪心算法一样，每一个子问题都用最优，最后结果不一定最优，做系统也是一样。只有做出最适合客户需求，系统最适合客户需求就好。还有一点，就是一切要以数据说话，做系统需要真实数据去测试，测试不同方法的反应时间，最后选择一个最合适的方法。