Cannot close Excel.exe after Interop process

I'm having an issue with Excel Interop.

The Excel.exe doesn't close even if when I realease instances.

Here is my code :

using xl = Microsoft.Office.Interop.Excel;


xl.Application excel = new xl.Application();
excel.Visible = true;
excel.ScreenUpdating = false;
if (wordFile.Contains(".csv") || wordFile.Contains(".xls"))
{
   //typeExcel become a string of the document name
   string typeExcel = wordFile.ToString();
   xl.Workbook workbook = excel.Workbooks.Open(typeExcel,
                                                oMissing,  oMissing,  oMissing,  oMissing,
                                                oMissing,  oMissing,  oMissing,  oMissing,
                                                oMissing,  oMissing,  oMissing,  oMissing,
                                                oMissing,  oMissing);
   object outputFileName = null;
   if (wordFile.Contains(".xls"))
   {
     outputFileName = wordFile.Replace(".xls", ".pdf");
   }
   else if (wordFile.Contains(".csv"))
   {
     outputFileName = wordFile.Replace(".csv", ".pdf");
   }

   workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, outputFileName, 
                                 XlFixedFormatQuality.xlQualityStandard, oMissing,
                                 oMissing, oMissing, oMissing, oMissing, oMissing);

   object saveChanges = xl.XlSaveAction.xlDoNotSaveChanges;
   ((xl._Workbook)workbook).Close(saveChanges, oMissing, oMissing);

   Marshal.ReleaseComObject(workbook);
   workbook = null;
}

I saw that, with the Marshal.RealeaseComObject it should be work, but nothing. How can I fix this?

Thank you.

Solution 1:

Simple rule: avoid using double-dot-calling expressions, such as this:

var workbook = excel.Workbooks.Open(/*params*/)

...because in this way you create RCW objects not only for workbook, but for Workbooks, and you should release it too (which is not possible if a reference to the object is not maintained).

So, the right way will be:

var workbooks = excel.Workbooks;
var workbook = workbooks.Open(/*params*/)

//business logic here

Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(workbooks);
Marshal.ReleaseComObject(excel);

Solution 2:

Here is a snippet of code I wrote, because I had the same problem as you. Basically, you need to close the workbook, quit the application, and then release ALL of your COM objects (not just the Excel Application object). Finally, call the garbage collector for good measure.

    /// <summary>
    /// Disposes the current <see cref="ExcelGraph" /> object and cleans up any resources.
    /// </summary>
    public void Dispose()
    {
        // Cleanup
        xWorkbook.Close(false);
        xApp.Quit();

        // Manual disposal because of COM
        while (Marshal.ReleaseComObject(xApp) != 0) { }
        while (Marshal.ReleaseComObject(xWorkbook) != 0) { }
        while (Marshal.ReleaseComObject(xWorksheets) != 0) { }
        while (Marshal.ReleaseComObject(xWorksheet) != 0) { }
        while (Marshal.ReleaseComObject(xCharts) != 0) { }
        while (Marshal.ReleaseComObject(xMyChart) != 0) { }
        while (Marshal.ReleaseComObject(xGraph) != 0) { }
        while (Marshal.ReleaseComObject(xSeriesColl) != 0) { }
        while (Marshal.ReleaseComObject(xSeries) != 0) { }
        xApp = null;
        xWorkbook = null;
        xWorksheets = null;
        xWorksheet = null;
        xCharts = null;
        xMyChart = null;
        xGraph = null;
        xSeriesColl = null;
        xSeries = null;

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

Solution 3:

Rules - never use no more that one dot

-- one dot

var range = ((Range)xlWorksheet.Cells[rowIndex, setColumn]);
var hyperLinks = range.Hyperlinks;
hyperLinks.Add(range, data);

-- Two or more dots

 (Range)xlWorksheet.Cells[rowIndex, setColumn]).Hyperlinks.Add(range, data);

-- Example

 using Microsoft.Office.Interop.Excel;

 Application xls = null;
 Workbooks workBooks = null;
 Workbook workBook = null;
 Sheets sheets = null;
 Worksheet workSheet1 = null;
 Worksheet workSheet2 = null;

 workBooks = xls.Workbooks;
 workBook = workBooks.Open(workSpaceFile);
 sheets = workBook.Worksheets;
 workSheet1 = (Worksheet)sheets[1];


// removing from Memory
 if (xls != null)
 {    
   foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets)
   {
      ReleaseObject(sheet);
   }

   ReleaseObject(sheets);
   workBook.Close();
   ReleaseObject(workBook);
   ReleaseObject(workBooks);

   xls.Application.Quit(); // THIS IS WHAT IS CAUSES EXCEL TO CLOSE
   xls.Quit();
   ReleaseObject(xls);

   sheets = null;
   workBook = null;
   workBooks = null;
   xls = null;

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

Solution 4:

It is tricky to get rid of all references since you have to guess if calls like:

var workbook = excel.Workbooks.Open("")

Creates an instance of Workbooks that you do not hold a reference to.

Even references like:

targetRange.Columns.AutoFit()

Will create an instance of .Columns() without you knowing and not released properly.

I ended up writing a class holding a list of object references that could dispose all objects in reverse order.

The class has a list of objects and Add() functions for anything you reference as you use Excel interop that returns the object itself:

    public List<Object> _interopObjectList = new List<Object>();

    public Excel.Application add(Excel.Application obj)
    {
        _interopObjectList.Add(obj);
        return obj;
    }

    public Excel.Range add(Excel.Range obj)
    {
        _interopObjectList.Add(obj);
        return obj;
    }

    public Excel.Workbook add(Excel.Workbook obj)
    {
        _interopObjectList.Add(obj);
        return obj;
    }

    public Excel.Worksheet add(Excel.Worksheet obj)
    {
        _interopObjectList.Add(obj);
        return obj;
    }

    public Excel.Worksheets add(Excel.Worksheets obj)
    {
        _interopObjectList.Add(obj);
        return obj;
    }

    public Excel.Sheets add(Excel.Sheets obj)
    {
        _interopObjectList.Add(obj);
        return obj;
    }


    public Excel.Workbooks add(Excel.Workbooks obj)
    {
        _interopObjectList.Add(obj);
        return obj;
    }

Then to unregister objects I used the following code:

    //Release all registered interop objects in reverse order
    public void unregister()
    {
        //Loop object list in reverse order and release Office object
        for (int i=_interopObjectList.Count-1; i>=0 ; i -= 1)
        { ReleaseComObject(_interopObjectList[i]); }

        //Clear object list
        _interopObjectList.Clear();
    }


    /// <summary>
    /// Release a com interop object 
    /// </summary>
    /// <param name="obj"></param>
     public static void ReleaseComObject(object obj)
     {
         if (obj != null && InteropServices.Marshal.IsComObject(obj))
             try
             {
                 InteropServices.Marshal.FinalReleaseComObject(obj);
             }
             catch { }
             finally
             {
                 obj = null;
             }

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

Then principle is to create the class and capture references like this:

//Create helper class
xlsHandler xlObj = new xlsHandler();

..

//Sample - Capture reference to excel application
Excel.Application _excelApp = xlObj.add(new Excel.Application());

..
//Sample - Call .Autofit() on a cell range and capture reference to .Columns() 
xlObj.add(_targetCell.Columns).AutoFit();

..

//Release all objects collected by helper class
xlObj.unregister();

Not perhaps code of great beauty but may inspire to something useful.