基于.Net的Excel和數據庫數據轉換

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

關鍵詞： .Net； Excel； 數據庫； 數據轉換

中圖分類號：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 引言

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

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

1 .Net中數據轉換的原理分析

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

[數據庫] [.NET平臺] [Excel]

圖1

1.1 從Excel表格中導入數據至數據庫

1.1.1 使用數據庫管理工具實現

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

1.1.2 編寫代碼實現

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

[Excel] [DataSet] [Access]

圖2

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

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

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

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

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

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

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

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

f. Constraint：為DataColumn（或一組數據列）定義規則，例如惟一值。

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

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

圖3 DataSet中各個類的關系圖

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

a. OleDbConnection：數據庫連接對象，用于創建一個程序和access數據庫的連接。

b. OleDbCommand： 表示要對數據源執行的 SQL 語句或存儲過程。

c. OleDbCommandBuilder：用于從一個SELECT語句中生成Sql命令。自動生成用于協調對 DataSet 的更改與關聯數據庫的單表命令。

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

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

1.2 將數據庫中的數據導出至Excel電子表格

1.2.1 使用數據庫管理工具實現

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

1.2.2 編碼方式實現

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

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

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

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

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

⑴ Application對象

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

⑵ Workbook對象

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

⑶ Worksheet對象

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

⑷ Range對象

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

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

1.3 實現方式的比較

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

1.4 使用多線程技術

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

在C#中，多線程的實現主要有兩種方法。

⑴ 通過異步委托實現線程

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

a. 定義了一個委托：

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

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

b. 定義一個方法：

Public int add（int x，int y）

{ return x+y；

}

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

c. 將委托實例化：

delegate Add=new DelegateAdd （add）；

即將委托指向方法的地址，然后操作委托調用方法：

Int z=delegateAdd（1，2）；

⑵ 通過Thread類實現多線程

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

a. 創建一個方法，并不直接在主線程使用而是通過線程調用：

Public void threadDemo（）

{ Console.WriteLine（"test"）；

}

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

Thread t=new Thread（threadDemo）；

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

c. 啟動線程：

// 開啟線程

t.Start（）；

//線程休眠

Thread.Sleep（0）；[3]

2 具體實現

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

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數據庫連接字符串

string AccessConnString=string.Format（"Provider=Microsoft

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

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

//創建與Excel電子表格的連接

OleDbConnection ExcelConnection=new OleDbConnection

（ExcelConnString）；

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

ExcelConnection.Open（）；

//執行的SQL語句

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

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

（strExcel）

OleDbDataAdapter ExcelCommand=new OleDbDataAdapter

（strExcel， ExcelConnection）；

//創建一個脫機容器，并將查詢數據生成（ExcelCommand.Fill（））

到這個脫機容器（ds）中

DataSet ds=new DataSet（）；

ExcelCommand.Fill（ds，"DB_Base"）；

//將查找到的表保存至access數據庫

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

{ //創建Access數據庫的連接并打開一個連接

OleDbConnection AccessConnection=new

OleDbConnection（AccessConnString）；

AccessConnection.Open（）；

//通過可用連接將數據通過SQL語句插入到Access數據庫中

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； //工作表中區間對象

m_objSheet=null； //工作表對象

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

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

m_objBook=null； //工作薄對象

m_objExcel=null； //Excel對象

object[] objHeaders=null；

int nFields=0；//記錄字段總數

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））；

//查找數據庫中的數據

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

nFields=dt.Columns.Count；//得到記錄數

objHeaders=new object[nFields]；

int i=0；

//得到數據庫的字段名

foreach （DataColumn dc in dt.Columns）

{ objHeaders[i]=dc.ColumnName；

i++；

}

//設置單元格格式

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

m_objRange.EntireColumn.NumberFormatLocal="@"；

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

m_objRange=m_objRange.get_Resize（1， nFields）；

m_objRange.Value2=objHeaders；

//將數據依次插入單元格

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 多線程技術的使用

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

3 結束語

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

參考文獻：

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

出版社，2008.

[2] 王毅飛.基于網絡協同的IT技能計算機輔助評價系統研究[D].浙江

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

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

2009.2.

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

計算機系統應用，2010.19（3）.

[5] 張麗英.基于.NET的Excel數據批量導入SQLServer的設計與實現[J].

南通紡織職業技術學院學報，2012.1.