How to Properly Clean Up Excel Interop Objects

How do I properly clean up Excel interop objects?

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

How do I properly clean up Excel interop objects from an C# application?

As my use of the C# Excel interop got more sophisticated, I began having headless copies of 'Microsoft Office Excel (32 bit)' objects running in Task Manager after I closed my app down. I found no combination of voodoo Marshal.ReleaseComObject() and GC.Collect() that would completely eliminate them. I finally removed all the voodoo code and followed Hans Passent's advice. I was able to terminate them under most circumstances when the app closed by using the following pattern:

using System;
using System.IO;
using excel = Microsoft.Office.Interop.Excel;

namespace ExcelInterop {
    static class Program {
        // Create only one instance of excel.Application(). More instances create more Excel objects in Task Manager.
        static excel.Application ExcelApp { get; set; } = new excel.Application();

        [STAThread]
        static int Main() {
            try {
                ExcelRunner excelRunner = new ExcelRunner(ExcelApp)
                // do your Excel interop activities in your excelRunner class here
                // excelRunner MUST be out-of-scope when the finally clause executes
                excelRunner = null;  // not really necessary but kills the only reference to excelRunner 
            } catch (Exception e) {
                // A catch block is required to ensure that the finally block excutes after an unhandled exception
                // see: https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/try-finally
                Console.WriteLine($"ExcelRunner terminated with unhandled Exception: '{e.Message}'");
                return -1;
            } finally {
                // this must not execute until all objects derived from 'ExcelApp' are out of scope
                if (ExcelApp != null) {
                    ExcelApp.Quit();
                    ExcelApp = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
            }
            Console.WriteLine("ExcelRunner terminated normally");
            return 0;
        }
    }
}

In my ExcelRunner class I'm reading hundreds of csv files into excel Workbooks, and creating dozens of .xlsx files with tables and charts. I create only one instance of Microsoft.Office.Interop.Excel.Application() and reuse it over and over. More instances mean more 'Microsoft Office Excel' objects running in Task Manager that need to be cleaned up.

Note that the finally clause must execute to get rid of the headless Excel objects. The pattern above handles most app shutdown situations (including most aborts caused by unhandled exceptions - but see Does the C# "finally" block ALWAYS execute?). One notable exception occurs when you abort the app from the the VS debugger (Shift-F5 or the red 'Stop Debugging' square on the toolbar). If you abort the app from the debugger, the finally clause does not execute and an Excel object is left runnning. This is unfortunate, but I have found no way around it.

I tested this in Visual Studio 2019 and .NET Framework 4.7.2 using Excel 2007 interop and Excel 2016 interop.

How do I properly clean up Excel interop objects?

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

Clean up Excel Interop Objects with IDisposable

Are there any disadvantages using the IDisposable Interace

Sure, it accomplishes absolutely nothing. Using Using or calling Dispose() is never an appropriate way to set a variable to Nothing. Which is all that your code does.

We completely avoid to use the two dot rule.

Feel free to continue to ignore it, it is nonsense and causes nothing but grief. The blog author's implied assertion is that doing so would force the programmer to use a variable to store the value of xlApp.Workbooks. So he'd have a fighting chance, later, to not forget to call releaseObject(). But there are many more statements that produce an interface reference that don't use dots. Something like Range(x,y), there's a hidden Range object reference there that you'll never see. Having to store them as well just produces incredibly convoluted code.

And overlooking just one is enough to completely fail to get the job done. Utterly impossible to debug. This is the kind of code that C programmers have to write. And often failed at miserably, large C programs often leak memory and their programmers spend a great deal of time finding those leaks. Not the .NET way of course, it has a garbage collector to do this automatically. It never gets it wrong.

Trouble is, it is a bit slow at taking care of the job. Very much by design. Nobody ever notices this, except in this kind of code. You can see that the garbage collector didn't run, you still see the Office program running. It didn't quit when you wrote xlapp.Quit(), it is still present in the Processes tab of Task Manager. What they want to happen is for it to quit when they say so.

That's very possible in .NET, you can certainly force the GC to get the job done:

GC.Collect()
GC.WaitForPendingFinalizers()

Boom, every Excel object reference gets released automatically. There is no need to store these object references yourself and explicitly call Marshal.ReleaseComObject(), the CLR does it for you. And it never gets it wrong, it doesn't use or need a "two dot rule" and it has no trouble finding those hidden interface references back.

---

What matters a great deal however is exactly where you put this code. And most programmers put it in the wrong place, in the same method that used those Excel interfaces. Which is fine, but does not work when you debug the code, a quirk that's explained in this answer. The proper way to do it in the blog author's code is to move the code into a little helper method, let's call it DoExcelThing(). Like this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DoExcelThing()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    '' Excel.exe no longer running anymore at this point
End Sub

And do keep in mind that this is truly all just a debugging artifact. Programmers just hate to have to use Task Manager to kill the zombie Excel.exe instances. Zombified when they stopped the debugger, preventing the program from exiting normally and collect garbage. This is normal. It will also happen when your program dies in production for any kind of reason. Put your energy where it belongs, getting the bugs out of your code so your program won't die. The GC doesn't need more help than that.

How to properly clean up interop objects in C#

It's definitely more important to handle releases properly when dealing with Office applications than many other COM libraries, for two reasons.

1. The Office apps run as out of process servers, not in-proc libraries. If you fail to clean up properly, you leave a process running.
2. Office apps (especially Excel IIRC) don't terminate properly even if you call Application.Quit, if there are outstanding references to its COM objects.

For regular, in-proc COM libraries, the consequences of failing to clean up properly are not so dramatic. When your process exits, all in-proc libraries goes away with it. And if you forget to call ReleaseComObject on an object when you no longer need it, it will still be taken care of eventually when the object gets finalized.

That said, this is no excuse to write sloppy code.

Need Clarification On Answer For Cleaning Up Excel Interop Objects Properly

I was in the habit of releasing objects as soon as I was done with them to minimize memory usage while the macro ran. Should I be concerned with cleaning up objects as I go or is it ok to just do a cleanup at the end of the macro like Hans suggested?

As a general rule, release any resource as soon as you are done with it. The more expensive and/or scarce the resource, the more important that becomes. That is true of database connections, memory, file handles, etc.

It is also valid for references to COM objects.

Having said that, performing

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

is a sensible thing to do in order to release any references that your as-soon-as-feasible cleanup may have caught.

There is a counter-argument to be made, which you can apply using common sense and looking at your own situation. You end up writing more code to clean up every COM reference as you go. If you are not creating an undue amount of COM objects before reaching a point where you can clean up, it might be worth opting for simpler code (e.g. using double-dots), knowing that you are temporarily consuming more resources than you absolutely need to.

Using Wrapper objects to Properly clean up excel interop objects

Is it impossible/bad/dangerous to do the ReleaseCOMObject in the destructor? (I've only seen proposals to put it in a Dispose() rather than in a destructor - why?)

It is recommended not to put your clean up code in the finalizer because unlike the destructor in C++ it is not called deterministically. It might be called shortly after the object goes out of scope. It might take an hour. It might never be called. In general if you want to dispose unmanaged objects you should use the IDisposable pattern and not the finalizer.

This solution that you linked to attempts to work around that problem by explicitly calling the garbage collector and waiting for the finalizers to complete. This is really not recommended in general but for this particular situation some people consider it to be an acceptable solution due to the difficulty of keeping track of all the temporary unmanaged objects that get created. But explicitly cleaning up is the proper way of doing it. However given the difficulty of doing so, this "hack" may be acceptable. Note that this solution is probably better than the idea you proposed.

If instead you want to try to explicitly clean up, the "don't use two dots with COM objects" guideline will help you to remember to keep a reference to every object you create so that you can clean them up when you're done.

COM object excel interop clean up

You'll have to release all local objects manually in the scope where you create them. When using Office applications through Automation, don't rely on the garbage collector to clean up these objects - even if it gets it right, it may take time for it to kick in and you may end up with temporary objects holding references to other objects that you think are already gone.

This is a somewhat related question with more details that may apply to you if you try to run Excel from your application with Excel being hidden.

The part that's definitely relevant to you is this:

• Wrap every single function that uses Excel in a try..catch block to capture any possible exception.
• Always explicitly release all Excel objects by calling Marshal.ReleaseComObject() and then setting your variables to null as soon as you don't need them. Always release these objects in a finally block to make sure that a failed Excel method call won't result in a dangling COM object.
• When an error happens, close the Excel instance that you're using. It's not likely that you can recover from Excel-related errors and the longer you keep the instance, the longer it uses resources.
• When you quit Excel, make sure that you guard that code against recursive calls - if your exception handlers try to shut down Excel while your code is already in the process of shutting down Excel, you'll end up with a dead Excel instance.
• Call GC.Collect() and GC.WaitForPendingFinalizers() right after calling the Application.Quit() method to make sure that the .NET Framework releases all Excel COM objects immediately.

Edit: this is after you added more details to your question.

In otherComponent you don't need to release the Workbook and Document objects. These two objects are created in your first object which implies that the first object is the owner. Since it's the first object that owns your top-level Excel objects (assuming you also have an Application object somewhere), your first object can call otherComponent, pass in Workbook and Document and then on return, clean them up. If you never use any of these objects in your MainComponent, then you should create the Excel-related objects inside otherComponent and clean them up there.

With COM interop, you should create your COM objects as close to the place where you need them and release them explicitly as soon as you can. This is especially true for Office applications.

I made this class to make using COM objects easier: this wrapper is disposable, so you can use using(...) with your COM objects - when the using scope is over, the wrapper releases the COM object.

using System;
using System.Runtime.InteropServices;

namespace COMHelper
{
    /// <summary>
    /// Disposable wrapper for COM interface pointers.
    /// </summary>
    /// <typeparam name="T">COM interface type to wrap.</typeparam>
    public class ComPtr<T> : IDisposable
    {
        private object m_oObject;
        private bool m_bDisposeDone = false;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="oObject"></param>
        public ComPtr ( T oObject )
        {
            if ( oObject == null )
                throw ( new ArgumentNullException ( "Invalid reference for ComPtr (cannot be null)" ) );

            if ( !( Marshal.IsComObject ( oObject ) ) )
                throw ( new ArgumentException ( "Invalid type for ComPtr (must be a COM interface pointer)" ) );

            m_oObject = oObject;
        }

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="oObject"></param>
        public ComPtr ( object oObject ) : this ( (T) oObject )
        {
        }

        /// <summary>
        /// Destructor
        /// </summary>
        ~ComPtr ()
        {
            Dispose ( false );
        }

        /// <summary>
        /// Returns the wrapped object.
        /// </summary>
        public T Object
        {
            get
            {
                return ( (T) m_oObject );
            }
        }

        /// <summary>
        /// Implicit cast to type T.
        /// </summary>
        /// <param name="oObject">Object to cast.</param>
        /// <returns>Returns the ComPtr object cast to type T.</returns>
        public static implicit operator T ( ComPtr<T> oObject )
        {
            return ( oObject.Object );
        }

        /// <summary>
        /// Frees up resources.
        /// </summary>
        public void Dispose ()
        {
            Dispose ( true );
            GC.SuppressFinalize ( this );
        }

        /// <summary>
        /// Frees up resurces used by the object.
        /// </summary>
        /// <param name="bDispose">When false, the function is called from the destructor.</param>
        protected void Dispose ( bool bDispose )
        {
            try
            {
                if ( !m_bDisposeDone && ( m_oObject != null ) )
                {
                    Marshal.ReleaseComObject ( m_oObject );
                    m_oObject = null;
                }
            }
            finally
            {
                m_bDisposeDone = true;
            }
        }
    }
}

---

Related Topics