Christa Carpentiere
適用范圍：
Microsoft® Access
Microsoft® Excel
Microsoft® Office
Microsoft® Visual Basic® .NET
Microsoft® Visual Studio® .NET
摘要：查看如何從 Office 文件中獲取 Microsoft Access 和 Excel 數(shù)據(jù)并將其放入 .NET 應(yīng)用程序中，并查看 OLE DB 提供程序怎樣用于編寫(xiě)使處理 .xls 或 .mdb 文件更容易的通用代碼。
引言
在 Microsoft .NET 應(yīng)用程序中使用 Microsoft Office 數(shù)據(jù)時(shí)所需的大多數(shù)任務(wù)與使用任何其他數(shù)據(jù)時(shí)的任務(wù)相同 - 創(chuàng)建連接、根據(jù)使用數(shù)據(jù)時(shí)的需要?jiǎng)?chuàng)建 DataReader 或 DataAdapter、創(chuàng)建一個(gè)或多個(gè) DataSet 來(lái)封裝相關(guān)數(shù)據(jù)的 DataTable，等等。因此，我不打算在此重新編寫(xiě) ADO.NET 文檔。本文的目的在于，提供按 Office 數(shù)據(jù)源編寫(xiě)基礎(chǔ)數(shù)據(jù)檢索代碼時(shí)所需的缺失或廣為分布的信息段。好像那些知道 Jet 的奇特之處的人士卻不是很熟悉 ADO.NET，而大部分的 ADO.NET 專(zhuān)業(yè)人士不是很了解支持 Office 可編程序性的技術(shù)。因此提供一個(gè)快速的概述，希望可以解答我所見(jiàn)到的這兩方的常見(jiàn)問(wèn)題。
首先讓我們討論一下連接到 Microsoft Excel 和 Microsoft Access 的數(shù)據(jù)并從中檢索數(shù)據(jù)的基礎(chǔ)。我認(rèn)為此領(lǐng)域中存在最大的問(wèn)題 - 通常一旦人們能夠解決該問(wèn)題，Office 特定問(wèn)題將會(huì)得到解決，而且其余問(wèn)題主要集中于如何使用 ADO.NET。下面我將討論如何使用 OleDbConnection 對(duì)象的 GetOleDbSchemaTable 方法來(lái)檢索關(guān)于運(yùn)行時(shí) Access 或 Excel 數(shù)據(jù)的架構(gòu)信息。這樣，您可以避開(kāi)關(guān)于數(shù)據(jù)源的硬編碼信息；數(shù)據(jù)源非常有用，從中可以獲取數(shù)據(jù)。
建立連接
如果要連接到 Excel 電子表格或 Access 數(shù)據(jù)庫(kù)，則需要?jiǎng)?chuàng)建一個(gè) OleDbConnection 對(duì)象，并向其傳遞一個(gè)帶有目標(biāo)數(shù)據(jù)源詳細(xì)信息的連接字符串。您需要為 Provider 屬性指定 Jet 4.0 OLE DB 提供程序。
連接到 Access
讓我們看一看用于 Access 數(shù)據(jù)庫(kù)的典型連接字符串：
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Databases\mymusic.mdb"
這非常簡(jiǎn)單 - 它僅指定 Provider 和 Data Source 屬性。如上所述，Provider 是 Jet 4.0 提供程序，而 Data Source 屬性包含數(shù)據(jù)庫(kù)的完全限定路徑。
當(dāng)然，您甚至還需要確保 Access 數(shù)據(jù)庫(kù)的安全，對(duì)不對(duì)？這樣，您還需要指定工作組信息文件（默認(rèn)情況下為 system.mdw）的路徑，并提供用戶(hù) ID 和密碼：
"Provider=Microsoft.Jet.OLEDB.4.0;Data " & _ "Source=C:\Databases\mymusic.mdb; " & _ "Jet OLEDB:System database=" & _ "C:\Databases\system.mdw; " & _ "User ID=UserX;Password=UserXPassword"
請(qǐng)注意，指定控制 Access 數(shù)據(jù)庫(kù)安全性的 .mdw 文件時(shí)需要使用 Jet OLE DB 特定的屬性。如果希望更好地控制數(shù)據(jù)庫(kù)的行為，則可以瀏覽 Jet OLE DB 提供程序（該提供程序位于 Appendix B:Microsoft Jet 4.0 OLE DB Properties Reference）所提供的其他屬性，來(lái)控制鎖定行為和如何處理特定類(lèi)型的失敗，等等。請(qǐng)注意，只有那些可以在連接字符串中設(shè)置的屬性才可以訪問(wèn)；指定提供程序的屬性之前需要連接處于打開(kāi)狀態(tài)，否則將無(wú)法設(shè)置這些屬性。
可以使用 Microsoft® Visual Studio® .NET，來(lái)獲取包括所有的 Jet OLE DB 提供程序設(shè)置、完整的 Access 連接字符串的模板。在服務(wù)器資源管理器中創(chuàng)建一個(gè)與 Access 數(shù)據(jù)庫(kù)的數(shù)據(jù)連接，然后使用工具箱的數(shù)據(jù)部分創(chuàng)建一個(gè) OleDbConnection 對(duì)象。得到的對(duì)象的 ConnectionString 屬性會(huì)包含默認(rèn)的所有 Jet OLE DB 提供程序?qū)傩浴?/P>
連接到 Excel
現(xiàn)在，讓我們看看 Excel 電子表格的典型連接字符串的外觀：
"Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Spreadsheets\calculations.xls;" & _ "Extended Properties=Excel 8.0"
在連接到 Excel 文件的情況下，可以看到我們必須在連接字符串中使用 Extended Properties 屬性以及 Provider 和 Data Source。如果是 Excel 8.0 和更高版本，則使用“Excel 8.0”設(shè)置。如果希望獲取此屬性的其他可接受值的更多信息，請(qǐng)參閱 ADO Provider Properties and Settings 的“Extended Properties Property Settings”部分。
您會(huì)說(shuō)：“啊，但是 Excel 中的安全性怎么樣？”嗯，恐怕沒(méi)有激動(dòng)人心的消息。您無(wú)法打開(kāi)與受密碼保護(hù)的電子表格的連接，除非已手動(dòng)地在 Excel 中打開(kāi)此電子表格（有關(guān)詳細(xì)信息，請(qǐng)參閱 XL2000:"Could Not Decrypt File" Error with Password Protected File）。描述的錯(cuò)誤出現(xiàn)于 Excel ODBC 提供程序，但是在 Jet 4.0 OLE DB 提供程序中該行為相同。其他選項(xiàng)為，刪除電子表格的密碼并使用某些其他安全機(jī)制（例如，限制文件所在文件夾的權(quán)限）來(lái)控制訪問(wèn)。
不幸的是，您也無(wú)法使用 Visual Studio .NET 獲取 Excel 連接字符串的模板。進(jìn)行一些小小的嘗試，即可創(chuàng)建 Excel 數(shù)據(jù)連接；但是您會(huì)發(fā)現(xiàn)其屬性不可編輯，而且 ConnectionString 屬性將保留空白 - 這是 IDE（集成的開(kāi)發(fā)環(huán)境）中的奇特之處。有關(guān)詳細(xì)信息，請(qǐng)參閱 PRB:Cannot Configure Data Connection to Excel Files in Visual Studio .NET。這樣，對(duì)于 Excel 連接，基本上都需要自己進(jìn)行工作，但在這種情況下對(duì)連接進(jìn)行編碼就像在用戶(hù)界面 (UI) 中創(chuàng)建連接一樣簡(jiǎn)單。
檢索數(shù)據(jù)
現(xiàn)在已經(jīng)明白了如何建立與 Office 數(shù)據(jù)源的連接，就讓我們看看它是怎樣檢索數(shù)據(jù)的。出于簡(jiǎn)單性的考慮，我打算使用 OleDbConnection/OleDbCommand/OleDbDataReader 數(shù)據(jù)檢索方案。相同的方法在稍稍調(diào)整之后，可以用于構(gòu)建 OleDbDataAdapter 并填充 DataSet。如果想要大體上了解一下 ADO.NET，請(qǐng)參閱《.NET Framework Developer's Guide》的 Accessing Data with ADO.NET 部分，它會(huì)為您帶來(lái)閱讀的樂(lè)趣。
檢索 Access 數(shù)據(jù)
編寫(xiě) Access 數(shù)據(jù)檢索代碼時(shí)請(qǐng)切記，指定 SQL 時(shí)必須遵循的語(yǔ)法具有某些特性。無(wú)法在圖形 UI 中創(chuàng)建 Access 查詢(xún)，無(wú)法訪問(wèn) SQL 視圖，無(wú)法復(fù)制得到的 SQL 語(yǔ)句并將其粘貼到代碼中。不，這本應(yīng)非常簡(jiǎn)單的。生成的 SQL 代碼通常具有一部分但不是全部的所需語(yǔ)法。必須在 Access 開(kāi)發(fā)環(huán)境中編寫(xiě)代碼的任何人都會(huì)知道這一點(diǎn)，但是對(duì)于一般的 .NET 客戶(hù)端應(yīng)用程序開(kāi)發(fā)人員而言，這算的上是新聞。最讓人煩心的是條件表達(dá)式，它需要以某種方式分隔 WHERE 字句中的特定類(lèi)型的數(shù)據(jù)。日期和時(shí)間值必須使用數(shù)字記號(hào) (#) 進(jìn)行分隔。文本值必須使用單引號(hào) (') 進(jìn)行分隔。例如：
SELECT City, Neighborhood, SalePrice, MonthsOnMarket FROM RealEstate WHERE ListingDate > #1/1/04#
有關(guān)這些問(wèn)題的詳細(xì)信息，請(qǐng)參閱 Date and Time Criteria Expressions。
需要記住的另一點(diǎn)看上去好像顯而易見(jiàn)，但是卻使人們感到困惑，所以我將討論這一點(diǎn)：請(qǐng)確保 Access 表的列名稱(chēng)不使用保留字。可以在 SQL Reserved Words 處查詢(xún)保留字。如果使用任何保留字，我想說(shuō)的是如果可能就重命名列。我知道如果您已使用數(shù)據(jù)庫(kù)，該操作則不太可能，所以必要時(shí)可以創(chuàng)建查詢(xún)并將其用作有問(wèn)題列的表的替換查詢(xún)。僅使用 AS 重命名列，就如：
SELECT Artists.ArtistName, Genres.Genre, Labels.Label, Tracks.Public AS Track, Releases.ReleaseName FROM (Labels INNER JOIN ((Artists...
記住這些要點(diǎn)，讓我們來(lái)看一個(gè)示例：
Imports System
Imports System.Data
Imports System.Data.OleDb
...
Public Function GetAccessData(ByVal UID As String, _
ByVal pwd As String, ByVal artist As String)
Dim conn As New OleDbConnection
Dim musicReader As OleDbDataReader
Dim cmd As New OleDbCommand
Dim connString As String
Dim i As Integer
Try
' 設(shè)置連接字符串。
connString = "Jet OLEDB:System database=" & _
"C:\Databases\system.mdw;" & _
"Data Source=C:\Databases\mymusic.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=" & UID & ";Password=" & pwd
' 打開(kāi)連接。
conn.ConnectionString = connString
conn.Open()
'設(shè)置命令屬性。
cmd.Connection = conn
cmd.CommandText = "SELECT * from music " & _
"WHERE ArtistName = '" & artist & "'"
' 獲取 OleDbDataReader
' 并對(duì)其進(jìn)行一些處理。
musicReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Try
While (musicReader.Read)
'處理數(shù)據(jù)。
End While
Finally
musicReader.Close()
End Try
Catch ex As Exception
'錯(cuò)誤處理
End Try
End Function 'GetAccessData
檢索 Excel 數(shù)據(jù)
關(guān)于列名稱(chēng)，Excel 和 Access 一樣具有相同的保留字限制。一般而言，如果記住 SQL 保留字，并在創(chuàng)建可能用作數(shù)據(jù)源的任何對(duì)象時(shí)避開(kāi)保留字，則會(huì)更好。還有一點(diǎn)需要考慮。
Excel 的語(yǔ)法同樣也有奇特之處。對(duì)代碼影響最大的項(xiàng)是用于引用要返回的數(shù)據(jù)集的語(yǔ)法。
注意：對(duì)于最簡(jiǎn)單的 Excel 數(shù)據(jù)檢索，請(qǐng)使用維護(hù)類(lèi)似表格式的電子表格。
第一個(gè)選項(xiàng)是指定工作表和該表中的單元格集（可選）。需要確保工作表名稱(chēng)后跟美元符號(hào)和單元格集（可選）。通過(guò)使用冒號(hào)分隔集合中的起始單元格和終止單元格，來(lái)指定此單元格集。然后，使用括號(hào)將整個(gè)數(shù)據(jù)標(biāo)識(shí)字符串括起。使用此類(lèi)型語(yǔ)法的 SELECT 語(yǔ)句可能如下所示：
SELECT SalesMonth, TotalSales, PercentageChange1Year FROM [Sheet1$A1:E24]
另一個(gè)選項(xiàng)是在 Excel 中創(chuàng)建一個(gè)命名范圍，它將起到類(lèi)似于表的作用。要?jiǎng)?chuàng)建命名范圍，請(qǐng)參閱 Create named cell references or ranges。要使用的范圍名稱(chēng)就像 SELECT 語(yǔ)句中的表名稱(chēng)：
SELECT SalesMonth, TotalSales, PercentageChange1Year FROM SalesHighlights
記住這些要點(diǎn)，讓我們來(lái)看一個(gè)示例：
Imports System
Imports System.Data
Imports System.Data.OleDb
...
Public Function GetExcelData()
Dim conn As New OleDbConnection
Dim salesReader As OleDbDataReader
Dim connString As String
Dim cmd As New OleDbCommand
Try
' 設(shè)置連接字符串。
connString = "Data Source=" & _
"C:\Spreadsheets\calculations.xls;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;"
' 打開(kāi)連接。
conn.ConnectionString = connString
conn.Open()
'設(shè)置命令屬性。
cmd.Connection = conn
cmd.CommandText = "SELECT SalesMonth, " & _
"TotalSales, PercentageChange1Year, " & _
"VolumeDiscounts, Profit from [Sheet1$]"
' 獲取 OleDbDataReader
' 并對(duì)其進(jìn)行一些處理。
salesReader = _
cmd.ExecuteReader(CommandBehavior.CloseConnection)
Try
While (salesReader.Read)
'處理數(shù)據(jù)。
End While
Finally
salesReader.Close()
End Try
Catch ex As Exception
'錯(cuò)誤處理
End Try
End Function 'GetExcelData
對(duì)通用數(shù)據(jù)檢索使用元數(shù)據(jù)
現(xiàn)在可以連接到 Excel 或 Access 數(shù)據(jù)源并檢索數(shù)據(jù)，讓我們?cè)偕钊胍徊健Ｗ屛覀兛纯磸钠渲幸粋�(gè)數(shù)據(jù)源檢索元數(shù)據(jù)時(shí)需要執(zhí)行的操作，隨后您可以使用數(shù)據(jù)源構(gòu)造數(shù)據(jù)訪問(wèn)代碼。如果希望創(chuàng)建的過(guò)程提供某些通用功能，并且不希望將這些功能綁定在特定數(shù)據(jù)源上，則該進(jìn)程可能會(huì)有用處。
使用 OleDbConnection.GetOleDbSchemaTable
要獲取所需的元數(shù)據(jù)，則需要使用 OleDbConnection.GetOleDbSchemaTable 方法。此方法的構(gòu)造函數(shù)采用一個(gè)代表 OLE DB 架構(gòu)行集的 OleDbSchemaGuid 對(duì)象，和一組代表要返回的架構(gòu)信息的選擇條件基本內(nèi)容的對(duì)象。
注意：對(duì)于那些不熟悉 OLE DB 架構(gòu)行集的人而言，它們基本上是由 ANSI SQL-92 定義的數(shù)據(jù)庫(kù)構(gòu)造的標(biāo)準(zhǔn)化架構(gòu)。每個(gè)架構(gòu)行集具有為指定構(gòu)造提供定義元數(shù)據(jù)的一組列（稱(chēng)作 .NET 文檔中的“限制列”）。這樣，如果請(qǐng)求架構(gòu)信息（例如，列的架構(gòu)信息或排序規(guī)則的架構(gòu)信息），則您會(huì)明確知道可以得到哪種類(lèi)型的數(shù)據(jù)。如果希望了解更多信息，請(qǐng)?jiān)L問(wèn) Appendix B:Schema Rowsets。
Object 數(shù)組在文檔中定義為“限制值的數(shù)組”。它用于確定（即限制）返回的數(shù)據(jù)集，有些類(lèi)似于 SQL 中的 WHERE 子句。例如，連接到具有工作表 Alpha、Beta 和 Pi 的工作簿。需要架構(gòu)信息來(lái)確定工作表 Beta 所包含的列。您的代碼將如下所示：
schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, _
"Beta", Nothing})
使用 OleDbSchemaGuid.Columns 字段，來(lái)表示因需要返回列信息而應(yīng)使用 COLUMNS 架構(gòu)。COLUMNS 架構(gòu)包含 TABLE_CATALOG、TABLE_SCHEMA、TABLE_NAME 和 COLUMN_NAME 限制列，并且需要提供代表數(shù)組中每個(gè)限制列的限制值的對(duì)象。通過(guò)將“Beta”指定為 TABLE_NAME 值，可以將返回的列信息限制為僅來(lái)自于“表”的信息。
現(xiàn)在，您非常熟悉我們的朋友 GetOleDbSchemaTable，讓我們來(lái)看看它是如何實(shí)現(xiàn)的。通過(guò)使用它遍歷數(shù)據(jù)源中的表和列，可以獲取檢索數(shù)據(jù)時(shí)所需的所有信息，而無(wú)須提前熟悉架構(gòu)。讓我們來(lái)看看使用 Excel 的一個(gè)示例：
Imports System
Imports System.Data
Imports System.Data.OleDb
...
Public Function GetExcelSchema(ByVal xlsPath As String) As DataSet
Dim schemaTable As New DataTable
Dim workAdapter As New OleDbDataAdapter
Dim workSet As New DataSet
Dim conn As New OleDbConnection
Dim i As Integer
Dim x As Integer
Dim charArray As Char() = {",", " "}
Dim charArray2 As Char() = {"$"}
Dim cmdString As String
Dim cmdString2 As String
Dim cmd As New OleDbCommand
Dim tableName As String
workSet.DataSetName = "excelData"
Try
' 設(shè)置連接字符串。
Dim connString As String = _
"Data Source=" & xlsPath & _
";Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0"
' 打開(kāi)連接。
conn.ConnectionString = connString
conn.Open()
' 使用數(shù)據(jù)源表中的架構(gòu)信息
' 填充 DataTable。
schemaTable = _
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
' 使用表名稱(chēng)填充數(shù)組。
i = schemaTable.Rows.Count - 1
Dim tablesArray(i) As String
For i = 0 To schemaTable.Rows.Count - 1
tablesArray(i) = schemaTable.Rows(i).Item("Table_Name")
Next
' 清除 DataTable
schemaTable.Clear()
' 使用表名稱(chēng)和列架構(gòu)
' 信息來(lái)構(gòu)造 SELECT 語(yǔ)句，
' 并為數(shù)據(jù)源中的每個(gè)表返回?cái)?shù)據(jù)。
For i = 0 To tablesArray.GetLength(0) - 1
' 使用數(shù)據(jù)源列中的架構(gòu)信息
' 填充 DataTable。
schemaTable = _
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, _
tablesArray(i).ToString(), Nothing})
' 逐個(gè)查看列名稱(chēng)，并將其附加到
' SELECT 語(yǔ)句中
cmdString = "SELECT "
For x = 0 To schemaTable.Rows.Count - 1
cmdString = cmdString & _
schemaTable.Rows(x).Item("Column_Name") & _
", "
Next
cmdString2 = cmdString.TrimEnd(charArray)
' 請(qǐng)注意，無(wú)須將“$”附加到
' 表名稱(chēng)中 - 它已包括在內(nèi)。
cmdString2 = cmdString2 & " FROM [" & _
tablesArray(i).ToString() & "]"
' 使用 SELECT 命令和
' OleDbDataAdapter 填充 DataSet。
cmd.CommandText = cmdString2
workAdapter.SelectCommand = cmd
workAdapter.SelectCommand.Connection = conn
tableName = _
tablesArray(i).ToString().TrimEnd(charArray2)
workAdapter.Fill(workSet, tableName)
schemaTable.Clear()
Next
Catch ex As Exception
'錯(cuò)誤處理
Finally
conn.Close()
End Try
Return workSet
End Function 'GetExcelSchema
正如您所看到的，涉及的代碼相當(dāng)簡(jiǎn)單。如果想對(duì) Access 數(shù)據(jù)庫(kù)進(jìn)行同樣的操作，唯一真正的不同之處在于連接字符串，而無(wú)須將表名稱(chēng)格式化為工作表并使其可在 SELECT 語(yǔ)句中使用。
另外，可以將此方法用于 SQL Server™ 數(shù)據(jù)庫(kù)，效果也很好；或者可以對(duì)其進(jìn)行修改，使其根據(jù)從數(shù)據(jù)源中獲取的架構(gòu)信息執(zhí)行其他操作。如果要嘗試執(zhí)行任何類(lèi)型的查找或文檔管理，這將非常有用。
結(jié)論
非常感謝與我一起完成了這個(gè)將 Office 數(shù)據(jù)和 ADO.NET 配合使用的簡(jiǎn)短介紹。它并不是什么特殊的任務(wù)，而且我認(rèn)為此任務(wù)本不應(yīng)有這么高的難度，只是因?yàn)榇巳蝿?wù)的某些基本信息不容易獲取。無(wú)論您是只希望從部門(mén)的 Access 數(shù)據(jù)庫(kù)中拖入信息，還是希望從每個(gè)人的每月費(fèi)用電子表格中收集數(shù)據(jù)，我都希望可以為您提供一個(gè)開(kāi)始的平臺(tái)。
參考資料
© 2004 Microsoft Corporation 版權(quán)所有。保留所有權(quán)利。使用規(guī)定。
