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

来源：互联网发布：python 打包发布编辑：程序博客网时间：2024/05/22 16:55

Excel批量导入数据库是用到批量导入系统的一个难题，特别是需要批量导入的Excel表比较复杂，或者这张Excel表需要多表插入的时候，批量导入就变得复杂起来。其实了解了批量导入的原理之后，批量导入也就不再复杂。

批量导入的原理其实很简单，首先下载模板，填入信息后进行导入；然后读取Excel文件的路径，上传Excel文件，如果需要保存的话；其次进行数据转化，例如将Excel表的信息转化成DataTable；最后将DataTable导入到数据库中。知道了批量导入的原理之后，就应该一步一步的解决问题，剩下的就是代码了。

一中考评系统中，后台管理需要导入比较多，最主要的就是导入教职工。因为这个考评系统本来就是用于教师和教师之间测评，所以教职工信息肯定需要后台管理员导入，而不是一条一条添加。一个学校两百多个教师，一个一个添加就太不为用户考虑了。

一中考评的导入首先用的是SqlBulkCopy的批量导入，因为这种方式是性能比较不错的一种方式，有人进行测试，导入68万条数据大概需要53秒，所以就果断直接采用的这种方式。我们是用它导入DataTable，就是先把Excel转化成DataTable，然后直接用SqlBulkCopy向数据库中写入DataTable。代码如下：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;">        /// <summary>        /// 批量导入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                    {                        //批量写入                        sqlBC.WriteToServer(dt);                        scope1.Complete();                        return true;                    }                    catch                    {                        throw new Exception("导入数据失败！");                    }                }            }        }</span></span>

但是这种导入方法有两个缺陷，一个是数据类型转化为Guid不成功，一个是导入数据库时列乱序。而且SqlBulkCopy做批量导入的时候，需要保证导入的DataTable的顺序和数据库表是一样的，这样就给复杂的Excel表的插入造成了一定的困难。简单的导入Excel文件，可以在设置模板的时候，就把顺序和数据库表对应好。

然后我们就采取了另外一种方式，那就是拼接Sql语句，直接用sql语句导入。其实对于大批量导入数据，sql语句执行起来比较慢，特别是数据量在十万条以上的。但是对于小数据量，就比如我们系统，需要导入几百个教师信息，改动数据库字段类型对系统其它地方改动太大，除非在设计数据库的时候就能注意到这个问题。对于我们这种情况，还是改导入方式比较方便，于是就采用了拼接sql语句。

因为DataTable已经转化完成，所以我们可以直接循环DataTable的行去拼接sql语句：

<span style="font-family:KaiTi_GB2312;font-size:18px;"><span style="font-family:KaiTi_GB2312;font-size:18px;"> /// <summary>        /// 导入Excel数据至DB的方法        /// </summary>        /// <param name="strPath">导入Excel文件全路径</param>        /// <param name="strXMLName">相关XML名称</param>        /// <param name="dicDefaultColumn">默认列数据</param>        /// <param name="strDBKey">数据库连接WebConfig配置键值</param>        /// <returns>过程中出现的问题数据</returns>        public Dictionary<int, DataTable> ImportExcel(string strPath, string strXMLName, Dictionary<string, string> dicDefaultColumn, string strDBKey)        {            //得到导入目标表的DataTable            Dictionary<int, DataTable> dicTargetTable = this.GetImportTable(strPath, strXMLName, dicDefaultColumn, strDBKey);            //得到导入第三张表的DataTable            Dictionary<int, DataTable> dicThirdTable = this.GetThirdTable();            //得到过程中出现的问题表            Dictionary<int, DataTable> 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;        }</span></span>

这样循环拼接其实是拼接了多条insert语句，DataTable每一行数据都是一个insert语句，多条一起执行，就实现了Excel的批量导入。

总结

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

1 0