从 .NET 应用程序访问 Microsoft Office 数据

核心提示：引言在 Microsoft .NET 应用程序中使用 Microsoft Office 数据时所需的大多数任务与使用任何其他数据时的任务相同 - 创建连接、根据使用数据时的需要创建 DataReader 或 DataAdapter、创建一个或多个 DataSet 来封装相关数据的 DataTable，等等，从 .NET

　　引言
　　在 Microsoft .NET 应用程序中使用 Microsoft Office 数据时所需的大多数任务与使用任何其他数据时的任务相同 - 创建连接、根据使用数据时的需要创建 DataReader 或 DataAdapter、创建一个或多个 DataSet 来封装相关数据的 DataTable，等等。因此，我不打算在此重新编写 ADO.NET 文档。本文的目的在于，提供按 Office 数据源编写基础数据检索代码时所需的缺失或广为分布的信息段。似乎那些知道 Jet 的奇异之处的人士却不是很熟悉 ADO.NET，而大部分的 ADO.NET 专业人士不是很了解支持 Office 可编程序性的技术。因此提供一个快速的概述，希望可以解答我所见到的这两方的常见问题。
　　
　　首先让我们讨论一下连接到 Microsoft Excel 和 Microsoft access 的数据并从中检索数据的基础。我认为此领域中存在最大的问题 - 通常一旦人们能够解决该问题，Office 特定问题将会得到解决，而且其余问题主要集中于如何使用 ADO.NET。下面我将讨论如何使用 OleDbConnection 对象的 GetOleDbSchemaTable 方法来检索关于运行时 Access 或 Excel 数据的架构信息。这样，您可以避开关于数据源的硬编码信息；数据源非常有用，从中可以获取数据。
　　
　　建立连接
　　假如要连接到 Excel 电子表格或 Access 数据库，则需要创建一个 OleDbConnection 对象，并向其传递一个带有目标数据源具体信息的连接字符串。您需要为 PRovider 属性指定 Jet 4.0 OLE DB 提供程序。
　　
　　连接到 Access
　　让我们看一看用于 Access 数据库的典型连接字符串：
　　
　　"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Databases\mymusic.mdb"
　　
　　这非常简单 - 它仅指定 Provider 和 Data Source 属性。如上所述，Provider 是 Jet 4.0 提供程序，而 Data Source 属性包含数据库的完全限定路径。
　　
　　当然，您甚至还需要确保 Access 数据库的安全，对不对？这样，您还需要指定工作组信息文件（默认情况下为 system.mdw）的路径，并提供用户 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"
　　
　　请注重，指定控制 Access 数据库安全性的 .mdw 文件时需要使用 Jet OLE DB 特定的属性。假如希望更好地控制数据库的行为，则可以浏览 Jet OLE DB 提供程序（该提供程序位于 Appendix B:Microsoft Jet 4.0 OLE DB Properties Reference）所提供的其他属性，来控制锁定行为和如何处理特定类型的失败，等等。请注重，只有那些可以在连接字符串中设置的属性才可以访问；指定提供程序的属性之前需要连接处于打开状态，否则将无法设置这些属性。
　　
　　可以使用 Microsoft® Visual Studio® .NET，来获取包括所有的 Jet OLE DB 提供程序设置、完整的 Access 连接字符串的模板。在服务器资源治理器中创建一个与 Access 数据库的数据连接，然后使用工具箱的数据部分创建一个 OleDbConnection 对象。得到的对象的 ConnectionString 属性会包含默认的所有 Jet OLE DB 提供程序属性。
　　
　　连接到 Excel
　　现在，让我们看看 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”设置。假如希望获取此属性的其他可接受值的更多信息，请参阅 ADO Provider Properties and Settings 的“Extended Properties Property Settings”部分。
　　
　　您会说：“啊，但是 Excel 中的安全性怎么样？”嗯，恐怕没有激动人心的消息。您无法打开与受密码保护的电子表格的连接，除非已手动地在 Excel 中打开此电子表格（有关具体信息，请参阅 XL2000:"Could Not Decrypt File" Error with Password Protected File）。描述的错误出现于 Excel ODBC 提供程序，但是在 Jet 4.0 OLE DB 提供程序中该行为相同。其他选项为，删除电子表格的密码并使用某些其他安全机制（例如，限制文件所在文件夹的权限）来控制访问。
　　
　　不幸的是，您也无法使用 Visual Studio .NET 获取 Excel 连接字符串的模板。进行一些小小的尝试，即可创建 Excel 数据连接；但是您会发现其属性不可编辑，而且 ConnectionString 属性将保留空白 - 这是 IDE（集成的开发环境）中的奇异之处。有关具体信息，请参阅 PRB:Cannot Configure Data Connection to Excel Files in Visual Studio .NET。这样，对于 Excel 连接，基本上都需要自己进行工作，但在这种情况下对连接进行编码就像在用户界面 (UI) 中创建连接一样简单。
　　
　　检索数据
　　现在已经明白了如何建立与 Office 数据源的连接，就让我们看看它是怎样检索数据的。出于简单性的考虑，我打算使用 OleDbConnection/OleDbCommand/OleDbDataReader 数据检索方案。相同的方法在稍稍调整之后，可以用于构建 OleDbDataAdapter 并填充 DataSet。假如想要大体上了解一下 ADO.NET，请参阅《.NET Framework Developer's Guide》的 Accessing Data with ADO.NET 部分，它会为您带来阅读的乐趣。
　　
　　检索 Access 数据
　　编写 Access 数据检索代码时请切记，指定 SQL 时必须遵循的语法具有某些特性。无法在图形 UI 中创建 Access 查询，无法访问 SQL 视图，无法复制得到的 SQL 语句并将其粘贴到代码中。不，这本应非常简单的。生成的 SQL 代码通常具有一部分但不是全部的所需语法。必须在 Access 开发环境中编写代码的任何人都会知道这一点，但是对于一般的 .NET 客户端应用程序开发人员而言，这算的上是新闻。最让人烦心的是条件表达式，它需要以某种方式分隔 WHERE 字句中的特定类型的数据。日期和时间值必须使用数字记号 (#) 进行分隔。文本值必须使用单引号 (') 进行分隔。例如：
　　
　　SELECT City, Neighborhood, SalePrice, MonthsOnMarket
　　FROM RealEstate
　　WHERE ListingDate > #1/1/04#
　　
　　有关这些问题的具体信息，请参阅 Date and Time Criteria Expressions。
　　
　　需要记住的另一点看上去似乎显而易见，但是却使人们感到困惑，所以我将讨论这一点：请确保 Access 表的列名称不使用保留字。可以在 SQL Reserved Words 处查询保留字。假如使用任何保留字，我想说的是假如可能就重命名列。我知道假如您已使用数据库，该操作则不太可能，所以必要时可以创建查询并将其用作有问题列的表的替换查询。仅使用 AS 重命名列，就如：
　　
　　SELECT Artists.ArtistName, Genres.Genre, Labels.Label,
　　Tracks.Public AS Track, Releases.ReleaseName
　　FROM (Labels INNER JOIN ((Artists...
　　
　　记住这些要点，让我们来看一个示例：
　　
　　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
　　' 设置连接字符串。
　　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
　　
　　' 打开连接。
　　conn.ConnectionString = connString
　　conn.Open()
　　
　　'设置命令属性。
　　cmd.Connection = conn
　　cmd.CommandText = "SELECT * from music " & _
　　"WHERE ArtistName = '" & artist & "'"
　　
　　' 获取 OleDbDataReader
　　' 并对其进行一些处理。
　　musicReader = _
　　cmd.ExecuteReader(CommandBehavior.CloseConnection)
　　Try
　　While (musicReader.Read)
　　'处理数据。
　　End While
　　Finally
　　musicReader.Close()
　　End Try
　　
　　Catch ex As Exception
　　'错误处理
　　End Try
　　
　　End Function 'GetAccessData
　　
　　检索 Excel 数据
　　关于列名称，Excel 和 Access 一样具有相同的保留字限制。一般而言，假如记住 SQL 保留字，并在创建可能用作数据源的任何对象时避开保留字，则会更好。还有一点需要考虑。
　　
　　Excel 的语法同样也有奇异之处。对代码影响最大的项是用于引用要返回的数据集的语法。
　　
　　注重：对于最简单的 Excel 数据检索，请使用维护类似表格式的电子表格。
　　
　　第一个选项是指定工作表和该表中的单元格集（可选）。需要确保工作表名称后跟美元符号和单元格集（可选）。通过使用冒号分隔集合中的起始单元格和终止单元格，来指定此单元格集。然后，使用括号将整个数据标识字符串括起。使用此类型语法的 SELECT 语句可能如下所示：
　　
　　SELECT SalesMonth, TotalSales, PercentageChange1Year
　　FROM [Sheet1$A1:E24]
　　
　　另一个选项是在 Excel 中创