How do I properly clean up Excel interop objects?

Question

I'm using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause:

while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();

Although this kind of works, the Excel.exe process is still in the background even after I close Excel. It is only released once my application is manually closed.

What am I doing wrong, or is there an alternative to ensure interop objects are properly disposed of?

Answer 1

Common developers, none of your solutions worked for me, so I decide to implement a new trick.

First let specify "What is our goal?" => "Not to see excel object after our job in task manager"

Ok. Let no to challenge and start destroying it, but consider not to destroy other instance os Excel which are running in parallel.

So , get the list of current processors and fetch PID of EXCEL processes , then once your job is done, we have a new guest in processes list with a unique PID ,find and destroy just that one.

< keep in mind any new excel process during your excel job will be detected as new and destroyed > < A better solution is to capture PID of new created excel object and just destroy that>

Process[] prs = Process.GetProcesses();
List<int> excelPID = new List<int>();
foreach (Process p in prs)
   if (p.ProcessName == "EXCEL")
       excelPID.Add(p.Id);

.... // your job 

prs = Process.GetProcesses();
foreach (Process p in prs)
   if (p.ProcessName == "EXCEL" && !excelPID.Contains(p.Id))
       p.Kill();

This resolves my issue, hope yours too.

Answer 2

The accepted answer here is correct, but also take note that not only "two dot" references need to be avoided, but also objects that are retrieved via the index. You also do not need to wait until you are finished with the program to clean up these objects, it's best to create functions that will clean them up as soon as you're finished with them, when possible. Here is a function I created that assigns some properties of a Style object called xlStyleHeader:

public Excel.Style xlStyleHeader = null;

private void CreateHeaderStyle()
{
    Excel.Styles xlStyles = null;
    Excel.Font xlFont = null;
    Excel.Interior xlInterior = null;
    Excel.Borders xlBorders = null;
    Excel.Border xlBorderBottom = null;

    try
    {
        xlStyles = xlWorkbook.Styles;
        xlStyleHeader = xlStyles.Add("Header", Type.Missing);

        // Text Format
        xlStyleHeader.NumberFormat = "@";

        // Bold
        xlFont = xlStyleHeader.Font;
        xlFont.Bold = true;

        // Light Gray Cell Color
        xlInterior = xlStyleHeader.Interior;
        xlInterior.Color = 12632256;

        // Medium Bottom border
        xlBorders = xlStyleHeader.Borders;
        xlBorderBottom = xlBorders[Excel.XlBordersIndex.xlEdgeBottom];
        xlBorderBottom.Weight = Excel.XlBorderWeight.xlMedium;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        Release(xlBorderBottom);
        Release(xlBorders);
        Release(xlInterior);
        Release(xlFont);
        Release(xlStyles);
    }
}

private void Release(object obj)
{
    // Errors are ignored per Microsoft's suggestion for this type of function:
    // http://support.microsoft.com/default.aspx/kb/317109
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
    }
    catch { } 
}

Notice that I had to set xlBorders[Excel.XlBordersIndex.xlEdgeBottom] to a variable in order to clean that up (Not because of the two dots, which refer to an enumeration which does not need to be released, but because the object I'm referring to is actually a Border object that does need to be released).

This sort of thing is not really necessary in standard applications, which do a great job of cleaning up after themselves, but in ASP.NET applications, if you miss even one of these, no matter how often you call the garbage collector, Excel will still be running on your server.

It requires a lot of attention to detail and many test executions while monitoring the Task Manager when writing this code, but doing so saves you the hassle of desperately searching through pages of code to find the one instance you missed. This is especially important when working in loops, where you need to release EACH INSTANCE of an object, even though it uses the same variable name each time it loops.

Answer 3

Make sure that you release all objects related to Excel!

I spent a few hours by trying several ways. All are great ideas but I finally found my mistake: If you don't release all objects, none of the ways above can help you like in my case. Make sure you release all objects including range one!

Excel.Range rng = (Excel.Range)worksheet.Cells[1, 1];
worksheet.Paste(rng, false);
releaseObject(rng);

The options are together here.

Answer 4

First - you never have to call Marshal.ReleaseComObject(...) or Marshal.FinalReleaseComObject(...) when doing Excel interop. It is a confusing anti-pattern, but any information about this, including from Microsoft, that indicates you have to manually release COM references from .NET is incorrect. The fact is that the .NET runtime and garbage collector correctly keep track of and clean up COM references. For your code, this means you can remove the whole `while (...) loop at the top.

Second, if you want to ensure that the COM references to an out-of-process COM object are cleaned up when your process ends (so that the Excel process will close), you need to ensure that the garbage collector runs. You do this correctly with calls to GC.Collect() and GC.WaitForPendingFinalizers(). Calling this twice is safe, and ensures that cycles are definitely cleaned up too (though I'm not sure it's needed, and would appreciate an example that shows this).

Third, when running under the debugger, local references will be artificially kept alive until the end of the method (so that local variable inspection works). So GC.Collect() calls are not effective for cleaning object like rng.Cells from the same method. You should split the code doing the COM interop from the GC cleanup into separate methods. (This was a key discovery for me, from one part of the answer posted here by @nightcoder.)

The general pattern would thus be:

Sub WrapperThatCleansUp()

    ' NOTE: Don't call Excel objects in here... 
    '       Debugger would keep alive until end, preventing GC cleanup

    ' Call a separate function that talks to Excel
    DoTheWork()

    ' Now let the GC clean up (twice, to clean up cycles too)
    GC.Collect()    
    GC.WaitForPendingFinalizers()
    GC.Collect()    
    GC.WaitForPendingFinalizers()

End Sub

Sub DoTheWork()
    Dim app As New Microsoft.Office.Interop.Excel.Application
    Dim book As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Add()
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = book.Worksheets("Sheet1")
    app.Visible = True
    For i As Integer = 1 To 10
        worksheet.Cells.Range("A" & i).Value = "Hello"
    Next
    book.Save()
    book.Close()
    app.Quit()

    ' NOTE: No calls the Marshal.ReleaseComObject() are ever needed
End Sub

There is a lot of false information and confusion about this issue, including many posts on MSDN and on Stack Overflow (and especially this question!).

What finally convinced me to have a closer look and figure out the right advice was blog post Marshal.ReleaseComObject Considered Dangerous together with finding the issue with references kept alive under the debugger that was confusing my earlier testing.

Answer 5

The accepted answer did not work for me. The following code in the destructor did the job.

if (xlApp != null)
{
    xlApp.Workbooks.Close();
    xlApp.Quit();
}

System.Diagnostics.Process[] processArray = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process process in processArray)
{
    if (process.MainWindowTitle.Length == 0) { process.Kill(); }
}

Answer 6

Anything that is in the Excel namespace needs to be released. Period

You can't be doing:

Worksheet ws = excel.WorkBooks[1].WorkSheets[1];

You have to be doing

Workbooks books = excel.WorkBooks;
Workbook book = books[1];
Sheets sheets = book.WorkSheets;
Worksheet ws = sheets[1];

followed by the releasing of the objects.