CODEWITHSUNDEEP
c#exceloffice-interop
d ei
d ei

Reputation: 555

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

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:

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.

Upvotes: 3

Views: 1532

Answers (1)

d ei
d ei

Reputation: 555

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.

Upvotes: 2

Related Questions