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

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

周 虎

（江苏联合职业技术学院徐州财经分院，江苏　徐州 ２２１００８）

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

关键词：Excel；SqlServer；批量数据导入；NPOI；C#编程中图分类号：TP311.11 文献标识码：A

Research and Implementation of Excel Data Import to SqlServer Base on C#

ZHOU Hu

(Xuzhou Finance Branch ,Jiangsu Unit Technical Institute ,Xuzhou 221008,China )

Abstract:Bulk data import improve the efficiency of data processing in practical work to ensure the integrity of the data.In this paper,we take some basic information of women for example,details the Excel data design ideas and implementation of bulk import to SqlServer.

Keywords:excel;SqlServer;bulk data import;NPOI;C# programming

文章编号：1008-0775(2014)-12-54-02

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所示。

图１　ＮＰＯＩ读取Ｅｘｃｅｌ数据到数据库

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所示。

图２　增量数据导入

Fig.2 Incremental data import

在计算出增量数据后，Excle文件中一条记录需要同时保

软件工程师 SOFTWARE ENGINEER

第17卷第12期2014年12月

V ol.17 No.12Dec. 2014

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

3 Excel 批量数据导入的实现(Excel data import implementation)

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

3.1 Excel 数据读取的实现

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

图３　Ｅｘｃｅｌ文件表头

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=stCellNum; i

{//统一定制字段名称

dt.Columns.Add("item"+i.ToString()); }

f o r (i n t r =0, j =t e m p R o w.F i r s t C e l l N u m , len2=stCellNum;j

{///循环遍历Excel数据行ICell cell=tempRow.GetCell(j);if (cell!=null) {switch (cell.CellType)//类型处理 {

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

dt.Rows.Add(dataRow);

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

3.2 数据处理

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

Listl1=newList();//保存Excel中妇女

编码

Listl3=newList();//保存数据库中妇女编码

for (int i=0;i

l1.Add(dt1.Rows[i][0].ToString());//保存妇女编码for (int i=0;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

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();

} mit();}catch

{

第17卷第12期 55周 虎：基于C#的Excel 数据批量导入SqlServer 的方法研究与实现