编程用户定义的功能
Excel可以创建可在Excel公式中使用的用户定义的函数。 开发人员必须创建一种称为XLL的特殊类型的DLL。 Excel还允许您在VBA中编写可在Excel公式中使用的自定义函数。 不幸的是，Excel不支持或建议编写使用托管代码的XLL。
构建提供用户定义函数的管理型自动化加载项
幸运的是，创建一个不需要创建XLL的用户定义函数有一个更简单的方法。 Excel 2003支持称为自动化加载项的定制技术，可以轻松地在C＃或Visual Basic中创建。
首先，启动Visual Studio并创建一个新的C＃类库项目。将项目命名为AutomationAddin。在您在新项目中为您创建的Class1.cs文件中，输入如清单3-1所示的代码。该代码定义了一个名为MyFunctions的类，它实现了一个名为MultiplyNTimes的函数。我们将使用此函数作为自定义公式。我们的类还实现了RegisterFunction和UnregisterFunction，它们分别归因于ComRegisterFunction属性和ComUnregisterFunction属性。当COM程序集注册为COM互操作时，RegisterFunction将被调用。当汇编未注册COM互操作时，UnregisterFunction将被调用。这些功能在注册表中放置了一个必要的密钥，允许Excel知道该类可以用作自动化加载项。
清单3-1 一个C＃类被称为MyFunction，它暴露了一个用户定义的函数乘法
using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace AutomationAddin
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class MyFunctions
{
public MyFunctions()
{
}
public double MultiplyNTimes(double number1, double number2, double timesToMultiply)<br /> {<br /> double result = number1;<br /> for (double i = 0; i < timesToMultiply; i++)<br /> {<br /> result = result * number2;<br /> }<br /> return result;<br /> }[ComRegisterFunctionAttribute]<br /> public static void RegisterFunction(Type type)<br /> {<br /> Registry.ClassesRoot.CreateSubKey( GetSubKeyName(type));<br /> }[ComUnregisterFunctionAttribute]<br /> public static void UnregisterFunction(Type type)<br /> {<br /> Registry.ClassesRoot.DeleteSubKey（GetSubKeyName(type),false);<br /> }private static string GetSubKeyName(Type type)<br /> {<br /> System.Text.StringBuilder s = new System.Text.StringBuilder();<br /> s.Append(@"CLSID\{");<br /> s.Append(type.GUID.ToString().ToUpper());<br /> s.Append(@"}\Programmable");<br /> return s.ToString();<br /> }<br /> }<br />}<br /><br />使用这段代码编写，您需要修改项目，以便在构建COM互操作时自动注册该类。 首先，通过双击“解决方案资源管理器”中项目节点下的“属性”节点来显示项目的属性。 在出现的属性设计器中，单击“构建”选项卡，然后选中“注册COM Interop”复选框，如图3-7所示。 然后从Build菜单中选择Build Solution来构建类库项目。 您的操作将导致您的类库项目正在构建以及在注册表中作为自动化加载项注册。 Excel现在可以看到你的C＃类并使用它。<br />**图3-7 设置构建选项以注册COM互操作**<br /><br />在Excel中使用管理的自动化加载项<br />启动Excel并从工具菜单中选择加载项以显示加载项对话框。 在加载项对话框中，单击自动化按钮。 您可以通过在自动化服务器列表中查找AutomationAddin.MyFunctions找到您创建的类，如图3-8所示。<br />图3-8 从“自动化服务器”对话框中选择“AutomationAddin.MyFunction”<br /><br />单击此对话框中的确定，您已将AutomationAddin.MyFunctions类添加到已安装的自动化加载项列表中，如图3-9所示。<br />**图3-9 AutomationAddin.MyFunction已安装 **<br /><br />现在，尝试在Excel公式中使用函数MultiplyNTimes。 首先创建一个简单的电子表格，其中包含一个数字，第二个数字乘以第一个数字，第三个数字用于将第一个数字乘以第二个数字的次数。 电子表格如图3-10所示。<br />**图3-10 一个简单的电子表格来测试自定义公式**<br /><br />单击数字下方工作簿中的空单元格，然后单击公式栏中的插入函数按钮（带有“fx”标签的按钮）。 从可用公式的对话框中，下拉“或选择类别”下拉框，然后选择AutomationAddin.MyFunction。 然后单击“Multiplyntimes”功能，如图3-11所示。<br />**图3-11 从“插入函数”对话框中选取“乘数”**<br /><br />单击“确定”按钮后，Excel弹出对话框，帮助从电子表格中的单元格中选择功能参数，如图3-12所示。<br />图3-12 设置函数参数<br /><br />从相应的单元格中选择功能参数后，单击“确定”创建最终的电子表格，如图3-13所示，单元格C5中的自定义公式。<br />图3-13 最终的电子表格<br /><br />一些其他用户定义的函数<br />您可以尝试Excel公式中可以使用的其他功能。 例如，清单3-2显示了可以添加到MyFunctions类的其他几个函数。 要使用清单3-2，您必须添加对Excel 11.0对象库的引用，并将代码使用Excel = Microsoft.Off-ice.Interop.Excel添加到类文件的顶部。 请注意，当您将参数声明为对象时，Excel会传递一个Range对象。 还要注意AddNumbers函数支持可选参数。 当省略一个参数时，System.Type.Missing作为参数的值传递。<br />**清单3-2 可以添加到MyFunctions类的其他用户定义的函数**<br /><br />public string GetStars(double number)<br />{<br /> System.Text.StringBuilder s = new System.Text.StringBuilder();<br /> s.Append('*', number);<br /> return s.ToString();<br />}
public double AddNumbers(double number1, [Optional] object number2, [Optional] object number3)
{
double result = number1;
if (number2 != System.Type.Missing)
{
Excel.Range r2 = number2 as Excel.Range;
double d2 = Convert.ToDouble(r2.Value2);
result += d2;
}
if (number3 != System.Type.Missing)
{
Excel.Range r3 = number3 as Excel.Range;
double d3 = Convert.ToDouble(r3.Value2);
result += d3;
}
return result;
}
public double CalculateArea(object range)
{
Excel.Range r = range as Excel.Range;
return Convert.ToDouble(r.Width) + Convert.ToDouble(r.Height);
}
public double NumberOfCells(object range)
{
Excel.Range r = range as Excel.Range;
return r.Cells.Count;
}
public string ToUpperCase(string input)
{
return input.ToUpper();
}
在管理型自动化加载项中调试用户定义的函数
您可以通过将Excel设置为您的类库项目在调试时启动的程序来调试作为自动化加载项的C＃类库项目。 通过双击“解决方案资源管理器”中项目节点下的“属性”节点来显示项目的属性。 在出现的属性设计器中，单击“调试”选项卡，在“启动外部程序”文本框中，键入Excel.exe的完整路径，如图3-14所示。 现在，在您的一个用户功能上设置断点，按F5，然后使用电子表格中的功能。 调试器将停止执行断点设置的用户函数。
图3-14 设置调试选项以启动Excel
部署管理型自动化加载项
要部署自动化加载项，请在解决方案资源管理器中右键单击解决方案，然后从“添加”菜单中选择“新建项目”。 从“添加新项目”对话框中，在“项目类型”树中选择“从其他项目类型\安装和部署”中的“安装项目”。
右键单击解决方案资源管理器中添加的安装项目，然后从添加菜单中选择项目输出。 从“添加项目输出组”对话框中，选择“AutomationAddin”项目，选择“主要输出”，如图3-15所示。
图3-15 将Automation Addin项目的主输出添加到安装项目中
因为我们告诉项目注册我们用于COM互操作的托管对象，所以安装项目应该已经被正确设置，以便在安装时注册COM互操作的托管对象。 要验证此，请单击安装项目中AutomationAddin节点的主输出。 在主输出的属性窗口（我们的C＃DLL）中，确保将Register设置为vsdrpCOM。
Excel对象模型简介
无论您选择将代码与Excel集成在一起，您最终都需要与Excel对象模型进行交流，以完成任务。 在本书中完全描述Excel对象模型是不可能的，但我们尝试让您熟悉Excel对象模型中最重要的对象，并显示这些对象上最常用的方法，属性和事件。
对象层次结构
学习Excel对象模型的第一步是获取对象模型层次结构的基本结构。 图3-16显示了Excel对象模型中最关键的对象及其层次关系。
图3-16 Excel对象模型的基本层次结构
一个Workbook对象有一个名为Sheets的集合。 “表”集合可以包含“工作表”或“图表”类型的对象。 图表有时被称为图表，因为它涵盖了工作表将涵盖的整个区域。 您可以通过右键单击Excel工作簿左下角的工作表选项卡并选择“插入”，将工作表插入到工作簿中。 图3-17显示出现的对话框。 请注意，在Sheets集合中还有两个附加对象：MS Excel 4.0宏表和MS Excel 5.0对话框。 如果将宏表单或对话框插入到Excel工作簿中，则将其视为特殊类型的工作表，而不是与宏表或对话框对应的特殊对象模型类型。
图3-17 将各种“工作表”插入到Excel工作簿中
因为工作簿可以包含这些各种对象，Excel会从Workbook对象中提供多个集合。 Worksheets集合仅包含工作簿中的Worksheet对象。 “图表”集合仅包含工作簿中的图表。 Sheets集合是两者的混合集合。 Sheets集合将集合的成员作为类型对象返回，您必须将返回的对象转换为工作表或图表。在这本书中，当我们谈论一个可能是工作表或图表的对象时，我们将其称为工作表。
图3-18显示了一个更完整的层次结构树，其主要对象与图3-16中的对象相关联。这将开始让您了解Excel对象模型中对象的广泛层次结构，特别是当您意识到该图显示的可用对象不足一半时。以灰色显示的对象来自Microsoft.Office.Core命名空间，它与Microsoft Office 11.0 PIA（office.dll）相关联。这些对象由所有Office应用程序共享。
图3-18 Excel对象模型中某些主要对象的详细层次结构
图3-19显示了与Range相关联的对象层次结构，这是Excel中非常重要的一个对象，代表您要在代码中处理的单元格范围。 我们已经使用了清单3-2中的RangXe对象。
图3-19 与Excel对象模型中的Range相关联的对象的更详细层次结构
图3-20显示了与Shapea Shape相关联的对象层次结构，表示浮动在工作表上不是单元格的东西，例如嵌入的按钮，图形，注释气泡等。
图3-20 在Excel对象模型中与Shape相关联的对象的更详细层次结构
结论
本章介绍了将代码集成到Excel中的各种方法。 本章介绍如何构建自动化加载项来为Excel创建用户定义的函数。 您还学习了Excel对象模型的基本层次结构。 第4章“使用Excel事件”讨论Excel对象模型中的事件。 第5章“使用Excel对象”涵盖了Excel对象模型中最重要的对象。
若有收获，就点个赞吧
0 人点赞
