Advertisement
Advertisement


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?

2016/12/26
1
754
12/26/2016 2:28:27 PM

Accepted Answer

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

2018/10/26
689
10/26/2018 9:28:11 AM

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

2015/05/18

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 =)

2019/02/09

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);
2012/10/16

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.

2020/04/23

First - you never have to call Marshal.ReleaseComObject(...) or Marshal.FinalReleaseComObject(...) when doing Excel interop. It is a confusing anti-pattern, but any information about this, including from Microsoft, that indicates you have to manually release COM references from .NET is incorrect. The fact is that the .NET runtime and garbage collector correctly keep track of and clean up COM references. For your code, this means you can remove the whole `while (...) loop at the top.

Second, if you want to ensure that the COM references to an out-of-process COM object are cleaned up when your process ends (so that the Excel process will close), you need to ensure that the garbage collector runs. You do this correctly with calls to GC.Collect() and GC.WaitForPendingFinalizers(). Calling this twice is safe, and ensures that cycles are definitely cleaned up too (though I'm not sure it's needed, and would appreciate an example that shows this).

Third, when running under the debugger, local references will be artificially kept alive until the end of the method (so that local variable inspection works). So GC.Collect() calls are not effective for cleaning object like rng.Cells from the same method. You should split the code doing the COM interop from the GC cleanup into separate methods. (This was a key discovery for me, from one part of the answer posted here by @nightcoder.)

The general pattern would thus be:

Sub WrapperThatCleansUp()

    ' NOTE: Don't call Excel objects in here... 
    '       Debugger would keep alive until end, preventing GC cleanup

    ' Call a separate function that talks to Excel
    DoTheWork()

    ' Now let the GC clean up (twice, to clean up cycles too)
    GC.Collect()    
    GC.WaitForPendingFinalizers()
    GC.Collect()    
    GC.WaitForPendingFinalizers()

End Sub

Sub DoTheWork()
    Dim app As New Microsoft.Office.Interop.Excel.Application
    Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = book.Worksheets("Sheet1")
    app.Visible = True
    For i As Integer = 1 To 10
        worksheet.Cells.Range("A" & i).Value = "Hello"
    Next
    book.Save()
    book.Close()
    app.Quit()

    ' NOTE: No calls the Marshal.ReleaseComObject() are ever needed
End Sub

There is a lot of false information and confusion about this issue, including many posts on MSDN and on Stack Overflow (and especially this question!).

What finally convinced me to have a closer look and figure out the right advice was blog post Marshal.ReleaseComObject Considered Dangerous together with finding the issue with references kept alive under the debugger that was confusing my earlier testing.

2016/12/26

Source: https://stackoverflow.com/questions/158706
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]