How can i dispose Excel.interop COM object properly in c#?

Question

I am trying to automate a task that put data into excel worksheets. Everything is going well but when I look through the task manager the process "EXCEL.exe" still remains. I already use Marshal.ReleaseComObject to dispose my excel object. I did some test in order to isolate the line that makes the process remains and here it is :

CurrentSheet = ExcelWorkbook.Worksheets.Add();

Once I add a new sheet to the current workbook. even if I dispose it after its creation the process persists in the memory. My question is : What am I missing in order to make it work properly ?

I advise you not to post something with process killing because it is a bad solution and I would not be sure that I will kill the right process.

Here is my code :

//Declaration
static object misValue = System.Reflection.Missing.Value;
Excel.Application ExcelApp {get;set;}
Excel.Workbook ExcelWorkbook { get; set; }
protected Excel.Sheets ExcelSheets { get; set; }
protected Excel.Worksheet CurrentSheet { get; set; }

//Constructor
public ExcelLib()
{
      //Open in memory
      ExcelApp = new Excel.Application();
      ExcelWorkbook = ExcelApp.Workbooks.Add(misValue);
      ExcelSheets = ExcelWorkbook.Worksheets;
      CurrentSheet = (Excel.Worksheet)ExcelWorkbook.Worksheets[1];

}
// Add new Excel sheet
public virtual bool CreateNewSheet(String SheetName,DataSet ds)
{
    if (SheetName.Length > 31)
        SheetName = SheetName.Substring(0, 31);
    CurrentSheet = ExcelWorkbook.Worksheets.Add(); //this cause the process remains
    CurrentSheet.Name = SheetName;

    ////// Code below doesn't matter
    SetRange("A1", ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count);
    SetRangeValue(ds.ToStrArray(true));//ds.Tables[0].Rows;
    return true;
}
//The way I dispose the excel objects
public void Close()
{
    ExcelApp.Quit();
    releaseObject(CurrentSheet);
    releaseObject(ExcelSheets);
    releaseObject(ExcelWorkbook);
    releaseObject(ExcelApp);


}
    //release Object

private void releaseObject(object obj)
{
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
        }
        finally
        {
            GC.Collect(); //Useless
        }
}

In the whole application i just created a new instance of an ExcelLib , call the method CreateNewSheet(...) and then Close. If I only do the ExcelLib() and the Close() the process immediatly disapear from the task manager. Thank you for your help !

To use excel from C# http://epplus.codeplex.com/ is the way to go. Excel.interop is a pain — Sibster, Mar 05 '15 at 13:57
I totally agree with you but it will be a pain to rewrite every libraries in the project :) So I have to find another way to solve the current issue ... — user2493388, Mar 05 '15 at 14:01
Maybe the line causing the leak is because you are referencing 'ExcelWorkbook.Worksheets' again. Replace that line with 'CurrentSheet = ExcelSheets.Add()'. Also, try releasing the objects before calling ExcelApp.Quit() -- you release them after calling Quit(). — David Ching, Mar 05 '15 at 14:12
http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects — Sibster, Mar 05 '15 at 14:14
http://stackoverflow.com/questions/2191489/releasing-temporary-com-objects — Henrik, Mar 05 '15 at 14:45

score 0 · Answer 1 · answered Mar 05 '15 at 14:25

I recommend calling Marshal.FinalReleaseComObject instead of Marshal.ReleaseComObject. Although it works a bit better in my experience, Excel still remains running sometimes.

Therefore I usually monitor the started Excel process and kill it directly. Obviously this method is not very elegant and has some caveats, e.g. when Excel is started by the user at the same time, but I have not found a better solution to the problem yet than to completely get rid of Office interop.

// start excel

var before = Process.GetProcessesByName("Excel");
_application = new Excel.Application();
var after = Process.GetProcessesByName("Excel");

_process = after.Where(p => !before.Any(x => x.Id == p.Id)).FirstOrDefault();

// clean up

_application.Quit();
Marshal.FinalReleaseComObject(_application);
_application = null;

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

if (_process != null && !_process.HasExited)
{
    _process.Kill();
}

A note about your code: You set objto null in the releaseObject method, however the COM instances will still remain in the properties. You need to mark the obj parameter with ref and use explicit fields instead of automatic properties.

score 0 · Answer 2 · answered Mar 05 '15 at 14:31

0

You set CurrentSheet in the ctor, do not release it, then set it again in the line causing the leak. Before executing the line causing the leak you need to do if(CurrentSheet != null) releaseObject(CurrentSheet);.

answered Mar 05 '15 at 14:31

David Ching

1,903
17
21

I believe you maybe right with this comment `CurrentSheet = ExcelWorkbook.Worksheets.Add(); //this cause the process remains`. Try creating a object to hold the worksheets collection, then creating your new sheet from that collection object. For example `Worksheets sheets = ExcelWorkbook.Worksheets;` // newline `CurrentSheet = sheets.Add();`. As a good rule of thumb, never use two dots in any statement when mixing .NET and COM. – Seth Mar 05 '15 at 21:41

How can i dispose Excel.interop COM object properly in c#?

2 Answers2