How do I properly clean up Excel interop objects?

Solution 1:

Excel does not quit because your application is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn't know that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn't know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don't go the way of "the two dots": Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

Solution 2:

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#.

Solution 3:

Preface: my answer contains two solutions, so be careful when reading and don't miss anything.

There are different ways and advice of how to make Excel instance unload, such as:

• Releasing EVERY com object explicitly with Marshal.FinalReleaseComObject() (not forgetting about implicitly created com-objects). To release every created com object, you may use the rule of 2 dots mentioned here:
  How do I properly clean up Excel interop objects?

• Calling GC.Collect() and GC.WaitForPendingFinalizers() to make CLR release unused com-objects * (Actually, it works, see my second solution for details)

• Checking if com-server-application maybe shows a message box waiting for the user to answer (though I am not sure it can prevent Excel from closing, but I heard about it a few times)

• Sending WM_CLOSE message to the main Excel window

• Executing the function that works with Excel in a separate AppDomain. Some people believe Excel instance will be shut, when AppDomain is unloaded.

• Killing all excel instances which were instantiated after our excel-interoping code started.

BUT! Sometimes all these options just don't help or can't be appropriate!

For example, yesterday I found out that in one of my functions (which works with excel) Excel keeps running after the function ends. I tried everything! I thoroughly checked the whole function 10 times and added Marshal.FinalReleaseComObject() for everything! I also had GC.Collect() and GC.WaitForPendingFinalizers(). I checked for hidden message boxes. I tried to send WM_CLOSE message to the main Excel window. I executed my function in a separate AppDomain and unloaded that domain. Nothing helped! The option with closing all excel instances is inappropriate, because if the user starts another Excel instance manually, during execution of my function which works also with Excel, then that instance will also be closed by my function. I bet the user will not be happy! So, honestly, this is a lame option (no offence guys). So I spent a couple of hours before I found a good (in my humble opinion) solution: Kill excel process by hWnd of its main window (it's the first solution).

Here is the simple code:

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

/// <summary> Tries to find and kill process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <returns>True if process was found and killed. False if process was not found by hWnd or if it could not be killed.</returns>
public static bool TryKillProcessByMainWindowHwnd(int hWnd)
{
    uint processID;
    GetWindowThreadProcessId((IntPtr)hWnd, out processID);
    if(processID == 0) return false;
    try
    {
        Process.GetProcessById((int)processID).Kill();
    }
    catch (ArgumentException)
    {
        return false;
    }
    catch (Win32Exception)
    {
        return false;
    }
    catch (NotSupportedException)
    {
        return false;
    }
    catch (InvalidOperationException)
    {
        return false;
    }
    return true;
}

/// <summary> Finds and kills process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <exception cref="ArgumentException">
/// Thrown when process is not found by the hWnd parameter (the process is not running). 
/// The identifier of the process might be expired.
/// </exception>
/// <exception cref="Win32Exception">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="NotSupportedException">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="InvalidOperationException">See Process.Kill() exceptions documentation.</exception>
public static void KillProcessByMainWindowHwnd(int hWnd)
{
    uint processID;
    GetWindowThreadProcessId((IntPtr)hWnd, out processID);
    if (processID == 0)
        throw new ArgumentException("Process has not been found by the given main window handle.", "hWnd");
    Process.GetProcessById((int)processID).Kill();
}

As you can see I provided two methods, according to Try-Parse pattern (I think it is appropriate here): one method doesn't throw the exception if the Process could not be killed (for example the process doesn't exist anymore), and another method throws the exception if the Process was not killed. The only weak place in this code is security permissions. Theoretically, the user may not have permissions to kill the process, but in 99.99% of all cases, user has such permissions. I also tested it with a guest account - it works perfectly.

So, your code, working with Excel, can look like this:

int hWnd = xl.Application.Hwnd;
// ...
// here we try to close Excel as usual, with xl.Quit(),
// Marshal.FinalReleaseComObject(xl) and so on
// ...
TryKillProcessByMainWindowHwnd(hWnd);

Voila! Excel is terminated! :)

Ok, let's go back to the second solution, as I promised in the beginning of the post. The second solution is to call GC.Collect() and GC.WaitForPendingFinalizers(). Yes, they actually work, but you need to be careful here!
Many people say (and I said) that calling GC.Collect() doesn't help. But the reason it wouldn't help is if there are still references to COM objects! One of the most popular reasons for GC.Collect() not being helpful is running the project in Debug-mode. In debug-mode objects that are not really referenced anymore will not be garbage collected until the end of the method.
So, if you tried GC.Collect() and GC.WaitForPendingFinalizers() and it didn't help, try to do the following:

1) Try to run your project in Release mode and check if Excel closed correctly

2) Wrap the method of working with Excel in a separate method. So, instead of something like this:

void GenerateWorkbook(...)
{
  ApplicationClass xl;
  Workbook xlWB;
  try
  {
    xl = ...
    xlWB = xl.Workbooks.Add(...);
    ...
  }
  finally
  {
    ...
    Marshal.ReleaseComObject(xlWB)
    ...
    GC.Collect();
    GC.WaitForPendingFinalizers();
  }
}

you write:

void GenerateWorkbook(...)
{
  try
  {
    GenerateWorkbookInternal(...);
  }
  finally
  {
    GC.Collect();
    GC.WaitForPendingFinalizers();
  }
}

private void GenerateWorkbookInternal(...)
{
  ApplicationClass xl;
  Workbook xlWB;
  try
  {
    xl = ...
    xlWB = xl.Workbooks.Add(...);
    ...
  }
  finally
  {
    ...
    Marshal.ReleaseComObject(xlWB)
    ...
  }
}

Now, Excel will close =)

Solution 4:

UPDATE: Added C# code, and link to Windows Jobs

I spent sometime trying to figure out this problem, and at the time XtremeVBTalk was the most active and responsive. Here is a link to my original post, Closing an Excel Interop process cleanly, even if your application crashes. Below is a summary of the post, and the code copied to this post.

• Closing the Interop process with Application.Quit() and Process.Kill() works for the most part, but fails if the applications crashes catastrophically. I.e. if the app crashes, the Excel process will still be running loose.
• The solution is to let the OS handle the cleanup of your processes through Windows Job Objects using Win32 calls. When your main application dies, the associated processes (i.e. Excel) will get terminated as well.

I found this to be a clean solution because the OS is doing real work of cleaning up. All you have to do is register the Excel process.

Windows Job Code

Wraps the Win32 API Calls to register Interop processes.

public enum JobObjectInfoType
{
    AssociateCompletionPortInformation = 7,
    BasicLimitInformation = 2,
    BasicUIRestrictions = 4,
    EndOfJobTimeInformation = 6,
    ExtendedLimitInformation = 9,
    SecurityLimitInformation = 5,
    GroupInformation = 11
}

[StructLayout(LayoutKind.Sequential)]
public struct SECURITY_ATTRIBUTES
{
    public int nLength;
    public IntPtr lpSecurityDescriptor;
    public int bInheritHandle;
}

[StructLayout(LayoutKind.Sequential)]
struct JOBOBJECT_BASIC_LIMIT_INFORMATION
{
    public Int64 PerProcessUserTimeLimit;
    public Int64 PerJobUserTimeLimit;
    public Int16 LimitFlags;
    public UInt32 MinimumWorkingSetSize;
    public UInt32 MaximumWorkingSetSize;
    public Int16 ActiveProcessLimit;
    public Int64 Affinity;
    public Int16 PriorityClass;
    public Int16 SchedulingClass;
}

[StructLayout(LayoutKind.Sequential)]
struct IO_COUNTERS
{
    public UInt64 ReadOperationCount;
    public UInt64 WriteOperationCount;
    public UInt64 OtherOperationCount;
    public UInt64 ReadTransferCount;
    public UInt64 WriteTransferCount;
    public UInt64 OtherTransferCount;
}

[StructLayout(LayoutKind.Sequential)]
struct JOBOBJECT_EXTENDED_LIMIT_INFORMATION
{
    public JOBOBJECT_BASIC_LIMIT_INFORMATION BasicLimitInformation;
    public IO_COUNTERS IoInfo;
    public UInt32 ProcessMemoryLimit;
    public UInt32 JobMemoryLimit;
    public UInt32 PeakProcessMemoryUsed;
    public UInt32 PeakJobMemoryUsed;
}

public class Job : IDisposable
{
    [DllImport("kernel32.dll", CharSet = CharSet.Unicode)]
    static extern IntPtr CreateJobObject(object a, string lpName);

    [DllImport("kernel32.dll")]
    static extern bool SetInformationJobObject(IntPtr hJob, JobObjectInfoType infoType, IntPtr lpJobObjectInfo, uint cbJobObjectInfoLength);

    [DllImport("kernel32.dll", SetLastError = true)]
    static extern bool AssignProcessToJobObject(IntPtr job, IntPtr process);

    private IntPtr m_handle;
    private bool m_disposed = false;

    public Job()
    {
        m_handle = CreateJobObject(null, null);

        JOBOBJECT_BASIC_LIMIT_INFORMATION info = new JOBOBJECT_BASIC_LIMIT_INFORMATION();
        info.LimitFlags = 0x2000;

        JOBOBJECT_EXTENDED_LIMIT_INFORMATION extendedInfo = new JOBOBJECT_EXTENDED_LIMIT_INFORMATION();
        extendedInfo.BasicLimitInformation = info;

        int length = Marshal.SizeOf(typeof(JOBOBJECT_EXTENDED_LIMIT_INFORMATION));
        IntPtr extendedInfoPtr = Marshal.AllocHGlobal(length);
        Marshal.StructureToPtr(extendedInfo, extendedInfoPtr, false);

        if (!SetInformationJobObject(m_handle, JobObjectInfoType.ExtendedLimitInformation, extendedInfoPtr, (uint)length))
            throw new Exception(string.Format("Unable to set information.  Error: {0}", Marshal.GetLastWin32Error()));
    }

    #region IDisposable Members

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    #endregion

    private void Dispose(bool disposing)
    {
        if (m_disposed)
            return;

        if (disposing) {}

        Close();
        m_disposed = true;
    }

    public void Close()
    {
        Win32.CloseHandle(m_handle);
        m_handle = IntPtr.Zero;
    }

    public bool AddProcess(IntPtr handle)
    {
        return AssignProcessToJobObject(m_handle, handle);
    }

}

Note about Constructor code

• In the constructor, the info.LimitFlags = 0x2000; is called. 0x2000 is the JOB_OBJECT_LIMIT_KILL_ON_JOB_CLOSE enum value, and this value is defined by MSDN as:

Causes all processes associated with the job to terminate when the last handle to the job is closed.

Extra Win32 API Call to get the Process ID (PID)

    [DllImport("user32.dll", SetLastError = true)]
    public static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

Using the code

    Excel.Application app = new Excel.ApplicationClass();
    Job job = new Job();
    uint pid = 0;
    Win32.GetWindowThreadProcessId(new IntPtr(app.Hwnd), out pid);
    job.AddProcess(Process.GetProcessById((int)pid).Handle);