65.9K
CodeProject 正在变化。 阅读更多。
Home

Excel 插件，用于通过用户定义函数进行数据库查询

rkapl
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.63/5 (18投票s)

2007年2月2日

GPL3

18分钟阅读

viewsIcon

124205

downloadIcon

3471

DBFuncs 是一个 Excel 插件，用于通过用户定义函数进行数据库查询。

引言

重要提示：DBFuncs 已合并到 DBSheets 并已移至此处！

DBFuncs 是一个用于通过用户定义函数查询数据库的 Excel 插件。这与 Excel 集成的 Microsoft Query 不同，Microsoft Query 是静态集成到工作表中的，并且在查询可能性和构造参数化查询的灵活性方面存在一些限制（Microsoft Query 仅允许在可以图形显示的简单查询中使用参数化查询）。

此外，它还提供了方便地将所谓的“数据绑定控件”（组合框和列表框）填充查询数据的可能性。

DBFuncs 已得到广泛测试（实际上已投入生产），但仅与 Excel XP 和 Microsoft-SQL Server 一起测试，其他数据库（MySQL、Oracle、PostgreSQL、DB2 和 Sybase SQL Server）仅使用相关的 Testworkbook DBFuncsTest.xls 进行了测试。

要使用该 Testworkbook，您需要 pubs 数据库，我可以在我的网站上提供 Oracle、Sybase、DB2、PostgreSQL 和 MySql 的脚本（Microsoft-SQL Server 版本可以在此处下载）。

您还可以从我的网站下载最新版本的 DBFuncs（以 7-ZIP 格式打包），它是在 GNU 公共许可证下分发的。

使用 DBFuncs 查询数据主要有两种方式：

  1. 一种（快速的）面向列表的方式，使用 DBListFetch。
    在此，值从 TargetRange 单元格开始输入到一个矩形列表中（类似于 Microsoft Query，实际上使用的是 QueryTables 对象将数据填充到工作表中）。
  2. 一种面向记录的方式，使用 DBRowFetch。
    在此，值按出现的顺序输入到 Parameter list TargetArray 中给出的几个区域。这些区域中的每一个都将用查询结果填充。

这些用户定义函数会将查询到的数据插入到其调用单元格上下文之外，这意味着目标区域可以放在工作簿中的任何位置（甚至工作簿之外）。

此外，还有一些辅助函数可用：

  • chainCells，它使用“,”作为分隔符将给定区域中的值串联起来，从而简化了 select 字段子句的创建。
  • concatCells，简单地连接单元格（使“&”运算符过时）。
  • DBString，从参数中给出的开放式参数列表中构建一个带引号的字符串。这也可以用来轻松地将通配符构建到字符串中。
  • DBinClause，从参数中给出的开放式参数列表中构建一个 SQL in 子句。
  • DBDate，根据参数中给定的日期值构建一个带引号的日期字符串（格式 YYYYMMDD）。
  • MarktwertHolen，将面向日期的市场数据提取到单个单元格中。

最后，有一个支持工具可用于构建查询并将它们放入 DBListFetch 或 DBRowFetch 函数中（类似于 Microsoft Query）。

Using the Code

DBListFetch

DBListFetch (Query, ConnectionString (optional), TargetRange,
             FormulaRange (optional), ExtendDataArea (optional),
             HeaderInfo (optional), AutoFit (optional),
             AutoFormat (optional), ShowRowNum (optional))

用于查询值的 select 语句作为文本字符串在参数 Query 中给出。此文本字符串可以是动态公式，即通过将查询与其他单元格连接起来轻松提供参数，例如：
"select * from TestTable where TestName = "&A1

连接字符串是在公式中给出的，或者对于标准配置可以省略，然后会在 DBfuncs.xla 插件（模块 DBfunctions，顶部）中全局设置。

Public Const ConstConnString = "provider=SQLOLEDB,Server=..."
Public Const CnnTimeout = 10

返回的列表值被写入由 TargetRange 表示的区域。这可以是：

  • 任意区域，结果数据将从最左上角的单元格开始复制。
  • 一个自定义的命名区域（任意大小），作为 TargetRange，它会将命名区域的大小调整为输出大小。此命名区域可以在查询结果之前或之后定义（并设置为函数参数）。

还有一个可选的 FormulaRange，可以指定用于填充“关联”公式（可以放在任何地方（甚至其他工作簿中），但只允许放在数据区域之外）。此 FormulaRange 可以是：

  • 一维的类似行的区域，或者
  • 一个自定义的命名区域（任意大小的范围，列必须包括所有计算/填充的单元格），它会将命名区域的大小调整为输出大小。此命名区域可以在查询结果之前或之后定义（并设置为函数参数）。注意，当只给出一个单元格作为命名区域时，这不起作用，因为 VBA 无法检索单元格的另一个指定名称，并且会使用一个隐藏名称来存储公式范围的最后范围。解决方法是将至少两个单元格（列或行）分配给该名称。

它通常包含引用数据区域内获取的单元格值的公式。此区域中的所有公式都会向下填充到 TargetRange 的最后一行。如果 FormulaRange 开始的行在 TargetRange 的最顶行之下，则上面的任何公式都将保持不变（例如，可能允许与其余数据进行不同的计算）。如果 FormulaRange 开始的行在 TargetRange 之上，则会给出错误并且不会刷新任何公式。如果在数据区域内分配了 FormulaRange，也会给出错误。

如果 TargetRange 是一个命名区域并且 FormulaRange 是相邻的，则 TargetRange 会自动扩展以覆盖 FormulaRange。这在使用复合 TargetRange 作为查找引用（Vlookup）时特别有用。

下一个参数 ExtendDataArea 定义了 DBListFetch 在查询数据扩展或缩短时应如何行为：

  • 0：DBListFetch 仅覆盖当前 TargetRange 下方的现有数据。
  • 1：在当前 TargetRange 下方插入仅具有 TargetRange 宽度的新单元格，从而保留任何现有数据。但是，目标范围右侧的任何数据都不会随着插入的数据向下移动。注意：在与 FormulaRange 结合使用时，在当前版本中，FormulaRange 下方的单元格不会向下移动！！
  • 2：在当前 TargetRange 下方插入整行，从而保留任何现有数据。目标范围右侧的数据现在会随着插入的数据向下移动。此选项对于 FormulaRange 下方的单元格是安全的。

参数 headerInfo 定义了是否应在返回的列表中显示字段标题（TRUE）或不显示（FALSE = 默认值）。

参数 AutoFit 定义了是否应根据数据内容自动调整行和列的大小（TRUE）或不自动调整（FALSE = 默认值）。当有多个自动调整的目标范围在彼此下方时，存在一个问题，此时自动调整不可预测（由于计算顺序的不可预测性），有时会导致列未正确调整。

参数 AutoFormat 定义了第一数据行的格式信息是否应自动向下填充以反映在所有行中（TRUE）或不填充（FALSE = 默认值）。

参数 ShowRowNums 定义了是否应在第一列显示行号（TRUE）或不显示（FALSE = 默认值）。

DBRowFetch

DBRowFetch (Query, ConnectionString (optional),
    headerInfo(optional/ contained in paramArray), TargetRange (paramArray))

对于查询和连接字符串，与 DBListFetch 中提到的相同。
值目标在查询、连接字符串和可选的 headerInfo 参数之后，以开放式参数数组的形式给出。这些参数参数包含区域（单个单元格或较大的区域），它们将按出现的顺序用查询结果顺序填充。
例如

DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs " &_
           "where job_id = 1",,A1,A8:A9,C8:D8)

这将把给定查询的第一个返回字段（job_desc）插入 A1，然后将 min_lvl、max_lvl 插入 A8 和 A9，最后将 job_id 插入 C8。

可选的 headerInfo 参数（在查询和连接字符串之后）定义了在填充数据之前是否应将字段标题填充到目标区域。
例如

DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs",_
           ,TRUE,B8:E8, B9:E20)

这将把给定查询的标题（job_desc、min_lvl、max_lvl、job_id）插入 B8:E8，然后逐行将数据插入 B9:E20。

填充行的方向始终由 TargetRange 参数数组中的第一个区域确定：如果该区域的列数多于行数，则按行填充数据，否则按列填充数据。
例如

DBRowFetch("select job_desc, min_lvl, max_lvl, job_id from jobs",_
           ,TRUE,A5:A8, B5:I8)

这将填充与上面相同的数据（包括标题），但按列填充。通常，第一个区域与 headerInfo 参数一起用作标题区域。

请注意，数据填充比 DBlistFetch 慢得多，因此请仅将 DBRowFetch 用于较小的数据集。

chainCells(Range)

chainCells(ParameterList)

chainCells 使用“,”作为分隔符将给定区域中的值“链接”在一起。其主要用途是方便在 Query 参数中创建 select 字段子句，例如：

DBRowFetch("select " & chainCells(E1:E4) & " from jobs where job_id = 1",_
           ,A1,A8:A9,C8:D8)

其中单元格 E1:E4 分别包含 job_desc、min_lvl、max_lvl、job_id。

concatCells

concatCells(ParameterList)

concatCells 将给定区域中的值连接在一起。其主要用途是方便构建非常长且复杂的查询。

DBRowFetch(concatCells(E1:E4),,A1,A8:A9,C8:D8)

在此，单元格 E1:E4 分别包含查询的组成部分。

chainCells 和 concatCells 都使用矩阵条件，即形式为：{=chainCells(IF(C2:C65535="Value";A2:A65535;""))} 的矩阵函数，仅当 C 列的相应单元格包含 Value 时，才链接/连接 A 列的值。

DBinClause

DBinClause(ParameterList)

从单元格值创建 in 子句，字符串带引号，日期使用 DBDate（参见下文了解详情，格式为 0）。

DBinClause("ABC", 1, DateRange)

如果 DateRange 包含15/01/2007作为日期值，上面的语句将返回 "('ABC',1,'20070115')"。

DBString

DBString(ParameterList)

这会根据参数中给出的开放式参数列表构建一个数据库兼容的字符串（带引号）。这也可以用来轻松地将通配符构建到字符串中，例如：

DBString("_",E1,"%")

当 E1 包含“test”时，结果为“_test%”，在 like 子句中匹配字符串 'stestString'、'atestAnotherString' 等。

DBDate

DBDate(DateValue, formatting (optional))

根据参数 formatting，此函数根据给定的日期/日期时间/时间值，生成以下之一：

  1. （默认格式 = 0）一个简单的日期字符串（格式 'YYYYMMDD'），日期时间值转换为 'YYYYMMDD HH:MM:SS'，时间值转换为 'HH:MM:SS'。
  2. （格式 = 1）一个符合 ANSI 标准的日期字符串（格式 date 'YYYY-MM-DD'），日期时间值转换为 timestamp 'YYYY-MM-DD HH:MM:SS'，时间值转换为 time 'HH:MM:SS'。
  3. （格式 = 2）一个符合 ODBC 标准的日期字符串（格式 {d 'YYYY-MM-DD'}），日期时间值转换为 {ts 'YYYY-MM-DD HH:MM:SS'}，时间值转换为 {t 'HH:MM:SS'}。

下面给出了一个示例：

DBDate(E1)
  • 当 E1 包含 Excel 本地日期 18/04/2005 时，结果为：'20050418'（ANSI：date '2005-04-18'，ODBC：{d '2005-04-18'}）。
  • 当 E1 包含 Excel 本地日期/时间值 10/01/2004 08:05 时，结果为：'20040110 08:05:00'（ANSI：timestamp '2004-01-10 08:05:00'，ODBC：{ts '2004-01-10 08:05:00'}）。
  • 当 E1 包含 Excel 本地时间值 08:05:05 时，结果为 '08:05:05'（ANSI：time '08:05:05'，ODBC：{t '08:05:05'}）。

当然，您也可以通过更改 DBfuncs.xla 中 Module DBfunctions 的函数头来更改格式的默认设置。

DBDate(datVal As Date, Optional formatting As Integer = 0) As String

Plookup

Plookup(inputRange, selectionRange, targetRange)

在模式查找区域 selectionRange 中，对输入值区域 inputValues 执行模式加权查找，并返回查找行中 targetRange 区域包含的值（如果作为矩阵函数输入）。

如果 Plookup 未作为矩阵函数输入，Plookup 将返回 targetRange 匹配行的第一列。如果有多行匹配，它将始终返回第一行的值。

示例

selection range | target range
1 * 3 4 5       | 11  12  13
* 2 3 * 5       | 21  22  23
* * 3 * 5       | 31  32  33
1 2 3 4 *       | 41  42  43
1 2 3 4 5       | 51  52  53

input: 1 2 3 4 x > matches 4th row -> returns 41  42  43
input: 1 2 3 4 5 > matches 5th row -> returns 51  52  53
input: x y 3 z 5 > matches 3rd row -> returns 31  32  33
input: x 2 3 z 5 > matches both 2nd and 3rd row -> returns 21  22  23
                        because row 2 is more precise

支持工具查询生成器

有一个支持工具用于构建查询（类似于 Microsoft Query，实际上 Microsoft Query 用作构建查询的前端），名为 DBfuncSupport.xla。

通过右键单击打开的 Excel 工作表中的任意位置并选择“build DBfunc query”来调用查询生成器。这会启动 Microsoft Query（请参阅有关使用 Microsoft Query 定义查询的相关文档），在 Query Assistant 或 Microsoft Query 中构建查询，选项“Back to Excel”（在德语版本中为“Zurück an Excel”）允许您将查询插入到活动工作表中，作为以下“DB items”之一：

  • 一个 DBListFetch 函数
  • 一个 DBRowFetch 函数
  • 一个 Dropdown 数据库窗体
  • 一个 Listbox 数据库窗体

以下对话框用于实现此目的：

首先，必须通过在“DB function/ DB bound control”选择框中进行选择来选择要插入的 DB item。然后，根据上面的选择，可以为函数公式或函数的参数选择目标单元格。

  • Function Target：放置 DB 函数（DBRowFetch 或 DBListFetch）的单元格。仅适用于 DBRowFetch 和 DBListFetch。

  • Data Target：对于 DB 函数，检索到的数据库数据将放置的单元格。对于 DB 绑定控件，这对应于 LinkedCell 属性，即选择控件值放置的目标单元格。适用于所有 DB items。

  • Query Target：在查询大于 255 个字符时放置查询的单元格。仅适用于 DBRowFetch 和 DBListFetch。如果显式设置了此项，则无论查询大小如何，都会将其放置在那里！查询中包含引号（"）时，也会将其放置在那里。

  • ConnDef Target：在选择“use custom database setting”时放置连接定义的单元格（用于覆盖 DBfuncs.xla 中的标准连接定义，参见上面的 DBListFetch 函数说明），并且连接定义字符串大于 255 个字符。仅适用于 DBRowFetch 和 DBListFetch。

  • Range Calc：要随数据一起填充的公式所在的区域。仅适用于 DBListFetch。

其他可能的选择是：

  • additional Data choice：DBListFetch 中附加数据应如何处理的方式（请参阅上面的解释）。仅适用于 DBListFetch。
  • "include Header Info?"：DBListFetch 是否应包含字段标题（请参阅上面的解释）。适用于 DBListFetch、DBDropDown 和 DBListbox。
  • "automatic Column Fit?"：DBListFetch 的列是否应自动调整大小（请参阅上面的解释）。仅适用于 DBListFetch。
  • "automatic Format fill?"：DBListFetch 的第 1 行格式是否应自动向下填充（请参阅上面的解释）。仅适用于 DBListFetch。
  • "show row numbers?"：DBListFetch 的第一列是否应显示行号（请参阅上面的解释）。仅适用于 DBListFetch。
  • custom database setting：如果使用的是不同于全局标准连接定义的数据库（参见 DBListFetch 函数），则激活此项。适用于所有 DB items。ODBC 提供程序 MSDASQL.1 也会自动添加到连接字符串的其余部分之前。

对于 DB 绑定控件，Data target 对应于控件的 LinkedCell。实际上，只有 data target、“include Header Info?”和“use custom database setting?”可用，因为查询和（可能的自定义）连接定义始终放置在 data target（LinkedCell）的右侧。

使用 DB 绑定控件时，还会添加一个“header”标签（灰色背景，条形分隔符）。这也是列表框/组合框中的字体始终是固定宽度的原因，以便能够计算和对齐标题/数据宽度。

创建 DB 绑定控件后，将显示一个关于是否应立即填充工作簿中所有现有 DB 控件的问题，以及一个提示将 auto_open 过程中的命令（在剪贴板中）添加到 workbook_open 过程中（或创建一个）。一个最小的 auto_open 过程被添加到剪贴板，可以粘贴到工作簿的 ThisWorkbook 模块中（这使用了 Terry Kreft 最初编写的代码）。

全局连接定义和查询生成器

连接字符串有两种可能性：ODBC 或 OLEDB。ODBC 的优点是可以与 Microsoft Query 无缝协作，原生 OLEDB 据说更快、更可靠（微软还有一个通用的 OLEDB over ODBC，如果您只有原生 ODBC 驱动程序，它会模拟 OLEDB）。

现在，如果使用 **ODBC** 连接字符串（包含“Driver=”的字符串），则有一种直接从包含 DB 函数的单元格重新定义查询的简单方法：只需右键单击函数单元格并选择“build DBfunc query”。然后 Microsoft Query 将允许您重新定义可以用来覆盖函数查询的查询。

如果使用 **OLEDB** 连接字符串，Microsoft Query 将尝试使用与数据库同名的系统 DSN 进行连接，该数据库在全局常量连接定义中的 DBidentifierCCS 之后识别。

Public Const ConstConnString = "Provider=OraOLEDB.Oracle;.. ..;User ID=pubs"
Public Const DBidentifierCCS = "User ID="
Public Const DBidentifierODBC = "UID="

DBidentifierCCS 用于在全局常量连接定义中标识数据库，DBidentifierODBC 用于在 Microsoft Query 返回的连接定义中标识数据库（用于比较并可能允许在 DB 函数/控件中插入自定义连接定义）。通常，这些标识符称为“Database=”（所有 SQL Server、MySQL）、“location=”（PostgreSQL）、“User ID/UID”（Oracle）、“Data source=”（DB2）。

DB Bound Controls

创建 DB 绑定控件

可以使用上面描述的支持工具创建 DB 绑定控件，但也可以按照约定手动创建：

  • 控件的 name 属性必须以“DB_”开头，这是填充过程（见下文）所必需的。
  • LinkedCell 属性必须已填充，这对于定位关联的查询（在 linked cell 的右侧）是必需的。
  • 相应地，包含查询的单元格必须正好在 LinkedCell 的右侧一格。
  • 可选的连接定义字符串必须正好在 LinkedCell 的右侧两格。
  • ColumnCount 属性应反映查询预期的列数。

填充 DB 绑定控件

通过调用 DBfuncSupport.xla 插件中的宏 initDBforms 来填充 DB 绑定控件，方法如下（最好在 Workbook_open 事件过程中）：

Private Sub Workbook_Open()
 Application.Run "DBFuncs.xla!initDBforms", ThisWorkbook.Name
End Sub

这会遍历提供的所有工作簿的表，使用直接位于 LinkedCell **右侧**的单元格中包含的查询来刷新所有具有以“DB_”开头的名称的控件。此外，它会检查查询单元格右侧的下一个单元格是否包含除空字符串之外的内容，如果是，则将该内容作为自定义连接定义。

DB 绑定控件在刷新期间（例如，保存/重新打开工作簿）保留选择。DB 绑定控件的高度和宽度也会被保留。

安装

安装只需将两个 Excel 插件 DBFuncs.xla 和 DBFuncSupport.xla 复制到您选择的 XLStart 文件夹中（基本上有三个：

  • 一个在 C:\Programs\Microsoft Office\Office<X>\XLStart
  • 一个在 C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLStart
  • 最后一个在你指定的选项/常规中的备用启动文件夹中。

然后，您需要调整标准连接字符串，该字符串在未提供特定于函数的连接字符串时全局应用。这在 DBFuncs.xla 的模块 DBfunctions 的顶部完成。

Public Const ConstConnString = "provider=SQLOLEDB,Server=…. "
Public Const CnnTimeout = 10

启动 Testworkbook 时，在等待 – 可能 – 连接错误后，您必须更改连接字符串以满足您的需求（有关说明，请参阅下文）。

DBFuncsTest.xls 中的连接信息存储在黑线右侧，实际连接是通过选择黄色输入字段中的相应短名称（下拉列表）来选择的。更改连接后，不要忘记通过右键单击并选择“refresh data”来刷新查询/DBforms。

关注点

将结果返回到数据库查询函数外部区域的基本原理是利用计算事件（如在 excelmvf 项目中提到的并受其启发，更多详情请参阅此处），因为 Excel 不允许 UDF 产生任何副作用。

函数调用和事件之间（以及返回状态信息）需要传递大量信息。这是通过利用所谓的 calcContainer 和 statusMsgContainer 来实现的，它们基本上是 VBA 类，被滥用为简单的结构，存储在名为 allCalcContainers 和 allStatusContainers 的全局集合中。正确的 calcContainers 和 statusMsgContainers 的引用是 Workbook 名称、表名称以及调用函数的单元格地址，这对于函数调用来说是一个相当独特的描述（此描述在代码中称为 callID）。

下面给出的图示应该能说明这个过程：

真正的诀窍在于找出何时以及在哪里删除 calc container，考虑到 Excel 调用函数和 calc 事件处理程序的复杂方式（上面的图示有点简化，因为在依赖树的计算中，调用链远非线性）。

Excel 有时会进行额外的计算来采取快捷方式，这使得调用顺序基本不可预测，因此您必须非常小心地仅处理每个函数一次，然后删除 calcContainer （如果您对实际调用的内容感兴趣，请在类模块 calcClass 和模块 DBfunctions 中设置 #Const DebugMe = True ，以便在“直接窗口”中查看发生了什么）。

每次计算事件后，都会删除工作的 calcContainers ，如果没有 calcContainers left，则 allCalcContainers 重置为 Nothing，准备好应对输入数据或函数布局的变化。有关计算顺序/背景的更多详细信息，可以参考 Decision Model 的 Excel Pages，特别是 Calculation Secrets。

用于调整 DBListFetch 中列表区域大小的信息存储在分配给调用函数单元格的隐藏命名区域中。

历史

  • 2006 年：首次使用 CopyFromRecordSet 的版本，版本 1.5 是最后一个利用 Range.CopyFromRecordSet 用于 DBListFetch 的版本，因为我发现此函数非常不可靠。
    从 2.0 开始，我改为使用 QueryTables，它（至少到目前为止）将所有数据正确复制到工作表中。
  • 2007/01/31：版本 2.0：发布 Codeproject 文章
  • 2007/02/03：版本 2.1：现在可以单独自动调整 FormulaRange 的命名大小。
  • 2007/02/27：版本 2.2
    • 大修复
      • 检查 DBRowFetch 参数列表中的非区域参数。
      • chainCells & concatCells 现在可以处理一般参数。
      • 现在会删除 Querytables 中剩余的名称。
      • 自动调整名称大小现在也适用于单个单元格。
  • 2007/03/12：版本 2.3
    • 错误修正
      • 将单个单元格命名为 TargetRange 现在可以正确调整到整个范围。
      • 自动格式向下填充现在也适用于公式单元格。
      • 解决了验证列表错误（在事件过程中无法将计算设置为手动）。
      • 格式填充现在也能与标题正确配合。
      • 消除了一个非常微妙的错误，该错误会导致多个调整大小的 TargetRanges 彼此相邻，导致 DBlistFetch 丢失对上方 TargetRange 的跟踪。
      • 格式未能正确填充到数据区域的末尾。
    • 增强功能
      • 一个命名的 TargetRange 会扩展到 FormulaRange，如果 FormulaRange 与 TargetRange 相邻。
      • 添加了 DBinClause 函数，用于从参数数组（区域等）创建带括号的 in 子句。
      • 包含 Option explicit 以获得更好的编译器支持。
  • 2007/03/25：版本 2.4
    • 错误修正
      • 过滤现在可以正常工作了。
      • verketteZellen 与非区域不工作。
    • 增强功能
      • 为所有公共函数添加了函数帮助。
  • 2007/04/09：版本 2.5
    • 错误修正
      • 自动格式现在适用于一般格式（不仅是数值格式）。
      • DBListFetch 的公式区域中可能存在错误值（#NV！...）。
    • 增强功能
      • 添加了 Plookup 函数，用于加权模式查找。
  • 2007/11/02：DBAddin 版本 1.0 现已在 sourceforge 上提供。
© . All rights reserved.