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

基于C#的Excel 数据批量导入SqlServer 的方法研究与实现周 虎（江苏联合职业技术学院徐州财经分院，江苏　徐州 ２２１００８）摘 要：批量数据导入在实际工作中提高了数据处理的效率，保证了数据的完整性。

本文以某地育龄妇女基本信息为例，详细介绍了Excel数据批量导入SqlServer的设计思路与实现方法。

关键词：Excel；SqlServer；批量数据导入；NPOI；C#编程中图分类号：TP311.11 文献标识码：AResearch 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-021 引言(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 NPOINPOI是一个开源的基于.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<len;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<len2;j++,r++){///循环遍历Excel数据行ICell cell=tempRow.GetCell(j);if (cell!=null) {switch (cell.CellType)//类型处理 {…..//根据相应类型添加到指定字段 } } }dt.Rows.Add(dataRow);程序1 读取Excel文件内容到DataTable3.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();} mit();}catch{第17卷第12期 55周 虎：基于C#的Excel 数据批量导入SqlServer 的方法研究与实现56 软件工程师 2014年12月trans.Rollback();throw;}}程序4 SQL事务导入数据通过以上几个步骤，最终实现了基于NPOI的Excel数据导入SqlServer程序，程序运行界面如图4所示。

图４　程序运行效果Fig.4 Effect of operation procedure4 结论(Conclusion)本文通过对育龄妇女基本信息Excel文件的处理，实现了对增量数据批量导入数据库的功能，在实际工作中提高了工作效率，但程序运行的效率还有待于进一步的提高。