3

This question has been asked and answered many times, for example:

How do I properly clean up Excel interop objects?

How to dispose Interop Excel Application and workbook correctly?

Why does Microsoft.Office.Interop.Excel.Application.Quit() leave the background process running?

How can I dispose my Excel Application

But the answers by Hans Passent to the following questions lead me to believe that they are obsolete and/or simply incorrect:

Clean up Excel Interop Objects with IDisposable

Understanding garbage collection in .NET

So, my question is: How do I clean up my Excel interop objects so that all managed and unmanaged Excel resources are released in a timely fashion (i.e. when memory pressure triggers a garbage collection)?

  • In release mode?
  • In debug mode (if we care)?

In release mode:

Is it enough to simply let all managed Excel interop objects go out of scope?

Do I need to call excelApp.Quit() as well?

Will memory pressure on the unmanaged heap trigger a garbage collection? i.e. Do I also need to call:

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

to ensure my managed app doesn't run out of memory? Do I ever need to call: System.Runtime.InteropServices.Marshal.FinalReleaseComObject(managedExcelObject)?

Please do not answer this question unless you have read and understood Hans Passent's answers.

d ei
  • 493
  • 4
  • 16
  • 5
    You'll find that everyone who's ever worked with COM understands those answers and can tell you the same. The solution in most cases is to *not* use Office interop if possible. Use a library like Epplus instead to create real `xlsx` files. – Panagiotis Kanavos Feb 10 '20 at 16:22
  • 4
    I concur. Don't use interop if you don't have to. Use something like the [Open XML SDK](https://learn.microsoft.com/en-us/office/open-xml/open-xml-sdk) instead. – Robert Harvey Feb 10 '20 at 16:23
  • Have you actually written your program and proved that the items highlighted here are a problem? – Neil Feb 10 '20 at 16:24
  • 1
    Having been in the same boat, the interop stuff always ends up being more of a headache than it's worth. `ExcelDataReader` is another worth looking at if you're just trying to load sheets into memory. – Parrish Husband Feb 10 '20 at 16:25
  • It's a shame Andrew Whitechapel's chapter on "interoping" with Office is no longer available on MSDN as he explains things quite clearly. In summary: YES, you need to explicitly quit. YES, you need to set all object to `null`, in the reverse order they were created (and be careful about implicitly creating objects using "dot notation"). YES, you should perform garbage collection - *twice*. Theoretically, it should not be necessary to use Release; if you do, you need to understand the implications. It might be worthwhile to buy the book... – Cindy Meister Feb 10 '20 at 17:49
  • @Neil Yes, I've written an app, but no, I don't have evidence that it's cleanup is a problem. As a last step in writing the app, l looked up how to clean up my Excel resources. I read the many answers and had the impression that most were voodoo or expressing cargo cult programming 'knowledge'. When I came to Hans Passent's answers, and he explicitly stated what I had been thinking, I decided to create this question to get a definitive answer. – d ei Feb 10 '20 at 20:47
  • @Cindy Meister Then the need to invoke the garbage collector is not just to mitigate a debugger artifact as Hans Passent asserts? – d ei Feb 10 '20 at 21:16
  • There are 2 cases where .Net automatically releases COM objects. The 1st is when the GC cleans up unreferenced COM objects and the 2nd is a lesser known requirement of COM that all references are released when the COM apartment in which they created is destroyed. In .Net a thread will run in either a multi-threaded (MTA) or a static (STA) apartment; an application will only ever have a single MTA apartment but can create multiple STA apartments tied to their respective threads. I recommend using the GC to release COM objects as using a STA thread properly takes a bit of work. – TnTinMn Feb 11 '20 at 01:15
  • The issue of the debugger holding method variables in scope can be addressed by employing the pattern shown in this [post](https://stackoverflow.com/questions/36550446/vb-net-excel-com-object-not-getting-released/36578663#36578663) (it is in VB, but a code converter should provide a close enough translation for you to follow the pattern). Pay attention to the `CleanUp` method as it shows to use `Marshal.AreComObjectsAvailableForCleanup` so that you don't need to follow someone's empirical guess of how many GC cycles are required. – TnTinMn Feb 11 '20 at 01:15
  • 1
    @Robert Harvey I originally planned to write my app in Java. this article: https://stackoverflow.com/questions/38913412/create-bar-chart-in-excel-with-apache-poi led me to believe that charts were not terribly well supported by Apache POI or OpenXML. Consequently, I chose C# and Excel interops in the belief that that it would be complete and easy to use - and it was. My app is a one-off to generate a bunch (150+) of charts for csv data I've captured. I don't really care if it leaks if it completes. The question was really meant to broaden my understanding of the problem. – d ei Feb 11 '20 at 02:09

1 Answers1

1

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://learn.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.

d ei
  • 493
  • 4
  • 16