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

As others have pointed out, you need to create an explicit reference for every Excel object you use, and call Marshal.ReleaseComObject on that reference, as described in this KB article. You also need to use try/finally to ensure ReleaseComObject is always called, even when an exception is thrown. I.e. instead of:

Worksheet sheet = excelApp.Worksheets(1)
... do something with sheet

you need to do something like:

Worksheets sheets = null;
Worksheet sheet = null
try
{ 
    sheets = excelApp.Worksheets;
    sheet = sheets(1);
    ...
}
finally
{
    if (sheets != null) Marshal.ReleaseComObject(sheets);
    if (sheet != null) Marshal.ReleaseComObject(sheet);
}

You also need to call Application.Quit before releasing the Application object if you want Excel to close.

As you can see, this quickly becomes extremely unwieldy as soon as you try to do anything even moderately complex. I have successfully developed .NET applications with a simple wrapper class that wraps a few simple manipulations of the Excel object model (open a workbook, write to a Range, save/close the workbook etc). The wrapper class implements IDisposable, carefully implements Marshal.ReleaseComObject on every object it uses, and does not pubicly expose any Excel objects to the rest of the app.

But this approach doesn't scale well for more complex requirements.

This is a big deficiency of .NET COM Interop. For more complex scenarios, I would seriously consider writing an ActiveX DLL in VB6 or other unmanaged language to which you can delegate all interaction with out-proc COM objects such as Office. You can then reference this ActiveX DLL from your .NET application, and things will be much easier as you will only need to release this one reference.

Answer 2

A great article on releasing COM objects is 2.5 Releasing COM Objects (MSDN).

The method that I would advocate is to null your Excel.Interop references if they are non-local variables, and then call GC.Collect() and GC.WaitForPendingFinalizers() twice. Locally scoped Interop variables will be taken care of automatically.

This removes the need to keep a named reference for every COM object.

Here's an example taken from the article:

public class Test {

    // These instance variables must be nulled or Excel will not quit
    private Excel.Application xl;
    private Excel.Workbook book;

    public void DoSomething()
    {
        xl = new Excel.Application();
        xl.Visible = true;
        book = xl.Workbooks.Add(Type.Missing);

        // These variables are locally scoped, so we need not worry about them.
        // Notice I don't care about using two dots.
        Excel.Range rng = book.Worksheets[1].UsedRange;
    }

    public void CleanUp()
    {
        book = null;
        xl.Quit();
        xl = null;

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

These words are straight from the article:

In almost all situations, nulling the RCW reference and forcing a garbage collection will clean up properly. If you also call GC.WaitForPendingFinalizers, garbage collection will be as deterministic as you can make it. That is, you'll be pretty sure exactly when the object has been cleaned up—on the return from the second call to WaitForPendingFinalizers. As an alternative, you can use Marshal.ReleaseComObject. However, note that you are very unlikely to ever need to use this method.

Answer 3

I am currently working on Office automation and have stumbled across a solution for this that works every time for me. It is simple and does not involve killing any processes.

It seems that by merely looping through the current active processes, and in any way 'accessing' an open Excel process, any stray hanging instance of Excel will be removed. The below code simply checks for processes where the name is 'Excel', then writes the MainWindowTitle property of the process to a string. This 'interaction' with the process seems to make Windows catch up and abort the frozen instance of Excel.

I run the below method just before the add-in which I am developing quits, as it fires it unloading event. It removes any hanging instances of Excel every time. In all honesty I am not entirely sure why this works, but it works well for me and could be placed at the end of any Excel application without having to worry about double dots, Marshal.ReleaseComObject, nor killing processes. I would be very interested in any suggestions as to why this is effective.

public static void SweepExcelProcesses()
{           
            if (Process.GetProcessesByName("EXCEL").Length != 0)
            {
                Process[] processes = Process.GetProcesses();
                foreach (Process process in processes)
                {
                    if (process.ProcessName.ToString() == "excel")
                    {                           
                        string title = process.MainWindowTitle;
                    }
                }
            }
}

Answer 4

¨°º¤ø„¸ Shoot Excel proc and chew bubble gum ¸„ø¤º°¨

public class MyExcelInteropClass
{
    Excel.Application xlApp;
    Excel.Workbook xlBook;

    public void dothingswithExcel() 
    {
        try { /* Do stuff manipulating cells sheets and workbooks ... */ }
        catch {}
        finally {KillExcelProcess(xlApp);}
    }

    static void KillExcelProcess(Excel.Application xlApp)
    {
        if (xlApp != null)
        {
            int excelProcessId = 0;
            GetWindowThreadProcessId(xlApp.Hwnd, out excelProcessId);
            Process p = Process.GetProcessById(excelProcessId);
            p.Kill();
            xlApp = null;
        }
    }

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

Answer 5

I've traditionally followed the advice found in VVS's answer. However, in an effort to keep this answer up-to-date with the latest options, I think all my future projects will use the "NetOffice" library.

NetOffice is a complete replacement for the Office PIAs and is completely version-agnostic. It's a collection of Managed COM wrappers that can handle the cleanup that often causes such headaches when working with Microsoft Office in .NET.

Some key features are:

• Mostly version-independent (and version-dependant features are documented)
• No dependencies
• No PIA
• No registration
• No VSTO

I am in no way affiliated with the project; I just genuinely appreciate the stark reduction in headaches.

Answer 6

When all the stuff above didn't work, try giving Excel some time to close its sheets:

app.workbooks.Close();
Thread.Sleep(500); // adjust, for me it works at around 300+
app.Quit();

...
FinalReleaseComObject(app);