Best practice for obtaining ExcelDnaUtil.Application

Jan 6, 2012 at 8:20 PM
Edited Jan 6, 2012 at 8:26 PM

Hi,

I have a question regarding ExcelDnaUtil.Application. Originally, I assumed this was a simple C# property backed by some COM object reference. However now I see that there's a fair amount of code that tries to find the excel window using win32 calls etc. It also doesn't cache the value because (as I understand it) Excel will not exit properly.

So, my question is: If I intend to use this object very often from different threads, and I want to be efficient about it, is it ok to cache the value myself? If so, do I need a separate instance for each thread that plans to use it? Do the threads need to be special in any way (i.e. STA thread, etc)? Also, if the application is busy, I'll get COM exceptions with HRESULT 0x80010001 / 0x800AC472, so I have to run a loop which retries/sleeps until it gets the reference. Is it ok to do this, or do I run the risk of corrupting anything?

Sorry if this question is vague, I just get lots of exceptions related to accessing this method, and I want to make sure I'm not doing anything fundamentally wrong or inefficient. I have an application that periodically invokes macros from a background thread, and I'm currently using two threads and a dispatcher to accomplish this. Plus I'm re-fetching the Application object each time I invoke the macro, (which does the win32 lookup each time). I have a snippet below which shows how I'm doing this. Something tells me there must be a simpler way! But anytime I try to simplify, I get some horrible memory violation exception, or Excel doesn't quit gracefully, the process has to be killed...

Any help is greatly appreciated. Thanks so much!

Ismail

 

using System;
using System.Runtime.InteropServices;
using System.Threading;
using System.Windows.Threading;
using ExcelDna.Integration;
using Action = System.Action;
using Application = Microsoft.Office.Interop.Excel.Application;
using Window = System.Windows.Window;

namespace MyProject
{
    public class MacroRunner
    {
        // hidden window (http://www.codeguru.com/cpp/com-tech/activex/apts/article.php/c5529)
private static Window _macroRunnerWindow; private readonly Thread _macroRunnerThread; public const int MaxRetries = 15; // this is called by the excel main thread (i.e. from IExcelAddin.AutoOpen()) public MacroRunner() { // create a hidden Window object with its own dispatcher thread _macroRunnerWindow = AsyncWindowLauncher.CreateAsync(); _macroRunnerThread = new Thread(MacroRunThread) { IsBackground = true, Name = "MacroUpdateThread" }; _macroRunnerThread.SetApartmentState(ApartmentState.STA); _macroRunnerThread.Start(); } private static void MacroRunThread() { try { while (true) { Thread.Sleep(5000); // invoke the macro from the hidden window's dispatcher... _macroRunnerWindow.Dispatcher.Invoke((Action)delegate { try { Application app = null; for (var i = 0; i < MaxRetries; i++) { try { app = (Application)ExcelDnaUtil.Application; break; } catch (COMException e) { // call was rejected, excel was busy. // Try again after one second... var hresult = Marshal.GetHRForException(e); if (hresult == 0x80010001 || hresult == 0x800AC472) { Thread.Sleep(1000); } } } if (app == null) { return; } // now run some macro for (var i = 0; i < MaxRetries; i++) { try { app.Run("MyPeriodicMacro"); break; } catch (COMException e) { var hresult = Marshal.GetHRForException(e); if (hresult == 0x80010001 || hresult == 0x800AC472) { Thread.Sleep(1000); } } } } catch (Exception e) { // the invocation died... } }); } } catch(Exception e) { // the entire thread died... } } } /// <summary> /// Launches a WPF Window in a separate thread /// </summary> public class AsyncWindowLauncher { private readonly EventWaitHandle _waitHandle; public Window Window { get; private set; } public EventWaitHandle WaitHandle { get { return _waitHandle; } } public AsyncWindowLauncher(EventWaitHandle waitHandle) { _waitHandle = waitHandle; } private void CreateWindow() { Window = new Window(); Window.Closed+= WindowClosed; WaitHandle.Set(); Dispatcher.Run(); } private void WindowClosed(object sender, EventArgs e) { Window.Dispatcher.InvokeShutdown(); } public static Window CreateAsync() { var launcher = new AsyncWindowLauncher(new ManualResetEvent(false)); var windowThread = new Thread(launcher.CreateWindow) { IsBackground = true }; windowThread.SetApartmentState(ApartmentState.STA); windowThread.Start(); launcher.WaitHandle.WaitOne(); return launcher.Window; } } }
Coordinator
Jan 7, 2012 at 9:17 PM

Hi Ismail,

My own understanding of these things is not perfect.

You are right that the reason we don't cache the Application object in the ExcelDnaUtil.Application accessor is because it was preventing Excel from closing. In cases where Excel-DNA loads a COM add-in behind the scenes - like if a Ribbon is loaded - Excel-DNA will indeed cache the Application object received in the COM add-in connection, but only for the main thread (in a ThreadStatic variable).

I now suspect that the Application object can always be cached for the main thread without danger. However, I have not been able to test this yet - you might like to try. I conjecture that only the access from another thread causes Excel to not close properly with the outstanding references.

It is important that the Application object be properly marshalled before being used from another thread. I think the implementation of ExcelDnaUtil.Application ensures this by getting the object via the AccessibleObjectForWindow function, though the documentation does not say anything about the apartments or threading for this function.

Now for your questions:

Can you cache it? 

If you want to access it often from other threads, you can cache the object but you need to call Marshal.ReleaseComObject() when you're done (relying on the garbage collector won't work, since it might not run for a long time). And if you never know that you are done, your Excel process probably won't stop. 

Can you share between threads?

You need to know what COM Apartment the other threads are in. Threadpool threads are in the process MTA by default, in which case the cross-apartment marshalled interface you get from ExcelDnaUtil.Application should now live in the MTA, and can be accesses freely from different threads. If the threads are marked as STA (as they are in your example), each thread lives in its own STA apartment, and will need its own marshalled copy.

> The errors I would expect are

 

    const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
    const uint VBA_E_IGNORE = 0x800AC472;
.
You have instead of the first one the error:
    const uint RPC_E_CALL_REJECTED = 0x800100012;
which I have not seen before - is this really what you are getting?

 

Anyway, these errors are expected when calling the Excel object model from another thread. They indicate either that the COM message processing is busy, or that Excel has suspended the object model. Examples of ways to get these errors is to be in the process of editing a cell, or to click and hold the mouse down on the sheet. There is no other way to handle the errors - any COM call  from another thread on any Excel object might fail with one of these errors, at any time.

Your code needs to handle these calls in some way - you can decide to fail or to retry later, it doesn't matter to Excel. Considering the cell-editing case, it's clear that Excel might be in a state where your COM calls won't be processed for an indefinite time. You need to deal with that case too - if you have some data structure that grows without bound, this might cause trouble.

 

You don't really explain what you are trying to do. From a performance point of view you are probably doing the worst thing possible - talking to the single threaded Excel app from many other threads at once.

It seems a bit tricky and very inefficient to use the COM marshalling to synchronize your work from other threads to the (essentially) single-threaded Excel application. It might make for a nice programming model, but you should expect rather ppor performance.  Even when you get this to be reliable it might be pretty slow. It's like trying to get lots of people out of a room by letting many people storm the door at once, instead of going through one-by-one. Much better would by to have your own timer on the Excel main thread, and check periodically for work to do on the main thread which has been posted somewhere by the worker threads.  

What is the simpler approach?

You seem to be mixing two approaches, both of which should work independently.

  • Approach one:

Make a Window (or Timer) on the Excel main thread. Then call Invoke/BeginInvoke to run code on the main thread. This should work if you don't create the extra thread in your CreateAsync function. The Window needs to run on the Excel main thread, where it is created.

In this case you should not need to funny Excel COM errors, but the Invoke might not return or raise an error if Excel is 'busy'. I haven't tried this way.

  • Approach two

Just have the code in your MacroRunThread, without the Dispatcher.Invoke part. Then you don't need that Window class at all. It should already be safe to call from any thread, since the ExcelDnaUtil.Application should do the marshaling and you are ready to deal with the errors.

 

You seem to only ever invoke one method in the Excel object model from another thread - Application.Run("MyPeriodicMacro") - with a macro that will process your result on the main Excel thread. This is good - you don't have multiple threads trying to talk to the object model at once, except through this method. Otherwise accessing the object model from multiple threads is a recipe for trouble.

The worker threads can post their 'work-to-do' information onto a queue that is periodically checked and when there is some work-to-do, you invoke the "MyPeriodicMacro" macro via Application.Run. In the "MyPeriodicMacro" macro you check the queue, process all the results without cross-thread issues or needing to handle the problematic exceptions for every COM method call. 

 

I'll probably try to work out how to use the IGlobalInterfaceTable to marshal the interface across, but the end result will still be quite similar. 


I hope that gives you some thoughts, and look forward to further questions or reports of what you are doing and learning in this regard.

Regards,

Govert