彭磊+李先國

摘 要： 針對現(xiàn)有大數(shù)據(jù)量Excel導(dǎo)入系統(tǒng)效率較低的問題，設(shè)計并實現(xiàn)了一種新的大數(shù)據(jù)量Excel數(shù)據(jù)導(dǎo)入系統(tǒng)。該系統(tǒng)采用C#語言，在VS2012的開發(fā)環(huán)境中，利用多線程技術(shù)將大數(shù)據(jù)量Excel表格數(shù)據(jù)導(dǎo)入到SQL Server中，加快了信息導(dǎo)入的速度。并且在臨時表與目標(biāo)表之間的合并過程中，摒棄了傳統(tǒng)的游標(biāo)逐條插入方式，取而代之的是SQL Server 2008中的Merge技術(shù)，極大地加快了數(shù)據(jù)庫端不同表之間數(shù)據(jù)的同步過程。實驗測試結(jié)果表明，整個系統(tǒng)導(dǎo)入100萬條數(shù)據(jù)耗時9 534 ms，遠(yuǎn)少于傳統(tǒng)Excel導(dǎo)入系統(tǒng)所耗時間，有效提高大數(shù)據(jù)量Excel導(dǎo)入系統(tǒng)的效率。

關(guān)鍵詞： 大數(shù)據(jù)量； Excel； SQL Server； 多線程； Merge

中圖分類號： TN911?34； TP391.13 文獻(xiàn)標(biāo)識碼： 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是目前廣泛使用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。基于安全性、通用性和共享性等考慮，信息系統(tǒng)一般都會給用戶提供借助Excel進(jìn)行數(shù)據(jù)導(dǎo)入導(dǎo)出數(shù)據(jù)庫的功能。在一些文獻(xiàn)中，介紹了將Excel轉(zhuǎn)化為xml文件再進(jìn)行導(dǎo)入[1]，或者將Excel轉(zhuǎn)換成csv文件再進(jìn)行導(dǎo)入，還有的文獻(xiàn)介紹用自動化的方法啟動Excel進(jìn)程逐行讀取，但此類方法操作復(fù)雜或者運(yùn)行緩慢，在實際應(yīng)用中效果均不是很理想[1]。如何能將大數(shù)據(jù)量的Excel高效準(zhǔn)確地導(dǎo)入到SQL Server仍然是目前研究的重點(diǎn)和難點(diǎn)。

本文介紹在.Net環(huán)境下，使用C#語言實現(xiàn)大數(shù)據(jù)量Excel數(shù)據(jù)導(dǎo)入SQL Server的一種新系統(tǒng)。該系統(tǒng)采用多線程技術(shù)，最大限度的發(fā)揮計算機(jī)性能，提高了大數(shù)據(jù)量的Excel外部數(shù)據(jù)文件加載到內(nèi)存的效率；并且使用新的Merge技術(shù)，加快了數(shù)據(jù)庫端不同數(shù)據(jù)表之間的數(shù)據(jù)同步過程。該系統(tǒng)實現(xiàn)了全自動化的錄入信息，并且方便了錄入數(shù)據(jù)后的查詢、備份、挖掘和分析，減輕了工作人員的工作量，有效地提高了工作效率。

1 概 述

簡單地講，Excel表格數(shù)據(jù)導(dǎo)入SQL Server可以分為三個步驟，其系統(tǒng)結(jié)構(gòu)圖，如圖1所示。

（1） Excel數(shù)據(jù)文件加載到內(nèi)存數(shù)據(jù)庫中。

（2） 內(nèi)存數(shù)據(jù)庫到SQL Server的轉(zhuǎn)存過程。

（3） 數(shù)據(jù)庫端的臨時表與目標(biāo)表之間的數(shù)據(jù)合并同步過程。

本系統(tǒng)在上述三個步驟中，分別采用目前先進(jìn)的技術(shù)來提高執(zhí)行效率，從而提高整個系統(tǒng)的導(dǎo)入效率。下面進(jìn)行詳細(xì)的敘述。

圖1 系統(tǒng)結(jié)構(gòu)圖

2 關(guān)鍵技術(shù)

（1） .NET平臺。.Net Framework 是微軟公司W(wǎng)indows 操作系統(tǒng)的組件，該組件使軟件應(yīng)用程序和Web 服務(wù)的建立更加容易，其對于軟件開發(fā)的支持提供了許多新特性，并基于該平臺推出了包括Visual C++.NET、VisualBasic.NET 和Visual C#.NET 等開發(fā)工具。其中，C#是微軟力推的.NET 標(biāo)準(zhǔn)開發(fā)語言，具有很多眾所周知的優(yōu)點(diǎn)。因此，關(guān)于C#應(yīng)用開發(fā)的幫助文檔及相關(guān)文獻(xiàn)資料較為廣泛且全面[1]。

（2） OLEDB組件。ODBC.NET是ODBC數(shù)據(jù)庫通信標(biāo)準(zhǔn)的.NET 升級，同時也是微軟的戰(zhàn)略性的通向不同的數(shù)據(jù)源的低級應(yīng)用程序接口。OLE DB 不僅包括微軟資助的標(biāo)準(zhǔn)數(shù)據(jù)接口開放數(shù)據(jù)庫連通性（ODBC）的結(jié)構(gòu)化查詢語言（SQL）能力，還具有面向其他非SQL 數(shù)據(jù)類型的通路。因此，出于應(yīng)用開發(fā)的數(shù)據(jù)庫通用性和維護(hù)升級和長遠(yuǎn)考慮，Ole DB.NET 是較為理想的選擇[1]。

（3） SqlBulkCopy類。SqlBulkCopy 類提供了一種將其他源的數(shù)據(jù)批量復(fù)制到SQL Server 數(shù)據(jù)庫表中高性能的方法。SqlBulkCopy 包含可以重載的方法WriteToServer，它用來從其他數(shù)據(jù)源復(fù)制數(shù)據(jù)目的地。SqlBulkCopy 復(fù)制數(shù)據(jù)的原理是采用SQL Server 提供的Bcp 命令提示符讀取到內(nèi)存數(shù)據(jù)庫（DataSet）中，用工具進(jìn)行數(shù)據(jù)的批量復(fù)制。Bcp 在SQL Server 實例和數(shù)據(jù)文件之間以用戶指定的格式復(fù)制數(shù)據(jù)[2]。

（4） 多線程技術(shù)。多線程是為了同步完成多項任務(wù)，不是為了提高運(yùn)行效率，而是為了提高資源使用效率來提高系統(tǒng)的效率。線程是在同一時間需要完成多項任務(wù)的時候?qū)崿F(xiàn)的。使用C#編寫任何程序時，都有一個入口：Main（）方法。程序從Main 方法的第一條語句開始執(zhí)行，直到這個方法為止，Main（）方法的執(zhí)行是在一個線程中即主線程[3]。在信息系統(tǒng)的應(yīng)用程序中，導(dǎo)入的數(shù)據(jù)量比較小時，基本上不需要使用多線程，但當(dāng)導(dǎo)入的數(shù)據(jù)量較大不使用多線程技術(shù)就會產(chǎn)生“假死”現(xiàn)象，而使用多線程可以很好地解決這一問題[4]。

（5） 數(shù)據(jù)庫中的Merge技術(shù)。Merge關(guān)鍵字是一個神奇的DML關(guān)鍵字。它在SQL Server 2008被引入，它能將Insert，Update，Delete簡單的并為一句，根據(jù)一個源數(shù)據(jù)表對另一個數(shù)據(jù)表進(jìn)行確定性的插入、更新和刪除這樣復(fù)雜的操作。例如，根據(jù)在兩個表中找到的差異行在其中一個表中進(jìn)行插入、更新或刪除行，可以對兩個表進(jìn)行同步。Merge的高效在于它僅需要一次全表掃描即可完成全部工作。Merge語句還有一個強(qiáng)大的功能是通過OUTPUT子句，可以將剛剛做過變動的數(shù)據(jù)進(jìn)行輸出至另一張數(shù)據(jù)庫表中。

3 系統(tǒng)設(shè)計

大數(shù)據(jù)量Excel數(shù)據(jù)導(dǎo)入系統(tǒng)的系統(tǒng)流程圖，如圖2所示。

圖2 系統(tǒng)流程圖

該系統(tǒng)中所涉及三個關(guān)鍵步驟的詳細(xì)說明如下：

（1） Excel數(shù)據(jù)文件加載到內(nèi)存數(shù)據(jù)庫（DataSet）中。該系統(tǒng)將采用多線程技術(shù)來將Excel數(shù)據(jù)文件加載到內(nèi)存中。通過OLEDB技術(shù)讀取Excel文件的大小、Excel文件中Sheet數(shù)目，以及每個Sheet中的數(shù)據(jù)行總數(shù)，來確定生成線程的數(shù)目和Datatable的數(shù)目，然后每個線程讀取部分?jǐn)?shù)據(jù)到各自對應(yīng)Datatable中。

（2） 內(nèi)存數(shù)據(jù)庫（DataSet）到SQL Server的轉(zhuǎn)存過程。該系統(tǒng)將采用SqlBulkCopy 將DataSet中的數(shù)據(jù)進(jìn)行批量復(fù)制到數(shù)據(jù)庫的臨時表中。在步驟（1）中，當(dāng)DataSet中的數(shù)據(jù)量超過一定界限時，Excel數(shù)據(jù)讀取線程全部暫停，執(zhí)行步驟（2），然后清空DataSet中的數(shù)據(jù)，繼續(xù)執(zhí)行步驟（1），直到所有數(shù)據(jù)都轉(zhuǎn)存到數(shù)據(jù)庫中的臨時表中。

如果在執(zhí)行過程中，由于新導(dǎo)入的數(shù)據(jù)在主鍵列上有重復(fù)數(shù)據(jù)導(dǎo)致SqlBulkCopy的WriteToServer方法出錯時，系統(tǒng)將捕獲異常，然后在異常處理中，采用折半的方式批量復(fù)制數(shù)據(jù)到臨時表，直至剩下出錯的記錄返回個客戶端。

（3） 數(shù)據(jù)庫端的臨時表與目標(biāo)表之間的數(shù)據(jù)合并同步過程。該系統(tǒng)中采用SQL Server中的Merge技術(shù)，步驟（2）中新生成的臨時表與系統(tǒng)中原有的目標(biāo)表中的數(shù)據(jù)進(jìn)行一次同步，如果數(shù)據(jù)已存在，該記錄將被更新，如果記錄不存在，插入新紀(jì)錄。Merge語句還有一個強(qiáng)大的功能是通過OUTPUT子句，可以將剛剛做過變動的數(shù)據(jù)進(jìn)行輸出至另一張數(shù)據(jù)庫表中。

4 性能分析

大數(shù)據(jù)量Excel數(shù)據(jù)導(dǎo)入系統(tǒng)，在數(shù)據(jù)導(dǎo)入方面的性能有了很大的提升。主要體現(xiàn)在以下兩點(diǎn)：

（1） 高效性。多線程技術(shù)的使用以及對線程數(shù)目的控制，縮短了Excel文件數(shù)據(jù)加載到內(nèi)存的時間。在Excel文件數(shù)據(jù)量小時，由于使用多線程增加了資源的開銷，導(dǎo)致執(zhí)行效率反而不如單線程的執(zhí)行效率的情況，本系統(tǒng)同樣做了判斷處理。SqlBulkCopy的使用極大的提高了數(shù)據(jù)從內(nèi)存到數(shù)據(jù)庫的轉(zhuǎn)存的執(zhí)行效率。SqlBulkCopy采用的的SQL Server的Bcp命令。Bcp命令在SQL Server 實例和數(shù)據(jù)文件之間復(fù)制數(shù)據(jù)的效率非常高。本系統(tǒng)并沒有單獨(dú)進(jìn)行新導(dǎo)入數(shù)據(jù)的查重檢測，而是在導(dǎo)入失敗后的異常處理中采用了折半導(dǎo)入的方式來找到出錯數(shù)據(jù)行，在導(dǎo)入的同時查找重復(fù)的數(shù)據(jù)，縮短系統(tǒng)執(zhí)行時間。本系統(tǒng)中同樣摒棄了費(fèi)時且根本無法完成大容量數(shù)據(jù)合并的編程循環(huán)SQL 語句轉(zhuǎn)換插入方式，采用的新的Merge技術(shù)。Merge技術(shù)在兩個表之間的數(shù)據(jù)同步的過程中執(zhí)行效率非常高。

（2） 安全性。在數(shù)據(jù)轉(zhuǎn)存到數(shù)據(jù)庫的過程中，該系統(tǒng)并沒有直接對系統(tǒng)中的目標(biāo)表進(jìn)行操作，取而代之的是將新導(dǎo)入的數(shù)據(jù)放到臨時表中。如果導(dǎo)入數(shù)據(jù)出錯只需要將臨時表數(shù)據(jù)清空即可，并不需要對目標(biāo)表進(jìn)行操作。Merge技術(shù)中的OUTPUT功能能夠把更新過的記錄行輸出到另一張數(shù)據(jù)庫表中，為日后的查看修改記錄提供了方便，增強(qiáng)了導(dǎo)入數(shù)據(jù)安全性。

5 測試結(jié)果

5.1 測試環(huán)境

硬件：PC（CPU：Intel（R） i3?2350M CPU @ 2.30 GHz；內(nèi)存：2 GB，DDR3；硬盤：5 400 R/S）。軟件：Windows7家庭版（X64）；SQL Server R2（X64）；VS2012（X64）。

5.2 測試結(jié)果

系統(tǒng)測試結(jié)果，如表1所示。

表1 系統(tǒng)測試結(jié)果表 ms

步驟（1）使用單線程與多線程對比測試結(jié)果見表2。

表2 步驟一測試結(jié)果表 ms

步驟（2）中使用SqlBulkCopy與其他方法對比的測試結(jié)果，如表3所示。

表3 步驟二測試結(jié)果表

步驟（3）中使用Merge與游標(biāo)逐條插入對比的測試結(jié)果，如表4所示。

表4 步驟三測試結(jié)果表

5.3 結(jié)果分析

步驟（1）中外部文件數(shù)據(jù)加載至內(nèi)存時間明顯縮短，但數(shù)據(jù)量成倍增加時，運(yùn)行時間并沒有成倍增加，可見在數(shù)據(jù)量越大，多線程的優(yōu)勢更加明顯。步驟（2）中SqlBulkCopy導(dǎo)入效率在同樣數(shù)據(jù)量情況下與其他相比有了明顯提高。步驟（3）的Merge在百萬級大表合并效率非常高，同時在合并過程中，表的列數(shù)以及對目標(biāo)表的insert數(shù)目和update數(shù)目都會影響Merge的執(zhí)行時間。綜上所述，系統(tǒng)執(zhí)行時間明顯縮短，達(dá)到了預(yù)期的效果。

6 結(jié) 語

大數(shù)據(jù)量Excel數(shù)據(jù)導(dǎo)入系統(tǒng)，實現(xiàn)了高效安全的數(shù)據(jù)導(dǎo)入功能。在大數(shù)據(jù)時代，如何能將大數(shù)據(jù)量外部文件數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，方便導(dǎo)入數(shù)據(jù)后查詢、備份、挖掘和分析一直是人們研究的焦點(diǎn)問題。該系統(tǒng)為這一問題提供了一種可行的方案，具有一定的實用價值。

參考文獻(xiàn)

[1] 喬治強(qiáng).基于C#的Excel 數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server 技術(shù)研究[J].電腦知識與技術(shù)，2012（26）：195?196.

[2] 劉小豫，趙薔.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server的研究與實現(xiàn)[J].中小企業(yè)管理與科技，2012（31）：287?288.

[3] 王毅飛.基于.Net 的Excel和數(shù)據(jù)庫數(shù)據(jù)轉(zhuǎn)換[J].計算機(jī)時代，2013（5）：33?36.

[4] 羅琴媚.千萬級大表轉(zhuǎn)換導(dǎo)入的實現(xiàn)研究[J].計算機(jī)光盤軟件與應(yīng)用，2013（6）：288?289.

[5] HILLAR G C. C#并行編程高級教程[M].北京：清華大學(xué)出版社，2012.

[6] 王旭輝.Excel數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫的設(shè)計與實現(xiàn)[J].現(xiàn)代電子技術(shù)，2013，36（12）：71?73.

[7] 李曉京，文治洪，胡文東，等.C++/CLR 數(shù)據(jù)庫與Excel并行數(shù)據(jù)轉(zhuǎn)換技術(shù)研究[J].計算機(jī)技術(shù)與發(fā)展，2013（7）：155?158.

5.2 測試結(jié)果

系統(tǒng)測試結(jié)果，如表1所示。

表1 系統(tǒng)測試結(jié)果表 ms

步驟（1）使用單線程與多線程對比測試結(jié)果見表2。

表2 步驟一測試結(jié)果表 ms

步驟（2）中使用SqlBulkCopy與其他方法對比的測試結(jié)果，如表3所示。

表3 步驟二測試結(jié)果表

步驟（3）中使用Merge與游標(biāo)逐條插入對比的測試結(jié)果，如表4所示。

表4 步驟三測試結(jié)果表

5.3 結(jié)果分析

步驟（1）中外部文件數(shù)據(jù)加載至內(nèi)存時間明顯縮短，但數(shù)據(jù)量成倍增加時，運(yùn)行時間并沒有成倍增加，可見在數(shù)據(jù)量越大，多線程的優(yōu)勢更加明顯。步驟（2）中SqlBulkCopy導(dǎo)入效率在同樣數(shù)據(jù)量情況下與其他相比有了明顯提高。步驟（3）的Merge在百萬級大表合并效率非常高，同時在合并過程中，表的列數(shù)以及對目標(biāo)表的insert數(shù)目和update數(shù)目都會影響Merge的執(zhí)行時間。綜上所述，系統(tǒng)執(zhí)行時間明顯縮短，達(dá)到了預(yù)期的效果。

6 結(jié) 語

大數(shù)據(jù)量Excel數(shù)據(jù)導(dǎo)入系統(tǒng)，實現(xiàn)了高效安全的數(shù)據(jù)導(dǎo)入功能。在大數(shù)據(jù)時代，如何能將大數(shù)據(jù)量外部文件數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，方便導(dǎo)入數(shù)據(jù)后查詢、備份、挖掘和分析一直是人們研究的焦點(diǎn)問題。該系統(tǒng)為這一問題提供了一種可行的方案，具有一定的實用價值。

參考文獻(xiàn)

[1] 喬治強(qiáng).基于C#的Excel 數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server 技術(shù)研究[J].電腦知識與技術(shù)，2012（26）：195?196.

[2] 劉小豫，趙薔.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server的研究與實現(xiàn)[J].中小企業(yè)管理與科技，2012（31）：287?288.

[3] 王毅飛.基于.Net 的Excel和數(shù)據(jù)庫數(shù)據(jù)轉(zhuǎn)換[J].計算機(jī)時代，2013（5）：33?36.

[4] 羅琴媚.千萬級大表轉(zhuǎn)換導(dǎo)入的實現(xiàn)研究[J].計算機(jī)光盤軟件與應(yīng)用，2013（6）：288?289.

[5] HILLAR G C. C#并行編程高級教程[M].北京：清華大學(xué)出版社，2012.

[6] 王旭輝.Excel數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫的設(shè)計與實現(xiàn)[J].現(xiàn)代電子技術(shù)，2013，36（12）：71?73.

[7] 李曉京，文治洪，胡文東，等.C++/CLR 數(shù)據(jù)庫與Excel并行數(shù)據(jù)轉(zhuǎn)換技術(shù)研究[J].計算機(jī)技術(shù)與發(fā)展，2013（7）：155?158.

5.2 測試結(jié)果

系統(tǒng)測試結(jié)果，如表1所示。

表1 系統(tǒng)測試結(jié)果表 ms

步驟（1）使用單線程與多線程對比測試結(jié)果見表2。

表2 步驟一測試結(jié)果表 ms

步驟（2）中使用SqlBulkCopy與其他方法對比的測試結(jié)果，如表3所示。

表3 步驟二測試結(jié)果表

步驟（3）中使用Merge與游標(biāo)逐條插入對比的測試結(jié)果，如表4所示。

表4 步驟三測試結(jié)果表

5.3 結(jié)果分析

步驟（1）中外部文件數(shù)據(jù)加載至內(nèi)存時間明顯縮短，但數(shù)據(jù)量成倍增加時，運(yùn)行時間并沒有成倍增加，可見在數(shù)據(jù)量越大，多線程的優(yōu)勢更加明顯。步驟（2）中SqlBulkCopy導(dǎo)入效率在同樣數(shù)據(jù)量情況下與其他相比有了明顯提高。步驟（3）的Merge在百萬級大表合并效率非常高，同時在合并過程中，表的列數(shù)以及對目標(biāo)表的insert數(shù)目和update數(shù)目都會影響Merge的執(zhí)行時間。綜上所述，系統(tǒng)執(zhí)行時間明顯縮短，達(dá)到了預(yù)期的效果。

6 結(jié) 語

大數(shù)據(jù)量Excel數(shù)據(jù)導(dǎo)入系統(tǒng)，實現(xiàn)了高效安全的數(shù)據(jù)導(dǎo)入功能。在大數(shù)據(jù)時代，如何能將大數(shù)據(jù)量外部文件數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，方便導(dǎo)入數(shù)據(jù)后查詢、備份、挖掘和分析一直是人們研究的焦點(diǎn)問題。該系統(tǒng)為這一問題提供了一種可行的方案，具有一定的實用價值。

參考文獻(xiàn)

[1] 喬治強(qiáng).基于C#的Excel 數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server 技術(shù)研究[J].電腦知識與技術(shù)，2012（26）：195?196.

[2] 劉小豫，趙薔.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server的研究與實現(xiàn)[J].中小企業(yè)管理與科技，2012（31）：287?288.

[3] 王毅飛.基于.Net 的Excel和數(shù)據(jù)庫數(shù)據(jù)轉(zhuǎn)換[J].計算機(jī)時代，2013（5）：33?36.

[4] 羅琴媚.千萬級大表轉(zhuǎn)換導(dǎo)入的實現(xiàn)研究[J].計算機(jī)光盤軟件與應(yīng)用，2013（6）：288?289.

[5] HILLAR G C. C#并行編程高級教程[M].北京：清華大學(xué)出版社，2012.

[6] 王旭輝.Excel數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫的設(shè)計與實現(xiàn)[J].現(xiàn)代電子技術(shù)，2013，36（12）：71?73.

[7] 李曉京，文治洪，胡文東，等.C++/CLR 數(shù)據(jù)庫與Excel并行數(shù)據(jù)轉(zhuǎn)換技術(shù)研究[J].計算機(jī)技術(shù)與發(fā)展，2013（7）：155?158.