By ruedi


2014-08-05 08:12:53 8 Comments

In my company the common way to release Excel Interop Objects is to use IDisposable the following way:

Public Sub Dispose() Implements IDisposable.Dispose
    If Not bolDisposed Then
        Finalize()
        System.GC.SuppressFinalize(Me)
    End If
End Sub

Protected Overrides Sub Finalize()
    _xlApp = Nothing
    bolDisposed = True
    MyBase.Finalize()
End Sub

where _xlApp was created in the constructor the following way:

Try
    _xlApp = CType(GetObject(, "Excel.Application"), Excel.Application)
Catch e As Exception
    _xlApp = CType(CreateObject("Excel.Application"), Excel.Application) 
End Try

And the client uses the using-statement to execute code concerning excel interop objects.

We completely avoid to use the two dot rule. Now I started researching how to realease (Excel) Interop Objects and almost all discussions I found about it like How to properly clean up excel interop objects or Release Excel Objects are using mostly Marshal.ReleaseComObject(), none of them using the IDisposable Interface.

My questions is: Are there any disadvantages using the IDisposable Interace for releasing excel interop objects? If so, what are these disatvantages.

2 comments

@toinetoine 2014-08-21 22:00:53

If you're looking for a cleaner way, you can use Koogra. It's not much extra overhead (just two dll's your have to include in you're refrences) and you don't have to deal with implicit garbage collection.

The code bellow is all that's needed to read 10 rows of 10 columns from an excel file with no EXCEL32 or Excel.exe processes left behind. I had this issue a month ago and wrote instructions on how to do it. So much easier and cleaner than dealing with Excel Interop directly.

Koogra.IWorkbook workbook = Koogra.WorkbookFactory.GetExcel2007Reader("MyExcelFile.xlsx");
Net.SourceForge.Koogra.IWorksheet worksheet = workbook.Worksheets.GetWorksheetByName("Sheet1");

//This will invididually print out to the Console the columns A-J (10 columns) for rows 1-10.
for (uint rowIndex = 1; rowIndex <= 10; rowIndex++)
{
    for (uint columnIndex = 1; columnIndex <= 10; columnIndex++)
    {
        Console.WriteLine(worksheet.Rows.GetRow(rowIndex).GetCell(columnIndex).GetFormattedValue());
    }
}

@Hans Passant 2014-08-05 09:43:12

Are there any disadvantages using the IDisposable Interace

Sure, it accomplishes absolutely nothing. Using Using or calling Dispose() is never an appropriate way to set a variable to Nothing. Which is all that your code does.

We completely avoid to use the two dot rule.

Feel free to continue to ignore it, it is nonsense and causes nothing but grief. The blog author's implied assertion is that doing so would force the programmer to use a variable to store the value of xlApp.Workbooks. So he'd have a fighting chance, later, to not forget to call releaseObject(). But there are many more statements that produce an interface reference that don't use dots. Something like Range(x,y), there's a hidden Range object reference there that you'll never see. Having to store them as well just produces incredibly convoluted code.

And overlooking just one is enough to completely fail to get the job done. Utterly impossible to debug. This is the kind of code that C programmers have to write. And often failed at miserably, large C programs often leak memory and their programmers spend a great deal of time finding those leaks. Not the .NET way of course, it has a garbage collector to do this automatically. It never gets it wrong.

Trouble is, it is a bit slow at taking care of the job. Very much by design. Nobody ever notices this, except in this kind of code. You can see that the garbage collector didn't run, you still see the Office program running. It didn't quit when you wrote xlapp.Quit(), it is still present in the Processes tab of Task Manager. What they want to happen is for it to quit when they say so.

That's very possible in .NET, you can certainly force the GC to get the job done:

GC.Collect()
GC.WaitForPendingFinalizers()

Boom, every Excel object reference gets released automatically. There is no need to store these object references yourself and explicitly call Marshal.ReleaseComObject(), the CLR does it for you. And it never gets it wrong, it doesn't use or need a "two dot rule" and it has no trouble finding those hidden interface references back.


What matters a great deal however is exactly where you put this code. And most programmers put it in the wrong place, in the same method that used those Excel interfaces. Which is fine, but does not work when you debug the code, a quirk that's explained in this answer. The proper way to do it in the blog author's code is to move the code into a little helper method, let's call it DoExcelThing(). Like this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DoExcelThing()
    GC.Collect()
    GC.WaitForPendingFinalizers()
    '' Excel.exe no longer running anymore at this point
End Sub

And do keep in mind that this is truly all just a debugging artifact. Programmers just hate to have to use Task Manager to kill the zombie Excel.exe instances. Zombified when they stopped the debugger, preventing the program from exiting normally and collect garbage. This is normal. It will also happen when your program dies in production for any kind of reason. Put your energy where it belongs, getting the bugs out of your code so your program won't die. The GC doesn't need more help than that.

@Tronald 2014-08-05 11:19:49

I didn't post this question, but I really appreciated this answer!

@ruedi 2014-08-05 13:24:37

Thank you Hans, as always a very very good answer!

@Govert 2014-08-21 22:50:58

Amen! Please keep fighting the whole two-dot, ReleaseComObject voodoo crap that has infected the internet (and +1 for pointing to the debug quirk too).

@Mike 2014-10-24 19:21:34

Thank you for clarifying that the no-two-dot, Marshal.ReleaseComObject style is not necessary to do Excel (or I think most other COM) Interop and still release references. I have been hearing so much about it, I was afraid that I would have to rewrite large portions of my codebase . . .

@MickyD 2015-03-16 23:45:05

Amen for prevailing sanity. I thought I was the only one. Such silliness out there.

@rwong 2015-07-15 22:36:06

The whole RCW design is already a dead horse. The future is going to be an automatically generated C++/CLI wrapper assembly (enhanced interop assembly) that will release correctly whether you Dispose it or leave it to the GC. The misdesign is what causes every C# office automation unstable, cause everyone to runaway from COM, and eventually cause everyone to runaway from the Office suite itself. Too late, already happened.

@Cornelius 2015-07-22 21:37:07

On my first read of this post, I missed the part about WHERE I should put the GC.Collect() command. After putting it in the correct place it worked perfectly.

@jpcguy89 2015-10-06 19:46:05

I'm a tad confused by all this...I never have any zombie instances of Excel lying around. My boss cam in to show me, when I told him I was writing out to Excel, and they weren't there, checked both in processes and in applications inside of the Task Manager. My question is...is the GC needing additional help to cleanup COM objects this still an issue, as of VS2015 and .NET 4.6?

Related Questions

Sponsored Content

19 Answered Questions

[SOLVED] Proper use of the IDisposable interface

39 Answered Questions

[SOLVED] How do I properly clean up Excel interop objects?

3 Answered Questions

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 997860 View
  • 1732 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

41 Answered Questions

[SOLVED] Deep cloning objects

  • 2008-09-17 00:06:27
  • NakedBrunch
  • 740110 View
  • 2033 Score
  • 41 Answer
  • Tags:   c# .net clone

2 Answered Questions

3 Answered Questions

1 Answered Questions

5 Answered Questions

[SOLVED] Using Wrapper objects to Properly clean up excel interop objects

1 Answered Questions

[SOLVED] Excel interop libraries incompatible with ASP.NET?

Sponsored Content