Excel Instance wont close after Interop Operations

Question

I'm building a new Excel workbook in c# by combining the first sheet of a series of different Excel workbooks; subsequently I export the new Workbook to PDF. I made this work, but there is always one Excel instance running by the end of the method.

I had the same issue discussed here with a simpler setup and less Excel objects that I could solve with the GC.Collect command. Now, none of this is working.

public void CombineWorkBooks()
    {
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

        xlApp.DisplayAlerts = false;
        xlApp.Visible = false;

        Workbooks newBooks = null;
        Workbook newBook = null;
        Sheets newBookWorksheets = null;
        Worksheet defaultWorksheet = null;

        // Create a new workbook, comes with an empty default worksheet");
        newBooks = xlApp.Workbooks;

        newBook = newBooks.Add(XlWBATemplate.xlWBATWorksheet);
        newBookWorksheets = newBook.Worksheets;

        // get the reference for the empty default worksheet
        if (newBookWorksheets.Count > 0)
        {
            defaultWorksheet = newBookWorksheets[1] as Worksheet;
        }

        // loop through every line in Gridview and get the path' to each Workbook
        foreach (GridViewRow row in CertificadosPresion.Rows)
        {
            string path = row.Cells[0].Text;
            string CertName = CertificadosPresion.DataKeys[row.RowIndex].Value.ToString();

            Workbook childBook = null;
            Sheets childSheets = null;

            // Excel of each line in Gridview
            childBook = newBooks.Open(path,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            childSheets = childBook.Worksheets;

            if (childSheets != null)
            {
                // Build a new Worksheet
                Worksheet sheetToCopy = null;

                // Only first Worksheet of the Workbook belonging to that line
                sheetToCopy = childSheets[1] as Worksheet;
                if (sheetToCopy != null)
                {
                    // Assign the Certificate Name to the new Worksheet 
                    sheetToCopy.Name = CertName;
                    // set PageSetup for the new Worksheet to be copied
                    sheetToCopy.PageSetup.Zoom = false;
                    sheetToCopy.PageSetup.FitToPagesWide = 1;
                    sheetToCopy.PageSetup.FitToPagesTall = 1;
                    sheetToCopy.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                    // Copy that new Worksheet to the defaultWorksheet
                    sheetToCopy.Copy(defaultWorksheet, Type.Missing);
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheetToCopy);
                childBook.Close(false, Type.Missing, Type.Missing);
                
            }
            System.Runtime.InteropServices.Marshal.ReleaseComObject(childSheets);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(childBook);
        }

        //Delete the empty default worksheet
        if (defaultWorksheet != null) defaultWorksheet.Delete();

        //Export to PDF
        newBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, @"C:\pdf\" + SALESID.Text + "_CertPres.pdf", 0, false, true);

        newBook.Close();
        newBooks.Close();
        xlApp.DisplayAlerts = true;
        DownloadFile(SALESID.Text);

        System.Runtime.InteropServices.Marshal.ReleaseComObject(defaultWorksheet);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(newBookWorksheets);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(newBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(newBooks);

        xlApp.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

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

        protected void DownloadFile(string Salesid)
    {
        string path = @"c:\\pdf\" + Salesid + "_CertPres.pdf";
        byte[] bts = System.IO.File.ReadAllBytes(path);
        Response.Clear();
        Response.ClearHeaders();
        Response.AddHeader("Content-Type", "Application/octet-stream");
        Response.AddHeader("Content-Length", bts.Length.ToString());
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Salesid + "_CertPres.pdf");
        Response.BinaryWrite(bts);
        Response.Flush();
        Response.End();
    }

The problem must have been related to the call of the DownloadFile Method. I eliminated that call, and the Excel process was properly closed. Some of these operations must have kept a reference to one of the COM objects open, so that they could not be closed. By calling "DownloadFile" at the very end after the GarbageCollect the problem is solved. (I'm not quite sure why)

Have you tried killing that excel process https://msdn.microsoft.com/en-us/library/system.diagnostics.process.kill(v=vs.110).aspx — Ali Hasan, Jan 04 '16 at 13:18
Have you read http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects and tried all the approaches there? If yes, please create a [mcve] to reproduce the issue (your code is short, but it's obviously not minimal yet). — Heinzi, Jan 04 '16 at 13:26
@Ali Hasan: Killing the process was supposed to be the very last resource; I'm running a web application with possible contemporary users, so killing any excel instance is not a nice solution. — Barnabeck, Jan 05 '16 at 10:22
@Heinzi: I double checked the 2 dot rule that are well explained in that link. Building a minimal example lead me to the solution, so many thanks to you too: Removing the DownloadFile call made the Excel process disappear! — Barnabeck, Jan 05 '16 at 10:26
@Barnabeck: Yes, minimal examples sometimes do that to you. ;-) DownloadFile? But why... ah, now I understand *why* this happens. I'll write an answer about it. — Heinzi, Jan 05 '16 at 13:31

score 0 · Answer 1 · answered Jan 04 '16 at 13:21

0

I found that sometimes the only thing that helps is the "sledgehammer method". Killing all running excel instances:

foreach (Process p in Process.GetProcessesByName("EXCEL"))
{
    try
    {
        p.Kill();
        p.WaitForExit();
    }
    catch
    {
        //Handle exception here
    }
}

answered Jan 04 '16 at 13:21

waka

3,362
9
35
54

same as Ali Hasan: Killing the process was supposed to be the very last resource; I'm running a web application with possible contemporary users, so killing any excel instance is not a nice solution. – Barnabeck Jan 05 '16 at 10:27

score 0 · Answer 2 · answered Jan 05 '16 at 09:16

0

Looks to me that you have a reference not cleaned up. Probably something like the 'two dot rule' problem - which in my opinion is a silly rule because you can't code anything decent because it's to difficult to keep track of.

You could try Marshal.ReleaseComObject of your COM references but still asking for trouble...

My suggestion would be to try using VSTO to automate Excel. This will clear your references correctly on your behalf.

https://social.msdn.microsoft.com/Forums/vstudio/en-US/a12add6b-99ea-4677-8245-cd667101683e/vsto-and-office-objects-disposing

answered Jan 05 '16 at 09:16

PaulG

1,051
7
9

It was not the "2 dot rule", the call of the DownloadFile method -as explained above- must have kept some reference alive, though I do not understand why. – Barnabeck Jan 05 '16 at 10:41
Yup, I believe you :) – PaulG Jan 05 '16 at 10:42
Yup, I believe you :) My point is it will be something like this. Every time you transverse down the Excel object model there are various objects that get returned which are very easy to miss. Although you'll eventually find a fix by trial and error and various ReleaseCOMObjects etc, it will probably occur again if you change something later. Infact you can examine the return value of ReleaseCOMObjects to get the reference counts but it isn't reliable. If you use VSTO, this will be cleaned up when the application domain is pulled down. You can of course try to create your own app domain.. – PaulG Jan 05 '16 at 10:49

score 0 · Accepted Answer · edited May 23 '17 at 12:15

In your method DownloadFile, you call

Response.End()

HttpResponse.End throws an exception (emphasis mine):

To mimic the behavior of the End method in ASP, this method tries to raise a ThreadAbortException exception. If this attempt is successful, the calling thread will be aborted, [...]

This exception aborts your thread. Thus, all your ReleaseComObject, Excel.Quit, GC.Collect stuff is never executed.

The solution: Don't call Response.End. You probably don't need it. If you need it, you might want to consider the alternative mentioned in the documentation instead:

This method is provided only for compatibility with ASP—that is, for compatibility with COM-based Web-programming technology that preceded ASP.NET. If you want to jump ahead to the EndRequest event and send a response to the client, it is usually preferable to call CompleteRequest instead.

[...]

The CompleteRequest method does not raise an exception, and code after the call to the CompleteRequest method might be executed

PS: Using Excel automation from a web application is not officially supported by Microsoft. For future development, you might want to consider using a third-party Excel library instead.

Great explanation! Now everything is clear again, and as you were suggesting I didn't need the Response.End. I read so often that the Excel automation from web application is not supported by Microsoft, but now that everything works the way I want it to, why should I care? ;) — Barnabeck, Jan 06 '16 at 22:39

Excel Instance wont close after Interop Operations

3 Answers3