基于C#的Excel數(shù)據(jù)批量導(dǎo)入SqlServer的方法研究與實現(xiàn)

周虎

摘? 要：批量數(shù)據(jù)導(dǎo)入在實際工作中提高了數(shù)據(jù)處理的效率，保證了數(shù)據(jù)的完整性。本文以某地育齡婦女基本信息為例，詳細介紹了Excel數(shù)據(jù)批量導(dǎo)入SqlServer的設(shè)計思路與實現(xiàn)方法。

關(guān)鍵詞：Excel；SqlServer；批量數(shù)據(jù)導(dǎo)入；NPOI；C#編程

中圖分類號：TP311.11?????????? 文獻標識碼：A

1?? 引言（Introduction）

Excel是辦公中常用的電子表格處理工具，SqlServer是目前流行的數(shù)據(jù)庫管理系統(tǒng)，均在信息系統(tǒng)中被廣泛使用[1]。在某地數(shù)字化計生服務(wù)管理系統(tǒng)中需要從育齡婦女基本信息管理系統(tǒng)中導(dǎo)入育齡婦女的基本信息，而育齡婦女基本信息管理系統(tǒng)中只能將當前單位中所有的育齡婦女信息導(dǎo)出到Excel文件中，然后從Excel文件中將育齡婦女信息導(dǎo)入到SqlServer數(shù)據(jù)庫中。在進行數(shù)據(jù)導(dǎo)入時，每次只需將增量數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫，同時Excel中一條記錄要寫入到數(shù)據(jù)庫多張表中。目前Excel導(dǎo)入數(shù)據(jù)庫主要包括以下幾種方法：

（1）將Excel轉(zhuǎn)化為XML或者CSV文件，然后導(dǎo)入數(shù)據(jù)庫。

（2）使用Office提供的Com組件技術(shù)。

（3）使用第三方組件，如NPOI或OLEDB。

其中第一種方法和第二種方法操作復(fù)雜或者運行緩慢，在實際應(yīng)用中效果均不是很理想[2]，并且本系統(tǒng)在對Excel數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫時需要對需要對數(shù)據(jù)進行進一步加工和處理，所以本文主要借助第三方組件NPOI的方式來實現(xiàn)Excel數(shù)據(jù)的批量導(dǎo)入。

2? Excel批量數(shù)據(jù)導(dǎo)入技術(shù)分析（Excel import bulk

data technical analysis）

在本項目中，需要將育齡婦女基本信息Excel文件內(nèi)容讀取到內(nèi)存中，然后在內(nèi)存中和數(shù)據(jù)庫中的育齡婦女信息進行比對，將增量數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫，具體流程如圖1所示。

圖1 NPOI讀取Excel數(shù)據(jù)到數(shù)據(jù)庫

Fig.1 NPOI read excel data to database

在圖1操作流程中，關(guān)鍵技術(shù)包括使用NPOI讀取Excel數(shù)據(jù)到內(nèi)存中的DataTable和從內(nèi)存中將增量數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫。

2.1?? NPOI

NPOI是一個開源的基于.Net的讀寫Excel、Word等微軟OLE2組件文檔的項目，使用NPOI你就可以在沒有安裝Office或者相應(yīng)環(huán)境的機器上對Word/Excel文檔進行讀寫[3]。NPOI是一個免費的開源組件，包含了大部分的Excel功能，同時支持文件的導(dǎo)入與導(dǎo)出。

在本項目中使用NPOI將Excel表中的數(shù)據(jù)讀取到內(nèi)存中的Datatable中，同時將育齡婦女的編號存入集合中，便于和數(shù)據(jù)庫中的育齡婦女信息進行比對。

2.2?? 增量數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫

在本項目中，由于每次需要導(dǎo)入的育齡婦女基本信息都是包含所有的育齡婦女基本信息，為了進一步提高導(dǎo)入數(shù)據(jù)的效率，采用每次導(dǎo)入增量數(shù)據(jù)的方法，這里需要將Excel文件中的育齡婦女信息和數(shù)據(jù)庫中的育齡婦女信息進行集合運算，將數(shù)據(jù)庫中不包含的但Excel文件中包含的育齡婦女信息先計算出來，然后對增量數(shù)據(jù)進行批量導(dǎo)入數(shù)據(jù)庫。具體流程如圖2所示。

圖2 增量數(shù)據(jù)導(dǎo)入

Fig.2 Incremental data import

在計算出增量數(shù)據(jù)后，Excle文件中一條記錄需要同時保存到數(shù)據(jù)庫中多個表中，同時需要根據(jù)數(shù)據(jù)庫中數(shù)據(jù)字典中相應(yīng)的字段進行數(shù)據(jù)處理。具體操作步驟如下：首先將需要用到數(shù)據(jù)字典的字段內(nèi)容讀取到內(nèi)存中，如育齡婦女的現(xiàn)居地編號和婚姻狀況等；其次循環(huán)遍歷增量數(shù)據(jù)，同時生成相應(yīng)的添加數(shù)據(jù)的SQL語句；最后使用SQL事務(wù)操作，實現(xiàn)批量數(shù)據(jù)的導(dǎo)入功能。

3? Excel批量數(shù)據(jù)導(dǎo)入的實現(xiàn)（Excel data import

implementation）

實現(xiàn)Excel批量數(shù)據(jù)導(dǎo)入，主要分為Excel數(shù)據(jù)讀取，數(shù)據(jù)處理和數(shù)據(jù)導(dǎo)入。

3.1?? Excel數(shù)據(jù)讀取的實現(xiàn)

實現(xiàn)Excel數(shù)據(jù)的讀取功能，首先根據(jù)Excel表中的字段生成內(nèi)存中DataTable內(nèi)存表對象，如圖3所示。

圖3 Excel文件表頭

Fig.3 Excel file header

根據(jù)這些表頭字段，在內(nèi)存中生成DataTable的字段信息，并循環(huán)遍歷Excel中的數(shù)據(jù)行，將Excel中的數(shù)據(jù)讀取到DataTable中去，如程序1所示。

DataTable dt=newDataTable（）；//構(gòu)造內(nèi)存表

IRow headRow=sheet.GetRow（2）；//取第二行

//設(shè)置datatable字段

for （int i=headRow.FirstCellNum， len=headRow.LastCellNum； i<；len；i++）

{//統(tǒng)一定制字段名稱

dt.Columns.Add（"item"+i.ToString（））；

}

for （int r=0， j=tempRow.FirstCellNum， len2=tempRow.LastCellNum；j<；len2；j++，r++）endprint

{///循環(huán)遍歷Excel數(shù)據(jù)行

ICell cell=tempRow.GetCell（j）；

if （cell！=null）??????????????????? {

switch （cell.CellType）//類型處理

{

…..//根據(jù)相應(yīng)類型添加到指定字段

}

}

}

dt.Rows.Add（dataRow）；

程序1? 讀取Excel文件內(nèi)容到DataTable

3.2?? 數(shù)據(jù)處理

在數(shù)據(jù)處理環(huán)節(jié)，首先計算增量數(shù)據(jù)，然后對增量數(shù)據(jù)進行數(shù)據(jù)處理，將處理結(jié)果轉(zhuǎn)換為SQL插入語句，關(guān)鍵代碼如程序2所示。

List<；string>；l1=newList<；string>；（）；//保存Excel中婦女編碼

List<；string>；l3=newList<；string>；（）；//保存數(shù)據(jù)庫中婦女編碼

for （int i=0；i<；dt1.Rows.Count；i++）//循環(huán)內(nèi)存Datatable中數(shù)據(jù)

l1.Add（dt1.Rows[i][0].ToString（））；//保存婦女編碼

for （int i=0；i<；dt_db.Rows.Count；i++）//遍歷數(shù)據(jù)庫中婦女編碼

l3.Add（dt_db.Rows[i][0].ToString（））；//保存婦女編碼

var l2=l1.Except（l3）；//獲取增量婦女編碼

DataTable dt3=dt1.Clone（）；//克隆表結(jié)構(gòu)，用于保存增量數(shù)據(jù)

string cond=listToString（l2）；

DataRow[] rows=dt2.Select（"item1 in（"+cond+"）and item1<；>；''"）；//過濾增量數(shù)據(jù)

foreach （DataRow dr in rows）

dt3.Rows.Add（dr.ItemArray）；

程序2? 計算增量數(shù)據(jù)

獲取完增量數(shù)據(jù)后，對增量數(shù)據(jù)進行數(shù)據(jù)處理，并生成SQL語句，關(guān)鍵代碼如程序3所示。

for （int i=0；i<；dt3.Rows.Count；i++）//遍歷增量數(shù)據(jù)

{///添加到3張表中，并根據(jù)數(shù)據(jù)字典信息更新相應(yīng)字段

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?? 數(shù)據(jù)批量導(dǎo)入

批量數(shù)據(jù)導(dǎo)入的SQL語句生成后，使用SQL事務(wù)完成增量數(shù)據(jù)的添加，關(guān)鍵代碼如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

{

SqlCommand cmd=newSqlCommand（）；

try

{//循環(huán)

foreach （string sq in sql）

{

string cmdText=sq；

PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

}

trans.Commit（）；}

catch

{

trans.Rollback（）；

throw；

}}

程序4 SQL事務(wù)導(dǎo)入數(shù)據(jù)

通過以上幾個步驟，最終實現(xiàn)了基于NPOI的Excel數(shù)據(jù)導(dǎo)入SqlServer程序，程序運行界面如圖4所示。

圖4 程序運行效果

Fig.4 Effect of operation procedure

4?? 結(jié)論（Conclusion）

本文通過對育齡婦女基本信息Excel文件的處理，實現(xiàn)了對增量數(shù)據(jù)批量導(dǎo)入數(shù)據(jù)庫的功能，在實際工作中提高了工作效率，但程序運行的效率還有待于進一步的提高。

參考文獻（References）

[1] 張麗英.基于.NET的Excel數(shù)據(jù)批量導(dǎo)入SQL Server的設(shè)計與

實現(xiàn)[J].南通紡織職業(yè)技術(shù)學(xué)院學(xué)報，2012，12（1）：7-10.

[2] 喬治強.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server技術(shù)研究[J].

電腦知識與技術(shù)，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

com/.

作者簡介：

周? 虎（1977-），男，碩士，講師.研究領(lǐng)域：計算機應(yīng)用與軟

件開發(fā).endprint

string zf="insert into pub_zfxx（字段） values（值）；

；?；?；?；?；?； sql.Add（zf）；

；?；?；?；?；?；?； by="insert into pub_byqk （字段） values（值）；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； sql.Add（by）；

；}

程序3 生成SQL添加語句

3.3?；?； 數(shù)據(jù)批量導(dǎo)入

批量數(shù)據(jù)導(dǎo)入的SQL語句生成后，使用SQL事務(wù)完成增量數(shù)據(jù)的添加，關(guān)鍵代碼如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

SqlCommand cmd=newSqlCommand（）；

try

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {//循環(huán)

foreach （string sq in sql）

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

string cmdText=sq；

；PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； }

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； trans.Commit（）；}

catch

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； trans.Rollback（）；

throw；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； }}

程序4 SQL事務(wù)導(dǎo)入數(shù)據(jù)

通過以上幾個步驟，最終實現(xiàn)了基于NPOI的Excel數(shù)據(jù)導(dǎo)入SqlServer程序，程序運行界面如圖4所示。

圖4 程序運行效果

；?；?； Fig.4 Effect of operation procedure

4?；?； 結(jié)論（Conclusion）

本文通過對育齡婦女基本信息Excel文件的處理，實現(xiàn)了對增量數(shù)據(jù)批量導(dǎo)入數(shù)據(jù)庫的功能，在實際工作中提高了工作效率，但程序運行的效率還有待于進一步的提高。

參考文獻（References）

[1] 張麗英.基于.NET的Excel數(shù)據(jù)批量導(dǎo)入SQL Server的設(shè)計與

；?；?；?； 實現(xiàn)[J].南通紡織職業(yè)技術(shù)學(xué)院學(xué)報，2012，12（1）：7-10.

[2] 喬治強.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server技術(shù)研究[J].

；?；?；?； 電腦知識與技術(shù)，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

；?；?；?；?； com/.

作者簡介：

周?； 虎（1977-），男，碩士，講師.研究領(lǐng)域：計算機應(yīng)用與軟

；?；?；?；?； 件開發(fā).endprint

string zf="insert into pub_zfxx（字段） values（值）；

；?；?；?；?；?； sql.Add（zf）；

；?；?；?；?；?；?； by="insert into pub_byqk （字段） values（值）；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； sql.Add（by）；

；}

程序3 生成SQL添加語句

3.3?；?； 數(shù)據(jù)批量導(dǎo)入

批量數(shù)據(jù)導(dǎo)入的SQL語句生成后，使用SQL事務(wù)完成增量數(shù)據(jù)的添加，關(guān)鍵代碼如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

SqlCommand cmd=newSqlCommand（）；

try

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {//循環(huán)

foreach （string sq in sql）

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

string cmdText=sq；

；PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； }

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； trans.Commit（）；}

catch

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； trans.Rollback（）；

throw；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； }}

程序4 SQL事務(wù)導(dǎo)入數(shù)據(jù)

通過以上幾個步驟，最終實現(xiàn)了基于NPOI的Excel數(shù)據(jù)導(dǎo)入SqlServer程序，程序運行界面如圖4所示。

圖4 程序運行效果

；?；?； Fig.4 Effect of operation procedure

4?；?； 結(jié)論（Conclusion）

本文通過對育齡婦女基本信息Excel文件的處理，實現(xiàn)了對增量數(shù)據(jù)批量導(dǎo)入數(shù)據(jù)庫的功能，在實際工作中提高了工作效率，但程序運行的效率還有待于進一步的提高。

參考文獻（References）

[1] 張麗英.基于.NET的Excel數(shù)據(jù)批量導(dǎo)入SQL Server的設(shè)計與

；?；?；?； 實現(xiàn)[J].南通紡織職業(yè)技術(shù)學(xué)院學(xué)報，2012，12（1）：7-10.

[2] 喬治強.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server技術(shù)研究[J].

；?；?；?； 電腦知識與技術(shù)，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

；?；?；?；?； com/.

作者簡介：

周?； 虎（1977-），男，碩士，講師.研究領(lǐng)域：計算機應(yīng)用與軟

；?；?；?；?； 件開發(fā).endprint

string zf="insert into pub_zfxx（字段） values（值）；

；?；?；?；?；?； sql.Add（zf）；

；?；?；?；?；?；?； by="insert into pub_byqk （字段） values（值）；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； sql.Add（by）；

；}

程序3 生成SQL添加語句

3.3?；?； 數(shù)據(jù)批量導(dǎo)入

批量數(shù)據(jù)導(dǎo)入的SQL語句生成后，使用SQL事務(wù)完成增量數(shù)據(jù)的添加，關(guān)鍵代碼如程序4所示。

using （SqlTransaction trans=con.BeginTransaction（））

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

SqlCommand cmd=newSqlCommand（）；

try

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {//循環(huán)

foreach （string sq in sql）

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

string cmdText=sq；

；PrepareCommand（cmd，con，trans，cmdText，null）；

int val=cmd.ExecuteNonQuery（）；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； }

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； trans.Commit（）；}

catch

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； {

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； trans.Rollback（）；

throw；

；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?；?； }}

程序4 SQL事務(wù)導(dǎo)入數(shù)據(jù)

通過以上幾個步驟，最終實現(xiàn)了基于NPOI的Excel數(shù)據(jù)導(dǎo)入SqlServer程序，程序運行界面如圖4所示。

圖4 程序運行效果

；?；?； Fig.4 Effect of operation procedure

4?；?； 結(jié)論（Conclusion）

本文通過對育齡婦女基本信息Excel文件的處理，實現(xiàn)了對增量數(shù)據(jù)批量導(dǎo)入數(shù)據(jù)庫的功能，在實際工作中提高了工作效率，但程序運行的效率還有待于進一步的提高。

參考文獻（References）

[1] 張麗英.基于.NET的Excel數(shù)據(jù)批量導(dǎo)入SQL Server的設(shè)計與

；?；?；?； 實現(xiàn)[J].南通紡織職業(yè)技術(shù)學(xué)院學(xué)報，2012，12（1）：7-10.

[2] 喬治強.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出SQL Server技術(shù)研究[J].

；?；?；?； 電腦知識與技術(shù)，2012（26）：195-196.

[3] 佚名.NPOI HOME [EB/OL].CodePlex，http：//npoi.codeplex.

；?；?；?；?； com/.

作者簡介：

周?； 虎（1977-），男，碩士，講師.研究領(lǐng)域：計算機應(yīng)用與軟

；?；?；?；?； 件開發(fā).endprint