使用 Microsoft Excel 查询数据库

• 下载带示例数据库的代码 - 139.5 KB

引言

在企业中，尤其是在拥有成熟的 IT 系统来支持业务运营的企业中，有时会需要从系统中提取特定数据或报告。在某些情况下，所需的报告并不存在于现有的前端界面中。在其他情况下，对现有应用程序进行的必要修改可能过于复杂，甚至不可行。

本文介绍的 MS Excel 电子表格和 VBA 定制旨在为最终用户提供数据查询功能的捷径。利用 MS Excel 提供的功能比从头开始编码更可取。这也与所描述应用程序的另一个目标相关：让最终用户能够使用 Excel 提供的功能进行他们想要的筛选、格式化等操作。最重要的是，让最终用户自己完成，而不是由我们亲爱的开发者来完成。

应用程序用法

该应用程序的使用非常简单；用户在“用户输入”工作表中提供输入，数据将返回到“数据”工作表中。下面是一个示例。请参阅下一节“开发”，了解幕后细节。

用户从 B1 单元格的下拉列表中选择数据源。一个组织中可以存在多个数据库，这种情况相当普遍。

用户从 B2 单元格的下拉列表中选择报表。所选查询配置的输入字段（从 B6 单元格下方开始）将显示出来；此外，还会加载上次查询提交时提供的搜索条件。

在提供搜索条件后，单击绿色的“运行”按钮提交查询。数据随后将返回到“数据”工作表。用户可以进一步筛选和排序返回的数据；这些设置将在下次提交相同查询时保留。

除了获取数据库中的数据之外，还实现了一个实验性的“保存布局”功能。在当前实现中，可以保存并调用本地筛选、每列宽度和数据显示格式（例如，列是应显示为数字还是日期）以供将来使用。

开发

要向最终用户交付上述功能，需要完成两项任务。第一项是记录数据库连接字符串，另一项是编写参数化查询。

连接字符串保存在“数据源”工作表中。本文示例中提供了大多数流行数据库的连接字符串示例。如上所述，配置好的连接将显示在“用户输入”工作表中供用户选择。

参数化查询应保存在“查询”工作表中。每个查询都应有一个名称（A 列），供用户在“用户输入”工作表中选择，以及参数化查询本身（B 列）。每个查询可以有一个或零个命名参数。工作表内的 VBA 将在提交实际查询到数据库之前，用用户输入的值进行替换。

命名参数（如果有）应以 @s（字符串）、@n（数字）或 @dt（日期时间）为前缀。选择“字符串替换”而不是 ADO 参数绑定功能，是因为 ODBC 接口不支持命名参数。为了方便我的维护工作，我想用命名参数编写查询，而不是用“?”嵌入参数。

代码

这里是 Excel 文件中代码的简要说明：

• 我开发这个工具的初始目标之一是缩短满足最终用户数据/报告请求的周期。“最短路径”是让开发人员编写 SQL 查询，让最终用户自己进行格式化，利用 Excel 提供的格式化功能。
• 用户输入工作表：这里的代码用于处理用户交互，例如加载选定的参数化查询的输入字段，并在提交查询之前执行参数替换。
• 我想将最终用户的输入和设置保存在 Excel 文件中，我的选择是使用 XML。示例可以在“格式化”工作表的 E 列和“查询”工作表的 C 列中找到。
• XMLHelper 模块：用于隐藏调用 VBA 环境中可用的 XML API 的复杂性。
• SettingStorage 模块：用于将各种 Excel 对象“保存”到 XML 中。需要注意的是，如果我在 .Net 环境下编程，所有这些繁琐的手写代码都可以被反射取代。
• ReportSetting 模块：封装 SettingStorage 模块中的函数，以支持格式的保存和调用功能。

关注点

• 格式的保存和加载仅为实验性质。例如，希望也能处理条件格式。这可以通过扩展 SettingStorage 模块中的代码来实现。需要注意的是，Excel 对象模型似乎无法检索和设置排序顺序，这意味着无法实现调用排序顺序。
• 作为一个数据库客户端工具，该工具可以连接到任何具有 ODBC 或 OleDB 接口的数据库/对象。除了 Oracle 和 SQL Server，列表还包括格式化的文本文件、Excel 文件甚至 Outlook pst 文件，甚至 WMI 存储库。我相信您会对在事实上的主要 连接字符串参考网站上显示的列表感到惊讶。
• 我在编码过程中考虑的唯一安全问题是，我希望不处理安全问题；因此，请谨慎使用该代码，并自行承担风险。我的一个立场是，Excel 的原生密码保护（至少在 2003 版本或之前）非常薄弱，可以用一小段代码在几秒钟内破解。