Disposing static objects c#

Question

I have a static class which implements Excel-related functions (Class Library).

This dll is added as a reference to other applications, where I'm trying to use those functions.

I know that static objects are disposed when the main program terminates. Can I somehow dispose it before?

In my code, If I call CreateExcelDocument(excelFile), and instance of Excel is running in the background (I can see it in windows' processes manager). But, when I call DisposeExcelDocument(); the instance remains. How can I dispose it?

My goal is to open multiple Excel files, one by one, create graphs from the file currently open, and then close and move on to the next one. Is it even possible?

Here is the code:

public static class ExcelUtils
{
    #region Private Members

    private static Application m_excelApp;
    private static Workbook m_excelWorkBook;
    private static Worksheet m_excelWorkSheet;

    #endregion Private Members

    #region Properties

    public static Worksheet ExcelWorkSheet
    {
        get { return m_excelWorkSheet; }
        set { m_excelWorkSheet = value; }
    }

    #endregion Properties

    #region Public Functions

    public static void CreateExcelDocument(string excelFile)
    {
        try
        {
            m_excelApp = new Application();
            m_excelApp.DisplayAlerts = false;
            m_excelWorkBook = m_excelApp.Workbooks.Add(Type.Missing);
            m_excelWorkSheet = (Worksheet)m_excelApp.ActiveSheet;
            m_excelApp.DefaultSheetDirection = (int)Constants.xlLTR;
            m_excelWorkSheet.DisplayRightToLeft = false;

            if (excelFile.CompareTo("") != 0)
            {
                m_excelWorkBook = m_excelApp.Workbooks.Open(excelFile);
                m_excelWorkSheet = (Worksheet)m_excelApp.Worksheets.get_Item(1);
                m_excelWorkSheet.Columns.ClearFormats();
                m_excelWorkSheet.Rows.ClearFormats();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return;
        }
    }
    public static void DisposeExcelDocument()
    {
        try
        {
            m_excelApp.Quit();
            ReleaseObject(m_excelWorkSheet);
            ReleaseObject(m_excelWorkBook);
            ReleaseObject(m_excelApp);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return;
        }
    }

    public static void ReleaseObject(object currentObject)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(currentObject);
            currentObject = null;
        }
        catch (Exception ex)
        {
            currentObject = null;
            Console.WriteLine(ex.ToString());
            return;
        }
        finally
        {
            GC.Collect();
        }

    }

    public static uint GetNumberOfRowsOrCols(string excelFile, bool getRows)
    {
        CreateExcelDocument(excelFile);

        uint rowColNum = 0;

        if (getRows)
            rowColNum = (uint)m_excelWorkSheet.UsedRange.Rows.Count;
        else
            rowColNum = (uint)m_excelWorkSheet.UsedRange.Columns.Count;

        DisposeExcelDocument();

        return rowColNum;
    }

    #endregion Public Functions
}

Have you considered *not* having these as static variables to start with? Yes, you can dispose them if you really want - but it would be cleaner to have these as instance variables, and you can let the caller create a single instance or multiple instances as they wish. It isn't *inherently* static, after all. — Jon Skeet, Dec 10 '13 at 13:35
Yes I did, but then I can't call the public functions from outside, they are expected to be static. — Idanis, Dec 10 '13 at 13:38
Well yes, you'd need to change the rest of the code too - but that would be refactoring it to improve it. — Jon Skeet, Dec 10 '13 at 13:41
It looks like you expect the call to `ReleaseObject` to also `null` the object, however this won't null the static class variable holding a reference to it. All it is currently doing is nulling the method-scoped reference to the object, not the class-scoped references. This is probably why your instances "remain". You also don't need to call `GC.Collect`. That said, I would also advise refactoring to reduce the reliance on static global objects, however that isn't always possible / feasible. — Adam Houldsworth, Dec 10 '13 at 13:42
Could you please explain how the code should be changed so that I will be able to call non-static functions, located inside a class library, from an external project? — Idanis, Dec 10 '13 at 13:43
Not sure, but isn't "currentObject = null" setting the parameter reference to null rather than the static field value? — Paul Coghill, Dec 10 '13 at 13:46
possibly a double dot problem ... you do this many times ... m_excelWorkBook = m_excelApp.Workbooks.Add(Type.Missing); for example creates a Workbooks object that is never released ... http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects — DarkSquirrel42, Dec 10 '13 at 13:49

Ammar · Answer 1 · 2013-12-16T14:41:36.953

1

First of all I agree with the comments regarding making this as non-static class.

But as far as your question is concerned, the Garbage Collector is not collecting the objects as you are not setting null to the class members, but just the local reference in ReleaseObject method.

To null the class members with least changes, will be to pass currentObject parameter to ReleaseObject method as ref, and have to use generics instead of object data type. So the method will become:

public static void ReleaseObject<T>(ref T currentObject) where T : class

and to call this method you will change like this:

ReleaseObject(ref m_excelWorkSheet);

You can leave the body of ReleaseObject method as it is, but I think calling GC.Collect() is not needed, and if you really have to, then call in from DisposeExcelDocument only once in the end, after you have called ReleaseObject for all the objects.

edited Dec 16 '13 at 14:41

answered Dec 10 '13 at 14:19

Ammar

233
1
8

Thanks Ammar. Then I get the error: `cannot convert from 'ref Microsoft.Office.Interop.Excel.Worksheet' to 'ref object'` – Idanis Dec 10 '13 at 14:33
Sorry about that, I totally missed it. Polymorphism can't work with `ref` parameters. We would have to need generics here. – Ammar Dec 16 '13 at 14:35
I have changed the answer, hope it works now. Sorry for late response. – Ammar Dec 16 '13 at 14:42

Disposing static objects c#

1 Answers1