基于.Net的Excel和數(shù)據(jù)庫數(shù)據(jù)轉(zhuǎn)換

摘 要： 根據(jù)軍事理論練習系統(tǒng)的需求，分析了將Excel電子表格中的試題數(shù)據(jù)導入到Access數(shù)據(jù)庫中，以及將學生成績等信息導出到Excel電子表格的原理和方法，并應用于實際解決方案，有效提高了工作效率。

關鍵詞： .Net； Excel； 數(shù)據(jù)庫； 數(shù)據(jù)轉(zhuǎn)換

中圖分類號：TP393 文獻標志碼：A 文章編號：1006-8228（2013）05-33-04

Conversion of Excel and data in the database based on .NET

——take military theory practice system as an example

Wang Yilfei

（Comprehensive experimental teaching center of arts， Zhejiang normal university， Jinhua， Zhejiang 321004， China）

Abstract： According to requirements of the military practice theory system， the principles of importing the data of examination questions to Access database and exporting the students' information， such as marks to the spreadsheet of Excel， are analyzed. These principles， which can improve the working efficiency， are applied to the practical solutions.

Key words： .Net； Excel； database； data conversion

0 引言

軍事理論練習系統(tǒng)是在浙江省軍事理論教育基礎上，供學生練習軍事理論知識的系統(tǒng)。通過練習可使學生掌握相關軍事理論基礎知識，增強學生國防、愛國意識。

該系統(tǒng)是一個基于Web的.NET應用程序，數(shù)據(jù)庫服務器采用Access數(shù)據(jù)庫。在開發(fā)該系統(tǒng)時，原始試題庫是以Excle電子表格格式（.xls）存在，并且需要將學生成績導出到Excle電子表格中，因此手工操作任務繁重，效率低下。故需要實現(xiàn)Excle電子表格和Access數(shù)據(jù)庫之間的數(shù)據(jù)轉(zhuǎn)換。

1 .Net中數(shù)據(jù)轉(zhuǎn)換的原理分析

本文將Excel電子表格中的數(shù)據(jù)提取到Access數(shù)據(jù)庫中的過程稱為數(shù)據(jù)的導入，反之將Access數(shù)據(jù)庫中的數(shù)據(jù)提取到Excel電子表格的數(shù)據(jù)稱為數(shù)據(jù)的導出，通過.Net平臺實現(xiàn)，基本框架如圖1所示。

[數(shù)據(jù)庫] [.NET平臺] [Excel]

圖1

1.1 從Excel表格中導入數(shù)據(jù)至數(shù)據(jù)庫

1.1.1 使用數(shù)據(jù)庫管理工具實現(xiàn)

某些數(shù)據(jù)庫管理工具會提供相應的工具，將Excel電子表格的數(shù)據(jù)導入到相應的數(shù)據(jù)庫中，如Sql Server 2000提供了“導入數(shù)據(jù)”功能，通過DTS 導入/導出向?qū)?，方便實現(xiàn)數(shù)據(jù)的導入。

1.1.2 編寫代碼實現(xiàn)

通過.NET編寫代碼也可實現(xiàn)將Excel電子表格中的數(shù)據(jù)導入數(shù)據(jù)庫，實現(xiàn)過程如圖2所示。

[Excel] [DataSet] [Access]

圖2

具體實現(xiàn)步驟是：將Excel電子表格作為數(shù)據(jù)源，通過.NET Framework提供的相關類庫，將Excel數(shù)據(jù)源中的數(shù)據(jù)導入到DataSet中，然后將DataSet中的數(shù)據(jù)以循環(huán)的方式依次插入數(shù)據(jù)庫中。

下面為.NET相關類庫的介紹。

⑴ 公共類。.Net提供的斷開連接時數(shù)據(jù)的脫機容器相關類有以下。

a. DataSet：這個對象主要用于斷開數(shù)據(jù)庫連接時，是數(shù)據(jù)的脫機容器，它包含一組DataTable，以及這些表之間的關系。

b. DataTable：數(shù)據(jù)的一個容器，DataTable由一個或多個DataColumn組成，每個DataColumn由一個或多個包含數(shù)據(jù)的DataRow組成。

c. DataRow：包含許多數(shù)值，類似于數(shù)據(jù)庫表中的一行或電子表格中的一行。

d. DataColumn：包含列的定義，例如列名稱和數(shù)據(jù)類型。

e. DataRelation：DataSet中兩個DataTable之間的關系，用于外鍵和主從關系。

f. Constraint：為DataColumn（或一組數(shù)據(jù)列）定義規(guī)則，例如惟一值。

DataSet中各個類的關系如圖3所示。

[DataSet] [Tables] [DataTables] [Columns] [Rows] [Constraints] [DataColumn] [DataRow] [Constraint] [Tables] [Tables]

圖3 DataSet中各個類的關系圖

⑵ 操作數(shù)據(jù)庫的類。每個特定的數(shù)據(jù)庫都會有特定的數(shù)據(jù)庫連接的類，這里以Access數(shù)據(jù)庫為例介紹這些類。

a. OleDbConnection：數(shù)據(jù)庫連接對象，用于創(chuàng)建一個程序和access數(shù)據(jù)庫的連接。

b. OleDbCommand： 表示要對數(shù)據(jù)源執(zhí)行的 SQL 語句或存儲過程。

c. OleDbCommandBuilder：用于從一個SELECT語句中生成Sql命令。自動生成用于協(xié)調(diào)對 DataSet 的更改與關聯(lián)數(shù)據(jù)庫的單表命令。

d. OleDbDataAdapter：用于存儲選擇、插入、更新和刪除語句的類，也可以用于生成DataSet和更新數(shù)據(jù)庫。

使用相關類的流程是：通過OleDbConnection類建立一個與數(shù)據(jù)庫的連接，然后建立OleDbCommand對象，傳入要執(zhí)行的sql語句和要使用的連接，然后選擇要執(zhí)行的操作（例如執(zhí)行插入或更新操作）[1]。

1.2 將數(shù)據(jù)庫中的數(shù)據(jù)導出至Excel電子表格

1.2.1 使用數(shù)據(jù)庫管理工具實現(xiàn)

Excle的數(shù)據(jù)導出，有些數(shù)據(jù)庫管理工具也提供了相應的轉(zhuǎn)換工具。如sql server 2000“企業(yè)管理器”工具，提供了數(shù)據(jù)的導出功能，可以將數(shù)據(jù)導出至Excle電子表格。

1.2.2 編碼方式實現(xiàn)

將數(shù)據(jù)導出至Excel電子表格文件，也可類似于將Excel電子表格中的數(shù)據(jù)導入到數(shù)據(jù)庫的方法，將Excel電子表格當作數(shù)據(jù)源對其進行操作。

另一種方法是使用Excel文檔對象，建立一個Excel文檔，然后將數(shù)據(jù)插入文檔中，實現(xiàn)過程如下：

[Access] [DataSet] [Excel文檔對象] [Excel]

由于Excel文檔中的數(shù)據(jù)是高度結構化的，因此該對象模型也具有層次結構并且簡單明了。Excel提供了數(shù)百個可能需要與之交互的對象，完成數(shù)據(jù)導出只需掌握少數(shù)幾個對象模型。這些對象包括四種：

[ ][Application][ ][WorkBook][ ][WorkSheet][ ][Range]

⑴ Application對象

Microsoft.Office.Interop.Excel.Application類表示Excel應用程序本身。該類公開了大量有關正在運行的應用程序、應用于該實例的選項以及在該實例中打開的當前用戶的對象的信息[2]。

⑵ Workbook對象

Microsoft.Office.Interop.Excel.Workbook類表示Excel應用程序內(nèi)的單個工作簿，是Workbooks集合的成員[2]。

⑶ Worksheet對象

Microsoft.Office.Interop.Excel.Worksheet類表示Excel應用程序內(nèi)的單個工作表，是Worksheets集合的成員。Microsoft.Office.Interop.Excel.Worksheet的許多屬性、方法和事件與 Application或Microsoft.Office.Interop.Excel.Workbook類提供的成員完全相同或相似[2]。

⑷ Range對象

Microsoft.Office.Interop.Excel.Range對象是Excel應用程序中最常用的對象。在能夠處理 Excel 內(nèi)的任何范圍之前，必須將它表示為Range對象，并處理該對象的方法和屬性。Range對象表示一個單元格、一行、一列、包含一個或多個單元格塊（可以連續(xù)，也可以不連續(xù)）的單元格選定范圍，甚至可以是多個工作表中的一組單元格[2]。

要建立一個完整的Excel電子表格，就必須通過以上的對象來完成。

1.3 實現(xiàn)方式的比較

要實現(xiàn)Excel電子表格和數(shù)據(jù)庫之間的通訊，可以使用數(shù)據(jù)庫管理系統(tǒng)提供的相關工具進行導入和導出操作，但使用這種方法的缺點是，我們不能根據(jù)我們的需要對導入數(shù)據(jù)庫的數(shù)據(jù)進行更改，同樣的當導出時我們也不能根據(jù)我們的需要添加其他信息。因此，本系統(tǒng)采用編寫代碼的方式實現(xiàn)Excel電子表格和數(shù)據(jù)庫之間的數(shù)據(jù)轉(zhuǎn)換。

1.4 使用多線程技術

線程是程序中獨立的指令流，使用C#編寫任何程序時，都有一個入口：Main（）方法。程序從Main方法的第一條語句開始執(zhí)行，直到這個方法為止，Main（）方法的執(zhí)行是在一個線程中即主線程。在應用程序中，當用戶在一個任務正在執(zhí)行的過程中，希望執(zhí)行其他的任務時，應使用多線程技術實現(xiàn)。如在使用Microsoft Word時，當我們輸入英文單詞時，該軟件也會對單詞進行拼寫檢查，此外還有一個線程將Word文檔自動保存至臨時文件中等等，這些功能都是同時工作的，即使用了多線程技術[3]。

在C#中，多線程的實現(xiàn)主要有兩種方法。

⑴ 通過異步委托實現(xiàn)線程

委托是一種特殊的對象類型，一般對象類型都包含數(shù)據(jù)，但委托只包含方法的地址，可以通過委托調(diào)用方法。委托用于將方法作為參數(shù)傳遞給其他方法。事件處理程序就是通過委托調(diào)用的方法，可以創(chuàng)建一個自定義方法，當發(fā)生特定事件時某個類（如LABLE控件）就可以調(diào)用該方法。以下給出具體操作步驟[3]。

a. 定義了一個委托：

public delegate int DelegateAdd（int x， int y）；

該示例定義了一個委托（DelegateAdd），而且定義該委托的每個實例都包含一個方法的細節(jié)，該方法帶有兩個int類型的參數(shù)（x，y），且返回值是int類型的。

b. 定義一個方法：

Public int add（int x，int y）

{ return x+y；

}

該方法的定義符合委托的要求（兩個int類型的參數(shù)，int類型的返回值）。

c. 將委托實例化：

delegate Add=new DelegateAdd （add）；

即將委托指向方法的地址，然后操作委托調(diào)用方法：

Int z=delegateAdd（1，2）；

⑵ 通過Thread類實現(xiàn)多線程

Thread類是.NET框架中的一個多線程類，使用該類可以創(chuàng)建和控制線程。使用該線程類的步驟主要是：

a. 創(chuàng)建一個方法，并不直接在主線程使用而是通過線程調(diào)用：

Public void threadDemo（）

{ Console.WriteLine（"test"）；

}

b. 創(chuàng)建一個線程，并使該線程指向方法的引用，代碼如下：

Thread t=new Thread（threadDemo）；

該代碼定義了一個線程類（t），并使該示例指向。

c. 啟動線程：

// 開啟線程

t.Start（）；

//線程休眠

Thread.Sleep（0）；[3]

2 具體實現(xiàn)

為了方便操作，把對數(shù)據(jù)庫的操作封裝在一個類中。取名為LinkDataBase.cs，該類主要實現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的增、刪、改、查等操作。

2.1 導入

Public void ExcelToDB（） {

//Excel電子表格連接字符串

string ExcelConnString=string.Format（"Provider=Microsoft.Jet.

OLEDB.4.0；Data Source={0}； Extended Properties

=Excel8.0；"，

openFileDialog1.FileName）；

//Access數(shù)據(jù)庫連接字符串

string AccessConnString=string.Format（"Provider=Microsoft

.Jet.OLEDB.4.0；Data Source={0}；User ID=；Password=；"，

Application.StartupPath+"＼＼Data.mdb"）；

//創(chuàng)建與Excel電子表格的連接

OleDbConnection ExcelConnection=new OleDbConnection

（ExcelConnString）；

//打開一個電子表格的連接

ExcelConnection.Open（）；

//執(zhí)行的SQL語句

string strExcel="select * from [jichu$]"；

//通過打開的連接（ExcelConnection），執(zhí)行指點的SQL語句

（strExcel）

OleDbDataAdapter ExcelCommand=new OleDbDataAdapter

（strExcel， ExcelConnection）；

//創(chuàng)建一個脫機容器，并將查詢數(shù)據(jù)生成（ExcelCommand.Fill（））

到這個脫機容器（ds）中

DataSet ds=new DataSet（）；

ExcelCommand.Fill（ds，"DB_Base"）；

//將查找到的表保存至access數(shù)據(jù)庫

foreach （DataRow dr in ds.Tables[0].Rows）

{ //創(chuàng)建Access數(shù)據(jù)庫的連接并打開一個連接

OleDbConnection AccessConnection=new

OleDbConnection（AccessConnString）；

AccessConnection.Open（）；

//通過可用連接將數(shù)據(jù)通過SQL語句插入到Access數(shù)據(jù)庫中

OleDbCommand AccessCommand=new OleDbCommand

（"insert into DB_Base values（"+dr[0]+"，'"+dr[1]+"'，

'"+dr[2]+"'，'"+dr[3]+"'，'"+dr[4]+"'，'"+dr[5]+"'，'"+dr[6]+"'，

'"+dr[7]+"'，'"+dr[8]+"'，'"+dr[9]+"'）"， AccessConnection）；

AccessCommand.ExecuteNonQuery（）；

//關閉連接

accessConnection.Close（）；

}

}

2.2 導出

Public void DBToExcel（） {

//初始化Excel電子表格文檔對象

m_objRange=null； //工作表中區(qū)間對象

m_objSheet=null； //工作表對象

m_objSheets=null； //工作表集合對象

m_objBooks=null； //工作薄集合對象

m_objBook=null； //工作薄對象

m_objExcel=null； //Excel對象

object[] objHeaders=null；

int nFields=0；//記錄字段總數(shù)

DataSet ds=new DataSet（）；

// Start a new workbook in Excel.

//新建一個Excel應用程序，進而建立一個工作薄

m_objExcel=new Excel.Application（）；

m_objBooks=（Excel.Workbooks）m_objExcel.Workbooks；

m_objBook=（Excel._Workbook）（m_objBooks.Add（m_objOpt））；

//在工作薄中找到第一個工作表

m_objSheets=（Excel.Sheets）m_objBook.Worksheets；

m_objSheet=（Excel._Worksheet）（m_objSheets.get_Item（1））；

//查找數(shù)據(jù)庫中的數(shù)據(jù)

DataTable dt=dbhelp.SelectDataBase（"select * from users"）；

nFields=dt.Columns.Count；//得到記錄數(shù)

objHeaders=new object[nFields]；

int i=0；

//得到數(shù)據(jù)庫的字段名

foreach （DataColumn dc in dt.Columns）

{ objHeaders[i]=dc.ColumnName；

i++；

}

//設置單元格格式

m_objRange=m_objSheet.get_Range（"A1"， m_objOpt）；

m_objRange.EntireColumn.NumberFormatLocal="@"；

//設置單元格每一列標題，以數(shù)據(jù)庫字段命名

m_objRange=m_objRange.get_Resize（1， nFields）；

m_objRange.Value2=objHeaders；

//將數(shù)據(jù)依次插入單元格

for （int j=0； j

{ for （int k=0； k

{ m_objRange.Cells[j+2， k+1]=dt.Rows[j][k]；

}

}

//保存電子表格

m_objBook.SaveAs（m_strSampleFolder+"book.xls"，m_objOpt，

m_objOpt，m_objOpt， m_objOpt， m_objOpt，

Excel.XlSaveAsAccessMode.xlNoChange， m_objOpt，

m_objOpt， m_objOpt， m_objOpt， m_objOpt）；

m_objBook.Close（false， m_objOpt， m_objOpt）；

//退出電子表格應用程序

m_objExcel.Quit（）；

}

2.3 多線程技術的使用

當導入導出的數(shù)據(jù)量比較小時，基本上不需要使用多線程，但當導入導出的數(shù)據(jù)量較大時，不使用多線程技術就會產(chǎn)生“假死”現(xiàn)象，而使用多線程可以很好地解決這一問題。

3 結束語

軍事理論練習系統(tǒng)實現(xiàn)了C#將Exclel電子表格中的數(shù)據(jù)轉(zhuǎn)換到Access數(shù)據(jù)中，同時也實現(xiàn)了將Access數(shù)據(jù)庫的數(shù)據(jù)導出到Excel電子表格。通過相關方法的擴展，我們可以實現(xiàn)Excel電子表格與任何關系型數(shù)據(jù)庫的數(shù)據(jù)相互轉(zhuǎn)換；可將其應用于其他軟件或系統(tǒng)中，以提高效率。

參考文獻：

[1] Christian Nagel等著，李銘翻譯.C#高級編程（第6版）[M].清華大學

出版社，2008.

[2] 王毅飛.基于網(wǎng)絡協(xié)同的IT技能計算機輔助評價系統(tǒng)研究[D].浙江

師范大學碩士學位論文，2011.

[3] 張焰林.基于VB.NET的多線程技術應用[J].計算機系統(tǒng)應用，

2009.2.

[4] 張文博，余文芳.ASP.NET編程中對Excel文檔操作的探討及應用[J].

計算機系統(tǒng)應用，2010.19（3）.

[5] 張麗英.基于.NET的Excel數(shù)據(jù)批量導入SQLServer的設計與實現(xiàn)[J].

南通紡織職業(yè)技術學院學報，2012.1.