C# 互操作：将新工作表添加到现有文件后，excel 进程未退出

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

提问by yoyoyoyosef

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

可能的重复：
如何在 C# 中正确清理 Excel 互操作对象

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.

我在这里阅读了许多关于在使用 .Net-Excel 互操作时管理 COM 引用以确保 Excel 进程在退出时正确退出的其他线程，到目前为止，这些技术一直运行良好，但我最近遇到了将新工作表添加到现有工作簿文件时出现问题。

The code below leaves a zombie Excel process.

下面的代码留下了一个僵尸 Excel 进程。

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)

如果我将工作表添加到新创建的工作簿文件，它会正常退出。如果我运行不包括该.Add()行的代码，它会正常退出。（我正在读取的现有文件是由注释掉的代码创建的空文件）

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();
}

采纳答案by Andrew Rollings

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).

我手头没有代码，但我确实遇到了类似的问题。如果我没记错的话，我最终检索了 excel 实例的进程 ID，并杀死了它（在适当的等待期之后，并且当其他方法失败时）。

I think I used:

我想我用过：

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

GetWindowThreadProcessId（通过P/Invoke）在excel对象的hwnd属性上获取进程id，然后用来Process.GetProcessById获取进程对象。一旦我这样做了，我就会调用Kill这个过程。

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 Killon the process object immediately... You could first try calling Closebefore resorting to Kill.

EDIT2：您不必Kill立即调用process 对象...您可以先尝试调用，Close然后再使用Kill.

回答by Simon Wilson

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.

我知道不是很有建设性，但我完全按照上面显示的方式测试了代码，我的 Excel 进程按预期退出，我的 C:\addtest.xls 有 8 个新工作表，没有 Excel 进程正在运行。
互操作版本可能是我想知道的原因吗？我用 11 和 12 进行了测试。

回答by Jon

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:

我也做过类似的事情。我创建一个 Excel 文件或打开一个现有的。我删除所有工作表并添加我自己的工作表。这是我用来确保关闭所有引用的代码：

            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.

我已经用许多不同的选项对此进行了测试，但还没有失败。

回答by Jon

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

我正在使用 VB.NET 3.5 SP1 并且以下代码仍然打开 EXCEL.EXE：

        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)

回答by Kenny Mann

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;
    }
}
}

回答by AlanR

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

这是我使用 Office12 .Net 互操作库创建的 Excel 的完整代码：享受，-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.
    }
}

回答by io.gun

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