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);
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.
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.
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.