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

本文介绍了c# 和 excel 自动化 - 结束正在运行的实例的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

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

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

感谢任何帮助.谢谢.

这是我的代码的准系统:

 Excel.Application xl = null;Excel._Workbook wBook = null;Excel._Worksheet wSheet = null;Excel.Range range = null;对象 m_objOpt = System.Reflection.Missing.Value;尝试{//打开模板xl = 新 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);wSheet = (Excel._Worksheet)wBook.ActiveSheet;int iRowCount = 2;//枚举值并将其直接放入 Excel 文件中而 (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;}捕获(异常前){LogException.HandleException(ex);}最后{NAR(wSheet);如果(wBook ！= null)wBook.Close(false, m_objOpt, m_objOpt);NAR(wBook);xl.退出();NAR(xl);GC.Collect();}私有无效 NAR(对象 o){尝试{System.Runtime.InteropServices.Marshal.ReleaseComObject(o);}抓住 { }最后{o = 空；}}

更新

无论我尝试什么，干净"方法或丑陋"方法(请参阅下面的答案)，只要点击此行，excel 实例仍然会挂起:

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

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

我想我将不得不在分配 xl 变量之前检查是否有一个正在运行的实例，然后将其连接到该实例中.我忘了说这是一个 Windows 服务，但这应该没有关系吧?

解决方案

UPDATE(2016 年 11 月)

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

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

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

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

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

我确实认为在你走的时候，而不是在结束时清理你自己的东西.如果您只是开始使用 Excel 来填充几个单元格，那么这可能无关紧要 - 但如果您要进行一些繁重的工作，那就另当别论了.

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

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

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

using (ComWrapper application = new ComWrapper(new Excel.Application())){尝试{使用 (ComWrapper 工作簿 = 新 ComWrapper(application.ComObject.Workbooks)){使用 (ComWrapper workbook = new ComWrapper(workbooks.ComObject.Open(...))){使用 (ComWrapper 工作表 = 新 ComWrapper(workbook.ComObject.ActiveSheet)){填写工作表(工作表)；}//在此处关闭工作簿(请参阅下面的编辑 2)}}}最后{application.ComObject.Quit();}}

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

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

这里使用的 ComWrapper 类希望不需要解释.它只是存储对包装的 COM 对象的引用，并在其 Dispose 方法中显式释放它(使用 ReleaseComObject).ComObject 方法只返回对包装的 COM 对象的类型化引用.

希望这有帮助！

编辑:我现在才按照链接转到迈克对另一个问题的回答，我看到该问题的另一个答案有一个指向包装类的链接，就像我上面建议的那样.>

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

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

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

object saveChanges = Excel.XlSaveAction.xlSaveChanges;workbook.ComObject.Close(saveChanges, Type.Missing, Type.Missing);

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

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.

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.

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:

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.

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?

解决方案

UPDATE (November 2016)

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

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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:

object saveChanges = Excel.XlSaveAction.xlSaveChanges;

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

...and to not save changes, simply change xlSaveChanges to xlDoNotSaveChanges.

这篇关于c# 和 excel 自动化 - 结束正在运行的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！