多种方法实现Excel批量导入数据库-CDA数据分析师官网

热线电话：13121318867

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

2018-01-01

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

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

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

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

 /// <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;
 }
 这样循环拼接其实是拼接了多条insert语句，DataTable每一行数据都是一个insert语句，多条一起执行，就实现了Excel的批量导入。

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

CDA数据分析师考试相关入口一览（建议收藏）：

▷ 想报名CDA认证考试，点击>>> “CDA报名”