使用 SQL 从 Microsoft Excel 提取数据

引言

在本文中，我将向您展示如何将 Excel 工作簿用作数据源，并根据您的 SQL 查询获取数据。在第一步中，我将向您展示如何使用 SQL 语法编写查询，然后，我将向您展示如何在 DataGrid 中获取和绑定数据。

背景

当我开始使用传统的 VBA 代码读取 Excel 工作簿时，我发现读取单个单元格的值都很复杂。即使编写了成功的代码，您也可能会陷入 COM 内存泄漏问题。如果您的 Excel 文件包含大量数据需要读取，并且有很多工作表，那么您可能会看到一个弹出窗口显示“您正在耗尽虚拟内存”。即使在这个过程中删除了几个对象，您也无法确定您的对象会立即被释放。最后，我发现 SQL 可以降低代码的复杂性，我可以获得更高的性能，并且没有内存泄漏问题。我假设阅读本文的各位都具备 ADO.NET 和 Microsoft Excel 的基本知识。

Using the Code

使用 ADO.NET 从任何数据存储获取数据非常简单。在编写实际代码之前，让我们创建一个 Windows 应用程序（尽管您也可以使用 Web 应用程序），并在目标窗体的顶部添加以下行。我将使用 OLEDB API 来访问 Excel 数据。OLEDB 将借助 JET 引擎来执行查询并从 Excel 获取数据。

using System.Data.OleDb; 

请注意，我使用的是 Office 2003 和 Visual Studio 2003。

1. 连接字符串

首先，我们将建立到数据源的连接。这与连接到 SQL Server 或 Oracle 非常相似。

OleDbConnection con = new OleDbConnection(
    "provider=Microsoft.Jet.OLEDB.4.0;data source=" + 
    "File Name with Complete Path" +";Extended Properties=Excel 8.0;"); 

2. 编写查询

编写 Excel 查询与在任何其他传统数据存储（如 SQL Server、Oracle 等）中编写查询非常相似。但是，存在一些差异。首先，您需要指定工作表名称而不是表名称。接下来，您需要提供开始和结束单元格的引用。请仔细观看以下代码

 SELECT * FROM [42560035$A1:F500]

• 在上面的查询中，42560035 是我的工作表名称（请将其视为您的表名称）。在您的情况下，此名称可能不同。请不要忘记将您的工作表名称写在 [] 方括号内。
• 在工作表名称旁边是我的开始单元格和结束单元格的引用。您的 Excel 文件可能包含不同的单元格引用。请确保在工作表名称之后，您使用美元符号 ($)，然后用冒号 (:) 分隔开始单元格引用和结束单元格引用。如果您在查询中未提及开始和结束单元格引用，将会出错。

这是一个非常简单的查询。这是一个更复杂的查询

SELECT * FROM [42560030$A21:F500] 
    WHERE [Period_End Date] = #3/2/2007# 
    ORDER BY [Date_Incurred] 

在 *（返回所有列）的位置，您可以指定您正在查找的确切列名。这是一个例子

SELECT [Associate Name] as Associate,[Amount] as Amount 
    FROM [42560030$A21:F500] 
    WHERE [Period_End Date] = #3/2/2007# ORDER BY [Date_Incurred] 

请确保如果您的列名包含空格，请将其放在 [] 方括号内。否则，您的 JET 引擎将抛出异常。为了保持一致性，请将所有列名都放在 [] 方括号内。另一个有趣的点是：如果您仔细观察 [Period_End Date] 列名，在 Period 和 End 之间有一个下划线 (_)。这是因为在我的 Excel 表格中，我将 Period 写在一行，将 End Date 写在下一行。请注意，当我写下一行时，意味着“下一行”而不是“下一个单元格”。下一步是构建我们的数据适配器。

3. DataAdapter

StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT * FROM [42560035$A1:F500]");
OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.Tostring(),con);

4. 填充 DataSet

DataSet dsXLS = new DataSet() adp.Fill(dsXLS);

5. 绑定 Grid

在填充数据后，现在是时候查看数据了。为此，我们可以使用 DataGrid，或者您可以使用 WriteXml 方法将数据集写入 XML 文件。在此示例中，我将使用 DataGrid 显示我的数据。

DataView dvEmp = new DataView(ds.Tables[0]); 
dataGrid1.DataSource = dvEml; 

就这些了。

结束语

在我的下一篇文章中，我将向您展示如何将数据发布到 Excel 表格。

历史

• 2007年7月30日：初次发布