c# 和 excel 自动化 - 结束正在运行的实例

Question

提问by Sean

I'm attempting Excel automation through C#. I have followed all the instructions from Microsoft on how to go about this, but I'm still struggling to discard the final reference(s) to Excel for it to close and to enable the GC to collect it.

我正在通过 C# 尝试 Excel 自动化。我已按照 Microsoft 提供的所有说明进行操作，但我仍在努力放弃对 Excel 的最终引用以使其关闭并使 GC 能够收集它。

A code sample follows. When I comment out the code block that contains lines similar to:

代码示例如下。当我注释掉包含类似于以下行的代码块时：

Sheet.Cells[iRowCount, 1] = data["fullname"].ToString();

then the file saves and Excel quits. Otherwise the file saves but Excel is left running as a process. The next time this code runs it creates a new instance and they eventually build up.

然后文件保存并退出 Excel。否则文件会保存，但 Excel 会作为一个进程继续运行。下次运行此代码时，它会创建一个新实例，并且它们最终会建立起来。

Any help is appreciated. Thanks.

任何帮助表示赞赏。谢谢。

This is the barebones of my code:

这是我的代码的准系统：

        Excel.Application xl = null;
        Excel._Workbook wBook = null;
        Excel._Worksheet wSheet = null;
        Excel.Range range = null;

        object m_objOpt = System.Reflection.Missing.Value;

        try
        {
            // open the template
            xl = new Excel.Application();
            wBook = (Excel._Workbook)xl.Workbooks.Open(excelTemplatePath + _report.ExcelTemplate, false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            wSheet = (Excel._Worksheet)wBook.ActiveSheet;

            int iRowCount = 2;

            // enumerate and drop the values straight into the Excel file
            while (data.Read())
            {

                wSheet.Cells[iRowCount, 1] = data["fullname"].ToString();
                wSheet.Cells[iRowCount, 2] = data["brand"].ToString();
                wSheet.Cells[iRowCount, 3] = data["agency"].ToString();
                wSheet.Cells[iRowCount, 4] = data["advertiser"].ToString();
                wSheet.Cells[iRowCount, 5] = data["product"].ToString();
                wSheet.Cells[iRowCount, 6] = data["comment"].ToString();
                wSheet.Cells[iRowCount, 7] = data["brief"].ToString();
                wSheet.Cells[iRowCount, 8] = data["responseDate"].ToString();
                wSheet.Cells[iRowCount, 9] = data["share"].ToString();
                wSheet.Cells[iRowCount, 10] = data["status"].ToString();
                wSheet.Cells[iRowCount, 11] = data["startDate"].ToString();
                wSheet.Cells[iRowCount, 12] = data["value"].ToString();

                iRowCount++;
            }

            DirectoryInfo saveTo = Directory.CreateDirectory(excelTemplatePath + _report.FolderGuid.ToString() + "\");
            _report.ReportLocation = saveTo.FullName + _report.ExcelTemplate;
            wBook.Close(true, _report.ReportLocation, m_objOpt);
            wBook = null;

        }
        catch (Exception ex)
        {
            LogException.HandleException(ex);
        }
        finally
        {
            NAR(wSheet);
            if (wBook != null)
                wBook.Close(false, m_objOpt, m_objOpt);
            NAR(wBook);
            xl.Quit();
            NAR(xl);
            GC.Collect();
        }

private void NAR(object o)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
    }
    catch { }
    finally
    {
        o = null;
    }
}

Update

更新

No matter what I try, the 'clean' method or the 'ugly' method (see answers below), the excel instance still hangs around as soon as this line is hit:

无论我尝试什么，“干净”方法或“丑陋”方法（请参阅下面的答案），只要命中这一行，excel实例仍然会挂起：

wSheet.Cells[iRowCount, 1] = data["fullname"].ToString();

If I comment that line out (and the other similar ones below it, obviously) the Excel app exits gracefully. As soon as one line per above is uncommented, Excel sticks around.

如果我注释掉该行（显然还有其他类似的行），Excel 应用程序会正常退出。只要上面的一行被取消注释，Excel 就会继续存在。

I think I'm going to have to check if there's a running instance prior to assigning the xl variable and hook into that instead. I forgot to mention that this is a windows service, but that shouldn't matter, should it?

我想我将不得不在分配 xl 变量之前检查是否有一个正在运行的实例，然后将其挂钩。我忘了提到这是一个 Windows 服务，但这应该无关紧要，是吗？

Answer 1

回答by Ahmad Mageed

Add the following before your call to xl.Quit():

在调用 xl.Quit() 之前添加以下内容：

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

You can also use Marshal.FinalReleaseComObject()in your NAR method instead of ReleaseComObject. ReleaseComObjectdecrements the reference count by 1 while FinalReleaseComObjectreleases all references so the count is 0.

您还可以Marshal.FinalReleaseComObject()在 NAR 方法中使用而不是 ReleaseComObject。ReleaseComObject将引用计数减 1，而FinalReleaseComObject释放所有引用，因此计数为 0。

So your finally block would look like:

所以你的 finally 块看起来像：

finally
{
    GC.Collect(); 
    GC.WaitForPendingFinalizers(); 

    NAR(wSheet);
    if (wBook != null)
        wBook.Close(false, m_objOpt, m_objOpt);
    NAR(wBook);
    xl.Quit();
    NAR(xl);
}

Updated NAR method:

更新的 NAR 方法：

private void NAR(object o)
{
    try
    {
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(o);
    }
    catch { }
    finally
    {
        o = null;
    }
}

I had researched this awhile ago and in examples I found usually the GC related calls were at the end after closing the app. However, there's an MVP (Mike Rosenblum) that mentions that it ought to be called in the beginning. I've tried both ways and they've worked. I also tried it without the WaitForPendingFinalizers and it worked although it shouldn't hurt anything. YMMV.

我前段时间研究过这个，在示例中我发现通常在关闭应用程序后与 GC 相关的调用在最后。然而，有一个 MVP (Mike Rosenblum) 提到它应该在一开始就被调用。这两种方式我都试过了，都奏效了。我也在没有 WaitForPendingFinalizers 的情况下尝试过它，虽然它不应该伤害任何东西，但它仍然有效。天啊。

Here are the relevant links by the MVP I mentioned (they're in VB but it's not that different):

以下是我提到的 MVP 的相关链接（它们在 VB 中，但没有什么不同）：

Answer 2

回答by Andy Mikula

What I ended up doing to solve a similar problem was get the process Id and kill that as a last resort...

我最终为解决类似问题所做的是获取进程 ID 并将其作为最后的手段杀死......

[DllImport("user32.dll", SetLastError = true)]
   static extern IntPtr GetWindowThreadProcessId(int hWnd, out IntPtr lpdwProcessId);

...

objApp = new Excel.Application();

IntPtr processID;
GetWindowThreadProcessId(objApp.Hwnd, out processID);
excel = Process.GetProcessById(processID.ToInt32());

...

objApp.Application.Quit();
Marshal.FinalReleaseComObject(objApp);
_excel.Kill();

Answer 3

回答by Jamie Ide

Here's the contents of my hasn't-failed-yet finally block for cleaning up Excel automation. My application leaves Excel open so there's no Quit call. The reference in the commentwas my source.

这是用于清理 Excel 自动化的尚未失败的最终块的内容。我的应用程序让 Excel 保持打开状态，因此没有退出调用。评论中的参考是我的来源。

finally
{
    // Cleanup -- See http://www.xtremevbtalk.com/showthread.php?t=160433
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
    // Calls are needed to avoid memory leak
    Marshal.FinalReleaseComObject(sheet);
    Marshal.FinalReleaseComObject(book);
    Marshal.FinalReleaseComObject(excel);
}

Answer 4

回答by Mike Rosenblum

What is happening is that your call to:

发生的事情是您致电：

Sheet.Cells[iRowCount, 1] = data["fullname"].ToString();

Is essentially the same as:

本质上是一样的：

Excel.Range cell = Sheet.Cells[iRowCount, 1];
cell.Value = data["fullname"].ToString();

By doing it this way, you can see that you are creating an Excel.Rangeobject, and then assigning a value to it. This way also gives us a named reference to our range variable, the cellvariable, that allows us to release it directly if we wanted. So you could clean up your objects one of two ways:

通过这样做，您可以看到您正在创建一个Excel.Range对象，然后为其分配一个值。这种方式还为我们提供了对范围变量的命名引用，该cell变量允许我们在需要时直接释放它。因此，您可以通过以下两种方式之一清理对象：

(1) The difficult and ugly way:

(1)难且丑的方式：

while (data.Read())
{
    Excel.Range cell = Sheet.Cells[iRowCount, 1];
    cell.Value = data["fullname"].ToString();
    Marshal.FinalReleaseComObject(cell);

    cell = Sheet.Cells[iRowCount, 2];
    cell.Value = data["brand"].ToString();
    Marshal.FinalReleaseComObject(cell);

    cell = Sheet.Cells[iRowCount, 3];
    cell.Value = data["agency"].ToString();
    Marshal.FinalReleaseComObject(cell);

    // etc...
}

In the above, we are releasing each range object via a call to Marshal.FinalReleaseComObject(cell)as we go along.

在上面，我们通过调用来释放每个范围对象Marshal.FinalReleaseComObject(cell)。

(2) The easy and clean way:

(2)简单干净的方法：

Leave your code exactly as you currently have it, and then at the end you can clean up as follows:

保留您当前拥有的代码，然后最后您可以按如下方式进行清理：

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

if (wSheet != null)
{
    Marshal.FinalReleaseComObject(wSheet)
}
if (wBook != null)
{
    wBook.Close(false, m_objOpt, m_objOpt);
    Marshal.FinalReleaseComObject(wBook);
}
xl.Quit();
Marshal.FinalReleaseComObject(xl);

In short, your existing code is extremely close. If you just add calls to GC.Collect() and GC.WaitForPendingFinalizers() before your 'NAR' calls, I think it should work for you. (In short, both Jamie's code and Ahmad's code are correct. Jamie's is cleaner, but Ahmad's code is an easier "quick fix" for you because you would only have to add the calls to calls to GC.Collect() and GC.WaitForPendingFinalizers() to your existing code.)

简而言之，您现有的代码非常接近。如果您只是在“NAR”调用之前添加对 GC.Collect() 和 GC.WaitForPendingFinalizers() 的调用，我认为它应该适合您。（简而言之，Jamie 的代码和 Ahmad 的代码都是正确的。Jamie 的代码更简洁，但 Ahmad 的代码对您来说是一个更简单的“快速修复”，因为您只需要将调用添加到 GC.Collect() 和 GC.WaitForPendingFinalizers () 到您现有的代码。）

Jamie and Amhad also listed links to the .NET Automation Forumthat I participate on (thanks guys!) Here are a couple of related posts that I've made here on StackOverflow :

Jamie 和 Amhad 还列出了我参与的.NET 自动化论坛的链接（谢谢大家！）以下是我在StackOverflow上发表的一些相关帖子：

(1) How to properly clean up Excel interop objects in C#

(1)如何在C#中正确清理Excel互操作对象

(2) C# Automate PowerPoint Excel -- PowerPoint does not quit

(2) C# Automate PowerPoint Excel -- PowerPoint不退出

I hope this helps, Sean...

我希望这会有所帮助，肖恩...

Mike

麦克风

Answer 5

回答by Joe Erickson

Have you considered using a pure .NET solution such as SpreadsheetGear for .NET? Here is what your code might like like using SpreadsheetGear:

您是否考虑过使用纯 .NET 解决方案，例如用于 .NET 的 SpreadsheetGear？以下是您的代码可能喜欢使用 SpreadsheetGear：

// open the template            
using (IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet())
{
    IWorkbook wBook = workbookSet.Workbooks.Open(excelTemplatePath + _report.ExcelTemplate);
    IWorksheet wSheet = wBook.ActiveWorksheet;
    int iRowCount = 2;
    // enumerate and drop the values straight into the Excel file            
    while (data.Read())
    {
        wSheet.Cells[iRowCount, 1].Value = data["fullname"].ToString();
        wSheet.Cells[iRowCount, 2].Value = data["brand"].ToString();
        wSheet.Cells[iRowCount, 3].Value = data["agency"].ToString();
        wSheet.Cells[iRowCount, 4].Value = data["advertiser"].ToString();
        wSheet.Cells[iRowCount, 5].Value = data["product"].ToString();
        wSheet.Cells[iRowCount, 6].Value = data["comment"].ToString();
        wSheet.Cells[iRowCount, 7].Value = data["brief"].ToString();
        wSheet.Cells[iRowCount, 8].Value = data["responseDate"].ToString();
        wSheet.Cells[iRowCount, 9].Value = data["share"].ToString();
        wSheet.Cells[iRowCount, 10].Value = data["status"].ToString();
        wSheet.Cells[iRowCount, 11].Value = data["startDate"].ToString();
        wSheet.Cells[iRowCount, 12].Value = data["value"].ToString();
        iRowCount++;
    }
    DirectoryInfo saveTo = Directory.CreateDirectory(excelTemplatePath + _report.FolderGuid.ToString() + "\");
    _report.ReportLocation = saveTo.FullName + _report.ExcelTemplate;
    wBook.SaveAs(_report.ReportLocation, FileFormat.OpenXMLWorkbook);
}

If you have more than a few rows, you might be shocked at how much faster it runs. And you will never have to worry about a hanging instance of Excel.

如果您有不止几行，您可能会惊讶于它的运行速度有多快。而且您永远不必担心 Excel 的挂起实例。

You can download the free trial hereand try it for yourself.

您可以在此处下载免费试用版并亲自试用。

Disclaimer: I own SpreadsheetGear LLC

免责声明：我拥有 SpreadsheetGear LLC

Answer 6

回答by Gary McGill

UPDATE(November 2016)

更新（2016 年 11 月）

I've just read a convincing argumentby Hans Passant that using GC.Collectis actually the right way to go. I no longer work with Office (thank goodness), but if I did I'd probably want to give this another try - it would certainly simplify a lot of the (thousands of lines) of code I wrote trying to do things the "right" way (as I saw it then).

我刚刚阅读了Hans Passant 的一个令人信服的论点，即使用GC.Collect实际上是正确的方法。我不再使用 Office（谢天谢地），但如果我这样做了，我可能想再试一次 - 它肯定会简化我编写的许多（数千行）代码，试图做“正确的事情” “方式（正如我当时所看到的那样）。

I'll leave my original answer for posterity...

我会把我原来的答案留给后人...

As Mike says in his answer, there is an easy way and a hard way to deal with this. Mike suggests using the easy way because... it's easier. I don't personally believe that's a good enough reason, and I don't believe it's the right way. It smacks of "turn it off and on again" to me.

正如迈克在他的回答中所说，有一种简单的方法可以解决这个问题。Mike 建议使用简单的方法，因为……它更容易。我个人不认为这是一个足够好的理由，我也不认为这是正确的方法。对我来说，它有“关掉再打开”的感觉。

I have several years experience of developing an Office automation application in .NET, and these COM interop problems plagued me for the first few weeks & months when I first ran into the issue, not least because Microsoft are very coy about admitting there's a problem in the first place, and at the time good advice was hard to find on the web.

我有几年在 .NET 中开发 Office 自动化应用程序的经验，这些 COM 互操作问题在我第一次遇到这个问题的最初几周和几个月里一直困扰着我，尤其是因为 Microsoft 非常腼腆地承认存在问题首先，当时在网上很难找到好的建议。

I have a way of working that I now use virtually without thinking about it, and it's years since I had a problem. It's still important to be alive to all the hidden objects that you might be creating - and yes, if you miss one, you might have a leak that only becomes apparent much later. But it's no worse than things used to be in the bad old days of malloc/free.

我有一种工作方式，我现在几乎不假思索地使用它，而且我已经好几年没遇到问题了。对您可能正在创建的所有隐藏对象保持活跃仍然很重要 - 是的，如果您错过了一个，您可能会发现泄漏，直到很久以后才会变得明显。但这并不比过去糟糕的malloc/糟糕free。

I do think there's something to be said for cleaning up after yourself as you go, rather than at the end. If you're only starting Excel to fill in a few cells, then maybe it doesn't matter - but if you're going to be doing some heavy lifting, then that's a different matter.

我确实认为在你走的时候清理你自己，而不是在最后。如果您只是开始使用 Excel 来填充几个单元格，那么也许这无关紧要 - 但如果您要进行一些繁重的工作，那就是另一回事了。

Anyway, the technique I use is to use a wrapper class that implements IDisposable, and which in its Disposemethod calls ReleaseComObject. That way I can use usingstatements to ensure that the object is disposed (and the COM object released) as soon as I'm finished with it.

无论如何，我使用的技术是使用一个实现的包装类IDisposable，并在其Dispose方法中调用ReleaseComObject. 这样我就可以使用using语句来确保对象在我完成后立即被释放（并释放 COM 对象）。

Crucially, it'll get disposed/released even if my function returns early, or there's an Exception, etc. Also, it'll onlyget disposed/released if it was actually created in the first place - call me a pedant but the suggested code that attempts to release objects that may not actually have been created looks to me like sloppy code. I have a similar objection to using FinalReleaseComObject - you should know how many times you caused the creation of a COM reference, and should therefore be able to release it the same number of times.

至关重要的是，即使我的函数提前返回，或者出现异常等，它也会被释放/释放。此外，如果它实际上是首先创建的，它只会被释放/释放 - 称我为学究但建议试图释放实际上可能没有创建的对象的代码在我看来就像草率的代码。我对使用 FinalReleaseComObject 也有类似的反对意见——您应该知道创建 COM 引用的次数，因此应该能够释放相同的次数。

A typical snippet of my code might look like this (or it would, if I was using C# v2 and could use generics :-)):

我的代码的典型片段可能如下所示（或者，如果我使用的是 C# v2 并且可以使用泛型 :-)，则可能是这样）：

using (ComWrapper<Excel.Application> application = new ComWrapper<Excel.Application>(new Excel.Application()))
{
  try
  {
    using (ComWrapper<Excel.Workbooks> workbooks = new ComWrapper<Excel.Workbooks>(application.ComObject.Workbooks))
    {
      using (ComWrapper<Excel.Workbook> workbook = new ComWrapper<Excel.Workbook>(workbooks.ComObject.Open(...)))
      {
        using (ComWrapper<Excel.Worksheet> worksheet = new ComWrapper<Excel.Worksheet>(workbook.ComObject.ActiveSheet))
        {
          FillTheWorksheet(worksheet);
        }
        // Close the workbook here (see edit 2 below)
      }
    }
  }
  finally
  {
    application.ComObject.Quit();
  }
}

Now, I'm not about to pretend that that isn't wordy, and the indentation caused by object creation canget out of hand if you don't divide stuff into smaller methods. This example is something of a worst case, since all we're doing is creating objects. Normally there's a lot more going on between the braces and the overhead is much less.

现在，我不想假装这不是冗长的，如果不将内容分成更小的方法，由对象创建引起的缩进可能会失控。这个例子是最坏的情况，因为我们所做的只是创建对象。通常，大括号之间发生的事情更多，开销要少得多。

Note that as per the example above I would always pass the 'wrapped' objects between methods, never a naked COM object, and it would be the responsibility of the caller to dispose of it (usually with a usingstatement). Similarly, I would always return a wrapped object, never a naked one, and again it would be the responsibility of the caller to release it. You could use a different protocol, but it's important to have clear rules, just as it was when we used to have to do our own memory management.

请注意，根据上面的示例，我将始终在方法之间传递“包装”对象，而不是裸 COM 对象，并且调用者有责任处理它（通常使用using语句）。类似地，我总是会返回一个包装好的对象，而不是一个裸露的对象，再次释放它是调用者的责任。你可以使用不同的协议，但有明确的规则很重要，就像我们过去必须做我们自己的内存管理一样。

The ComWrapper<T>class used here hopefully requires little explanation. It simply stores a reference to the wrapped COM object, and releases it explicitly (using ReleaseComObject) in its Disposemethod. The ComObjectmethod simply returns a typed reference to the wrapped COM object.

ComWrapper<T>希望这里使用的类几乎不需要解释。它只是存储对包装的 COM 对象的引用，并ReleaseComObject在其Dispose方法中显式释放它（使用）。该ComObject方法仅返回对包装的 COM 对象的类型化引用。

Hope this helps!

希望这可以帮助！

EDIT: I've only now followed the link over to Mike's answer to another question, and I see that another answer to that question there has a link to a wrapper class, much as I suggest above.

编辑：我现在才关注迈克对另一个问题的回答的链接，我看到该问题的另一个答案有一个指向包装类的链接，就像我上面建议的那样。

Also, with regard to Mike's answer to that other question, I have to say I was very nearly seduced by the "just use GC.Collect" argument. However, I was mainly drawn to that on a false premise; it looked at first glance like there would be no need to worry about the COM references at all. However, as Mike says you do still need to explicitly release the COM objects associated with all your in-scope variables - and so all you've done is reduce rather than remove the need for COM-object management. Personally, I'd rather go the whole hog.

此外，关于迈克对另一个问题的回答，我不得不说我几乎被“只是使用GC.Collect”的论点所诱惑。然而，我主要是在一个错误的前提下被吸引住了。乍一看，它似乎根本不需要担心 COM 引用。然而，正如 Mike 所说，您仍然需要显式释放与所有范围内变量关联的 COM 对象 - 因此您所做的只是减少而不是消除对 COM 对象管理的需要。就个人而言，我宁愿全力以赴。

I also note a tendency in lots of answers to write code where everything gets released at the end of a method, in a big block of ReleaseComObjectcalls. That's all very well if everything works as planned, but I would urge anyone writing serious code to consider what would happen if an exception were thrown, or if the method had several exit points (the code would not be executed, and thus the COM objects would not be released). This is why I favor the use of "wrappers" and usings. It's wordy, but it does make for bulletproof code.

我还注意到在编写代码的许多答案中都有一种趋势，即在方法结束时，在一大块ReleaseComObject调用中释放所有内容。如果一切都按计划进行，那就太好了，但我会敦促任何编写严肃代码的人考虑如果抛出异常，或者如果该方法有多个退出点（代码不会被执行，因此 COM 对象不会被释放）。这就是为什么我喜欢使用“包装器”和usings。它很罗嗦，但它确实有助于防弹代码。

EDIT2: I've updated the code above to indicate where the workbook should be closed with or without saving changes. Here's the code to save changes:

EDIT2：我已经更新了上面的代码，以指示在保存或不保存更改的情况下应该关闭工作簿的位置。这是保存更改的代码：

object saveChanges = Excel.XlSaveAction.xlSaveChanges;

workbook.ComObject.Close(saveChanges, Type.Missing, Type.Missing);

...and to notsave changes, simply change xlSaveChangesto xlDoNotSaveChanges.

...并且不保存更改，只需更改xlSaveChanges为xlDoNotSaveChanges.

Answer 7

回答by Sean

The easiest way to paste code is through a question - this doesn't mean that I have answered my own question (unfortunately). Apologies to those trying to help me - I was not able to get back to this until now. It still has me stumped... I have completely isolated the Excel code into one function as per

粘贴代码的最简单方法是通过问题 - 这并不意味着我已经回答了我自己的问题（不幸的是）。向那些试图帮助我的人道歉 - 直到现在我才能够回到这个问题。它仍然让我感到困惑......我已经按照如下要求将 Excel 代码完全隔离到一个函数中

private bool GenerateDailyProposalsReport(ScheduledReport report)
{
    // start of test

    Excel.Application xl = null;
    Excel._Workbook wBook = null;
    Excel._Worksheet wSheet = null;
    Excel.Range xlrange = null;
    object m_objOpt = System.Reflection.Missing.Value;

    xl = new Excel.Application();
    wBook = (Excel._Workbook)xl.Workbooks.Open(@"E:\Development\Romain\APN\SalesLinkReportManager\ExcelTemplates\DailyProposalReport.xls", false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
    wSheet = (Excel._Worksheet)wBook.ActiveSheet;
    xlrange = wSheet.Cells[2, 1] as Excel.Range;

    // PROBLEM LINE ************
    xlrange.Value2 = "fullname";
    //**************************

    wBook.Close(true, @"c:\temp\DailyProposalReport.xls", m_objOpt);
    xl.Quit();

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

    Marshal.FinalReleaseComObject(xlrange);
    Marshal.FinalReleaseComObject(wSheet);
    Marshal.FinalReleaseComObject(wBook);
    Marshal.FinalReleaseComObject(xl);

    xlrange = null;
    wSheet = null;
    wBook = null;
    xl = null;

    // end of test
    return true;

}

If I comment out the PROBLEM LINE above, the instance of Excel is released from memory. As it stands, it does not. I'd appreciate any further help on this as time is fleeting and a deadline looms (don't they all).

如果我注释掉上面的问题行，Excel 实例将从内存中释放。就目前而言，它没有。我很感激这方面的任何进一步帮助，因为时间稍纵即逝，最后期限迫在眉睫（不是全部）。

Please ask if you need more information. Thanks in anticipation.

请询问您是否需要更多信息。感谢期待。

Addendum

附录

A bit more information that may or may not shed more light on this. I have resorted to killing the process (stopgap measure) after a certain time lapse (5-10 seconds to give Excel time to finish it's processes). I have two reports scheduled - the first report is created and saved to disk and the Excel process is killed, then emailed. The second is created, saved to disk, the process is killed but suddenly there is an error when attempting the email. The error is: The process cannot access the file'....' etc

更多的信息可能会或可能不会对此有更多的了解。在经过一段时间后（5-10 秒让 Excel 有时间完成它的进程），我采取了终止进程（权宜之计）的方法。我安排了两个报告——第一个报告被创建并保存到磁盘，Excel 进程被终止，然后通过电子邮件发送。第二个被创建，保存到磁盘，进程被终止，但在尝试发送电子邮件时突然出现错误。错误是：进程无法访问文件'....'等

So even when the Excel app has been killed, the actual Excel file is still being held by the windows service. I have to kill the service to delete the file...

因此，即使 Excel 应用程序已被终止，实际的 Excel 文件仍由 Windows 服务保存。我必须终止服务才能删除文件...

Answer 8

回答by Mike Rosenblum

I'm afraid I am running out of ideas here Sean. :-(

恐怕我的想法已经用完了，肖恩。:-(

Gary could have some thoughts, but although his wrapper approach is very solid, it won't actually help you in this case because you are already doing everything pretty much correctly.

Gary 可能有一些想法，但是尽管他的包装方法非常可靠，但在这种情况下它实际上并没有帮助您，因为您已经几乎正确地做了所有事情。

I'll list a few thoughts here. I don't see how any of them will actually work because your mystery line

我将在这里列出一些想法。我看不出它们中的任何一个实际上会如何工作，因为你的神秘线

xlrange.Value2 = "fullname";

would not seem to be impacted by any of these ideas, but here goes:

似乎不会受到任何这些想法的影响，但这里有：

(1) Don't make use of the _Workbook and _Worksheet interfaces. Use Workbook and Worksheet instead. (For more on this see: Excel interop: _Worksheet or Worksheet?.)

(1) 不要使用_Workbook 和_Worksheet 接口。改用工作簿和工作表。（有关更多信息，请参阅：Excel 互操作：_Worksheet 或 Worksheet？。）

(2) Any time you have two dots (".") on the same line when accessing an Excel object, break it up into two lines, assigning each object to a named variable. Then, within the cleanup section of your code, explicitly release each variable using Marshal.FinalReleaseComObject().

(2) 在访问 Excel 对象时，只要在同一行上有两个点（“.”），就将其分成两行，将每个对象分配给一个命名变量。然后，在代码的清理部分，使用 Marshal.FinalReleaseComObject() 显式释放每个变量。

For example, your code here:

例如，您的代码在这里：

 wBook = (Excel._Workbook)xl.Workbooks.Open(@"E:\Development\Romain\APN\SalesLinkReportManager\ExcelTemplates\DailyProposalReport.xls", false, false, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

could be broken up to:

可以分解为：

Excel.Workbooks wBooks = xl.Workbooks;
wBook = wBooks.Open("@"E:\Development\...\DailyProposalReport.xls", etc...);

And then later, within the cleanup section, you would have:

然后，在清理部分中，您将拥有：

Marshal.FinalReleaseComObject(xlrange);
Marshal.FinalReleaseComObject(wSheet);
Marshal.FinalReleaseComObject(wBook);
Marshal.FinalReleaseComObject(wBooks); // <-- Added
Marshal.FinalReleaseComObject(xl);

(3) I am not sure what is going on with your Process.Kill approach. If you call wBook.Close() and then xl.Quit() before calling Process.Kill(), you should have no troubles. Workbook.Close() does not return execution to you until the workbook is closed, and Excel.Quit() will not return execution until Excel has finished shutting down (although it might still be hanging).

(3) 我不确定你的 Process.Kill 方法发生了什么。如果在调用 Process.Kill() 之前先调用 wBook.Close() 和 xl.Quit()，则应该没有问题。Workbook.Close() 在工作簿关闭之前不会将执行返回给您，并且 Excel.Quit() 在 Excel 完成关闭之前不会返回执行（尽管它可能仍处于挂起状态）。

After calling Process.Kill(), you can check the Process.HasExited property in a loop, or, better, call the Process.WaitForExit() method which will pause until it has exited for you. I would guess that this will generally take well under a second to occur. It is better to wait less time and be certain than to wait 5 - 10 seconds and only be guessing.

在调用 Process.Kill() 之后，您可以在循环中检查 Process.HasExited 属性，或者更好的是调用 Process.WaitForExit() 方法，该方法将暂停，直到它为您退出。我猜这通常会在不到一秒钟的时间内发生。与其等待 5 到 10 秒而只是猜测，不如等待更少的时间并确定。

(4) You should try these cleanup ideas that I've listed above, but I am starting to suspect that you might have an issue with other processes that might be working with Excel, such as an add-in or anti-virus program. These add-ins can cause Excel to hang if they are not done correctly. If this occurs, it can be very difficult or impossible to get Excel to release. You would need to figure out the offending program and then disable it. Another possibility is that operating as a Windows Service somehow is an issue. I don't see why it would be, but I do not have experience automating Excel via a Windows Service, so I can't say. If your problems are related to this, then using Process.Kill will likely be your only resort here.

(4) 您应该尝试我在上面列出的这些清理方法，但我开始怀疑您可能在使用 Excel 的其他进程（例如加载项或防病毒程序）时遇到问题。如果这些加载项未正确完成，它们可能会导致 Excel 挂起。如果发生这种情况，将很难或不可能发布 Excel。您需要找出有问题的程序，然后将其禁用。另一种可能性是，以某种方式作为 Windows 服务运行是一个问题。我不明白为什么会这样，但我没有通过 Windows 服务自动化 Excel 的经验，所以我不能说。如果您的问题与此有关，那么使用 Process.Kill 可能是您唯一的选择。

This is all I can think of off-hand, Sean. I hope this helps. Let us know how it goes...

肖恩，我能想到的就这些。我希望这有帮助。让我们知道怎么回事...

-- Mike

——迈克

Answer 9

回答by Gary McGill

Sean,

肖恩，

I'm going to re-post your code again with my changes (below). I've avoided changing your code too much, so I haven't added any exception handling, etc. This code is notrobust.

我将通过我的更改（如下）再次重新发布您的代码。我避免过多地更改您的代码，所以我没有添加任何异常处理等。这段代码并不健壮。

private bool GenerateDailyProposalsReport(ScheduledReport report)
{
    Excel.Application xl = null;
    Excel.Workbooks wBooks = null;
    Excel.Workbook wBook = null;
    Excel.Worksheet wSheet = null;
    Excel.Range xlrange = null;
    Excel.Range xlcell = null;

    xl = new Excel.Application();

    wBooks = xl.Workbooks;

    wBook = wBooks.Open(@"DailyProposalReport.xls", false, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

    wSheet = wBook.ActiveSheet;

    xlrange = wSheet.Cells;

    xlcell = xlrange[2, 1] as Excel.Range;

    xlcell.Value2 = "fullname";

    Marshal.ReleaseComObject(xlcell);
    Marshal.ReleaseComObject(xlrange);
    Marshal.ReleaseComObject(wSheet);

    wBook.Close(true, @"c:\temp\DailyProposalReport.xls", Type.Missing);

    Marshal.ReleaseComObject(wBook);
    Marshal.ReleaseComObject(wBooks);

    xl.Quit();

    Marshal.ReleaseComObject(xl);

    return true;
}

Points to note:

注意事项：

The Workbooksmethod of the Applicationclass creates a Workbooksobject which holds a reference to the corresponding COM object, so we need to ensure we subsequently release that reference, which is why I added the variable wBooksand the corresponding call to ReleaseComObject.
Similarly, the Cellsmethod of the Worksheetobject returns a Rangeobject with another COM reference, so we need to clean that up too. Hence the need for 2 separate Rangevariables.
I've released the COM references (using ReleaseComObject) as soon as they're no longer needed, which I think is good practice even if it isn't strictly necessary. Also, (and this may be superstition) I've released all the objects owned by the workbook before closing the workbook, and released the workbook before closing Excel.
I'm not calling GC.Collectetc. because it shouldn't be necessary. Really!
I'm using ReleaseComObject rather than FinalReleaseComObject, because it should be perfectly sufficient.
I'm not null-ing the variables after use; once again, it's not doing anything worthwhile.
Not relevant here, but I'm using Type.Missinginstead of System.Reflection.Missing.Valuefor convenience. Roll on C#v4 where optional parameters will be supported by the compiler!

类的Workbooks方法 Application创建了一个Workbooks对象，该对象保存对相应 COM 对象的引用，因此我们需要确保随后释放该引用，这就是我添加变量 wBooks和对ReleaseComObject.
同样，对象的Cells方法 Worksheet返回一个Range带有另一个 COM 引用的对象，因此我们也需要清理它。因此需要 2 个单独的Range变量。
ReleaseComObject一旦不再需要COM 引用，我就会发布它们（使用），我认为这是一个很好的做法，即使它不是绝对必要的。另外，（这可能是迷信）我在关闭工作簿之前释放了工作簿拥有的所有对象，并在关闭 Excel 之前释放了工作簿。
我不打电话GC.Collect等，因为它不应该是必要的。真的！
我使用的是 ReleaseComObject 而不是 FinalReleaseComObject，因为它应该已经足够了。
使用后我不会将变量置空；再一次，它没有做任何有价值的事情。
这里不相关，但我使用Type.Missing而不是 System.Reflection.Missing.Value为了方便。继续使用 C#v4，其中编译器将支持可选参数！

I've not been able to compile or run this code, but I'm pretty confident it'll work. Good luck!

我无法编译或运行此代码，但我非常有信心它会起作用。祝你好运！

Answer 10

回答by nightcoder

I have just answered this question here:

我刚刚在这里回答了这个问题：

Killing excel process by its main window hWnd

通过主窗口 hWnd 杀死 excel 进程

c# 和 excel 自动化 - 结束正在运行的实例

提问by Sean

Update

更新

回答by Ahmad Mageed

回答by Andy Mikula

回答by Jamie Ide

回答by Mike Rosenblum

回答by Joe Erickson

回答by Gary McGill

回答by Sean

Addendum

附录

回答by Mike Rosenblum

回答by Gary McGill

回答by nightcoder

相关推荐

最近更新

标签

c# 和 excel 自动化 - 结束正在运行的实例

提问by Sean

Update

更新

回答by Ahmad Mageed

回答by Andy Mikula

回答by Jamie Ide

回答by Mike Rosenblum

回答by Joe Erickson

回答by Gary McGill

回答by Sean

Addendum

附录

回答by Mike Rosenblum

回答by Gary McGill

回答by nightcoder

相关推荐

Linux Suse Enterprise 11 SP2 如何安装git

如何在实例化时将值插入到 C# 字典中？

Linux 如何为服务器上的文件创建可下载的公共链接

Linux 添加文件时运行 shell 命令

相关推荐

最近更新

标签