C# quit excel appliction after using COM object

Following my last post of how to read and write excel files in C#, which can be found here: http://www.idothink.com/2009/03/c-read-write-excel-spreedsheet.html

I discovered some serious bug. The Excel process didn’t quit sometimes and leaves in the tast manager. Some one suggest that kill the process, but it is a dangerous move.

After some research, I have come up with the following solution.

Firstly, I create the following method to release the comobject.

 

        private void NAR(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                o = null;
            }
        }

Secondly, I put the release call in the finally block, and try to release everything. The reason is that

“When Visual Studio .NET calls a COM object from managed code, it automatically creates a Runtime Callable Wrapper (RCW). The RCW marshals calls between the .NET application and the COM object. The RCW keeps a reference count on the COM object. Therefore, if all references have not been released on the RCW, the COM object does not quit.”

            finally
            {

                if(objBooks != null)
                    objBooks.Close();
                if(objApp != null)
                    objApp.Quit();
                NAR(objSheet);
                NAR(objSheets);
                NAR(objBooks);
                NAR(objBook);
                NAR(objApp);
            }

After doing all these, the problem still wasn’t solved when I automate the whole process and try to have lots of Excel object open and close, few of them are still not release. I then use the very dangerous GC call.

 

                GC.Collect();
                GC.WaitForPendingFinalizers();    

Have Fun. Oh, BTW, the application is now significant slower :)