大數(shù)據(jù)量Excel數(shù)據(jù)導入系統(tǒng)的設計與實現(xiàn)

彭磊+李先國

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

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

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

1 概 述

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

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

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

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

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

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

2 關鍵技術

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

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

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

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

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

3 系統(tǒng)設計

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

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

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

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

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

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

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

4 性能分析

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

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

（2） 安全性。在數(shù)據(jù)轉(zhuǎn)存到數(shù)據(jù)庫的過程中，該系統(tǒng)并沒有直接對系統(tǒng)中的目標表進行操作，取而代之的是將新導入的數(shù)據(jù)放到臨時表中。如果導入數(shù)據(jù)出錯只需要將臨時表數(shù)據(jù)清空即可，并不需要對目標表進行操作。Merge技術中的OUTPUT功能能夠把更新過的記錄行輸出到另一張數(shù)據(jù)庫表中，為日后的查看修改記錄提供了方便，增強了導入數(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與游標逐條插入對比的測試結(jié)果，如表4所示。

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

5.3 結(jié)果分析

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

6 結(jié) 語

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

參考文獻

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

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

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

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

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

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

[7] 李曉京，文治洪，胡文東，等.C++/CLR 數(shù)據(jù)庫與Excel并行數(shù)據(jù)轉(zhuǎn)換技術研究[J].計算機技術與發(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與游標逐條插入對比的測試結(jié)果，如表4所示。

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

5.3 結(jié)果分析

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

6 結(jié) 語

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

參考文獻

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

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

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

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

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

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

[7] 李曉京，文治洪，胡文東，等.C++/CLR 數(shù)據(jù)庫與Excel并行數(shù)據(jù)轉(zhuǎn)換技術研究[J].計算機技術與發(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與游標逐條插入對比的測試結(jié)果，如表4所示。

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

5.3 結(jié)果分析

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

6 結(jié) 語

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

參考文獻

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

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

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

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

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

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

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