自动化插件与 COM 插件 [英] Automation add-in vs. COM add-in
问题描述
我是插件编程的新手，在以下情况下需要帮助:
我有一个调用一些 UDF 的 C# Excel 自动化插件.我想在自动化加载项加载期间进行用户名和密码检查，加载加载项时必须弹出用户可以输入其详细信息的屏幕.如何使用自动化插件来完成?
如何使用自动化插件来完成事件处理?我希望在用户按 F9 计算某些单元格中的 UDF 公式时进行一些计算.
是否有任何文章解释自动化插件中的事件处理?
自动化加载项通常并非设计用于处理此类功能.您可以让您的外接程序实现 IDTExtensibility2，以便获取对运行外接程序的Excel.Application"对象的引用.从那里，您可以访问Excel.Application"类的所有事件.但自动化加载项通常并非设计用于处理 Excel 对象模型事件——它仅设计用于支持用户定义的函数 (UDF).
<块引用>我想做一个用户名和密码在自动化插件时检查加载哪个屏幕用户可以输入他的详细信息必须弹出加载加载项时.怎么可能使用自动化插件完成?
当您的自动化加载项首次加载时，请小心尝试采取行动.自动化加载项是按需加载的，这意味着它只有在 Excel 需要时才会加载.它通常会在用户开始将自动化加载项的第一个用户定义函数 (UDF) 输入单元格时加载.问题是当用户仍在编辑单元格时尝试执行大多数编程命令将失败.因此，如果您在首次加载加载项时尝试执行此类操作，则很有可能是在 Excel 处于不允许您的代码安全执行的模式下时加载该加载项.(有关这方面的更多信息，请参阅:Excel 在自动化加载项加载时失败.)>
要解决此问题，您可以改用托管 COM 加载项，该加载项旨在处理 Excel 对象模型事件.如果您愿意，您的托管 COM 加载项甚至可以加载您的自动化加载项；或者托管 COM 加载项类和自动化加载项类可以都驻留在同一个程序集中，在这种情况下，它们可以直接通信.
<块引用>事件处理如何在一般使用自动化插件?一世想要在什么时候完成一些计算用户按 F9 计算某些单元格中的 udf 公式.
可以通过订阅Excel.Application.SheetCalculate"事件来检测何时按下 F9 键，该事件将在任何工作表完成计算时触发.在这种情况下，可以出于任何原因触发计算——不仅仅是按 F9 键.如果您希望专门捕获 F9 键，则需要使用Application.OnKey"回调，该回调仅可通过 VBA 使用.但是，您可以将项目中的一个类公开给 COM，并从从Application.OnKey"事件回调的 VBA 加载项中调用它.
有关自动化插件的文章，请参阅:
- 在 .NET 中为 Excel 编写用户定义的函数
- 用 C# 编写自定义 Excel 工作表函数
- 在自动化插件中实现 IDTExtensibility2
- 加载自动化加载项时 Excel 失败
- 如何通过 Visual Studio 安装包部署 .NET 自动化加载项
有关托管 COM 加载项的文章，请参阅:
- 如何使用 Visual C# .NET 构建 Office COM 加载项莉>
- 使用 Visual C# 构建 Microsoft Office 加载项 .NET 和 Visual Basic .NET
有关 COM 插件和自动化插件的文章，请参阅:
有关讨论使用调用托管应用程序的 VBA 加载项的文章，请参阅:
我知道这有很多东西需要消化，但希望这能让你继续前进.总的来说，我会推荐一个托管 COM 加载项来处理您的用户界面功能.托管 COM 加载项保存的数据可以与自动化加载项轻松共享，方法是让两个加载项都引用一个公共程序集，或者让托管 COM 加载项类和自动化加载项在同一个议会内举行的课堂上.
我希望这会有所帮助，请询问您是否希望我进一步澄清任何事情.
-- 迈克
后续回复:
<块引用>非常感谢您提供的链接和指针.我浏览了这些链接，对需要做的事情有了一个清晰的认识.:)
很高兴它有帮助.:) 阅读量很大，我建议您将它们打印出来并全部阅读.您希望做一些相当高级的事情，因此您对该主题了解得越多，您的境况就会越好.
<块引用>如果我使用基于 COM 的插件来处理我的 excel 事件，我该如何合并我的 UDF 函数?
托管 COM 加载项无法公开 UDF 函数.为此，您仍然需要一个自动化插件.但是，如果需要，托管 COM 加载项类和自动化加载项类可以位于同一个程序集中.因此，您的 COM 加载项可能会向用户询问您需要的信息，并且这些值将在自动化加载项中的 UDF 运行时可用.
<块引用>它们是否会在公式窗格中作为普通函数公开，类似于使用自动化插件的情况?
您的自动化插件公开的 UDF 将自动包含在插入函数"向导中，位于与您的自动化插件名称匹配的类别下.但是，描述不会自动包含为 Excel 的内置函数提供的那么多信息.默认功能通常很好，但如果您希望为插入函数"向导提供更完整的信息，这本身就是一个复杂的主题.请参阅:Excel 2007 UDF:如何添加函数描述，论证帮助.
<块引用>假设我需要调用一个返回数字立方的公式 getcubepublic double getcube(double a) { return a * a * a;}
当我的插件同时使用定义我的 UDF 和 IDTExtensibility2 的自定义接口时，我该如何处理这种情况?能否举例说明一下这个案例?
到目前为止，根据您在此处显示的内容，我认为不需要实现 IDTExtensibility2，您只需要一个标准的自动化加载项.对于标准自动化加载项，您应该阅读 Writing user defined.NET 中的 Excel 函数 和 在 C# 中编写自定义 Excel 工作表函数.有关如何为托管 COM 加载项实现 IDTExtensibility2 的讨论，请参阅 在自动化插件中实现 IDTExtensibility2.
<块引用>有没有办法只在自动化插件上实现 IDTExtensibility2 来访问 Excel.Application 对象，还是应该为它创建一个单独的 COM 插件?
您绝对可以直接在自动化加载项中实现 IDTExtensibility2，无需为此创建托管 COM 加载项.同样，请参阅在自动化插件中实现 IDTExtensibility2.但是，您希望实现的是针对 Excel 对象模型使用事件处理.虽然这可以使用自动化加载项完成，但它是非标准的，并且不是自动化加载项旨在执行的任务类型.特别是，您希望在首次加载自动化插件时让用户输入信息；这可能是一个特别棘手的情况，这就是为什么我建议您使用托管 COM 加载项来完成此任务.有关这方面的更多信息，请参阅 Excel 在自动化加载项加载时失败.
澄清一下，托管 COM 加载项和自动化加载项只是已使 COM 可见并正确注册的类.这两个类没有理由不能存在于同一个程序集中.而且，由于听起来您希望您的功能同时包含 UDF 和 Excel 对象模型事件处理，因此包含托管 COM 加载项和自动化加载项的单个程序集将使您能够处理您寻求的所有功能Excel 期望的方式.
我知道这里有很多东西需要消化，但如果你仔细阅读我在这里推荐的文章，我认为它会有意义......
迈克
I am a newbie with add-in programming and need help with the following scenario:
I have an C# Excel automation add-in that calls a few UDFs. I want to do a user name and password check during when the automation add-in loads for which a screen where the user can enter his details must pop up on load of the add-in. How can it be done using an automation add-in?
How can event handling be done in general using automation addins ? I want some calculations to be done when the user presses F9 to calculate the UDF formula in some of the cells.
Are there any articles that explains event handling in automation add-ins?
Automation add-ins, in general, are not designed to handle this kind of functionality. You can have your add-in implement IDTExtensibility2 in order to get a reference to the 'Excel.Application' object in which your add-in is running. From there, you can access all the events of the 'Excel.Application' class. But an automation add-in is not generally designed to handle Excel object model events -- it is designed to support user defined functions (UDFs) only.
I want to do a user name and password check during when the automation addin loads for which a screen where the user can enter his details must pop up on load of the add in. How can it be done using an automation addin?
Beware of attempting to to take action when your automation add-in first loads. An automation add-in is demand-loaded, meaning that it is not loaded until it is needed by Excel. It will typically load when the user begins entering the first user-defined function (UDF) of your automation add-in into a cell. The problem is that the majority of programmatic commands will fail when attempting to execute while the user is still editing the cell. Therefore, if you attempt to take such actions when your add-in first loads, there is a pretty good chance that it is loading while Excel is in a mode that would not allow your code to execute safely. (For more on this, see: Excel fails when Automation add-In loads.)
To get around this issue, you could use a managed COM add-in instead, which is designed to handle Excel object model events. Your managed COM add-in could even load your automation add-in, if you wanted; or the managed COM add-in class and the automation add-in class could both reside within the same assembly, in which case they could communicate directly.
How can event handling be done in general using automation addins ? I want some calculations to be done when the user presses F9 to calculate the udf formula in some of the cells.
Detecting when the F9 key is hit could be done by subscribing to the 'Excel.Application.SheetCalculate' event, which will fire any time any worksheet has completed calculating. Calculation, in this case, could be triggered for any reason -- not just for hitting the F9 key. If you wish to trap the F9 key specifically, then you would need to make use of the 'Application.OnKey' callback, which is only available via VBA. You could, however, expose a class in your project to COM and have it called from a VBA add-in that is called back from the 'Application.OnKey' event.
For articles on automation add-ins, see:
- Writing user defined functions for Excel in .NET
- Writing Custom Excel Worksheet Functions in C#
- Implementing IDTExtensibility2 in an Automation Add-in
- Excel fails when Automation add-In loads
- How to Deploy a .NET Automation Add-in via a Visual Studio Setup Package
For articles on managed COM add-ins, see:
- How to build an Office COM add-in by using Visual C# .NET
- Building Microsoft Office Add-ins with Visual C# .NET and Visual Basic .NET
For articles regarding both COM add-ins and automation add-ins, see:
For articles discussing the use of a VBA add-in that calls your managed application, see:
- A Simple C# DLL - how do I call it from Excel, Access, VBA, VB6?
- How to create Excel UDFs in VSTO managed code
- Calling a .NET Component from a COM Component
I know that this is a lot to digest, but hopefully this can get you going. Overall, I would recommend a managed COM add-in to handle your user-interface functionality. The data saved by the managed COM add-in could be shared with the automation add-in in easily, either by having both add-ins reference a common assembly, or by having both the managed COM add-in class and the automation add-in class held within the same assembly.
I hope this helps, and please ask if you would like me to clarify anything further.
-- Mike
Follow-Up Reply:
Thanks a lot for the links and pointers. I went through the links and have a fair idea of what needs to be done.:)
Glad it helps. :) It's a lot of reading, I suggest you print them up and read them all. You are looking to do something that is fairly advanced, so the more you know about the topic, the better off you will be.
If I am using a COM based addin to handle my excel events, how do I incorporate my UDF functions?
A managed COM add-in cannot expose UDF functions. You would still need an automation add-in for that. However, your managed COM add-in class and your automation add-in class could both be within the same assembly, if you wanted. Therefore, your COM add-in could ask the user for the information that you need, and these values would be available to the automation add-in when the UDFs within it are run.
Will they be exposed as normal functions in the formula pane similar to the case of using an automation addin?
The UDFs exposed by your automation add-in will be included within the 'Insert Function' wizard automatically, under a category matching the name of your automation add-in. The description, however, will not automatically include as much information as is provided for Excel's built-in functions. The default functionality is usually fine, but if you wish to provide more complete information for the 'Insert Function' wizard, this is a complex topic unto itself. See: Excel 2007 UDF: how to add function description, argument help.
Say I need to call a formula getcube that returns the cube of a number public double getcube(double a) { return a * a * a; }
When my addin uses both the custom interface that defines my UDFs and IDTExtensibility2, how do I handle such a case? Can you please explain this case with an example?
So far, I don't see any need to implement IDTExtensibility2 based on what you show here, you only need a standard automation add-in. For a standard automation add-in, you should read Writing user defined functions for Excel in .NET and Writing Custom Excel Worksheet Functions in C#. For a discussion regarding how to implement IDTExtensibility2 for a managed COM add-in, see Implementing IDTExtensibility2 in an Automation Add-in.
Is there a way to just implement the IDTExtensibility2 on the automation addin to get access to the Excel.Application object or should I create a separate COM addin for it?
You absolutely can implement IDTExtensibility2 directly within your automation add-in, there is no need to create a managed COM add-in for this. Again, see, Implementing IDTExtensibility2 in an Automation Add-in. What you wish to achieve, however, is to use event handling against the Excel object model. While this could be done using an automation add-in, it is non-standard and is not the kind of task that an automation add-in is designed to do. In particular, you wish to have the user input information when the automation add-in first loads; this can be a particularly tricky situation, which is why I am recommending that you utilize a managed COM add-in for this task. For more on this, see Excel fails when Automation add-In loads.
To clarify, managed COM add-ins and automation add-ins are merely classes that have been made COM-visible and are registered correctly. There is no reason why these two classes cannot exist within the same assembly. And, since it sounds like you want your functionality to include both UDFs and Excel object model event handling, a single assembly that includes both a managed COM add-in and an automation add-in would enable you to handle all the functionality you seek in the manner that Excel expects.
I know that this is a lot to digest, but if you carefully read the articles that I suggested here, I think that it will make sense...
Mike
这篇关于自动化插件与 COM 插件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
