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 some have probably already written, it's not just important how you close the Excel (object); it's also important how you open it and also by the type of the project.

In a WPF application, basically the same code is working without or with very few problems.

I have a project in which the same Excel file is being processed several times for different parameter value - e.g. parsing it based on values inside a generic list.

I put all Excel-related functions into the base class, and parser into a subclass (different parsers use common Excel functions). I didn't want that Excel is opened and closed again for each item in a generic list, so I've opened it only once in the base class and close it in the subclass. I had problems when moving the code into a desktop application. I've tried many of the above mentioned solutions. GC.Collect() was already implemented before, twice as suggested.

Then I've decided that I will move the code for opening Excel to a subclass. Instead of opening only once, now I create a new object (base class) and open Excel for every item and close it at the end. There is some performance penalty, but based on several tests Excel processes are closing without problems (in debug mode), so also temporary files are removed. I will continue with testing and write some more if I will get some updates.

The bottom line is: You must also check the initialize code, especially if you have many classes, etc.

Answer 2

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

There are different ways and advices 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 offense 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 a 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 exception if the Process could not be killed (for example the process doesn't exist anymore), and another method throws exception if the Process was not killed. The only weak place in this code is security permissions. Theoretically, 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 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 =)

Answer 3

This worked for a project I was working on:

excelApp.Quit();
Marshal.ReleaseComObject (excelWB);
Marshal.ReleaseComObject (excelApp);
excelApp = null;

We learned that it was important to set every reference to an Excel COM object to null when you were done with it. This included Cells, Sheets, and everything.

Answer 4

You need to be aware that Excel is very sensitive to the culture you are running under as well.

You may find that you need to set the culture to EN-US before calling Excel functions. This does not apply to all functions - but some of them.

    CultureInfo en_US = new System.Globalization.CultureInfo("en-US"); 
    System.Threading.Thread.CurrentThread.CurrentCulture = en_US;
    string filePathLocal = _applicationObject.ActiveWorkbook.Path;
    System.Threading.Thread.CurrentThread.CurrentCulture = orgCulture;

This applies even if you are using VSTO.

For details: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320369

Answer 5

After trying

1. Release COM objects in reverse order
2. Add GC.Collect() and GC.WaitForPendingFinalizers() twice at the end
3. No more than two dots
4. Close workbook and quit application
5. Run in release mode

the final solution that works for me is to move one set of

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

that we added to the end of the function to a wrapper, as follows:

private void FunctionWrapper(string sourcePath, string targetPath)
{
    try
    {
        FunctionThatCallsExcel(sourcePath, targetPath);
    }
    finally
    {
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}

Answer 6

I followed this exactly... But I still ran into issues 1 out of 1000 times. Who knows why. Time to bring out the hammer...

Right after the Excel Application class is instantiated I get a hold of the Excel process that was just created.

excel = new Microsoft.Office.Interop.Excel.Application();
var process = Process.GetProcessesByName("EXCEL").OrderByDescending(p => p.StartTime).First();

Then once I've done all the above COM clean-up, I make sure that process isn't running. If it is still running, kill it!

if (!process.HasExited)
   process.Kill();