C# Excel Interop – COM Object Excel Interop Clean Up

c++comexcel-interop

Lets say that I have one component which is doing something with Workbook object and somewhere in the middle of that method body I have call to some method of another class.
For example:

public class MainComponent
{

    public void MyMainMethod()
    {
       OtherComponent otherComponent = new OtherComponent();
       Workbook document;
       // some work with workbook object

       // working with document and worksheet objects.
       otherComponent.MethodCall(document);

       // some work with workbook object and it's worksheets.

       foreach(Worksheet sheet in document.Workheets)
         // do something with sheet
    }
}

public class OtherComponent
{
  public void MethodCall(Workbook document)
  {
    string worksheetNames = "";
    foreach(Worksheet sheet in document.Worksheets)
      worksheetNames += sheet.Name;
    Console.WriteLine(worksheetNames);
  }
}

And in that otherComponent.MethodCall(document); I'm using document and I'm iterating through it's worksheets.

EDIT TO be more concrete on question. Should I call ReleaseCOMObject on document and on Worksheets in otherComponent.MethodCall(document) or not?

I never really had any good explanation on how should I manage this unmanaged code.
I would really appreciate if someone could explain this to me.

Best Answer

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 Solutions

C# Excel Interop – How to Properly Clean Up 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

C# Excel Interop Cleanup – Using Wrapper Objects to Properly Clean Up

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.

Best Answer

Related Solutions

C# Excel Interop – How to Properly Clean Up Objects

C# Excel Interop Cleanup – Using Wrapper Objects to Properly Clean Up

Related Question