Guaranteed COM object release?

Question

I wrote the following code under the assumption that Excel will die with Monkey:

class ExcelMonkey
{
    private static Excel.Application xl = new Excel.Application();

    public static bool parse(string filename)
    {

        if (filename.Contains("foo"))
        { 
            var workbook = xl.Workbooks.Open(filename);
            var sheet = workbook.Worksheets.get_Item(1);

            // do stuff

            return true;

        }

        return false;
    }
}

How do I make sure it does? Do I need to release workbook and sheet separately?

I want to have Excel around for the lifetime of the program, it's a huge performance improvement.

Answer 1

Do I need to release workbook and sheet separately?

Yes, either will keep the underlying Excel Application object alive.

How do I make sure it does?

Marshal.ReleaseComObject will do that, but ensure you don't use references to the object after that.

Answer 2

in the destructor call

xl.Quit();

Answer 3

Here's the working solution, in case anyone's interested:

class ExcelMonkey
{
    private Excel.Application xl = new Excel.Application();

    ~ExcelMonkey()
    {
        xl.Quit();
        Dispose(false);
    }

    private bool isDisposed = false;


    protected void Dispose(bool disposing)
    {
        Marshal.ReleaseComObject(xl);
        isDisposed = true;
    }

    public bool parse(string filename)
    {
        if (filename.Contains("foo"))                  
        {
            var workbook = xl.Workbooks.Open(filename);
            var sheet = workbook.Worksheets.get_Item(1);

            try
            {
                // do stuff
            }
            finally
            {
                Marshal.ReleaseComObject(sheet);
                Marshal.ReleaseComObject(workbook);
            }
            return true;
        }
        return false;
    }
}

Answer 4

By way of a bit of background, Excel has a tendency to generate hidden references behind the scenes as a function of the COM defaulting mechanisms used to support VBA. For example, you could talk to 'Application' and it would quietly set up a COM reference that you could not detach from.

You still have to be pretty meticulous with COM references when programming Excel.

This is compounded by the fact that the PIA's also like to generate references behind the scenes and they do not tidy up those references properly when garbage collected. You have to explicitly close any COM reference.

Answer 5

If you want to work with office files, I recommend you take a look at NPOI lib. This is a dot net port of the POI lib for java it works wanders when working with office files. No messy COM needed, all done in CLR code, everything works as expected.

I have use it to generate excel reports with no problems. Give it a try I'm sure you will not regret it. Much better than using COM