大数据量Excel数据导入系统的设计与实现

彭磊+李先国

摘 要： 针对现有大数据量Excel导入系统效率较低的问题，设计并实现了一种新的大数据量Excel数据导入系统。该系统采用C#语言，在VS2012的开发环境中，利用多线程技术将大数据量Excel表格数据导入到SQL Server中，加快了信息导入的速度。并且在临时表与目标表之间的合并过程中，摒弃了传统的游标逐条插入方式，取而代之的是SQL Server 2008中的Merge技术，极大地加快了数据库端不同表之间数据的同步过程。实验测试结果表明，整个系统导入100万条数据耗时9 534 ms，远少于传统Excel导入系统所耗时间，有效提高大数据量Excel导入系统的效率。

关键词： 大数据量； Excel； SQL Server； 多线程； Merge

中图分类号： TN911?34； TP391.13 文献标识码： A 文章编号： 1004?373X（2014）14?0057?03

Design and implementation of Excel mass data importing system

PENG Lei， LI Xian?guo

（School of Computer Science and Technology， Northwestern Polytechnical University， Xian 710129， China）

Abstract：In order to eliminate the poor efficiency phenomenon existing in mass data Excel importation system， a novel mass data Excel importing system was designed and implemented. C# language and multi?threading technology are used in the system to introduce the mass data Excel tabular data into SQL Server under VS2012 developing environment. The system can shorten the time in the process of information importation. Moreover， instead of the traditional way of inserting item by item， a new Merge technology in SQL Sever 2008 is employed in the process of the mergence between the target table and the temporary table to greatly accelerate the data synchronization between different tables. The experimental testing result demonstrates that it takes 9534 ms for this system to import 1，000，000 data， which is far shorter than the traditional Excel importing system. It proves that this system can improve the efficiency of mass data Excel importing system efficiently.

Keywords： mass data； Excel； SQL Server； multi?threading； Merge

Excel是办公常用的电子表格处理软件，SQL Server是目前广泛使用的关系型数据库管理系统。基于安全性、通用性和共享性等考虑，信息系统一般都会给用户提供借助Excel进行数据导入导出数据库的功能。在一些文献中，介绍了将Excel转化为xml文件再进行导入[1]，或者将Excel转换成csv文件再进行导入，还有的文献介绍用自动化的方法启动Excel进程逐行读取，但此类方法操作复杂或者运行缓慢，在实际应用中效果均不是很理想[1]。如何能将大数据量的Excel高效准确地导入到SQL Server仍然是目前研究的重点和难点。

本文介绍在.Net环境下，使用C#语言实现大数据量Excel数据导入SQL Server的一种新系统。该系统采用多线程技术，最大限度的发挥计算机性能，提高了大数据量的Excel外部数据文件加载到内存的效率；并且使用新的Merge技术，加快了数据库端不同数据表之间的数据同步过程。该系统实现了全自动化的录入信息，并且方便了录入数据后的查询、备份、挖掘和分析，减轻了工作人员的工作量，有效地提高了工作效率。

1 概 述

简单地讲，Excel表格数据导入SQL Server可以分为三个步骤，其系统结构图，如图1所示。

（1） Excel数据文件加载到内存数据库中。

（2） 内存数据库到SQL Server的转存过程。

（3） 数据库端的临时表与目标表之间的数据合并同步过程。

本系统在上述三个步骤中，分别采用目前先进的技术来提高执行效率，从而提高整个系统的导入效率。下面进行详细的叙述。

图1 系统结构图

2 关键技术

（1） .NET平台。.Net Framework 是微软公司Windows 操作系统的组件，该组件使软件应用程序和Web 服务的建立更加容易，其对于软件开发的支持提供了许多新特性，并基于该平台推出了包括Visual C++.NET、VisualBasic.NET 和Visual C#.NET 等开发工具。其中，C#是微软力推的.NET 标准开发语言，具有很多众所周知的优点。因此，关于C#应用开发的帮助文档及相关文献资料较为广泛且全面[1]。

（2） OLEDB组件。ODBC.NET是ODBC数据库通信标准的.NET 升级，同时也是微软的战略性的通向不同的数据源的低级应用程序接口。OLE DB 不仅包括微软资助的标准数据接口开放数据库连通性（ODBC）的结构化查询语言（SQL）能力，还具有面向其他非SQL 数据类型的通路。因此，出于应用开发的数据库通用性和维护升级和长远考虑，Ole DB.NET 是较为理想的选择[1]。

（3） SqlBulkCopy类。SqlBulkCopy 类提供了一种将其他源的数据批量复制到SQL Server 数据库表中高性能的方法。SqlBulkCopy 包含可以重载的方法WriteToServer，它用来从其他数据源复制数据目的地。SqlBulkCopy 复制数据的原理是采用SQL Server 提供的Bcp 命令提示符读取到内存数据库（DataSet）中，用工具进行数据的批量复制。Bcp 在SQL Server 实例和数据文件之间以用户指定的格式复制数据[2]。

（4） 多线程技术。多线程是为了同步完成多项任务，不是为了提高运行效率，而是为了提高资源使用效率来提高系统的效率。线程是在同一时间需要完成多项任务的时候实现的。使用C#编写任何程序时，都有一个入口：Main（）方法。程序从Main 方法的第一条语句开始执行，直到这个方法为止，Main（）方法的执行是在一个线程中即主线程[3]。在信息系统的应用程序中，导入的数据量比较小时，基本上不需要使用多线程，但当导入的数据量较大不使用多线程技术就会产生“假死”现象，而使用多线程可以很好地解决这一问题[4]。

（5） 数据库中的Merge技术。Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入，它能将Insert，Update，Delete简单的并为一句，根据一个源数据表对另一个数据表进行确定性的插入、更新和删除这样复杂的操作。例如，根据在两个表中找到的差异行在其中一个表中进行插入、更新或删除行，可以对两个表进行同步。Merge的高效在于它仅需要一次全表扫描即可完成全部工作。Merge语句还有一个强大的功能是通过OUTPUT子句，可以将刚刚做过变动的数据进行输出至另一张数据库表中。

3 系统设计

大数据量Excel数据导入系统的系统流程图，如图2所示。

图2 系统流程图

该系统中所涉及三个关键步骤的详细说明如下：

（1） Excel数据文件加载到内存数据库（DataSet）中。该系统将采用多线程技术来将Excel数据文件加载到内存中。通过OLEDB技术读取Excel文件的大小、Excel文件中Sheet数目，以及每个Sheet中的数据行总数，来确定生成线程的数目和Datatable的数目，然后每个线程读取部分数据到各自对应Datatable中。

（2） 内存数据库（DataSet）到SQL Server的转存过程。该系统将采用SqlBulkCopy 将DataSet中的数据进行批量复制到数据库的临时表中。在步骤（1）中，当DataSet中的数据量超过一定界限时，Excel数据读取线程全部暂停，执行步骤（2），然后清空DataSet中的数据，继续执行步骤（1），直到所有数据都转存到数据库中的临时表中。

如果在执行过程中，由于新导入的数据在主键列上有重复数据导致SqlBulkCopy的WriteToServer方法出错时，系统将捕获异常，然后在异常处理中，采用折半的方式批量复制数据到临时表，直至剩下出错的记录返回个客户端。

（3） 数据库端的临时表与目标表之间的数据合并同步过程。该系统中采用SQL Server中的Merge技术，步骤（2）中新生成的临时表与系统中原有的目标表中的数据进行一次同步，如果数据已存在，该记录将被更新，如果记录不存在，插入新纪录。Merge语句还有一个强大的功能是通过OUTPUT子句，可以将刚刚做过变动的数据进行输出至另一张数据库表中。

4 性能分析

大数据量Excel数据导入系统，在数据导入方面的性能有了很大的提升。主要体现在以下两点：

（1） 高效性。多线程技术的使用以及对线程数目的控制，缩短了Excel文件数据加载到内存的时间。在Excel文件数据量小时，由于使用多线程增加了资源的开销，导致执行效率反而不如单线程的执行效率的情况，本系统同样做了判断处理。SqlBulkCopy的使用极大的提高了数据从内存到数据库的转存的执行效率。SqlBulkCopy采用的的SQL Server的Bcp命令。Bcp命令在SQL Server 实例和数据文件之间复制数据的效率非常高。本系统并没有单独进行新导入数据的查重检测，而是在导入失败后的异常处理中采用了折半导入的方式来找到出错数据行，在导入的同时查找重复的数据，缩短系统执行时间。本系统中同样摒弃了费时且根本无法完成大容量数据合并的编程循环SQL 语句转换插入方式，采用的新的Merge技术。Merge技术在两个表之间的数据同步的过程中执行效率非常高。

（2） 安全性。在数据转存到数据库的过程中，该系统并没有直接对系统中的目标表进行操作，取而代之的是将新导入的数据放到临时表中。如果导入数据出错只需要将临时表数据清空即可，并不需要对目标表进行操作。Merge技术中的OUTPUT功能能够把更新过的记录行输出到另一张数据库表中，为日后的查看修改记录提供了方便，增强了导入数据安全性。

5 测试结果

5.1 测试环境

硬件：PC（CPU：Intel（R） i3?2350M CPU @ 2.30 GHz；内存：2 GB，DDR3；硬盘：5 400 R/S）。软件：Windows7家庭版（X64）；SQL Server R2（X64）；VS2012（X64）。

5.2 测试结果

系统测试结果，如表1所示。

表1 系统测试结果表 ms

步骤（1）使用单线程与多线程对比测试结果见表2。

表2 步骤一测试结果表 ms

步骤（2）中使用SqlBulkCopy与其他方法对比的测试结果，如表3所示。

表3 步骤二测试结果表

步骤（3）中使用Merge与游标逐条插入对比的测试结果，如表4所示。

表4 步骤三测试结果表

5.3 结果分析

步骤（1）中外部文件数据加载至内存时间明显缩短，但数据量成倍增加时，运行时间并没有成倍增加，可见在数据量越大，多线程的优势更加明显。步骤（2）中SqlBulkCopy导入效率在同样数据量情况下与其他相比有了明显提高。步骤（3）的Merge在百万级大表合并效率非常高，同时在合并过程中，表的列数以及对目标表的insert数目和update数目都会影响Merge的执行时间。综上所述，系统执行时间明显缩短，达到了预期的效果。

6 结 语

大数据量Excel数据导入系统，实现了高效安全的数据导入功能。在大数据时代，如何能将大数据量外部文件数据导入到数据库中，方便导入数据后查询、备份、挖掘和分析一直是人们研究的焦点问题。该系统为这一问题提供了一种可行的方案，具有一定的实用价值。

参考文献

[1] 乔治强.基于C#的Excel 数据导入导出SQL Server 技术研究[J].电脑知识与技术，2012（26）：195?196.

[2] 刘小豫，赵蔷.基于C#的Excel数据导入导出SQL Server的研究与实现[J].中小企业管理与科技，2012（31）：287?288.

[3] 王毅飞.基于.Net 的Excel和数据库数据转换[J].计算机时代，2013（5）：33?36.

[4] 罗琴媚.千万级大表转换导入的实现研究[J].计算机光盘软件与应用，2013（6）：288?289.

[5] HILLAR G C. C#并行编程高级教程[M].北京：清华大学出版社，2012.

[6] 王旭辉.Excel数据导入数据库的设计与实现[J].现代电子技术，2013，36（12）：71?73.

[7] 李晓京，文治洪，胡文东，等.C++/CLR 数据库与Excel并行数据转换技术研究[J].计算机技术与发展，2013（7）：155?158.

5.2 测试结果

系统测试结果，如表1所示。

表1 系统测试结果表 ms

步骤（1）使用单线程与多线程对比测试结果见表2。

表2 步骤一测试结果表 ms

步骤（2）中使用SqlBulkCopy与其他方法对比的测试结果，如表3所示。

表3 步骤二测试结果表

步骤（3）中使用Merge与游标逐条插入对比的测试结果，如表4所示。

表4 步骤三测试结果表

5.3 结果分析

步骤（1）中外部文件数据加载至内存时间明显缩短，但数据量成倍增加时，运行时间并没有成倍增加，可见在数据量越大，多线程的优势更加明显。步骤（2）中SqlBulkCopy导入效率在同样数据量情况下与其他相比有了明显提高。步骤（3）的Merge在百万级大表合并效率非常高，同时在合并过程中，表的列数以及对目标表的insert数目和update数目都会影响Merge的执行时间。综上所述，系统执行时间明显缩短，达到了预期的效果。

6 结 语

大数据量Excel数据导入系统，实现了高效安全的数据导入功能。在大数据时代，如何能将大数据量外部文件数据导入到数据库中，方便导入数据后查询、备份、挖掘和分析一直是人们研究的焦点问题。该系统为这一问题提供了一种可行的方案，具有一定的实用价值。

参考文献

[1] 乔治强.基于C#的Excel 数据导入导出SQL Server 技术研究[J].电脑知识与技术，2012（26）：195?196.

[2] 刘小豫，赵蔷.基于C#的Excel数据导入导出SQL Server的研究与实现[J].中小企业管理与科技，2012（31）：287?288.

[3] 王毅飞.基于.Net 的Excel和数据库数据转换[J].计算机时代，2013（5）：33?36.

[4] 罗琴媚.千万级大表转换导入的实现研究[J].计算机光盘软件与应用，2013（6）：288?289.

[5] HILLAR G C. C#并行编程高级教程[M].北京：清华大学出版社，2012.

[6] 王旭辉.Excel数据导入数据库的设计与实现[J].现代电子技术，2013，36（12）：71?73.

[7] 李晓京，文治洪，胡文东，等.C++/CLR 数据库与Excel并行数据转换技术研究[J].计算机技术与发展，2013（7）：155?158.

5.2 测试结果

系统测试结果，如表1所示。

表1 系统测试结果表 ms

步骤（1）使用单线程与多线程对比测试结果见表2。

表2 步骤一测试结果表 ms

步骤（2）中使用SqlBulkCopy与其他方法对比的测试结果，如表3所示。

表3 步骤二测试结果表

步骤（3）中使用Merge与游标逐条插入对比的测试结果，如表4所示。

表4 步骤三测试结果表

5.3 结果分析

步骤（1）中外部文件数据加载至内存时间明显缩短，但数据量成倍增加时，运行时间并没有成倍增加，可见在数据量越大，多线程的优势更加明显。步骤（2）中SqlBulkCopy导入效率在同样数据量情况下与其他相比有了明显提高。步骤（3）的Merge在百万级大表合并效率非常高，同时在合并过程中，表的列数以及对目标表的insert数目和update数目都会影响Merge的执行时间。综上所述，系统执行时间明显缩短，达到了预期的效果。

6 结 语

大数据量Excel数据导入系统，实现了高效安全的数据导入功能。在大数据时代，如何能将大数据量外部文件数据导入到数据库中，方便导入数据后查询、备份、挖掘和分析一直是人们研究的焦点问题。该系统为这一问题提供了一种可行的方案，具有一定的实用价值。

参考文献

[1] 乔治强.基于C#的Excel 数据导入导出SQL Server 技术研究[J].电脑知识与技术，2012（26）：195?196.

[2] 刘小豫，赵蔷.基于C#的Excel数据导入导出SQL Server的研究与实现[J].中小企业管理与科技，2012（31）：287?288.

[3] 王毅飞.基于.Net 的Excel和数据库数据转换[J].计算机时代，2013（5）：33?36.

[4] 罗琴媚.千万级大表转换导入的实现研究[J].计算机光盘软件与应用，2013（6）：288?289.

[5] HILLAR G C. C#并行编程高级教程[M].北京：清华大学出版社，2012.

[6] 王旭辉.Excel数据导入数据库的设计与实现[J].现代电子技术，2013，36（12）：71?73.

[7] 李晓京，文治洪，胡文东，等.C++/CLR 数据库与Excel并行数据转换技术研究[J].计算机技术与发展，2013（7）：155?158.