.NET COM组件通过BackgroundWorker与Excel进行交互 [英] .NET COM assembly interacting with Excel via BackgroundWorker

问题描述

我在VB.NET 3.5编写一个实验程序集（并教会自己如何编写），该程序通过COM公开，然后从一些Excel VBA代码调用，以启动一个类的实例，该实例捕获一些Excel事件，然后执行

I am writing (and teaching myself how to write) an experimental assembly in VB.NET 3.5 that is exposed via COM and then called from some Excel VBA code to initiate an instance of a class that traps some Excel events and then perform some functions on the active workbook in Excel.

要启动类，将从VBA传递对Excel应用程序的引用（我在Excel中使用了PIA for Excel

To initiate the class, a reference to the Excel Application is passed from the VBA (I am using a PIA for Excel in the assembly).

我需要对我的程序集中的活动工作簿执行一个耗时的操作，所以我决定使用 BackgroundWorker 在WinForm中，以便我可以显示一个进度对话框的操作，同时操作完成在后台。

I needed to perform a time-consuming operation on the active workbook from my assembly so I decided to use a BackgroundWorker in a WinForm so that I can display a progress dialog for the operation whilst the operation completes in the background.

我想知道是否有任何通过COM使用后台工作者以这种方式与Excel交互的问题？请求的原因是程序集中的主类保存对Excel应用程序对象的引用，然后传递到 BackgroundWorker ，以便它可以确定活动工作簿和然后对其执行一些操作。我只是通过一个过程访问工作簿本身（而不是其他对象）。

I would like to know if there are any problems with interacting with Excel via COM using a background worker in this way? Reason for asking is that the main class in the assembly holds the reference to the Excel application object, and this is then passed to the BackgroundWorker so that it can determine the active workbook and then perform some operations on it. I am only accessing the workbook itself (not other objects) through one procedure.

由于某种原因，我有我的头，Excel可能不喜欢这 - ？

For some reason I have it in my head that Excel might not like this - am I right?

推荐答案

如果您指的是这样：

• 获取从VBA到您的VB.NET代码的方法调用


• 旋转新线程（或工作线程）并将引用传递给工作簿Excel.Application，a Range;没关系）（通过参数，静态...没关系）


• 使用原始线程显示UI，而后台线程访问Excel

你不能这样做。

Excel VBA是一个STA环境。所有访问Excel对象模型的输入和输出都必须来自同一个线程。你不能选择线程;它必须是调用你的方法开始的线程。

Excel VBA is an STA environment. All access to the Excel Object Model, in and out, must happen from the same thread. You don't get to pick the thread either; it has to be the thread that called your method to begin with.

要从工作线程运行代码访问Excel，你必须marshal接口指针你的工作线程。 Mashalling创建一对COM包装器（称为代理/存根对），根据需要穿过线程来传递方法调用 - 这类似于使用 Form.Invoke（）方法。我不知道在我的头如何做的顶部在.NET。

To run code accessing Excel from a worker thread, you would have to "marshal" the interface pointer to your worker thread. Mashalling creates a pair of COM wrappers (called 'proxy/stub' pairs) that shuttle the method call accross threads as needed - this is similar to what happens when you use the Form.Invoke() method in .NET. I don't know off the top of my head how to do that in .NET. This would not be my first approach anyway.

另一个选择是不从工作线程访问Excel对象，而是在主窗体对象中使用助手（你做想要一个表格，不是吗？）。让你的工作线程通过 Form.Invoke（）调用这些帮助，以确保它们从UI线程（VBA用来调用你的线程）运行。这在功能上与第一个选择相同，除了.NET做工作，而不是COM。

Another alternative is NOT to access the Excel object from your worker thread, but to use helpers in the main form object (you do want a form, don't you?). Let your worker thread call those helpers via Form.Invoke() to make sure they run from the UI thread (the thread that VBA used to call you). This is functionally the same as the first alternative, except that .NET does the work, instead of COM.

第三种方法是不使用工作线程 - 做它的VB方式：从一个和唯一的线程创建你的UI;显示它，然后从窗体的 Load 事件中执行您的工作。每隔一段时间调用 Application.DoEvents（）一次，让UI操作一些平滑度。

A third approach, is to not use worker threads at all - do it the VB way: create your UI from the one and only thread; show it, and then do your work from the Load event of the Form. Call Application.DoEvents() once in a while (a few times a second) to let the UI operate with some reseamblance of smoothness.

第四种方法是改变问题。使用一个全新的线程，而不是一个池线程（这是你从 BackgroundWorker ），并从那里处理UI。我们在这里反转角色：主线程将做奇特的工作，而额外的线程创建一个形式并显示UI。同样，请确保您使用 Form.Invoke（）更新进度窗口。当你完成后，确保你 Dispose（）你的线程。

A fourth alternative is to turn the problem around. Use a brand new thread instead of a pool thread (which is what you get from BackgroundWorker) and handle the UI from there. We reverse roles here: the main thread would do the fancy work, while the extra thread creates a form and shows the UI. Again, make sure you use Form.Invoke() to update the progress window. Make sure you Dispose() your thread when you're done. This might just be my preferred approach.

或者只是使用沙漏鼠标指针...慢是你的慢？

Or just use an hourglass mouse pointer... Just how "slow" is your "slow"?

这篇关于.NET COM组件通过BackgroundWorker与Excel进行交互的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！