自动化加载项与COM加载项 [英] Automation add-in vs. COM add-in

问题描述

我是一个加入编程的新手，需要帮助以下情况：

我有一个C＃Excel自动化加载项，调用几个 UDF 秒。在自动加载项加载期间，我想进行用户名和密码检查，在该加载项中，用户可以输入其详细信息的屏幕在加载加载项时弹出。使用自动化加载项如何完成？

如何通过自动化插件进行事件处理？当用户按 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编写用户定义的函数


• 在C＃中编写自定义Excel工作表函数


• 实施自动化加载项中的IDTExtensibility2


• Excel失败自动加载项加载


• 如何通过Visual Studio安装部署.NET Automation加载项套餐

有关托管COM加载项的文章，请参阅：

• 如何使用Visual C＃构建Office COM加载项.NET


• 使用Visual C＃.NET和Visual Basic .NET构建Microsoft Office加载项

对于有关COM加载项和自动化加载项，请参阅：

• Excel COM加载项和自动化加载项


• Bui ld和部署.NET COM程序集

对于讨论使用VBA加载项调用管理的文章应用程序，请参阅：

• 如何在VSTO管理中创建Excel UDF代码


• 致电来自COM组件的.NET组件

我知道这是很多要消化的，但希望这可以让你去。总的来说，我建议使用托管COM加载项来处理用户界面功能。托管COM加载项保存的数据可以轻松地与自动化加载项共享，无论是通过使两个加载项引用通用程序集，还是通过同时托管COM加载项类和自动添加 -

我希望这有帮助，请问你是否想让我进一步澄清。

- Mike

后续回复：

非常感谢链接和指针。我经历了链接，并且对于需要做什么有一个公平的想法。）

很高兴有帮助。 :)这是很多的阅读，我建议你打印他们并阅读它们。你正在寻找一些相当先进的东西，所以你对这个话题的了解越多越好，你将会越好。

如果我使用基于COM的插件来处理我的excel事件，如何并入我的UDF函数？

托管COM加载项无法公开UDF功能。您仍然需要一个自动化加载项。但是，如果需要，您的托管COM加载项类和自动化加载项类都可以在同一程序集中。因此，您的COM加载项可能会要求用户获取所需的信息，而在运行UDF时，这些值将可用于自动化加载项。

在公式窗格中，它们是否作为普通函数公开，与使用自动化插件的情况相似？

您的自动化加载项公开的UDF将自动包含在插入函数向导中，属于与自动化加载项名称相匹配的类别。但是，描述不会自动包含与Excel内置函数一样多的信息。默认功能通常很好，但如果您希望为插入函数向导提供更完整的信息，这是一个复杂的主题。请参阅： Excel 2007 UDF：如何添加功能描述，参数帮助。

说我需要调用一个公式getcube，返回一个数字的多维数据集
public double getcube（double a）{return a * a * a; }

当我的插件使用定义我的UDF和IDTExtensibility2的自定义界面时，我该如何处理这种情况？你可以用一个例子来解释这个例子吗？

到目前为止，我根本看不到需要实现IDTExtensibility2在这里显示，您只需要一个标准的自动化加载项。对于标准自动化加载项，您应该阅读编写用户定义Excel中的Excel功能和在C＃中编写自定义Excel工作表函数。有关如何为托管COM加载项实现IDTExtensibility2的讨论，请参阅在自动化加载项中实现IDTExtensibility2 。

有没有办法在自动化上实现IDTExtensibility2插件可以访问Excel.Application对象，还是应该为它创建一个单独的COM插件？

你绝对可以直接在您的自动化加载项中实现IDTExtensibility2，不需要为此创建一个托管COM加载项。再次参见在自动化加载项中实现IDTExtensibility2 。但是，您希望实现的是使用针对Excel对象模型的事件处理。虽然这可以使用自动化加载项完成，但它是非标准的，而不是自动化加载项设计的任务。特别是，您希望在自动化加载项首次加载时具有用户输入信息;这可能是一个特别棘手的情况，这就是为什么我建议您使用托管COM加载项来完成此任务。有关更多信息，请参阅自动化加载项加载时Excel失败。

为了澄清，托管的COM加载项和自动化加载项只是已经使COM可见并正确注册的类。没有理由这两个类不能存在于同一个程序集中。而且，由于您希望您的功能包含UDF和Excel对象模型事件处理，所以包含托管COM加载项和自动化加载项的单个程序集将使您能够处理您所寻求的所有功能Excel的预期方式。

我知道这是很多消化的，但如果仔细阅读我在这里提出的文章，我认为这将是有道理的...

Mike

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:

• Excel COM add-ins and Automation add-ins
• Build and Deploy a .NET COM Assembly

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屋！