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

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自动化。我已经按照微软的所有指示去做了，但是我仍然在努力放弃最终的参考，以便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:

无论我尝试什么，“clean”方法还是“ugly”方法(参见下面的答案)，只要这一行被点击，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服务，但这不重要，是吗?

---

14 个解决方案

#1

UPDATE (November 2016)

更新(2016年11月)

I've just read a convincing argument by Hans Passant that using GC.Collect is 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。收集实际上是正确的方法。我不再与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.

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

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开发办公自动化应用程序,而这些COM互操作问题困扰我前几周和几个月当我第一次遇到这个问题,尤其是因为微软很扭捏作态地承认有问题首先,和当时好建议在网上很难找到。

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 Dispose method calls ReleaseComObject. That way I can use using statements to ensure that the object is disposed (and the COM object released) as soon as I'm finished with it.

无论如何，我使用的技术是使用一个实现IDisposable的包装器类，在它的Dispose方法中调用ReleaseComObject。这样，我就可以使用语句来确保在结束时，对象被处理(以及发布的COM对象)。

Crucially, it'll get disposed/released even if my function returns early, or there's an Exception, etc. Also, it'll only get 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 can get 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 using statement). 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 Dispose method. The ComObject method simply returns a typed reference to the wrapped COM object.

这里使用的ComWrapper 类希望无需解释。它只是存储对包装好的COM对象的引用，并在其Dispose方法中显式地释放它(使用ReleaseComObject)。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.

编辑:我现在只是跟踪链接到Mike对另一个问题的回答，我看到这个问题的另一个答案有一个链接到包装类，就像我上面提到的那样。

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.

另外，关于Mike对另一个问题的回答，我不得不说我几乎被“使用GC”所诱惑。收集”的论点。然而，我主要是在一个错误的前提下被吸引;第一眼看上去，没有必要担心COM的引用。然而，正如Mike所说，您仍然需要显式地释放与所有范围内变量关联的COM对象——因此您所做的只是减少而不是删除COM-object管理的需要。就我个人而言，我宁愿全力以赴。

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 ReleaseComObject calls. 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对象将不会被释放)会发生什么。这就是为什么我喜欢使用“包装器”和“包装器”。它是冗长的，但它确实适合防弹代码。

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 not save changes, simply change xlSaveChanges to xlDoNotSaveChanges.

…为了不保存更改，只需将xlSaveChanges更改为xlDoNotSaveChanges。

#2

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.Range object, and then assigning a value to it. This way also gives us a named reference to our range variable, the cell variable, that allows us to release it directly if we wanted. So you could clean up your objects one of two ways:

通过这种方式，您可以看到您正在创建一个Excel。范围对象，然后赋值给它。这种方法也为我们提供了一个命名的范围变量，即单元变量，它允许我们直接释放它，如果我们想要的话。所以你可以用以下两种方法来清理你的物品:

(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 Forum that I participate on (thanks guys!) Here are a couple of related posts that I've made here on * :

Jamie和Amhad还列出了我参加的。net自动化论坛的链接(谢谢大家!)以下是我在*上发表的一些相关文章:

(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#自动化PowerPoint Excel——PowerPoint不会退出。

I hope this helps, Sean...

我希望这能帮上忙，肖恩…

Mike

迈克

#3

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. ReleaseComObject decrements the reference count by 1 while FinalReleaseComObject releases all references so the count is 0.

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

So your finally block would look like:

所以最后一个block会是:

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)提到应该在开始时调用它。这两种方法我都试过了，它们都奏效了。我也在没有等待终结器的情况下尝试了它，虽然它不会伤害任何东西。YMMV。

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

以下是我提到的MVP的相关链接(他们在VB中，但是没有那么大的不同):

• http://www.xtremevbtalk.com/showthread.php?p=1157109#post1157109
• http://www.xtremevbtalk.com/showthread.php?p=1157109 post1157109
• http://www.xtremevbtalk.com/showthread.php?s=bcdea222412c5cbfa7f02cfaf8f7b33f&p=1156479#post1156479
• http://www.xtremevbtalk.com/showthread.php?s=bcdea222412c5cbfa7f02cfaf8f7b33f&p=1156479 post1156479

#4

As others have already covered InterOp i would suggest that if you deal with Excel files with XLSX extension you should use EPPlus which will make your Excel nightmares go away.

由于其他人已经讨论过InterOp，我建议如果您使用XLSX扩展来处理Excel文件，您应该使用EPPlus，这会让您的Excel噩梦消失。

#5

I have just answered this question here:

我刚刚回答了这个问题:

Killing excel process by its main window hWnd

通过主窗口hWnd杀死excel进程

#6

Its over 4 years since this was posted but I came across the same problem and was able to solve it. Apparently just accessing the Cells array creates a COM object. So if you were to do:

这篇文章发表已经四年多了，但我遇到了同样的问题，并且能够解决它。显然，仅仅访问单元格数组就会创建一个COM对象。如果你这样做:

    wSheet = (Excel._Worksheet)wBook.ActiveSheet;
    Microsoft.Office.Interop.Excel.Range cells = wSheet.Cells;

    int iRowCount = 2;

    // enumerate and drop the values straight into the Excel file
    while (data.Read())
    {
        Microsoft.Office.Interop.Excel.Range cell = cells[iRowCount, 1];
        cell  = data["fullname"].ToString();
        Marshal.FinalReleaseComObject(cell);
    }
    Marshal.FinalReleaseComObject(cells);

and then the rest of your cleanup it should fix the problem.

剩下的清理工作应该能解决这个问题。

#7

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

#8

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 comment was 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);
}

#9

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 ?下面是您的代码可能喜欢使用电子表格工具:

// 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 here and try it for yourself.

你可以在这里下载免费试用，自己试试。

Disclaimer: I own SpreadsheetGear LLC

免责声明:我自己开的是SpreadsheetGear LLC。

#10

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服务保存。我必须删除服务才能删除文件……

#11

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 interop: _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).

我不知道你们的工艺流程是怎样的。杀死的方法。如果您在调用Process.Kill()之前调用wBook.Close()，然后调用x . quit()，那么您应该不会遇到任何麻烦。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.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。这里可能是你唯一的去处。

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

这就是我现在能想到的，肖恩。我希望这可以帮助。让我们知道这是怎么回事……

-- Mike

——迈克

#12

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 not robust.

我将用我的更改(如下)重新发布您的代码。我已经避免过多地修改您的代码，所以我没有添加任何异常处理等等。

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:

点需要注意:

1. The Workbooks method of the Application class creates a Workbooks object 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 wBooks and the corresponding call to ReleaseComObject.

   应用程序类的Workbooks方法创建一个Workbooks对象，该对象保存对相应的COM对象的引用，因此我们需要确保随后释放该引用，这就是为什么我添加了变量wBooks和相应的对ReleaseComObject的调用。

2. Similarly, the Cells method of the Worksheet object returns a Range object with another COM reference, so we need to clean that up too. Hence the need for 2 separate Range variables.

   类似地，工作表对象的单元格方法返回带有另一个COM引用的范围对象，因此我们也需要清理它。因此需要两个独立的范围变量。

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

   我已经在不再需要的时候发布了COM引用(使用ReleaseComObject)，我认为这是很好的实践，即使不是非常必要。另外，(这可能是迷信)我在关闭工作簿之前已经发布了工作簿所有的对象，并在关闭Excel之前发布了工作簿。

4. I'm not calling GC.Collect etc. because it shouldn't be necessary. Really!

   我不叫GC。收集等等，因为它不应该是必要的。真的!

5. I'm using ReleaseComObject rather than FinalReleaseComObject, because it should be perfectly sufficient.

   我使用的是ReleaseComObject，而不是FinalReleaseComObject，因为它应该已经足够了。

6. I'm not null-ing the variables after use; once again, it's not doing anything worthwhile.

   我不会在使用后取消变量;再说一次，它没有做任何有价值的事情。

7. Not relevant here, but I'm using Type.Missing instead of System.Reflection.Missing.Value for convenience. Roll on C#v4 where optional parameters will be supported by the compiler!

   这里不相关，我用的是Type。代替System.Reflection.Missing失踪。为了方便价值。在c# v4上运行，编译器将支持可选参数!

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

我无法编译或运行这段代码，但是我很有信心它能工作。好运！

#13

There's no need to use the excel com objects from C#. You can use OleDb to modify the sheets.

不需要使用c#中的excel com对象。您可以使用OleDb来修改这些表。

http://www.codeproject.com/KB/office/excel_using_oledb.aspx

http://www.codeproject.com/KB/office/excel_using_oledb.aspx

#14

I was having a similar problem. I removed _worksheet and _workbook and all was well.

我也有类似的问题。我删除了工作表和工作簿，一切正常。