Excel objects not getting disposed sometimes

Question

I have this piece of code for reading from an excel cell:

 public T GetValue<T>(string testsheet, string range)
    {
        Application excelApplication    = null;
        Workbooks workBooks             = null;
        Workbook activeWorkBook         = null;
        Worksheet activeWorkSheet       = null;

        try
        {
            excelApplication    = new Application();
            workBooks           = excelApplication.Workbooks;
            activeWorkBook      = workBooks.Open(workBook);

            activeWorkSheet     = activeWorkBook.ActiveSheet;
            var cells           = activeWorkSheet.get_Range(range);
            return cells.Value2;

        }
        catch (Exception theError)
        {
            Console.WriteLine(theError.Message);
            throw theError;
        }
        finally
        {         
            ReleaseComObject(activeWorkSheet);
            ReleaseComObject(activeWorkBook);
            ReleaseComObject(workBooks);
            ReleaseComObject(excelApplication);
        }            
    }

Also I have a Set value method which sets the value to the cell as below:

public void SetValue<T>(string testsheet, string range, T value)
    {
        Application excelApplication = null;
        Workbooks workBooks = null;
        Workbook activeWorkBook = null;
        Worksheet activeWorkSheet = null;

        try
        {
            excelApplication = new Application();
            workBooks = excelApplication.Workbooks;
            activeWorkBook = workBooks.Open(workBook);

            activeWorkSheet = activeWorkBook.ActiveSheet;
            var cells = activeWorkSheet.get_Range(range);
            cells.Value2 = value;
            activeWorkBook.Save();             
        }
        catch (Exception theError)
        {
            Console.WriteLine(theError.Message);
            throw theError;
        }
        finally
        {
            if (activeWorkBook != null)
                activeWorkBook.Close();

            ReleaseComObject(excelApplication);
            ReleaseComObject(workBooks);
            ReleaseComObject(activeWorkBook);
            ReleaseComObject(activeWorkSheet);                                                
        }           
    }

Here is my ReleaseComObject:

  private static void ReleaseComObject<T>(T comObject) where T : class
    {
        if (comObject != null)
            Marshal.ReleaseComObject(comObject);
    }

I have written a test to ensure that the excel objects are properly disposed as below:

  [Test]
    public void Should_dispose_excel_objects_created_after_io_operation()
    {
        var expected = Process.GetProcesses().Count(process => process.ProcessName.ToLower() == "excel");

        var automationClient = new ExcelAutomation.ExcelAutomationClient(ExcelSheet);

        automationClient.SetValue("Sheet1", "A1", 1200);
        automationClient.GetValue<double>("Sheet1", "A1");

        var actual = Process.GetProcesses().Count(process => process.ProcessName.ToLower() == "excel");

        actual.Should().Be(expected, "Excel workbook is not disposed properly. There are still excel processess in memory");
    }

This test passess if I'm invoking only SetValue method, however while invoking GetValue it fails. However I can see no Excel.exe in the taskmanager. Any idea why this is happening please? Is something wrong with my GetValue function?

I believe `get_Range` returns a COM object, so you should release that, too. Although, you're calling that in both of your methods. — Lance U. Matthews, Feb 08 '12 at 00:20
It doesn't have anything to do with disposing. You are writing C code, manually managing memory. With the inevitable mistakes that produces, "leaking" memory. You are not calling ReleaseComObject() for the *cells* object reference. Just don't write code like this, there's no point. The garbage collector already knows how to do this. If you want to hurry it along for some reason then call GC.Collect(). As long as you have a good reason. — Hans Passant, Feb 08 '12 at 00:30
If you compare both the methods, I'm doing almost the same things. If I dont call ReleaseComObject, the excel instance lingers in memory. Only after calling that it goes away. Even in this case it goes away, however the test does not pass. — Mike, Feb 08 '12 at 00:35
use kill application win32 API http://stackoverflow.com/questions/7956519/win32-api-how-to-kill-processes-by-name — Houshang.Karami, Feb 09 '12 at 23:58
@HansPassant : This is how you write code against Excel :) http://support.microsoft.com/kb/317109 — Gishu, Feb 11 '12 at 17:31
Sure, and this is how you screw it up :) http://stackoverflow.com/a/4964826/17034 — Hans Passant, Feb 11 '12 at 17:41
@HansPassant - Gotta try that out. So the kb article is incorrect ? I remember GC.Collect not working last time I was wrestling with Excel.. will give that a try next time. — Gishu, Feb 11 '12 at 17:58

score 0 · Accepted Answer · answered Feb 25 '12 at 16:55

When you done disposing the object, use

GC.Collect();

This is how I dispose my Excel object

        private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }

Excel objects not getting disposed sometimes

1 Answers1