2
votes

I'm reading/writing an excel file using Interop library like so

(Update: this isn't a duplicate of Why does Microsoft.Office.Interop.Excel.Application.Quit() leave the background process running?, this question is about how to release excel from memory when there is an exception)

var myExcelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbooks AllWorkbooks= xlApp.Workbooks;
Excel.Workbook MyWorkBook= //Open workbook
Excel.Worksheet MySheet= //Open worksheet
//Do the work
MyWorkBook.Close(false, file);
myExcelApp.Quit();
Marshal.FinalReleaseComObject(myExcelApp );
//FInalReleaseComObject for MyWorkBooks

This works OK except when an exception is thrown, and then Excel lingers on in the background and has to be killed off using the task manager.

I'm not sure what the best way is to release these COM objects. Should I use the using blocks, or should I enclose everything in try catch like this?

Excel.Workbooks AllWorkbooks= null;
Excel.Workbook MyWorkBook=null;
Excel.Worksheet MyWorkshet=null;
Excel.Application myExcelApp=null;         
try{
//Read file here
}
catch{
    MyWorkBook?.Close(false, file);
    myExcelApp?.Quit();
    Marshal.FinalReleaseComObject(myExcelApp );
    //FInalReleaseComObject for MyWorkBooks
}

Is the above the correct approach? I have to initialize excel objects as null, because they cannot be newed up.

Thanks

There are two main schools of thought. One is you should ReleaseComObject on every object - which will work and is the most efficient, but is more tedious. The other school of thought is to call GC.Collect multiple times - which is simpler and basically reliable if you call it enough. Which school are you interested in (so I can choose the best duplicate)?mjwills
@mjwills I'm the OP. I'm ok with calling ReleaseComObject for each object, but it seems that excel lingers on in the background if there is an exception with my code above. It quits fine if there is no exceptionLePrinceDeDhump
It lingers on because you haven't called ReleaseComObject on everything. Getting it to work 100% can be hard work. If you want the easy answer (school two) then check out stackoverflow.com/a/38111294/34092 (I disagree with this approach, but it does generally work).mjwills
@vc74 stackoverflow.com/a/118776/34092 - note Rule 2.mjwills