多种方法实现Excel批量导入数据库
Excel批量导入数据库是用到批量导入系统的一个难题，特别是需要批量导入的Excel表比较复杂，或者这张Excel表需要多表插入的时候，批量导入就变得复杂起来。其实了解了批量导入的原理之后，批量导入也就不再复杂。
批量导入的原理其实很简单，首先下载模板，填入信息后进行导入；然后读取Excel文件的路径，上传Excel文件，如果需要保存的话；其次进行数据转化，例如将Excel表的信息转化成DataTable；最后将DataTable导入到数据库中。知道了批量导入的原理之后，就应该一步一步的解决问题，剩下的就是代码了。
一中考评系统中，后台管理需要导入比较多，最主要的就是导入教职工。因为这个考评系统本来就是用于教师和教师之间测评，所以教职工信息肯定需要后台管理员导入，而不是一条一条添加。一个学校两百多个教师，一个一个添加就太不为用户考虑了。
一中考评的导入首先用的是SqlBulkCopy的批量导入，因为这种方式是性能比较不错的一种方式，有人进行测试，导入68万条数据大概需要53秒，所以就果断直接采用的这种方式。我们是用它导入DataTable，就是先把Excel转化成DataTable，然后直接用SqlBulkCopy向数据库中写入DataTable。代码如下：
[csharp] view plain copy
<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语句：
[csharp] view plain copy
<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的批量导入。
总结
什么是好的系统，并不是说技术用最新的，架构用最好的，最后系统一定是好的。就像贪心算法一样，每一个子问题都用最优，最后结果不一定最优，做系统也是一样。只有做出最适合客户需求，系统最适合客户需求就好。还有一点，就是一切要以数据说话，做系统需要真实数据去测试，测试不同方法的反应时间，最后选择一个最合适的方法。
数据分析咨询请扫描二维码
