基于C#的Excel数据批量导入SqlServer的方法研究与实现

周虎

摘  要：批量数据导入在实际工作中提高了数据处理的效率，保证了数据的完整性。本文以某地育龄妇女基本信息为例，详细介绍了Excel数据批量导入SqlServer的设计思路与实现方法。

关键词：Excel；SqlServer；批量数据导入；NPOI；C#编程

中图分类号：TP311.11           文献标识码：A

1   引言（Introduction）

Excel是办公中常用的电子表格处理工具，SqlServer是目前流行的数据库管理系统，均在信息系统中被广泛使用[1]。在某地数字化计生服务管理系统中需要从育龄妇女基本信息管理系统中导入育龄妇女的基本信息，而育龄妇女基本信息管理系统中只能将当前单位中所有的育龄妇女信息导出到Excel文件中，然后从Excel文件中将育龄妇女信息导入到SqlServer数据库中。在进行数据导入时，每次只需将增量数据导入到数据库，同时Excel中一条记录要写入到数据库多张表中。目前Excel导入数据库主要包括以下几种方法：

（1）将Excel转化为XML或者CSV文件，然后导入数据库。

（2）使用Office提供的Com组件技术。

（3）使用第三方组件，如NPOI或OLEDB。

其中第一种方法和第二种方法操作复杂或者运行缓慢，在实际应用中效果均不是很理想[2]，并且本系统在对Excel数据导入到数据库时需要对需要对数据进行进一步加工和处理，所以本文主要借助第三方组件NPOI的方式来实现Excel数据的批量导入。

2  Excel批量数据导入技术分析（Excel import bulk

data technical analysis）

在本项目中，需要将育龄妇女基本信息Excel文件内容读取到内存中，然后在内存中和数据库中的育龄妇女信息进行比对，将增量数据导入数据库，具体流程如图1所示。

图1 NPOI读取Excel数据到数据库

Fig.1 NPOI read excel data to database

在图1操作流程中，关键技术包括使用NPOI读取Excel数据到内存中的DataTable和从内存中将增量数据导入到数据库。

2.1   NPOI

NPOI是一个开源的基于.Net的读写Excel、Word等微软OLE2组件文档的项目，使用NPOI你就可以在没有安装Office或者相应环境的机器上对Word/Excel文档进行读写[3]。NPOI是一个免费的开源组件，包含了大部分的Excel功能，同时支持文件的导入与导出。

在本项目中使用NPOI将Excel表中的数据读取到内存中的Datatable中，同时将育龄妇女的编号存入集合中，便于和数据库中的育龄妇女信息进行比对。

2.2   增量数据导入到数据库

在本项目中，由于每次需要导入的育龄妇女基本信息都是包含所有的育龄妇女基本信息，为了进一步提高导入数据的效率，采用每次导入增量数据的方法，这里需要将Excel文件中的育龄妇女信息和数据库中的育龄妇女信息进行集合运算，将数据库中不包含的但Excel文件中包含的育龄妇女信息先计算出来，然后对增量数据进行批量导入数据库。具体流程如图2所示。

图2 增量数据导入

Fig.2 Incremental data import

在计算出增量数据后，Excle文件中一条记录需要同时保存到数据库中多个表中，同时需要根据数据库中数据字典中相应的字段进行数据处理。具体操作步骤如下：首先将需要用到数据字典的字段内容读取到内存中，如育龄妇女的现居地编号和婚姻状况等；其次循环遍历增量数据，同时生成相应的添加数据的SQL语句；最后使用SQL事务操作，实现批量数据的导入功能。

3  Excel批量数据导入的实现（Excel data import

implementation）

实现Excel批量数据导入，主要分为Excel数据读取，数据处理和数据导入。

3.1   Excel数据读取的实现

实现Excel数据的读取功能，首先根据Excel表中的字段生成内存中DataTable内存表对象，如图3所示。

图3 Excel文件表头

Fig.3 Excel file header

根据这些表头字段，在内存中生成DataTable的字段信息，并循环遍历Excel中的数据行，将Excel中的数据读取到DataTable中去，如程序1所示。

DataTable dt=newDataTable（）；//构造内存表

IRow headRow=sheet.GetRow（2）；//取第二行

//设置datatable字段

for （int i=headRow.FirstCellNum， len=headRow.LastCellNum； i<；len；i++）

{//统一定制字段名称

dt.Columns.Add（"item"+i.ToString（））；

}

for （int r=0， j=tempRow.FirstCellNum， len2=tempRow.LastCellNum；j<；len2；j++，r++）endprint

{///循环遍历Excel数据行

ICell cell=tempRow.GetCell（j）；

if （cell！=null）                    {

switch （cell.CellType）//类型处理

{

…..//根据相应类型添加到指定字段

}

}

}

dt.Rows.Add（dataRow）；

程序1  读取Excel文件内容到DataTable

3.2   数据处理

在数据处理环节，首先计算增量数据，然后对增量数据进行数据处理，将处理结果转换为SQL插入语句，关键代码如程序2所示。

List<；string>；l1=newList<；string>；（）；//保存Excel中妇女编码

List<；string>；l3=newList<；string>；（）；//保存数据库中妇女编码

for （int i=0；i<；dt1.Rows.Count；i++）//循环内存Datatable中数据

l1.Add（dt1.Rows[i][0].ToString（））；//保存妇女编码

for （int i=0；i<；dt_db.Rows.Count；i++）//遍历数据库中妇女编码

l3.Add（dt_db.Rows[i][0].ToString（））；//保存妇女编码

var l2=l1.Except（l3）；//获取增量妇女编码

DataTable dt3=dt1.Clone（）；//克隆表结构，用于保存增量数据

string cond=listToString（l2）；

DataRow[] rows=dt2.Select（"item1 in（"+cond+"）and item1<；>；''"）；//过滤增量数据

foreach （DataRow dr in rows）

dt3.Rows.Add（dr.ItemArray）；

程序2  计算增量数据

获取完增量数据后，对增量数据进行数据处理，并生成SQL语句，关键代码如程序3所示。

for （int i=0；i<；dt3.Rows.Count；i++）//遍历增量数据

{///添加到3张表中，并根据数据字典信息更新相应字段

string fn="insert into pub_fnxx（字段） values（值）；//添加到妇女基本信息表

sql.Add（fn）；//添加到集合列表中

string zf="insert into pub_zfxx（字段） values（值）；

sql.Add（zf）；

by="insert into pub_byqk （字段） values（值）；

sql.Add（by）；

}

程序3 生成SQL添加语句

3.3   数据批量导入

批量数据导入的SQL语句生成后，使用SQL事务完成增量数据的添加，关键代码如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

{

SqlCommand cmd=newSqlCommand（）；

try

{//循环

foreach （string sq in sql）

{

string cmdText=sq；

PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

}

trans.Commit（）；}

catch

{

trans.Rollback（）；

throw；

}}

程序4 SQL事务导入数据

通过以上几个步骤，最终实现了基于NPOI的Excel数据导入SqlServer程序，程序运行界面如图4所示。

图4 程序运行效果

Fig.4 Effect of operation procedure

4   结论（Conclusion）

本文通过对育龄妇女基本信息Excel文件的处理，实现了对增量数据批量导入数据库的功能，在实际工作中提高了工作效率，但程序运行的效率还有待于进一步的提高。

参考文献（References）

[1] 张丽英.基于.NET的Excel数据批量导入SQL Server的设计与

实现[J].南通纺织职业技术学院学报，2012，12（1）：7-10.

[2] 乔治强.基于C#的Excel数据导入导出SQL Server技术研究[J].

电脑知识与技术，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

com/.

作者简介：

周  虎（1977-），男，硕士，讲师.研究领域：计算机应用与软

件开发.endprint

string zf="insert into pub_zfxx（字段） values（值）；

； ； ； ； ； ； sql.Add（zf）；

； ； ； ； ； ； ； by="insert into pub_byqk （字段） values（值）；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； sql.Add（by）；

；}

程序3 生成SQL添加语句

3.3 ； ； 数据批量导入

批量数据导入的SQL语句生成后，使用SQL事务完成增量数据的添加，关键代码如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

SqlCommand cmd=newSqlCommand（）；

try

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {//循环

foreach （string sq in sql）

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

string cmdText=sq；

；PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； }

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； trans.Commit（）；}

catch

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； trans.Rollback（）；

throw；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； }}

程序4 SQL事务导入数据

通过以上几个步骤，最终实现了基于NPOI的Excel数据导入SqlServer程序，程序运行界面如图4所示。

图4 程序运行效果

； ； ； Fig.4 Effect of operation procedure

4 ； ； 结论（Conclusion）

本文通过对育龄妇女基本信息Excel文件的处理，实现了对增量数据批量导入数据库的功能，在实际工作中提高了工作效率，但程序运行的效率还有待于进一步的提高。

参考文献（References）

[1] 张丽英.基于.NET的Excel数据批量导入SQL Server的设计与

； ； ； ； 实现[J].南通纺织职业技术学院学报，2012，12（1）：7-10.

[2] 乔治强.基于C#的Excel数据导入导出SQL Server技术研究[J].

； ； ； ； 电脑知识与技术，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

； ； ； ； ； com/.

作者简介：

周 ； 虎（1977-），男，硕士，讲师.研究领域：计算机应用与软

； ； ； ； ； 件开发.endprint

string zf="insert into pub_zfxx（字段） values（值）；

； ； ； ； ； ； sql.Add（zf）；

； ； ； ； ； ； ； by="insert into pub_byqk （字段） values（值）；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； sql.Add（by）；

；}

程序3 生成SQL添加语句

3.3 ； ； 数据批量导入

批量数据导入的SQL语句生成后，使用SQL事务完成增量数据的添加，关键代码如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

SqlCommand cmd=newSqlCommand（）；

try

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {//循环

foreach （string sq in sql）

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

string cmdText=sq；

；PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； }

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； trans.Commit（）；}

catch

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； trans.Rollback（）；

throw；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； }}

程序4 SQL事务导入数据

通过以上几个步骤，最终实现了基于NPOI的Excel数据导入SqlServer程序，程序运行界面如图4所示。

图4 程序运行效果

； ； ； Fig.4 Effect of operation procedure

4 ； ； 结论（Conclusion）

本文通过对育龄妇女基本信息Excel文件的处理，实现了对增量数据批量导入数据库的功能，在实际工作中提高了工作效率，但程序运行的效率还有待于进一步的提高。

参考文献（References）

[1] 张丽英.基于.NET的Excel数据批量导入SQL Server的设计与

； ； ； ； 实现[J].南通纺织职业技术学院学报，2012，12（1）：7-10.

[2] 乔治强.基于C#的Excel数据导入导出SQL Server技术研究[J].

； ； ； ； 电脑知识与技术，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

； ； ； ； ； com/.

作者简介：

周 ； 虎（1977-），男，硕士，讲师.研究领域：计算机应用与软

； ； ； ； ； 件开发.endprint

string zf="insert into pub_zfxx（字段） values（值）；

； ； ； ； ； ； sql.Add（zf）；

； ； ； ； ； ； ； by="insert into pub_byqk （字段） values（值）；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； sql.Add（by）；

；}

程序3 生成SQL添加语句

3.3 ； ； 数据批量导入

批量数据导入的SQL语句生成后，使用SQL事务完成增量数据的添加，关键代码如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

SqlCommand cmd=newSqlCommand（）；

try

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {//循环

foreach （string sq in sql）

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

string cmdText=sq；

；PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； }

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； trans.Commit（）；}

catch

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； {

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； trans.Rollback（）；

throw；

； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； ； }}

程序4 SQL事务导入数据

通过以上几个步骤，最终实现了基于NPOI的Excel数据导入SqlServer程序，程序运行界面如图4所示。

图4 程序运行效果

； ； ； Fig.4 Effect of operation procedure

4 ； ； 结论（Conclusion）

本文通过对育龄妇女基本信息Excel文件的处理，实现了对增量数据批量导入数据库的功能，在实际工作中提高了工作效率，但程序运行的效率还有待于进一步的提高。

参考文献（References）

[1] 张丽英.基于.NET的Excel数据批量导入SQL Server的设计与

； ； ； ； 实现[J].南通纺织职业技术学院学报，2012，12（1）：7-10.

[2] 乔治强.基于C#的Excel数据导入导出SQL Server技术研究[J].

； ； ； ； 电脑知识与技术，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

； ； ； ； ； com/.

作者简介：

周 ； 虎（1977-），男，硕士，讲师.研究领域：计算机应用与软

； ； ； ； ； 件开发.endprint