1

I am using c# to create excel file. When my program is running the excel instance is created and showing in task manager. That is normal, but when my program closes, then the excel instance should not be shown in task manager, but it's showing. Please review my code and tell me what is my mistake there which causes the excel instance to be in task manager even after closing my apps. Here is my code.

private void GenerateExcel()
    Excel.Application xlApp = new Excel.Application();

    if (xlApp == null)
    {

        return;
    }


    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    xlWorkBook.Windows.Application.ActiveWindow.DisplayGridlines = false;
    xlWorkSheet.Cells[2, 2] = "Sheet 1 content";



    if (!Directory.Exists(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\Reports"))
    {
        Directory.CreateDirectory(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\Reports");
    }

    string strpath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "\\Reports\\test.xls";
    if (File.Exists(strpath))
    {
        File.Delete(strpath);
    }

    xlWorkBook.SaveAs(strpath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);

}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        //MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}
recursive
  • 83,943
  • 34
  • 151
  • 241
Monojit Sarkar
  • 2,353
  • 8
  • 43
  • 94
  • 1
    1) Make sure Excel isn't running before you test this. – H H Apr 29 '16 at 14:43
  • 2) The Close() and ReleaseObject() calls should be in a finally block – H H Apr 29 '16 at 14:44
  • 1
    In my experience it's usually due to your app holding onto references to COM objects due to 'double dots' - see [How to properly clean up Excel interop objects?](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects/158752#158752), or due to having documents unsaved - see [_Application.Quit method - MSDN](https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.quit.aspx) – Tone Apr 29 '16 at 14:50

1 Answers1

1

Every time you invoke a member of a COM object that object must be assigned to a variable and explicitly released. For example, this:

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Creates an unreleased reference to .Worksheets. It needs to be 

var worksheets = (Excel.Worksheet)xlWorkBook.Worksheets;
xlWorkSheet = worksheets.get_Item(1);

and eventually

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheets);

Unless every COM object is released the application will remain running.

In order to work with it I found myself either creating lots and lots of try/finally blocks or helper functions for cases like the above that would wrap the call to .Worksheets and ensure that any objects created were released. It can work, but it seems to negate the benefit of working in a framework that otherwise handles garbage collection. We all know to Dispose an IDisposable, but it's obviously not intuitive that one would need to release an object that a) wasn't explicitly created and b) doesn't implement IDisposable, and the consequences of not knowing that (or making a single mistake) is having left over instances of Excel running in the background. (End of rant.)

My earnest recommendation is to avoid using interop and automation if possible. If you must generate Excel, try a library like EPPlus. The interop was designed before Excel objects became essentially zipped collections of XML documents. Now you don't even need to have Excel installed or running to create or manipulate documents. The format they are stored in is perplexing, but the library mostly hides that for you. The code you write can even run on a web server and generate Excel in real time in response to web requests.

Scott Hannen
  • 27,588
  • 3
  • 45
  • 62