COM object excel interop clean up

Question

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.

Answer 1

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;
            }
        }
    }
}