Safely disposing Excel interop objects in C#?

Question

i am working on a winforms c# visual studio 2008 application. the app talks to excel files and i am using Microsoft.Office.Interop.Excel; to do this.

i would like to know how can i make sure that the objects are released even when there is an error?

here's my code:

private void button1_Click(object sender, EventArgs e)
{
    string myBigFile="";
    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    DialogResult result = openFileDialog1.ShowDialog(); // Show the dialog.
    if (result == DialogResult.OK) // Test result.
        myBigFile=openFileDialog1.FileName;

    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    Excel.Range range;

    string str;
    int rCnt = 0;
    int cCnt = 0;

    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Open(myBigFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 1, 0);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    range = xlWorkSheet.UsedRange;

    /*
    for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
    {
        for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
        {
            str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
            MessageBox.Show(str);
        }
    }
     */
    xlWorkSheet..EntireRow.Delete(Excel.XLDirection.xlUp)

    xlWorkBook.SaveAs(xlWorkBook.Path + @"\XMLCopy.xls",         Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,
   false, false, Excel.XlSaveAsAccessMode.xlNoChange,
   Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    xlWorkBook.Close(true, null, null);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Unable to release the Object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

how can i make sure that even if i get an error after the workbook opened, that i make sure to dispose of the objects:

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

In other words no matter what i need the following lines to run

xlWorkBook.Close(true, null, null);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

please note that i have tried this as well, resulting in the same issue

xlWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);


                xlApp.Quit();

                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);

                xlWorkSheet = null;
                xlWorkBook = null;
                xlApp = null;

                GC.GetTotalMemory(false);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.GetTotalMemory(true);

and i did this as well:

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

                Marshal.FinalReleaseComObject(xlWorkSheet);

                xlWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
                Marshal.FinalReleaseComObject(xlWorkBook); 

                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

at this point i do not think it's possible to close excel from visual studio 2008. it must be a bug or something, but i've tried the top 20 websites on this and getting the same result: excel is opening two instances for some reason and when i do the garbage collection etc.. (or not) it closes just ONE instance.

when i try to open the file, it says there's an error or it's corrupt.

when i go to task manager and kill the excel process, the file will open without problems.]

is there a way to close excel with visual studio 2008? if so, can you please provide me with guidance or a solution to this

Do not call GC.Collection()... http://blogs.msdn.com/b/ricom/archive/2004/11/29/271829.aspx — Erik Philips, Apr 01 '12 at 05:43
can you please tell me the difference between what is happening with xlworkbook.close and xlapp.quit vs the releaseObject ? why do i need both of these? — Alex Gordon, Apr 01 '12 at 05:46
[This](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c/159419#159419) answers your question nicely. — Jay Riggs, Apr 01 '12 at 05:48
@ErikPhilips jay quoted someone who said to use gc.collection twice! — Alex Gordon, Apr 01 '12 at 06:28
@I__ I think the link sums it up nicely with **Rule #1 should trump Rule #2 without strong evidence.** — Erik Philips, Apr 01 '12 at 06:35
I encourage both you and Erik to check out the response by Mike Rosenblum in my link. The reason why `GC.Collect` is a good idea for COM Interop is because it's difficult to track all references for release via `Marshal.FinalReleaseComObject()`. The code's worked for me. — Jay Riggs, Apr 01 '12 at 06:43
@JayRiggs i am actually trying to use this http://www.xtremevbtalk.com/showpost.php?p=1335552&postcount=22 however on this line Win32.CloseHandle(m_handle); i am getting win32 doesnt exist in current context — Alex Gordon, Apr 01 '12 at 06:50
@JayRiggs I disagree: `FinalReleaseComObject` *is almost always a bad idea*. On the other hand, strictly-controlled lifetimes can work well with `ReleaseComObject`. If one cannot ensure this, then by all means, please just use the GC (but without said GC.Collect "hack" which means *the lifetimes mattered and should ahve been controlled explicitly*). See http://stackoverflow.com/questions/9709141/net-and-com-interoperability-release-com-from-net-client/9709506#9709506 for details and my rationale. — , Apr 01 '12 at 06:57
@JayRiggs when you have a chance please see my edited question — Alex Gordon, Apr 01 '12 at 22:20
@ErikPhilips please see edited question, i would like ur assistance when u have a chance — Alex Gordon, Apr 01 '12 at 22:20
@pst You've given me something to think about next time I do Interop. Thanks for the link and for the code in your answer. — Jay Riggs, Apr 02 '12 at 03:58
I think I have solutions to the problems you're having; see my newly posted answer (it's too long for a comment). — Jay Riggs, Apr 02 '12 at 05:04

score 23 · Answer 1 · 2012-04-01T22:13:26.013

23

First I will present a modified releaseObject, and then I will provide a pattern to use it.

using Marshal = System.Runtime.InteropServices.Marshal;
private void releaseObject(ref object obj) // note ref!
{
    // Do not catch an exception from this.
    // You may want to remove these guards depending on
    // what you think the semantics should be.
    if (obj != null && Marshal.IsComObject(obj)) {
        Marshal.ReleaseComObject(obj);
    }
    // Since passed "by ref" this assingment will be useful
    // (It was not useful in the original, and neither was the
    //  GC.Collect.)
    obj = null;
}

Now, a pattern to use:

private void button1_Click(object sender, EventArgs e)
{
    // Declare. Assign a value to avoid a compiler error.
    Excel.Application xlApp = null;
    Excel.Workbook xlWorkBook = null;
    Excel.Worksheet xlWorkSheet = null;

    try {
        // Initialize
        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Open(myBigFile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 1, 0);
        // If the cast fails this like could "leak" a COM RCW
        // Since this "should never happen" I wouldn't worry about it.
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        ...
    } finally {
        // Release all COM RCWs.
        // The "releaseObject" will just "do nothing" if null is passed,
        // so no need to check to find out which need to be released.
        // The "finally" is run in all cases, even if there was an exception
        // in the "try". 
        // Note: passing "by ref" so afterwords "xlWorkSheet" will
        // evaluate to null. See "releaseObject".
        releaseObject(ref xlWorkSheet);
        releaseObject(ref xlWorkBook);
        // The Quit is done in the finally because we always
        // want to quit. It is no different than releasing RCWs.
        if (xlApp != null) {
            xlApp.Quit();
        }
        releaseObject(ref xlApp);    
    }
}

This simple approach can be extended/nested over most situations. I use a custom wrapper class that implements IDisposable to make this task easier.

edited Apr 01 '12 at 22:13

answered Apr 01 '12 at 06:47

1

thank sso much for helping! i am getting this error on all the releaseObject calls Error 1 A property or indexer may not be passed as an out or ref parameter – Alex Gordon Apr 01 '12 at 06:56
please note i am using .net 3.5 – Alex Gordon Apr 01 '12 at 06:58
I__ Yes, in C# `ref` can only be used with *variables*. In my projects I have two "releaseObject" methods. One uses `ref` (I use it for all *variables*) and another does not use "ref" (I use it rarely for some *properties*). If "ref" is *not used* then just know that you can't change the value that was passed in, in the caller. E.g. `realeaseObject(ref variable)` or `releaseObjectNonRef(Property); Property = null;` is required. – Apr 01 '12 at 07:00
i did get it compiled by removing the REF – Alex Gordon Apr 01 '12 at 07:05
my app opened two instances of EXCEL.exe and i still see those 2 instances in taskbar, the code you provided did not close them – Alex Gordon Apr 01 '12 at 07:06
@I__ without `ref` you need to do the assignment outside to really set the variable to `null` (which is handy to avoid accidentally using it again -- which can cause an "RCW detached" exception). – Apr 01 '12 at 07:06
@I__ That is another issue then :) Did it ever "close"? – Apr 01 '12 at 07:07
oh oh i see. nope it never closed – Alex Gordon Apr 01 '12 at 07:08
@I__ Then try to reduce it to the point that it does close correctly ... e.g. just xlApp, just xlWorkBook, just xlWorkSheet ... hopefully then the problem can be isolate. – Apr 01 '12 at 07:11
it does not close correctly, im sorry can you elaborate on "reduce it to the point" how can i do this? thank you again for your time – Alex Gordon Apr 01 '12 at 07:13
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9552/discussion-between-i-and-pst) – Alex Gordon Apr 01 '12 at 07:23
@I__ Remove code until it works ;-) Can you get it to launch and then close Excel? (e.g. don't do anything in it!) – Apr 01 '12 at 07:33
hi do you have a minute! i would be most grateful if you can do the chat with me – Alex Gordon Apr 01 '12 at 18:06
@I__ Because it's a sample :-) It is meant to show how to declare outside, initialize inside, and release in `finally`. – Apr 01 '12 at 21:52
@I__ I would put it in the `finally` as a special case. I have updated my answer as an example. This is because we really want it quit, even if an exception happens to occur. As long as the workbook, etc., are released before it should still let go of all the COM RCWs. – Apr 01 '12 at 22:12
yes i did try to do the finally, but getting same result can you please see edited question – Alex Gordon Apr 01 '12 at 22:21
really really appreciate your help - however, still getting same result – Alex Gordon Apr 02 '12 at 01:54

score 4 · Answer 2 · answered Apr 02 '12 at 05:03

4

Verify that there are two problems you're seeing in your code:

That when the program closes Excel remains as a running process
That when you open the Excel file your program creates you see an error in Excel saying the file is corrupted or some such

I copied the button1 click handler and pst's releaseObject method in your edited question into a clean VS2008, C#3.5 Winform application and made a couple minor changes to eliminate both the problems I listed above.

To fix Excel not unloading from memory, call releaseObject on the range object you created. Do this before your call to releaseObject(xlWorkSheet); Remembering all these references is what makes COM Interop programming so much fun.

To fix the corrupt Excel file problem update your WorkBook.SaveAs method call to replace the second parameter (Excel.XlFileFormat.xlXMLSpreadsheet) with Type.Missing. The SaveAs method will handle this correctly by default.

I'm sure the code you posted in your question is simplified to help debug the problems you're having. You should use the try..finally block pst demonstrates.

answered Apr 02 '12 at 05:03

Jay Riggs

53,046
9
139
151

thank you so much fo rthis!! im trying it right now. but please can you tell em what is the reason we are using xlXMLspreadsheet? i dont want it to be xml format – Alex Gordon Apr 02 '12 at 05:10
|_ Not sure I understand - I got the xml format specification from your code, from the `xlWorkBook.SaveAs` method call. I'm suggesting not doing this by using `Type.Missing` in it's place. – Jay Riggs Apr 02 '12 at 05:14
im sorry i meant that i wanted to save it exactlty the same format as is – Alex Gordon Apr 02 '12 at 05:21
|_ I'm not sure how you'd do that. There's an Excel [`Workbook.FileFormat` property](http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.fileformat.aspx) you can retrieve the value of and presumably use when you `SaveAs` the new file. – Jay Riggs Apr 02 '12 at 14:16

Safely disposing Excel interop objects in C#?

2 Answers2

Linked