How do I properly clean up Excel interop objects?

Question

I'm using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause:

while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();

Although this kind of works, the Excel.exe process is still in the background even after I close Excel. It is only released once my application is manually closed.

What am I doing wrong, or is there an alternative to ensure interop objects are properly disposed of?

Answer 1

You should be very careful using Word/Excel interop applications. After trying all the solutions we still had a lot of "WinWord" process left open on server (with more than 2000 users).

After working on the problem for hours, I realized that if I open more than a couple of documents using Word.ApplicationClass.Document.Open() on different threads simultaneously, IIS worker process (w3wp.exe) would crash leaving all WinWord processes open!

So I guess there is no absolute solution to this problem, but switching to other methods such as Office Open XML development.

Answer 2

I found a useful generic template that can help implement the correct disposal pattern for COM objects, that need Marshal.ReleaseComObject called when they go out of scope:

Usage:

using (AutoReleaseComObject<Application> excelApplicationWrapper = new AutoReleaseComObject<Application>(new Application()))
{
    try
    {
        using (AutoReleaseComObject<Workbook> workbookWrapper = new AutoReleaseComObject<Workbook>(excelApplicationWrapper.ComObject.Workbooks.Open(namedRangeBase.FullName, false, false, missing, missing, missing, true, missing, missing, true, missing, missing, missing, missing, missing)))
        {
           // do something with your workbook....
        }
    }
    finally
    {
         excelApplicationWrapper.ComObject.Quit();
    } 
}

Template:

public class AutoReleaseComObject<T> : IDisposable
{
    private T m_comObject;
    private bool m_armed = true;
    private bool m_disposed = false;

    public AutoReleaseComObject(T comObject)
    {
        Debug.Assert(comObject != null);
        m_comObject = comObject;
    }

#if DEBUG
    ~AutoReleaseComObject()
    {
        // We should have been disposed using Dispose().
        Debug.WriteLine("Finalize being called, should have been disposed");

        if (this.ComObject != null)
        {
            Debug.WriteLine(string.Format("ComObject was not null:{0}, name:{1}.", this.ComObject, this.ComObjectName));
        }

        //Debug.Assert(false);
    }
#endif

    public T ComObject
    {
        get
        {
            Debug.Assert(!m_disposed);
            return m_comObject;
        }
    }

    private string ComObjectName
    {
        get
        {
            if(this.ComObject is Microsoft.Office.Interop.Excel.Workbook)
            {
                return ((Microsoft.Office.Interop.Excel.Workbook)this.ComObject).Name;
            }

            return null;
        }
    }

    public void Disarm()
    {
        Debug.Assert(!m_disposed);
        m_armed = false;
    }

    #region IDisposable Members

    public void Dispose()
    {
        Dispose(true);
#if DEBUG
        GC.SuppressFinalize(this);
#endif
    }

    #endregion

    protected virtual void Dispose(bool disposing)
    {
        if (!m_disposed)
        {
            if (m_armed)
            {
                int refcnt = 0;
                do
                {
                    refcnt = System.Runtime.InteropServices.Marshal.ReleaseComObject(m_comObject);
                } while (refcnt > 0);

                m_comObject = default(T);
            }

            m_disposed = true;
        }
    }
}

Reference:

http://www.deez.info/sengelha/2005/02/11/useful-idisposable-class-3-autoreleasecomobject/

Answer 3

My solution

[DllImport("user32.dll")]
static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);

private void GenerateExcel()
{
    var excel = new Microsoft.Office.Interop.Excel.Application();
    int id;
    // Find the Excel Process Id (ath the end, you kill him
    GetWindowThreadProcessId(excel.Hwnd, out id);
    Process excelProcess = Process.GetProcessById(id);

try
{
    // Your code
}
finally
{
    excel.Quit();

    // Kill him !
    excelProcess.Kill();
}

Answer 4

This sure seems like it has been over-complicated. From my experience, there are just three key things to get Excel to close properly:

1: make sure there are no remaining references to the excel application you created (you should only have one anyway; set it to null)

2: call GC.Collect()

3: Excel has to be closed, either by the user manually closing the program, or by you calling Quit on the Excel object. (Note that Quit will function just as if the user tried to close the program, and will present a confirmation dialog if there are unsaved changes, even if Excel is not visible. The user could press cancel, and then Excel will not have been closed.)

1 needs to happen before 2, but 3 can happen anytime.

One way to implement this is to wrap the interop Excel object with your own class, create the interop instance in the constructor, and implement IDisposable with Dispose looking something like

if (!mDisposed) {
   mExcel = null;
   GC.Collect();
   mDisposed = true;
}

That will clean up excel from your program's side of things. Once Excel is closed (manually by the user or by you calling Quit) the process will go away. If the program has already been closed, then the process will disappear on the GC.Collect() call.

(I'm not sure how important it is, but you may want a GC.WaitForPendingFinalizers() call after the GC.Collect() call but it is not strictly necessary to get rid of the Excel process.)

This has worked for me without issue for years. Keep in mind though that while this works, you actually have to close gracefully for it to work. You will still get accumulating excel.exe processes if you interrupt your program before Excel is cleaned up (usually by hitting "stop" while your program is being debugged).

Answer 5

"Never use two dots with COM objects" is a great rule of thumb to avoid leakage of COM references, but Excel PIA can lead to leakage in more ways than apparent at first sight.

One of these ways is subscribing to any event exposed by any of the Excel object model's COM objects.

For example, subscribing to the Application class's WorkbookOpen event.

Some theory on COM events

COM classes expose a group of events through call-back interfaces. In order to subscribe to events, the client code can simply register an object implementing the call-back interface and the COM class will invoke its methods in response to specific events. Since the call-back interface is a COM interface, it is the duty of the implementing object to decrement the reference count of any COM object it receives (as a parameter) for any of the event handlers.

How Excel PIA expose COM Events

Excel PIA exposes COM events of Excel Application class as conventional .NET events. Whenever the client code subscribes to a .NET event (emphasis on 'a'), PIA creates an instance of a class implementing the call-back interface and registers it with Excel.

Hence, a number of call-back objects get registered with Excel in response to different subscription requests from the .NET code. One call-back object per event subscription.

A call-back interface for event handling means that, PIA has to subscribe to all interface events for every .NET event subscription request. It cannot pick and choose. On receiving an event call-back, the call-back object checks if the associated .NET event handler is interested in the current event or not and then either invokes the handler or silently ignores the call-back.

Effect on COM instance reference counts

All these call-back objects do not decrement the reference count of any of the COM objects they receive (as parameters) for any of the call-back methods (even for the ones that are silently ignored). They rely solely on the CLR garbage collector to free up the COM objects.

Since GC run is non-deterministic, this can lead to the holding off of Excel process for a longer duration than desired and create an impression of a 'memory leak'.

Solution

The only solution as of now is to avoid the PIA’s event provider for the COM class and write your own event provider which deterministically releases COM objects.

For the Application class, this can be done by implementing the AppEvents interface and then registering the implementation with Excel by using IConnectionPointContainer interface. The Application class (and for that matter all COM objects exposing events using callback mechanism) implements the IConnectionPointContainer interface.

Answer 6

You can actually release your Excel Application object cleanly, but you do have to take care.

The advice to maintain a named reference for absolutely every COM object you access and then explicitly release it via Marshal.FinalReleaseComObject() is correct in theory, but, unfortunately, very difficult to manage in practice. If one ever slips anywhere and uses "two dots", or iterates cells via a for each loop, or any other similar kind of command, then you'll have unreferenced COM objects and risk a hang. In this case, there would be no way to find the cause in the code; you would have to review all your code by eye and hopefully find the cause, a task that could be nearly impossible for a large project.

The good news is that you do not actually have to maintain a named variable reference to every COM object you use. Instead, call GC.Collect() and then GC.WaitForPendingFinalizers() to release all the (usually minor) objects to which you do not hold a reference, and then explicitly release the objects to which you do hold a named variable reference.

You should also release your named references in reverse order of importance: range objects first, then worksheets, workbooks, and then finally your Excel Application object.

For example, assuming that you had a Range object variable named xlRng, a Worksheet variable named xlSheet, a Workbook variable named xlBook and an Excel Application variable named xlApp, then your cleanup code could look something like the following:

// Cleanup
GC.Collect();
GC.WaitForPendingFinalizers();

Marshal.FinalReleaseComObject(xlRng);
Marshal.FinalReleaseComObject(xlSheet);

xlBook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(xlBook);

xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);

In most code examples you'll see for cleaning up COM objects from .NET, the GC.Collect() and GC.WaitForPendingFinalizers() calls are made TWICE as in:

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

This should not be required, however, unless you are using Visual Studio Tools for Office (VSTO), which uses finalizers that cause an entire graph of objects to be promoted in the finalization queue. Such objects would not be released until the next garbage collection. However, if you are not using VSTO, you should be able to call GC.Collect() and GC.WaitForPendingFinalizers() just once.

I know that explicitly calling GC.Collect() is a no-no (and certainly doing it twice sounds very painful), but there is no way around it, to be honest. Through normal operations you will generate hidden objects to which you hold no reference that you, therefore, cannot release through any other means other than calling GC.Collect().

This is a complex topic, but this really is all there is to it. Once you establish this template for your cleanup procedure you can code normally, without the need for wrappers, etc. :-)

I have a tutorial on this here:

Automating Office Programs with VB.Net / COM Interop

It's written for VB.NET, but don't be put off by that, the principles are exactly the same as when using C#.