找到你要的答案

Q:How do I properly clean up Excel interop objects?

Q:我该如何正确地清理Excel程序的对象吗?

I'm using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause:

while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();

Although this kind of works, the Excel.exe process is still in the background even after I close Excel. It is only released once my application is manually closed.

What am I doing wrong, or is there an alternative to ensure interop objects are properly disposed of?

我用Excel程序在C #(applicationclass),把下面的代码在我的最后条款:

while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();

虽然这类作品,即使我关闭Excel的excel.exe过程仍然在后台。只有在我的应用程序手动关闭时才发布。

我做错了什么,或者是有另一种确保互操作对象是妥善处置?

answer1: 回答1:

Just to add another solution to the many listed here, using C++/ATL automation (I imagine you could use something similar from VB/C#??)

Excel::_ApplicationPtr pXL = ...
  :
SendMessage ( ( HWND ) m_pXL->GetHwnd ( ), WM_DESTROY, 0, 0 ) ;

This works like a charm for me...

只是要添加另一个解决了许多在这里列出,使用C++ / ATL自动化(我想你可以利用VB / C #类似的东西吗??)

Excel::_ApplicationPtr pXL = ...
  :
SendMessage ( ( HWND ) m_pXL->GetHwnd ( ), WM_DESTROY, 0, 0 ) ;

这对我来说就像一个魅力…

answer2: 回答2:

I am currently working on Office automation and have stumbled across a solution for this that works every time for me. It is simple and does not involve killing any processes.

It seems that by merely looping through the current active processes, and in any way 'accessing' an open Excel process, any stray hanging instance of Excel will be removed. The below code simply checks for processes where the name is 'Excel', then writes the MainWindowTitle property of the process to a string. This 'interaction' with the process seems to make Windows catch up and abort the frozen instance of Excel.

I run the below method just before the add-in which I am developing quits, as it fires it unloading event. It removes any hanging instances of Excel every time. In all honesty I am not entirely sure why this works, but it works well for me and could be placed at the end of any Excel application without having to worry about double dots, Marshal.ReleaseComObject, nor killing processes. I would be very interested in any suggestions as to why this is effective.

public static void SweepExcelProcesses()
{           
            if (Process.GetProcessesByName("EXCEL").Length != 0)
            {
                Process[] processes = Process.GetProcesses();
                foreach (Process process in processes)
                {
                    if (process.ProcessName.ToString() == "excel")
                    {                           
                        string title = process.MainWindowTitle;
                    }
                }
            }
}

我目前在办公室自动化工作,并偶然发现了一个解决方案,这对我的每一次工作。它很简单,不涉及杀死任何进程。

看来,仅仅通过遍历当前活动的进程,并以任何方式访问一个打开Excel,Excel的任何杂散挂实例将被删除。下面的代码仅检查过程的名字是“Excel”,然后写过程的mainwindowtitle属性设置为一个字符串。这个“交互”的过程似乎使Windows赶上和中止冻结实例Excel。

我正在运行下面的方法,在我即将退出的插件中,当它触发卸载事件时。它每次删除Excel的任何挂起实例。老实说我也不清楚为什么这部作品,但它对我很有帮助,可以放置在任何Excel应用程序的结束而不必担心双点,marshal.releasecomobject,也杀死进程。我会非常感兴趣的任何建议,为什么这是有效的。

public static void SweepExcelProcesses()
{           
            if (Process.GetProcessesByName("EXCEL").Length != 0)
            {
                Process[] processes = Process.GetProcesses();
                foreach (Process process in processes)
                {
                    if (process.ProcessName.ToString() == "excel")
                    {                           
                        string title = process.MainWindowTitle;
                    }
                }
            }
}
answer3: 回答3:

So far it seems all answers involve some of these:

  1. Kill the process
  2. Use GC.Collect()
  3. Keep track of every COM object and release it properly.

Which makes me appreciate how difficult this issue is :)

I have been working on a library to simplify access to Excel, and I am trying to make sure that people using it won't leave a mess (fingers crossed).

Instead of writing directly on the interfaces Interop provides, I am making extension methods to make live easier. Like ApplicationHelpers.CreateExcel() or workbook.CreateWorksheet("mySheetNameThatWillBeValidated"). Naturally, anything that is created may lead to an issue later on cleaning up, so I am actually favoring killing the process as last resort. Yet, cleaning up properly (third option), is probably the least destructive and most controlled.

So, in that context I was wondering whether it wouldn't be best to make something like this:

public abstract class ReleaseContainer<T>
{
    private readonly Action<T> actionOnT;

    protected ReleaseContainer(T releasible, Action<T> actionOnT)
    {
        this.actionOnT = actionOnT;
        this.Releasible = releasible;
    }

    ~ReleaseContainer()
    {
        Release();
    }

    public T Releasible { get; private set; }

    private void Release()
    {
        actionOnT(Releasible);
        Releasible = default(T);
    }
}

I used 'Releasible' to avoid confusion with Disposable. Extending this to IDisposable should be easy though.

An implementation like this:

public class ApplicationContainer : ReleaseContainer<Application>
{
    public ApplicationContainer()
        : base(new Application(), ActionOnExcel)
    {
    }

    private static void ActionOnExcel(Application application)
    {
        application.Show(); // extension method. want to make sure the app is visible.
        application.Quit();
        Marshal.FinalReleaseComObject(application);
    }
}

And one could do something similar for all sorts of COM objects.

In the factory method:

    public static Application CreateExcelApplication(bool hidden = false)
    {
        var excel = new ApplicationContainer().Releasible;
        excel.Visible = !hidden;

        return excel;
    }

I would expect that every container will be destructed properly by the GC, and therefore automatically make the call to Quit and Marshal.FinalReleaseComObject.

Comments? Or is this an answer to the question of the third kind?

到目前为止,似乎所有的答案涉及其中的一些:

  1. Kill the process
  2. Use GC.Collect()
  3. Keep track of every COM object and release it properly.

这让我明白这个问题有多么困难:

我一直在一个图书馆,以简化访问Excel,我试图确保人们使用它不会留下一个烂摊子(手指交叉)。

而不是直接写在界面的互操作提供了,我正在扩展方法使生活更容易。像applicationhelpers。createexcel()或工作簿。CreateWorksheet(“mysheetnamethatwillbevalidated”)。当然,任何被创造出来的东西都可能导致清理后的问题,所以我实际上是喜欢杀死进程作为最后的手段。然而,清理正确(第三选项),可能是最破坏性和最可控的。

因此,在这种情况下,我想知道这不是最好的,使这样的东西:

public abstract class ReleaseContainer<T>
{
    private readonly Action<T> actionOnT;

    protected ReleaseContainer(T releasible, Action<T> actionOnT)
    {
        this.actionOnT = actionOnT;
        this.Releasible = releasible;
    }

    ~ReleaseContainer()
    {
        Release();
    }

    public T Releasible { get; private set; }

    private void Release()
    {
        actionOnT(Releasible);
        Releasible = default(T);
    }
}

我用“插销”来避免混淆和一次性。这个扩展IDisposable应该很容易的。

像这样的实现:

public class ApplicationContainer : ReleaseContainer<Application>
{
    public ApplicationContainer()
        : base(new Application(), ActionOnExcel)
    {
    }

    private static void ActionOnExcel(Application application)
    {
        application.Show(); // extension method. want to make sure the app is visible.
        application.Quit();
        Marshal.FinalReleaseComObject(application);
    }
}

一个可以做类似的各种COM对象。

工厂法:

    public static Application CreateExcelApplication(bool hidden = false)
    {
        var excel = new ApplicationContainer().Releasible;
        excel.Visible = !hidden;

        return excel;
    }

我希望每一个容器将破坏正常的GC,因此自动拨打电话辞职,marshal.finalreleasecomobject。

评论?或者这是对第三类问题的回答?

answer4: 回答4:

This is the only way that really works for me

        foreach (Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
        {
            proc.Kill();
        }

这是唯一真正适合我的方法

        foreach (Process proc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
        {
            proc.Kill();
        }
answer5: 回答5:

Excel is not designed to be programmed via C++ or C#. The COM API is specifically designed to work with Visual Basic, VB.NET, and VBA.

Also all the code samples on this page are not optimal for the simple reason that each call must cross a managed/unmanaged boundary and further ignore the fact that the Excel COM API is free to fail any call with a cryptic HRESULT indicating the RPC server is busy.

The best way to automate Excel in my opinion is to collect your data into as big an array as possible / feasible and send this across to a VBA function or sub (via Application.Run) which then performs any required processing. Furthermore - when calling Application.Run - be sure to watch for exceptions indicating excel is busy and retry calling Application.Run.

Excel的目的不是要通过C++或C #。COM API是专为Visual Basic,vb.net,VBA。

所有的示例代码在这个页面是不是最佳的理由很简单,每个电话必须跨托管/非托管边界进一步忽略Excel COM API是免费的失败任何电话与一个神秘的HRESULT表示RPC服务器忙。

自动化Excel我认为最好的方法是收集你的数据到尽可能大的数组/可行的发送到一个VBA函数或子(通过应用。运行),然后执行所需的任何处理。此外,调用应用程序时,运行一定要注意异常指示Excel是繁忙和重试调用application.run。

c#  excel  interop  com-interop