Two Useful Classes When Using Excel COM Interop


There are two known problems when working with Excel Interop.

  1. After having used Excel and shut down your application, an Excel process still “hangs” in background.
  2. When your Excel installation is English (en-US) only, but your Windows is configured for a different language (e.g. de-DE), some calls to the Interop fail with a COMException.

Luckily there are also workarounds for this problem which you can easily find when googling for them. The following classes have been handy for me to encapsulate these workarounds. Both providing IDisposable.

This way, you can use them as follows:

Excel.Workbooks myWorkbooks
        = myExcelApplication.Workbooks;
Excel.Workbook myWorkbook;

using (new Disposer(myWorkbooks))
    using (new EnUsCulture())
        myWorkbook = myWorkbooks.Open(fileName, [...]);

The Disposer takes care of properly disposing of the COM object. The EnUsCulture switches the current thread’s culture to en-US right on creation. On Dispose(), it switches the culture back to what it was before. Using “using” you make sure the thread’s culture is switched back even if an error occurs.

Disposer:

    public class Disposer : IDisposable
    {
        private object _comObject;

        public Disposer(object comObject)
        {
            _comObject = comObject;
        }

        public void Dispose()
        {
            if (_comObject != null)
            {
                Marshal.ReleaseComObject(_comObject);
                _comObject = null;
                GC.Collect();
            }
        }
    }

 
EnUsCulture:

    public class EnUsCulture : IDisposable
    {
        private CultureInfo _oldCulture;

        public EnUsCulture()
        {
            if (Thread.CurrentThread.CurrentCulture
                .Equals(new CultureInfo("en-US")))
            {
                _oldCulture = null;
            }
            else
            {
                _oldCulture = Thread.CurrentThread.CurrentCulture;
                Thread.CurrentThread.CurrentCulture
                    = new CultureInfo("en-US");
            }
        }

        public void Dispose()
        {
            if (_oldCulture != null)
                Thread.CurrentThread.CurrentCulture = _oldCulture;
        }
    }

Note that the Dispose() method only resets the thread’s culture if it wasn’t already en-US before. This is to prevent the following situation:

    EnUsCulture c1 = new EnUsCulture(); // switch from de-DE to en-US
    EnUsCulture c2 = new EnUsCulture(); // switch from en-US to en-US
    c1.Dispose(); // switch back to de-DE
    c2.Dispose(); // switch back to en-US

The thread would end up with the en-US culture if c2 is disposed after c1. To prevent that, we make c2 notice on its creation that the culture has already been switched before, so it won’t have any effect at all:

    EnUsCulture c1 = new EnUsCulture(); // switch from de-DE to en-US
    EnUsCulture c2 = new EnUsCulture(); // no effect
    c1.Dispose(); // switch back to de-DE
    c2.Dispose(); // no effect
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s