C# interop: excel process not exiting after adding new worksheet to existing file

Question

Possible Duplicate:
How to properly clean up Excel interop objects in C#

I've read many of the other threads here about managing COM references while using the .Net-Excel interop to make sure the Excel process exits correctly upon exit, and so far the techniques have been working very well, but I recently came across a problem when adding new worksheets to an existing workbook file.

The code below leaves a zombie Excel process.

If I add a worksheet to a newly created workbook file, it exits fine. If I run the code excluding the .Add() line, it exits fine. (The existing file I'm reading from is an empty file created by the commented out code)

Any ideas?

//using Excel = Microsoft.Office.Interop.Excel;
//using System.Runtime.InteropServices;
public static void AddTest()
{
  string filename = @"C:\addtest.xls";
  object m = Type.Missing;
  Excel.Application excelapp = new Excel.Application();
  if (excelapp == null) throw new Exception("Can't start Excel");
  Excel.Workbooks wbs = excelapp.Workbooks;

  //if I create a new file and then add a worksheet,
  //it will exit normally (i.e. if you uncomment the next two lines
  //and comment out the .Open() line below):
  //Excel.Workbook wb = wbs.Add(Excel.XlWBATemplate.xlWBATWorksheet);
  //wb.SaveAs(filename, m, m, m, m, m, 
  //          Excel.XlSaveAsAccessMode.xlExclusive,
  //          m, m, m, m, m);

  //but if I open an existing file and add a worksheet,
  //it won't exit (leaves zombie excel processes)
  Excel.Workbook wb = wbs.Open(filename,
                               m, m, m, m, m, m,
                               Excel.XlPlatform.xlWindows,
                               m, m, m, m, m, m, m);

  Excel.Sheets sheets = wb.Worksheets;

  //This is the offending line:
  Excel.Worksheet wsnew = sheets.Add(m, m, m, m) as Excel.Worksheet; 

  //N.B. it doesn't help if I try specifying the parameters in Add() above

  wb.Save();
  wb.Close(m, m, m);

  //overkill to do GC so many times, but shows that doesn't fix it
  GC();
  //cleanup COM references
  //changing these all to FinalReleaseComObject doesn't help either
  while (Marshal.ReleaseComObject(wsnew) > 0) { } 
  wsnew = null;
  while (Marshal.ReleaseComObject(sheets) > 0) { }
  sheets = null;
  while (Marshal.ReleaseComObject(wb) > 0) { }
  wb = null;
  while (Marshal.ReleaseComObject(wbs) > 0) { }
  wbs = null;
  GC();
  excelapp.Quit();
  while (Marshal.ReleaseComObject(excelapp) > 0) { }
  excelapp = null;
  GC();
}

public static void GC()
{
  System.GC.Collect();
  System.GC.WaitForPendingFinalizers();
  System.GC.Collect();
  System.GC.WaitForPendingFinalizers();
}

If the GC method doesn't work because you are still holding a reference somewhere. Are you sure that's all the relevant code? — Vinko Vrsalovic, Dec 10 '09 at 22:24
Yes, I get a zombie process left behind running the code above exactly as written — yoyoyoyosef, Dec 11 '09 at 13:47

score 13 · Answer 1 · answered Dec 10 '08 at 15:56

I have done a similar thing. I create an Excel file or open an existing. I delete all the sheets and add my own. here is the code I use to ensure all references are closed:

            workbook.Close(true, null, null);
            excelApp.Quit();

            if (newSheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
            }
            if (rangeSelection != null)
            {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(rangeSelection);
            }
            if (sheets != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
            }
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
            }
            if (excelApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            }

            newSheet = null;
            rangeSelection = null;
            sheets = null;
            workbook = null;
            excelApp = null;

            GC.Collect();

I have tested this with many different options and not had it fail on me yet.

score 4 · Answer 2 · answered Feb 16 '10 at 15:27

here's my full code to kill the Excel you created with the Office12 .Net interop library: Enjoy, -Alan.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;

class Program
{

    /// <summary> 
    /// Win32 API import for getting the process Id. 
    /// The out param is the param we are after. I have no idea what the return value is. 
    /// </summary> 
    [DllImport("user32.dll")]
    private static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out IntPtr ProcessId); 

    static void Main(string[] args)
    {
        var app = new Application();
        IntPtr hwnd = new IntPtr(app.Hwnd);
        IntPtr processId;
        IntPtr foo = GetWindowThreadProcessId(hwnd, out processId);
        Process proc = Process.GetProcessById(processId.ToInt32());
        proc.Kill(); // set breakpoint here and watch the Windows Task Manager kill this exact EXCEL.EXE
        app.Quit(); // should give you a "Sorry, I can't find this Excel session since you killed it" Exception.
    }
}

I am using your code and getting back a processId of zero. Any ideas of what could cause this to happen? — thiag0, Apr 11 '11 at 20:48

Andrew Rollings · Accepted Answer · 2008-12-10T15:12:27.303

I don't have the code to hand, but I did run into a similar problem. If I recall correctly, I ended up retrieving the process id of the excel instance, and killing it (after a suitable wait period, and when the other method failed).

I think I used:

GetWindowThreadProcessId (via P/Invoke) on the excel object hwnd property to get the process id, and then used Process.GetProcessById to get a process object. Once I'd done that, I'd call Kill on the process.

EDIT: I have to admit, this isn't the ideal solution, but if you can't find the rogue interface that isn't being released, then this will fix it in true eggshell/sledgehammer fashion. ;)

EDIT2: You don't have to call Kill on the process object immediately... You could first try calling Close before resorting to Kill.

score 1 · Answer 4 · answered Aug 09 '11 at 21:02

This is works very fine for me, without any exceptions.

Public Class ExcelHlpr

    Declare Function EndTask Lib "user32.dll" (ByVal hWnd As IntPtr, ByVal ShutDown As Boolean, ByVal Force As Boolean) As Integer

    Dim cXlApp As Microsoft.Office.Interop.Excel.Application

    Public Function GetExcel() As Microsoft.Office.Interop.Excel.Application
        cXlApp = New Microsoft.Office.Interop.Excel.Application
        Return cXlApp
    End Function

    Public Function EndExcel() As Integer
        Dim xlHwnd As New IntPtr(cXlApp.Hwnd)
        Return EndTask(xlHwnd, False, True)
    End Function

End Class

score 0 · Answer 5 · answered Dec 08 '08 at 20:22

0

Not very constructive I know but I tested the code exactly as shown above and my Excel process exits as expected, my C:\addtest.xls is sitting with 8 new sheets and no Excel process is running.
Could the interop version be the cause I wonder? I tested with 11 & 12.

answered Dec 08 '08 at 20:22

Simon Wilson

9,929
3
27
24

Can you get other guys in your shop replicate it? I just had 2 other guys stop there work :) and try with the no orphaned processes. – Simon Wilson Dec 08 '08 at 20:44
By "there" I meant "their", me English not gud – Simon Wilson Dec 08 '08 at 20:45
yep, replicated here on a different machine. Both are using PIA dll version 11.0.0.0, one with Excel 2003 SP2 (11.6560.6568) installed, the other with Excel 2003 SP3 (11.8231.8221). – yoyoyoyosef Dec 08 '08 at 21:47
Just a thought here. Ensure you kill al Excel process running then add a try{}finally{} block after Excel.Application excelapp = new Excel.Application() and stick the excelApp cleanup in he finally and see if it is still happening. I know...but give it a shot. – Simon Wilson Dec 08 '08 at 22:54

score 0 · Answer 6 · answered Feb 19 '09 at 15:04

I'm using VB.NET 3.5 SP1 and the following code STILL leaves EXCEL.EXE open:

        xlWorkbook.Close(SaveChanges:=False)
        xlApplication.Quit()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorksheet)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApplication)

        xlRange = Nothing
        xlWorksheet = Nothing
        xlSheets = Nothing
        xlWorkbook = Nothing
        xlApplication = Nothing

        GC.GetTotalMemory(False)
        GC.Collect()
        GC.WaitForPendingFinalizers()

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.GetTotalMemory(True)

move this code GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() above the ReleaseCOMObject calls change the ReleaseCOMObject calls to FinalReleaseCOMObject calls — Anonymous Type, Feb 14 '10 at 22:40

Kenny Mann · Answer 7 · 2009-03-16T14:57:32.927

Andrew, here is the code I've found that works. I thought I post post it here for others who come across:

namespace WindowHandler
{
using System;
using System.Text;
using System.Collections;
using System.Runtime.InteropServices;

/// <summary>
/// Window class for handling window stuff.
/// This is really a hack and taken from Code Project and mutilated to this small thing.
/// </summary>
public class Window
{
    /// <summary>
    /// Win32 API import for getting the process Id.
    /// The out param is the param we are after. I have no idea what the return value is.
    /// </summary>
    [DllImport("user32.dll")]
    private static extern IntPtr GetWindowThreadProcessId(IntPtr hWnd, out IntPtr ProcessId);

    /// <summary>
    /// Gets a Window's process Id.
    /// </summary>
    /// <param name="hWnd">Handle Id.</param>
    /// <returns>ID of the process.</returns>
    public static IntPtr GetWindowThreadProcessId(IntPtr hWnd)
    {
        IntPtr processId;
        IntPtr returnResult = GetWindowThreadProcessId(hWnd, out processId);

        return processId;
    }
}
}

C# interop: excel process not exiting after adding new worksheet to existing file

7 Answers7

Linked