C# – How to properly clean up Excel interop objects

ccom-interopexcelinterop

I'm using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause:

while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();

Although this kind of works, the Excel.exe process is still in the background even after I close Excel. It is only released once my application is manually closed.

What am I doing wrong, or is there an alternative to ensure interop objects are properly disposed of?

Best Answer

Excel does not quit because your application is still holding references to COM objects.

I guess you're invoking at least one member of a COM object without assigning it to a variable.

For me it was the excelApp.Worksheets object which I directly used without assigning it to a variable:

Worksheet sheet = excelApp.Worksheets.Open(...);
...
Marshal.ReleaseComObject(sheet);

I didn't know that internally C# created a wrapper for the Worksheets COM object which didn't get released by my code (because I wasn't aware of it) and was the cause why Excel was not unloaded.

I found the solution to my problem on this page, which also has a nice rule for the usage of COM objects in C#:

Never use two dots with COM objects.

So with this knowledge the right way of doing the above is:

Worksheets sheets = excelApp.Worksheets; // <-- The important part
Worksheet sheet = sheets.Open(...);
...
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(sheet);

POST MORTEM UPDATE:

I want every reader to read this answer by Hans Passant very carefully as it explains the trap I and lots of other developers stumbled into. When I wrote this answer years ago I didn't know about the effect the debugger has to the garbage collector and drew the wrong conclusions. I keep my answer unaltered for the sake of history but please read this link and don't go the way of "the two dots": Understanding garbage collection in .NET and Clean up Excel Interop Objects with IDisposable

Related Solutions

Ruby – How to call shell commands from Ruby

This explanation is based on a commented Ruby script from a friend of mine. If you want to improve the script, feel free to update it at the link.

First, note that when Ruby calls out to a shell, it typically calls /bin/sh, not Bash. Some Bash syntax is not supported by /bin/sh on all systems.

Here are ways to execute a shell script:

cmd = "echo 'hi'" # Sample string that can be used

Kernel#` , commonly called backticks – `cmd`

This is like many other languages, including Bash, PHP, and Perl.

Returns the result (i.e. standard output) of the shell command.

Docs: http://ruby-doc.org/core/Kernel.html#method-i-60
```
value = `echo 'hi'`
value = `#{cmd}`
```
Built-in syntax, %x( cmd )

Following the x character is a delimiter, which can be any character. If the delimiter is one of the characters (, [, {, or <, the literal consists of the characters up to the matching closing delimiter, taking account of nested delimiter pairs. For all other delimiters, the literal comprises the characters up to the next occurrence of the delimiter character. String interpolation #{ ... } is allowed.

Returns the result (i.e. standard output) of the shell command, just like the backticks.

Docs: https://docs.ruby-lang.org/en/master/syntax/literals_rdoc.html#label-Percent+Strings
```
value = %x( echo 'hi' )
value = %x[ #{cmd} ]
```
Kernel#system

Executes the given command in a subshell.

Returns true if the command was found and run successfully, false otherwise.

Docs: http://ruby-doc.org/core/Kernel.html#method-i-system
```
wasGood = system( "echo 'hi'" )
wasGood = system( cmd )
```
Kernel#exec

Replaces the current process by running the given external command.

Returns none, the current process is replaced and never continues.

Docs: http://ruby-doc.org/core/Kernel.html#method-i-exec
```
exec( "echo 'hi'" )
exec( cmd ) # Note: this will never be reached because of the line above
```

Here's some extra advice: $?, which is the same as $CHILD_STATUS, accesses the status of the last system executed command if you use the backticks, system() or %x{}. You can then access the exitstatus and pid properties:

$?.exitstatus

For more reading see:

C# – How to cast int to enum

From an int:

YourEnum foo = (YourEnum)yourInt;

From a string:

YourEnum foo = (YourEnum) Enum.Parse(typeof(YourEnum), yourString);

// The foo.ToString().Contains(",") check is necessary for enumerations marked with an [Flags] attribute
if (!Enum.IsDefined(typeof(YourEnum), foo) && !foo.ToString().Contains(","))
{
    throw new InvalidOperationException($"{yourString} is not an underlying value of the YourEnum enumeration.")
}

Update:

From number you can also

YourEnum foo = (YourEnum)Enum.ToObject(typeof(YourEnum) , yourInt);

Best Answer

Related Solutions

Ruby – How to call shell commands from Ruby

C# – How to cast int to enum

Related Topic