多种方法实现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的批量导入。
总结
什么是好的系统,并不是说技术用最新的,架构用最好的,最后系统一定是好的。就像贪心算法一样,每一个子问题都用最优,最后结果不一定最优,做系统也是一样。只有做出最适合客户需求,系统最适合客户需求就好。还有一点,就是一切要以数据说话,做系统需要真实数据去测试,测试不同方法的反应时间,最后选择一个最合适的方法。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
你是否被统计学复杂的理论和晦涩的公式劝退过?别担心,“山有木兮:统计学极简入门(Python)” 将为你一一化解这些难题。课程 ...
2025-03-31在电商、零售、甚至内容付费业务中,你真的了解你的客户吗? 有些客户下了一两次单就消失了,有些人每个月都回购,有些人曾经是 ...
2025-03-31在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的需求持续飙升。世界经济论坛发布的《未来就业报告》, ...
2025-03-28你有没有遇到过这样的情况?流量进来了,转化率却不高,辛辛苦苦拉来的用户,最后大部分都悄无声息地离开了,这时候漏斗分析就非 ...
2025-03-27TensorFlow Datasets(TFDS)是一个用于下载、管理和预处理机器学习数据集的库。它提供了易于使用的API,允许用户从现有集合中 ...
2025-03-26"不谋全局者,不足谋一域。"在数据驱动的商业时代,战略级数据分析能力已成为职场核心竞争力。《CDA二级教材:商业策略数据分析 ...
2025-03-26当你在某宝刷到【猜你喜欢】时,当抖音精准推来你的梦中情猫时,当美团外卖弹窗刚好是你想吃的火锅店…… 恭喜你,你正在被用户 ...
2025-03-26当面试官问起随机森林时,他到底在考察什么? ""请解释随机森林的原理""——这是数据分析岗位面试中的经典问题。但你可能不知道 ...
2025-03-25在数字化浪潮席卷的当下,数据俨然成为企业的命脉,贯穿于业务运作的各个环节。从线上到线下,从平台的交易数据,到门店的运营 ...
2025-03-25在互联网和移动应用领域,DAU(日活跃用户数)是一个耳熟能详的指标。无论是产品经理、运营,还是数据分析师,DAU都是衡量产品 ...
2025-03-24ABtest做的好,产品优化效果差不了!可见ABtest在评估优化策略的效果方面地位还是很高的,那么如何在业务中应用ABtest? 结合企业 ...
2025-03-21在企业数据分析中,指标体系是至关重要的工具。不仅帮助企业统一数据标准、提升数据质量,还能为业务决策提供有力支持。本文将围 ...
2025-03-20解锁数据分析师高薪密码,CDA 脱产就业班助你逆袭! 在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的 ...
2025-03-19在 MySQL 数据库中,查询一张表但是不包含某个字段可以通过以下两种方法实现:使用 SELECT 子句以明确指定想要的字段,或者使 ...
2025-03-17在当今数字化时代,数据成为企业发展的关键驱动力,而用户画像作为数据分析的重要成果,改变了企业理解用户、开展业务的方式。无 ...
2025-03-172025年是智能体(AI Agent)的元年,大模型和智能体的发展比较迅猛。感觉年初的deepseek刚火没多久,这几天Manus又成为媒体头条 ...
2025-03-14以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-13以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-12以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-11随着数字化转型的加速,企业积累了海量数据,如何从这些数据中挖掘有价值的信息,成为企业提升竞争力的关键。CDA认证考试体系应 ...
2025-03-10