Excel 用户定义函数无限
4.81/5 (14投票s)
2005年8月19日
19分钟阅读
227664
5668
描述了如何在 Excel 中将值表作为用户定义函数（UDF）的结果返回。
引言
本文演示了如何克服 Excel 中用户定义函数（UDF）的限制
- 当常规使用时（即，非数组函数），UDF 只能返回单个值。
- 当需要返回多个值时，这可以通过提前选择正确大小的范围，然后同时按下 <CTRL><SHIFT><ENTER> 来实现。这告诉 Excel 该公式应被用作数组公式。
该示例展示了如何将值表（例如，数据库查询的结果）作为单个函数条目的结果返回，该条目已常规输入（即，未按 <CTRL><SHIFT><ENTER>）。结果与数据源保持链接，因此会自动更新。这样，就可以创建始终返回数据库查询的当前结果或返回多个值的计算的函数！
背景
大多数数据库应用程序的最终用户会进行进一步的数据处理，最好是在 Excel 中进行。标准的数据导出方式（文件导出）总是涉及繁琐且重复的手动步骤。
用户定义函数是完成此任务的理想方式
- 它们易于使用，并且 Excel 用户都熟悉，
- 参数（例如，日期）可以轻松更新，
- 更新是自动完成的。
系统要求和安装
- Microsoft Excel 2002 或更高版本。
- Microsoft JET 4.0 OLE DB 提供程序。此提供程序随 Microsoft Office 一起自动安装，否则可以 在此处 下载。
演示项目安装
下载演示项目，并将文件 excelmvf.dll、excelmvf.xla、excelmvf.mdb、adortl60.bpl、dbrtl60.bpl 和 rtl60.bpl 复制到 C:\excelmvf。严格遵守此路径非常重要，因为在 excelmvf.xla 中声明 DLL 函数时使用了绝对路径（另一种选择是将文件复制到系统目录，但这会使清理工作更困难）。
要启动加载项，可以通过“文件”->“打开”对话框打开 excelmvf.xla 文件，或使用加载项管理器。
必须将 Excel 安全设置设置为允许执行宏。如果在 Excel 的函数输入对话框的“用户定义函数”部分出现 getCustomers 和 getOrderItems 函数，则表示这些函数已准备就绪。
函数语法和参数
getCustomers(fieldList as String, sortingCriteria as String, header as Boolean)fieldList：由逗号或分号分隔的要显示的列字符串列表。sortingCriteria：由逗号或分号分隔的排序条件字符串列表，可选地包括排序方向asc或desc。header：如果为True（默认值）：显示列标题，如果为False：不显示列标题。
getOrderItems(startdate as Date, enddate as Date, fieldlist as String, sortingCriteria as String, header as Boolean)fieldList、sortingCriteria和header如上所述。Startdate和enddate：日期值，用于限制在指定范围内处理的订单结果。
示例
=getcustomers("firstname,lastname,address,postal code";"firstname desc").=getOrderItems("2004/09/10";"2004/09/30";"customer,orderdate, description, amount,price,totalprice";"orderdate asc").
构建源文件
提供两个版本的示例源文件
- excelmvfVC2005.zip 包含 Microsoft VC++ 2005 Express Edition 的版本（此版本免费，可 在此处 下载）。
- excelmvf.zip 包含 C++ Builder 6.0 的版本。
主要区别在于数据库访问。C++ Builder 版本使用了一些专有的 VCL 类，而 VC 版本则使用基本的 ADO。
对于 Visual C++ 版本，请遵循以下步骤
- 下载并安装 Microsoft VC++ 2005 Express Edition 和 SDK（遵循 VC++ 2005 中的相关说明）。
- 将 excelmvfVC2005.zip 的内容解压缩到您选择的目录。
- 打开 VC 解决方案 excelmvf；您应该能够成功构建项目。要启用 DLL 的调试，必须在项目设置中设置 Excel 安装的路径。
对于 Borland C++ Builder 版本
- 将 excelmvf.zip 的内容解压缩到您选择的目录。
- 在 C++ Builder 中，打开项目文件 excelmvf.bpr。您应该能够成功构建项目。要启用 DLL 的调试，必须设置 Excel 安装的路径（菜单：运行，参数）。
示例加载项的设计方式使得根据自身需求进行修改尽可能简单。有关添加新函数的必要步骤的详细说明，请参阅 添加函数 getArticles。有关用于规避用户定义函数限制的机制的说明……
全文：Excel 函数无限
数据库相关应用程序的开发者经常会遇到用户要求将数据提供给 MS Excel 进行进一步处理的需求。基于文件的导出（CSV、XLS）通常无法满足要求，因为它们无法选择数据，也无法提供自动更新。相反，Excel 查询难以设置和管理。使用用户定义函数将提供一种简单灵活的方式将数据导入 Excel 表格，但 Excel 函数存在一些内置限制，阻止了将表作为返回值。主要限制在于 Excel 不允许函数将值写入调用函数时所选范围之外的单元格。以下示例将对此进行说明。
Public Function myuserdefinedfunction() As Variant
On Error GoTo myerr
Application.ActiveCell.Offset(1, 0).Value = 100
myuserdefinedfunction = myrange
Exit Function
myerr:
MsgBox Err.Number & " " & Err.Description
End Function
当此函数从代码编辑器内部调用时，一切都会正常工作，值 100 将被写入调用时活动单元格的下方。但如果函数是从单元格内部调用（=myuserdefinedfunction()），则会发生错误，下方的单元格将保持为空。
第二个困难是如何将多个值作为函数调用的结果返回。尽管这始终可以通过矩阵公式实现，但以下示例将向您展示限制。
Public Function myMatrixformula(base As Integer) As Variant
Dim retval(4, 4) As Variant
For i = 0 To 4
For j = 0 To 4
retval(i, j) = base ^ i
Next j
Next i
myMatrixformula = retval
End Function
此函数根据参数 base 的值填充二维数组。如果在 Excel 中选择了一个 6x6 单元格的范围，并通过同时按下 <CTL><SHIFT><ENTER> 输入表达式“=myMatrixformula(2)”，则该函数将被用作矩阵公式并显示以下结果。
尽管函数仅返回一个 5x5 的数组值，但 Excel 会填充调用时选择的整个范围。如果函数提供的数值不足，则多余的单元格会填充 #NA（不可用）值。如果选择的范围太小，Excel 只会显示填充此范围所需的值，并忽略其余值（没有错误消息或任何其他提示）。同时按下 <CTRL><SHIFT><ENTER> 的要求是矩阵公式的另一个缺点，这使得它们不够用户友好，因此应避免使用。因此，现在我们已经定义了函数应满足的前提条件。
- 不应提前选择任何范围，
- 不应有必要同时按下 <CTRL><SHIFT><ENTER> 来输入函数，
- 如果由于数据库的插入或删除或函数参数的更改导致返回数据表的大小发生变化，Excel 中的范围大小应自动调整。
除了这些功能要求外，这些函数还应包含其他一些功能。
- 要显示的列的数量和顺序应可更改。
- 应可以排序。
- 应可以显示或不显示列标题。
- 应可以过滤。
为了演示这些函数的功能，我开发了一个示例加载项，该加载项从简单的 MS-Access 数据库返回数据。
getCustomers 函数返回 customer 表中的所有行。fieldlist 参数期望显示列的列表，sortingCriterias 参数期望包含排序方向的排序参数列表，而 Headers 参数决定是否显示列标题。所有参数都是可选的，因此如果您输入 =getCustomers()，则该函数将返回整个 customer 表，无排序，包括列标题。getOrderItems 函数与 getCustomers 类似，但它通过输入 startDate 和 endDate 参数，额外提供了按指定范围过滤要显示的值的可能性。所有参数也都是可选的，因此 =getOrderItems() 显示数据库中所有订单中所有项目的表。
尽管函数是在单个单元格中输入的（单元格 A1 中的 getcustomers() 和单元格 A10 中的 getOrderItems()），但结果看起来就像使用正确的范围提前选择并以矩阵公式形式输入一样。通过在结果表中编辑任意单元格并同时按下 <CTRL><SHIFT><ENTER> 可以进行参数更改，即使结果表的大小由于参数更改而发生变化。这种行为是通过以下技巧实现的：
- 在初始函数调用中，执行数据库查询以获取结果表的大小。公式和参数会存储以供将来使用，并且函数会返回任意值（#n/a）。
- Excel 在每次函数调用后都会自动触发一个
Calculate事件。此事件会被加载项捕获。 - 在处理
Calculate事件期间，会选择一个足够大的范围来容纳结果表，并将先前存储的公式作为数组公式插入到该范围中。由于原始函数调用已经处理完毕，因此不再对函数范围之外的单元格进行写保护。 - 在
Calculate事件中插入数组公式会立即触发对该函数的进一步调用。所选数组的大小现在与所需数组的大小匹配，因此可以插入结果。
由于处理通常非常快，因此用户看不到重复的函数调用。这些函数就像其他任何 Excel 函数一样运行，但现在它们能够返回表数据。
DLL 函数与 calculate 事件处理之间的协作有些棘手，特别是要探索 Excel 的行为花费了一些时间，但现在机制已经运行起来，对于任何数据库环境中的任何视图或表来说，它都非常易于使用。我试图以一种可以作为开发自己加载项的框架的方式来构建示例加载项。仍然需要 C++ 开发技能，但在本文的帮助下，定制可以相当容易地完成。
加载项由以下组件组成：
- 一个 Microsoft Excel 加载项文件（excelmvf.xla，代表 Excel 多值公式）。
- 一个动态链接库（excelmvf.dll），用 C++ 编写。
Excelmvf.xla 非常简单，主要包含函数声明。声明了 getCustomers 和 getOrderItems 函数为 public，因此它们会作为用户定义函数出现在 Excel 中，以及分别声明为 private 的函数 getCustomers_dll 和 getOrderItems_dll。不直接将 DLL 函数声明为 public 的原因是存在一个隐藏参数 currentCell，它将一个指向调用单元格的 IDispatch 指针传递给 DLL 函数。稍后将详细介绍。
函数 setExcelApp 起着重要作用，它在启动时自动调用，并将指向 Excel 应用程序对象的指针传递给动态链接库。该指针稍后在 DLL 中用于初始化事件处理机制。
动态链接库 excelmvf.dll 是加载项的主要组件。它由一个 DLL 主模块和多个类组成，其中最重要的类将在下面进行解释。
在 DllEntryPoint 函数中（当 DLL 加载时也会自动调用），ADO 数据库连接的连接字符串会根据 DLL 模块的路径和数据库文件名（在本例中为 excelmvf.mdb）进行组合。使用 ADO（或 OLE-DB，我从不看微软的文档……）可以非常轻松地使用不同的数据库环境，因为只需更改此连接字符串即可与不同的数据库产品协同工作。以下是通过 Microsoft OLE-DB Bridge for ODBC 连接到 Oracle 数据库的连接字符串示例。
Provider=MSDASQL.1;Password=mypassword;Persist Security Info=True;
User ID=myuser;Data Source=mydatasource.
除了数据源的这些适配之外，数据库访问不再需要进一步的更改。
函数 getCustomers_dll 和 getOrderItems_dll 构成了与 Excel 的接口，因为当用户在 Excel 中输入 getCustomers 和 getOrderItems 函数时，会调用它们。这些函数将被称为 excelmvf 函数。由于它们对于理解操作模式至关重要，因此将展示最简单的函数 getCustomers_dll（参见列表 1）。
VARIANT __export __stdcall getCustomers_dll( char* pfieldlist, char* psortcriterias, bool pheader, VARIANT pcell) { bool rangeSizechanged=false; VARIANT retval,result; VARIANT callingCellAddress,callingCellWorksheet, callingCellFormula,callingCellRow,callingCellCol; VARIANT excelColRange,excelrangeNumCols, excelRowRange, excelrangeNumRows; HRESULT hr; HWND funcparamWindow = 0; //the function argument window is of class bosa_sdm_XL9. //If a window of this class is found the user enters //arguments in this window. In this case we always //return #NA funcparamWindow = FindWindow("bosa_sdm_XL9",NULL); if (funcparamWindow){ retval.vt = VT_ERROR; retval.scode=excelmvf::VALNOTAVAILABLE; return retval; } //read values we need to know for storage in the //FuncParam variable to later access this information //in the ExcelEventHandler TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &callingCellAddress,pcell.pdispVal,L"Address",0); TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &callingCellFormula,pcell.pdispVal,L"Formula",0); TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &callingCellCol,pcell.pdispVal,L"Column",0); TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &callingCellRow,pcell.pdispVal,L"Row",0); TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &callingCellWorksheet,pcell.pdispVal,L"Worksheet",0); //query current range and determine number //of cols and rows if pcell is part of an array //formula, Count delivers size of array TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &excelColRange,pcell.pdispVal,L"Columns",0); TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &excelrangeNumCols,excelColRange.pdispVal,L"Count",0); TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &excelRowRange,pcell.pdispVal,L"Rows",0); TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &excelrangeNumRows,excelRowRange.pdispVal,L"Count",0); //fetch data const TCustomerDataset* dataset = requestController.getCustomers( std::string(psortcriterias ? psortcriterias :"")); //if the formula is returned in an Array of Variants //(MultiArrayFormula) then read the formula from the //first element of the array if (callingCellFormula.vt == (VT_ARRAY | VT_VARIANT)){ //it's an 2-dimensional array of Variants (dim1=rows, dim2 cols) //Lbounds of Dimensions start with 1 long indices[] = {1,1}; hr = SafeArrayGetElement(callingCellFormula.parray, indices ,&callingCellFormula); } //compare the size of the range with the required size if (dataset!=NULL && !dataset->isempty() && dataset->isvalid() ){ TRangeSize neededRangeSize = dataset->getRangeSize( std::string(pfieldlist ? pfieldlist:""),pheader); if(neededRangeSize.rows != excelrangeNumRows.lVal || neededRangeSize.cols != excelrangeNumCols.lVal) rangeSizechanged=true; } else rangeSizechanged = false; //if the function call was not invoked by //the ExcelEventDispatchers calculate-event //and we are working on the active cell, //store all function parameters for later //processing in ExcelEventDispatchers calculate event if (TRequestController::funcParams.getCalculating()==false && rangeSizechanged == true){ //store parameters for later processing //in the Calculate Event TRequestController::funcParams.setFuncParams(NULL, NULL, std::string(pfieldlist ? pfieldlist : ""), std::string(psortcriterias ? psortcriterias :""), pheader, excelmvf::getCustomers, callingCellFormula, callingCellAddress, callingCellCol, callingCellRow, excelrangeNumCols, excelrangeNumRows, callingCellWorksheet ); TRequestController::funcParams.setState(true); //the correct values are retrieved by a second //function call triggered by the Calculate Event //in the meantime we return N/A retval.vt = VT_ERROR; retval.scode=excelmvf::VALNOTAVAILABLE; return retval; } //the function was either called by automatic calculation //or after rewriting the formula in the Calculate-Event //which triggers a further function call. Return the //results in an Array TRequestController::funcParams.setCalculating(false); if (dataset==NULL || dataset->isempty() || (dataset->isvalid() == false)){ retval.vt=VT_ERROR; retval.scode=excelmvf::VALNOTAVAILABLE; return retval; } retval = dataset->getResultArray( std::string(pfieldlist ? pfieldlist : ""), pheader ); return retval; }
列表 1：函数 getCustomers_dll
主函数与 TExcelEventDispatcher::Calculate 函数紧密协作，该函数在每次调用任何 Excel 函数后由 Excel 事件机制自动调用。该函数在 TExcelEventDispatcher 类中实现，该类继承自 Borland 的 TEventDispatcher 类，并使用在启动时从 .xla 文件传递的 Excel 应用程序对象的指针与 Excel 的事件机制建立连接。列表 2 显示了此函数。
void __fastcall TExcelEventDispatcher::Calculate(TVariant* params) { VARIANT result, retval; HRESULT hresult; if(!deleting){ if (TRequestController::funcParams.getState()){ try{ VARIANT worksheet = TRequestController::funcParams.getWorksheet(); VARIANT address = TRequestController::funcParams.getAddress(); VARIANT formula = TRequestController::funcParams.getFormula(); const excelmvf::TDatasetBase* dataset; switch ( TRequestController::funcParams.getFormulaID()){ case excelmvf::getOrderItems:{ dataset = dynamic_cast<CONST TOrderItemDataset*>( requestController.getOrderItems( TRequestController::funcParams.getStartdate(), TRequestController::funcParams.getEnddate(), TRequestController::funcParams.getSortparams()) ); break; } case excelmvf::getCustomers: dataset = dynamic_cast<CONST TCustomerDataset*> ( requestController.getCustomers( TRequestController::funcParams.getSortparams()) ); break; } // Data available if (!(dataset==NULL || dataset->isempty() || (dataset->isvalid() == false))){ VARIANT formulaLocation, neededcols, neededrows; VARIANT excelColRange,excelRowRange, excelrangeNumCols,excelrangeNumRows; VARIANT emptyVariant; emptyVariant.vt=VT_EMPTY; VARIANT EnableCalculation; EnableCalculation.vt=VT_BOOL; //get range represented by column and row value //ATTENTION: parameters must be passed to //Autowrap in reverse order! hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &formulaLocation, worksheet.pdispVal, L"Cells", 2, TRequestController::funcParams.getCol(), TRequestController::funcParams.getRow()); //get required number of rows and colums TRangeSize neededRangeSize = dataset->getRangeSize( TRequestController::funcParams.getFieldlist(), TRequestController::funcParams.getHeader()); //if the current range has exactly one column and one //row, it is a new user input if (TRequestController::funcParams.getNumCols().lVal == 1 && TRequestController::funcParams.getNumRows().lVal == 1){ neededrows.vt = VT_I4; neededcols.vt = VT_I4; neededrows.lVal = neededRangeSize.rows; neededcols.lVal = neededRangeSize.cols; //extend range to needed size //(attention: parameters in reverse //order -> limitation of AutoWrap) hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &formulaLocation, formulaLocation.pdispVal, L"Resize", 2, neededcols, neededrows); TRequestController::funcParams.setCalculating(false); TRequestController::funcParams.setState(false); //write Formula as Array hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYPUT, &result, formulaLocation.pdispVal, L"FormulaArray", 1, formula); } //if it is a multicolum range then the //request originates from an array formula //with changed parameters delete Formulaarray //and rewrite formula to restart the retrieval process else{ //create a range with the first cell hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &formulaLocation, worksheet.pdispVal, L"Cells", 2, TRequestController::funcParams.getCol(), TRequestController::funcParams.getRow()); //resize the range to the current size for deletion hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &formulaLocation, formulaLocation.pdispVal, L"Resize", 2, TRequestController::funcParams.getNumCols(), TRequestController::funcParams.getNumRows()); //delete the current formula array //set static variable deleting to true //to avoid reentering this code due to automatic //Excel recalculation after deleting the range deleting=true; hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYPUT, NULL, formulaLocation.pdispVal, L"Value", 1, emptyVariant); deleting=false; //create a range with the first cell hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &formulaLocation, worksheet.pdispVal, L"Cells", 2, TRequestController::funcParams.getCol(), TRequestController::funcParams.getRow()); TRequestController::funcParams.setCalculating(false); TRequestController::funcParams.setState(false); //rewrite formula to restart the whole retrieval process hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYPUT, &result, formulaLocation.pdispVal, L"Formula", 1, formula); //select the first cell //(otherwise the range from the former //delete operation would persist) hresult = TComHelper::AutoWrap(DISPATCH_METHOD, NULL, formulaLocation.pdispVal, L"Select", 0); } } } catch(const TCOMException& e){ VARIANT excelMainWindowHWND; hresult = TComHelper::AutoWrap(DISPATCH_PROPERTYGET, &excelMainWindowHWND, pExcelAppIDispatch, L"HWND", 0); int errcode = HRESULT_CODE(e.getErrCode()); if (errcode==1004) //this error occurs on writing into an protected area //(e.g. into an area with another formula array) MessageBox(excelMainWindowHWND.plVal, excelmvf::WriteError.c_str(), excelmvf::appName.c_str(), MB_ICONEXCLAMATION); else MessageBox(excelMainWindowHWND.plVal, e.getErrMsg().c_str(), excelmvf::appName.c_str(), MB_ICONEXCLAMATION); TRequestController::funcParams.setCalculating(false); } } } }
列表 2：每次发生 Excel 计算事件时都会调用 Calculate 函数
乍一看，在两个列表中频繁使用函数 TComHelper::Autowrap 引人注目。此函数用于在 DLL 中调用 Excel 函数或读取/设置 Excel 对象的属性值。它需要一个指向要调用方法或读取/写入属性的对象的 IDispatch 接口的指针。对于 excelmvf 函数，我们必须干预函数调用所源自的范围。指向此范围的指针作为最后一个参数 pcell 从 excelmvf 函数传递。
区分对 excelmvf 函数的两种函数调用类型很有帮助：
- 类型 1 调用源自一个范围，该范围由单个单元格组成。此类型的调用源于新的用户输入，或如我们稍后将看到的，源自类型 2 的处理调用。
- 类型 2 调用源自由至少两个相邻单元格组成的范围。它们源于对已存在的
excelmvf函数的重新计算，这是由于 Excel 的重新计算机制或用户对函数参数的更改。
列表 1 中的前几行确定 Excel 的函数输入对话框是否打开。Excel 在参数验证期间进行函数调用，并在对话框中显示中间结果。因为在这里只能显示单个值，所以当函数调用源自此对话框时，excelmvf 函数始终返回 #n/a。
在接下来的几行中，将读取调用 Excel 范围的属性以供稍后验证和存储。然后，对 requestController.getCustomers 函数的调用将返回一个指向 DataSet 对象的指针，该对象表示数据库查询的结果。
之后，会分析调用范围的类型。仅对于类型 2 调用，调用范围的类型为 SAFEARRAY-VARIANT，并且必须从数组的第一个单元格中读取原始公式。对于类型 1 调用，则不需要。
下一步，将调用范围的大小与返回值的所需大小进行比较。对于类型 1 调用，大小总是不同，但对于类型 2 调用，当结果表的大小发生变化时（例如，当添加新记录或用户更改函数参数时），大小可能会不同。
如果大小比较表明当前范围未正确调整以容纳数据库查询，则该函数会将所有必需的值存储在（static）TrequestController::funcParams struct 中，然后返回 #n/a 值。
Excel 现在触发一个 calculate 事件，因此我们的 TExcelEventDispatcher::Calculate 函数被调用（列表 2）。当删除标志未设置时（此标志稍后解释），必须处理该事件，并且 funcParams struct 的状态已在之前对 excelmvf 函数的调用中设置。如果不是这种情况，该函数将处理由常规 Excel 函数（例如 SUM()）触发的事件，与此无关。
在确定了哪些 excelmvf 函数最初是由查询 FunctionID 调用的之后，通过调用 requestController 的相应 get 函数来检索指向相应数据集的指针。如果返回有效数据集，则确定结果集的大小。此时，excelmvf 函数调用是类型 1 还是类型 2 很重要。
类型 1 调用（源自单个单元格）现在通过将 Excel 中的范围扩展到包含结果所需的大小来处理。然后，原始函数作为 FormulaArray 复制到此扩展范围中，从而触发对原始 excelmvf 函数的即时重新调用。在第二次调用中，所选范围与所需范围之间的比较不再返回差异，因此可以插入结果。搞定。
类型 2 的调用需要一些额外的处理才能将其转变为类型 1 的调用。原始选择的范围必须删除，因为它未正确调整以容纳结果。这可以通过简单地将一个空 VARIANT 值插入到整个原始数组来完成。在此之前，必须将删除标志设置为 true 以避免处理触发的计算事件。之后，将删除标志重置为 false，并将原始公式插入到原始范围的左上角单元格中。这将导致类型 1 的函数调用，然后按上述方式处理。
在我描述了对上述代码进行适应以满足我们自身需求所需的修改之前，应先介绍一些处理数据处理的重要类。正如您在列表中已经看到的，有几次对 TDataset 对象的调用。这些调用检索有关结果表大小的信息或检索结果表本身。因此，TDataset 的派生类代表数据库查询的结果，而数据库查询又由 TRecord 类派生的记录组成。要创建一个从不同表或视图检索数据的新函数，需要分别从 TDataset 和 TRecord 派生新类。由于 TDataset 在派生时期望其各自的 TRecord 类作为模板参数，因此我们必须首先从 TRecord 派生。查看 TCustomerRecord 头文件可以看到接下来需要考虑的事项。
TFieldDescriptor 值的静态数组包含数据库查询中字段的描述。每个字段至少需要定义一个 TFieldDescriptor 值，但定义更多带有不同 fieldNameAlias 成员值的字段，则可以实现用不同的同义词标识字段。在我们的示例中，当用户在 fieldList 参数中输入以下值之一时，邮政编码字段将显示在结果列表中：Postal code、Postalcode、ZIP、Postleitzahl、PLZ。defaultFieldList 字符串包含应默认显示的字段列表，即当用户未为 fieldList 参数输入值时。在类的私有部分，所有查询字段都定义为 VARIANT 成员。为了使它们能够被各自的 TDataset 派生类访问，而无需为所有查询字段定义 getter 函数，TDataset 类被声明为 friend 类。最后，必须以与 TCustomerRecord.cpp 中相同的方式定义 [] 运算符。
现在可以从 TDataset 派生新的数据集类。除了之前将 TRecordset 类派生为模板参数之外，还需要定义或重载以下成员变量和成员函数：SQL 成员包含从数据库检索所需数据集的 SQL 查询。对于 TCustomerDataset，它很简单：
Select * from customers
retrieve 函数从数据库获取查询结果并将其存储在 std::set 容器中。实现可以严格遵循 TCustomerDataset 的示例，只需用查询检索到的字段的数量和名称填充 while (!query->Eof) 循环即可。最后，还需要重载 < 运算符。这是必要的，以避免在短时间内重复且降低性能的数据库查询。这需要一点解释：如上文列表 1 和 2 代码说明中所述，在非常短的时间间隔内会调用两次 getRangeSize 函数（一次在 excelmvf 函数中，一次在 calculate 事件中）。为了能够告知结果表的大小，需要在第一次调用时执行数据库查询。为了避免第二次调用时再次查询，查询结果会在短时间内（在此实现中为 10 秒，可在 static 成员变量 TrequestController::cachingperiod 中设置）存储在由 std::set 容器组成的本地缓存中。需要在 cachingperiod 中查找数据集，因此需要 < 运算符。
示例加载项中定义的函数以及可能添加的所有函数都通过 TRequestController 类绑定在一起。对于要实现的每个函数，该类都包含一个相应的 getter 函数，在本例中为 getCustomers 和 getOrderItems。此外，它还包含实现本地缓存的容器，以避免不必要的数据库查询。
为了避免在尝试实现 excelmvf 函数时耗时的调试会话，我已详细描述了添加新函数的整个过程。您可以在此处找到此描述（添加函数 getArticles）。
基于此框架的加载项已在一家拥有 160 多名员工的公司中使用，并且受到了高度评价。该实现对 Excel 的稳定性和性能没有任何负面影响。这促使我发布本文并启动一个开源项目（Excel Multivalue formula framework at sourceforge.net），该项目希望能收集所有基于 Excel 多值公式思想的增强功能。
关注点
除了克服上述 Excel 限制之外，该示例还演示了：
- 如何在用 C++ 实现的 DLL 中捕获和处理 Excel 事件。
- 如何使用 COM 接口在用 C++ 实现的 DLL 中与 Excel 对象进行交互。