Geeks With Blogs

News

Series

Add to Technorati Favorites


An Archived Managed World This blog has moved to http://www.managed-world.com/blog

I ran across a nice little Com Interop “gotcha” when I was working with Excel automation from C# today. I'm sure that a lot of you with more COM Interop experience than me already know about this but I figured I would share so that those with lesser experience can avoid the heartburn that I had today. The gotcha occurred when I was working with Excel ranges to pull information out of a spreadsheet from C#. When working with Office automation from a managed language, one needs to understand that under-the-hood, there are still reference counters! If you are not careful when writing code, it is VERY easy to expose your program to some basic memory leaks.

Below is a sample of some easy pseudo-code to pull information out of a range. Can you spot the problem? (I know I sure couldn't earlier)

Excel.Range range;

range = GetFirstRange(....);
// Do Something With Range

range = GetSecondRange(....);
// Do Something With New Range

Marshal.ReleaseComObject(range);
range = null;

The problem is that range is not released before the call to GetSecondRange and hence the reference counter after the call is at two, instead of one (I assume). What does this mean? This means that when your program exits, Excel will remain loaded in memory. The correct code looks like:

Excel.Range range;

range = GetFirstRange(....);
// Do Something With Range

Marshal.ReleaseComObject(range);
range = GetSecondRange(....);
// Do Something With New Range

Marshal.ReleaseComObject(range);
range = null;

While this gets pretty easy to spot with the example above, it gets a little more tricky when dealing with loops and test conditions.

Sounds easy enough, right? WRONG! The real tricky part comes when you are dealing with temporary variables used by the runtime. For instance, when dealing with opening up a workbook, the workbooks collection will be a temporary variable used by the runtime:

Excel.Workbook myWorkbook = myExcelApplication.Workbooks.Open(....);
// Do something with workbook

Marshal.ReleaseComObject(myWorkbook);
myWorkbook = null;

Can you spot what's wrong with the code above? The problem is that “.Workbooks.Open” will introduce a memory leak. The answer? Change to the following:

Excel.Workbooks myWorkbooks = myExcelApplication.Workbooks;
Excel.Workbook myWorkbook = myWorkbooks.Open(....);
// Do something with workbook

Marshal.ReleaseComObject(myWorkbook);
Marshal.ReleaseComObject(myWorkbooks);
myWorkbook = null;
myWorkbooks = null;

Now I assume (because I haven't researched for sure yet) that this memory leak is caused because the reference counter on the Workbooks com wrapper will still be increased even though there is not a local variable reference to that object.

Am I wrong here? My feeling is that most of this SHOULD be taken care of for you when using a managed language, but ISN'T. Because of this, one is almost no better off using a managed language with COM interop than using an unmanaged language. In all fairness, I know that it would most likely be quite tricky for the runtime to take care of this under-the-hood, and that if you are programming COM Interop you are most likely a “Big Boy“ programmer and should be able to take care of this yourself. After all, even one of my biggest pet peeves is a lazy programmer. Perhaps I should just file it away in the back of my mind as another lesson learned in order to make me a better programmer. What are all of your thoughts on this situation?

 

Posted on Tuesday, February 3, 2004 4:39 PM | Back to top


Comments on this post: GOTCHA: Com Interop

# re: GOTCHA: Com Interop
Requesting Gravatar...
We've a Windows application(rich-client application) which uses set of data in the excel input files and generates around 58 workbooks with each workbook containing around 32 sheets each.

We tried it in Excel 97.

Application makes extensive use of the Excel object model to generate the files.

Application release all the Excel hidden objects using Marshal.ReleaseCOMOBject and it does all this thing correctly to the best of my knowledge. We also call the GC.Collect() & GC.WaitForPendingFinalizer() just to be sure that the memory is cleaned-up. I understand there'll be a performance hit. But it's okay as long it's stable but it's not the case right now.

Problem is after 5 successful test runs, without closing the application, Not Enough Memory to Run Microsoft Excel error pops-up and everything is over. After this, i see the same message when trying to launch Excel 97 or Word from my computer. I've to restart so as to use any of Office applications again.

I'd really appreciate someone telling me if there are any bugs in Excel 97 itself that resuls in these or any workaround.

I need some help ASAP.

I looked at the log using the PerfMon and the following values:
Process - Private bytes -> 5.7 MB - 27.1 MB with average around 19 MB
.NET CLR Memory - #Bytes in all heaps -> 0.312 MB - 3.3 MB with an average value around 0.9 MB

SInce the above counters are kinda okay, i'm not sure whether there is a memory leak.

My computer has 256 MB RAM and i've set the initial size of the virtual memory to 700 MB.

Thanks!

E-mail: uttar_16@yahoo.co.uk
Left by Ashley gr on Feb 22, 2004 10:07 AM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Unfortunately, I'm not an Excel interop expert. I see that you posted to the DotNet Com interop usenet group already. Try posting to the Excel group also if you haven't already.

Anyways, if you open Task Manager and check "Processes" after your program has run once, are there any instances of Excel.EXE still running? If so, not all the references are being released. My guess is that most of the obvious references are being released and you have to check "temporary" references that will be created by the runtime. For instance, make sure that there isn't code like "myExcelApplication.Workbooks.Open(....);". If there is, a temp variable for the Workbooks property is being created and never released. It's basically outlined in the post above.
Left by Jason Olson on Feb 22, 2004 11:08 AM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Jason,

>> The problem is that range is not released before the call to GetSecondRange and hence the reference counter after the call is at two, instead of one (I assume). What does this mean? This means that when your program exits, Excel will remain loaded in memory

The reference count will not be 2. Instead, there will be two separate Range objects, each with a reference count of 1. That is why:

Excel.Range range;

range = GetFirstRange(....);
// Do Something With Range

Marshal.ReleaseComObject(range);
range = GetSecondRange(....);
// Do Something With New Range

Marshal.ReleaseComObject(range);
range = null;

would work, but:

Excel.Range range;

range = GetFirstRange(....);
// Do Something With Range

range = GetSecondRange(....);
// Do Something With New Range

Marshal.ReleaseComObject(range);
Marshal.ReleaseComObject(range);

range = null;

wouldn't.

Seeya
Matthew
Left by Matthew on Sep 06, 2004 2:18 AM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Ah, you're right Matthew :). My bad :).
Left by Jason Olson on Sep 12, 2004 2:52 PM

# re: GOTCHA: Com Interop
Requesting Gravatar...
My question is what is the code inside GetFirstRange() and GetSecondRange() that doesn't cause COM object leaks?
e.g.
if I have
Excel.Application xl;
...
Excel.Range topleftcell = xl.Cells("A1");
Excel.Range bottomrightcell = xl.Cells("E5");
Excel.Range myrange = xl.Range(topleftcell, bottomrightcell) // <-- this line causes the leak!

Any idea how to get round this one
Left by Bonj on Jun 24, 2005 4:51 PM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Actually that example I previously posted doesn't leak, I just tested it successfully... I think if I'd used Cells instead of range that would have done because Cells returns the entire range of the sheet first.
Left by Bonj on Jun 24, 2005 5:08 PM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Good way to free them all:
For Each o As Object In New Object() _
{allrows, rowsfirst, rowslast, rows, entirerows}
ReleaseComObject(o)
Next
Left by Bonj on Jun 24, 2005 5:39 PM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Another thing I discovered is you don't actually need to set the objects to nothing, the GC will take care of that. You only need to call ReleaseComObject to release it from the CCR.
Left by Bonj on Jun 25, 2005 1:54 PM

# re: GOTCHA: Com Interop
Requesting Gravatar...
You saved my life with the trick about the Workbooks.Open leak !

Thanks
Laurent
Left by Laurent Pierre on Jun 29, 2005 7:36 AM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Jason, this post was more than insightful. I think that this is a hidden mistake which is very difficult to notice unless you have a certain amount of experience in the .NET area. I had no idea up to this point that COM InterOp poses so many problems. Thanks, and keep up the good work!
Left by Andrei Ismail on Jul 04, 2005 7:12 AM

# re: GOTCHA: Com Interop
Requesting Gravatar...
I see this thread is very old, but will post anyway. This is very Frustrating. I am re-writting some code to open an Excel file, do some stuff and close the file.
Excel will not shut down if I use this line

Code:
rng = WS.Range("A65536").End(XlDirection.xlUp)



it will how ever close down if I use this line.

Code:
rng = WS.Range("A1")



What is going on? My code is below.



Code:
Sub Verispan(OpenPath as string)
Dim XL As Excel.Application
Dim WBs As Excel.Workbooks
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim lrow As Integer

If XL Is Nothing Then XL = New Excel.Application
Try
'START EXCEL
WBs = XL.Workbooks
WB = WBs.Open(OpenPath)
WS = WB.ActiveSheet
WB.Activate()
WS.Select()
XL.ScreenUpdating = False
XL.Visible = True

'Find the last row, and the start range for the data
'rng = WS.Range("A1")
rng = WS.Range("A65536").End(XlDirection.xlUp)
'lrow = rng.Row

'Close Down Excel. Must Remove all references to COM objects that are created,
'or Excel will remain as running process

Call ReleaseCom(rng)
Call ReleaseCom(WS)
WB.Close(False)
Call ReleaseCom(WB)
Call ReleaseCom(WBs)
XL.Quit()
Call ReleaseCom(XL)
GC.Collect()

Catch ex As Exception
MessageBox.Show("Error in ScrubDataFiles Module, VerispanProcedure" & Chr(10) & ex.Message)
End Try




Code:
Sub ReleaseCom(ByVal o As Object)
Try
If Not o Is Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If Not o Is Nothing Then o = Nothing
End Try
End Sub
Left by kalelpack on Jun 06, 2006 4:01 AM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Another thing that may be causing a memory leak is if you try and be tricky by trying to accomplish "too much" on the same line

Let's take series for example. If your chart has too many series and you need to get rid of some, using c# and the following code you may do so:

((Excel.Series)myChart.SeriesCollection(num)).Delete();

the more times you do it, the greater the number of leaks that occur. You need to store each instance in a tangible object on which you can perform a Marshal.ReleaseComObject for.

Hope this helps people...this was giving me grief for a while since it seemed like I was releasing everything I was using until I figured that out.
Left by tdeve on Nov 29, 2006 10:02 AM

# re: GOTCHA: Com Interop
Requesting Gravatar...
Thank you VERY MUCH!!!
Left by Alexander Byndyu on May 20, 2009 3:32 AM

Your comment:
 (will show your gravatar)


Copyright © Jason Olson | Powered by: GeeksWithBlogs.net