CODEWITHSUNDEEP
c#excelinterop
Rick Harby
Rick Harby

Reputation: 11

C# .net Excel Interop leaves Excel process hanging

We use Excel interop in numerous places in our code, however I have one function which doesn't ever seem to close down the Excel process it uses.. I've simplified the code down, and it just seems like whenever I open a workbook in this function it stays hanging around. I've included the code below, I've made sure every object is defined, released and nulled, and yet Excel stays running.

        System.Data.DataTable dtExcelSheet = new System.Data.DataTable();
        Microsoft.Office.Interop.Excel.Application excelObject = new Microsoft.Office.Interop.Excel.Application();

        dtExcelSheet.Columns.Add("SheetName", typeof(string));
        dtExcelSheet.Columns["SheetName"].ReadOnly = false;
        dtExcelSheet.Columns["SheetName"].Caption = "Sheet Name";


        Workbooks wbs = excelObject.Workbooks;

        Workbook excelWorkbook = wbs.Add(excelFile);

        excelWorkbook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        wbs.Close();
        excelObject.Quit();

        int i1 = Marshal.FinalReleaseComObject(excelWorkbook);
        int i2 = Marshal.FinalReleaseComObject(wbs);
        int i3 = Marshal.FinalReleaseComObject(excelObject);


        excelWorkbook = null;
        wbs = null;
        excelObject = null;

        GC.GetTotalMemory(false);
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.GetTotalMemory(true);

Upvotes: 1

Views: 5924

Answers (5)

user195488
user195488

Reputation:

Same thing happened to me the other day. See my question on Excel Automation as to the fix (question deals with multiple row deletion mostly but I also had the same problem as you turns out it has to do with releasing all the COM objects properly).

Upvotes: 0

Jim Killingsworth
Jim Killingsworth

Reputation: 154

I tried running your code, but I could not reproduce the issue. If I step through it with a debugger, the Excel process terminates after the last call to FinalReleaseComObject. Is it possible that the culprit lies in some code not present in your listing?

When using COM interop, I have found that it is all too easy to increment the reference count on COM objects in very subtle ways. For example, let's say you do something like this:

excelWorkbook.Foo.Bar();

This can increment the reference count on the Foo object, leaving you with no means of releasing it afterwards...and leaving the Excel process lingering around until you shut down your app. You can re-write the above line of code like this:

Foo foo = excelWorkbook.Foo;
foo.Bar();
Marshal.ReleaseComObject(foo);

It's not as pretty, but it will decrement the reference count on the Foo object after you are done using it.

Upvotes: 1

Jim L
Jim L

Reputation: 2327

It's been a while since I've mucked around with this stuff, so nothing jumps out at me.

My usual recommendation in these cases is to set your excel application object to Visible = true to see if there's not a dialog popping up on you. Excel/Word will sometimes refuse to shut down if they think there's a modal dialog open no matter what else you may do. It's the first thing to check anyway.

Upvotes: 1

Hans Olsson
Hans Olsson

Reputation: 54999

Complete guess, but does this row:

dtExcelSheet.Columns.Add("SheetName", typeof(string));

return the column that is created?

If so you'd probably need to store that reference and clean that up at the end.

Edit: Also, I don't think you should be setting the variables to null at the end, I think that just accesses them again.

And you shouldn't have to tell the GC to collect etc, but I assume that might be your test code.

Upvotes: 1

Samuel Jack
Samuel Jack

Reputation: 33270

Make sure you're not calling Excel on a background thread. I had a similar problem where I was cleaning up all the COM objects, but Excel still wasn't dying and that turned out to be the problem.

I wrote up my experiences and a solution here.

Upvotes: 1

Related Questions