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

本文介绍了c＃和excel自动化 - 结束正在运行的实例的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我正在通过C＃尝试Excel自动化。我已经遵循了微软的所有说明如何处理这个问题，但我仍然在努力放弃Excel的最终引用，以关闭它并使GC能够收集。 以下是代码示例。当我注释掉包含类似于以下行的代码块： Sheet.Cells [iRowCount，1] = data [fullname ]的ToString（）; 然后文件保存，Excel退出。否则文件保存，但Excel将作为进程运行。下次此代码运行时，会创建一个新的实例，并最终建立。 任何帮助都不胜感激。谢谢。 这是我的代码的准系统： Excel应用程序xl = null; Excel._Workbook wBook = null; Excel._Worksheet wSheet = null; Excel.Range range = null; 对象m_objOpt = System.Reflection.Missing.Value; try { //打开模板 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; //枚举并将值直接放入Excel文件 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; } } 更新 无论我尝试什么，干净方法或丑陋方法（请参阅下面的答案），一旦这一行，excel实例仍然挂起被击中： wSheet.Cells [iRowCount，1] = data [fullname] ToString（）; 如果我注释该行（和其他类似的，显然）Excel应用程序退出优雅。一旦上面的一行被取消注释，Excel就会停留。 我想我将在分配xl变量之前检查是否有运行的实例并挂钩。我忘了提到这是一个Windows服务，但是这应该不重要吗？ 解决方案 更新（2016年11月） =https://stackoverflow.com/a/25135685/98422>令人信服的论据由Hans Passant使用 GC.Collect 实际上是正确的方法走。我不再与Office（感谢善良）合作，但如果我可能想再给这个尝试 - 这肯定会简化很多（数千行）的代码，我写的试图做的事情是正确的（我看到的那样）。 我将把我原来的答案留给后代... 正如迈克在答案中所说，有一个简单的方法和艰难的方法来处理这个问题。 Mike建议使用简单的方法，因为...更容易。我不认为这是一个很好的理由，我不认为这是正确的方法。我有这么多年的经验，在.NET中开发Office自动化应用程序，这些COM互操作问题困扰着我们。我在头几个星期几个月来，当我第一次遇到这个问题时，尤其是因为微软首先承认有问题，而且当时很难在网路上找到好的建议。 我有一种工作方式，我现在几乎没有想到它，这是几年以来我有一个问题。对于您可能正在创建的所有隐藏对象，还有其重要性 - 而且，如果您错过了一个隐藏的对象，则可能会有一个泄漏只能在以后变得明显。但这并不比以前在 malloc / 免费的不好的旧日子中的东西更糟。 b $ b 我认为有一些事情要做，因为你自己去清理，而不是结束。如果你只是启动Excel来填充几个单元格，那么也许没关系，但是如果你要做一些重要的工作，那就是另一回事。 无论如何，我使用的技术是使用一个实现 IDisposable 的包装器类，其中 Dispose 方法调用 ReleaseComObject 。这样，我可以使用语句来确保对象被处理（并且COM对象被释放）。 至关重要的是，即使我的函数早期返回，或者有异常等等，它也会被处理/发布。而且，只有只能它实际上是在第一个地方创建的 - 打电话给我一个脚步，但是建议的代码试图释放实际上可能没有被创建的对象，看起来像是一般的代码。我也有类似的反对使用FinalReleaseComObject - 你应该知道多少次引起了COM引用的创建，因此应该能够释放相同的次数。 我的代码的一个典型代码片段可能看起来像这样（或者，如果我使用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）） { FillWorksWorks（工作表）; } //在这里关闭工作簿（见下面的编辑2）} } } finally { application.ComObject.Quit（）; } } 现在，我不是假装不是冗长的，如果不将东西分成较小的方法，则由对象创建导致的缩进可以失去效用。这个例子是最糟糕的情况，因为我们所做的就是创建对象。通常情况下，大括号之间会发生更大的变化，而开销更少。 请注意，根据上述示例，我总是将包装对象传递到方法之间，从来不是一个裸COM对象，调用者有责任处理它（通常使用使用语句）。同样，我总是返回一个被包装的对象，而不是一个裸体的对象，并且再次发起调用者的责任。您可以使用不同的协议，但重要的是要有明确的规则，就像我们以前一直在做自己的内存管理一样。 ComWrapper< T> 这里使用的类希望需要很少的解释。它简单地存储对包装的COM对象的引用，并在其 Dispose 方法中显式释放它（使用 ReleaseComObject ）。 ComObject 方法只返回一个打包的COM对象的引用。 希望这有帮助！ 编辑：我现在只是链接到，我看到那个问题的另一个答案有一个包装类的链接，就像我上面提出的那样。 另外，关于Mike对另一个问题的回答，我不得不说，我刚刚使用code> GC.Collect 参数。但是，我主要是在错误的前提下提出来的;它乍一看，就像没有必要担心COM引用一样。但是，正如Mike所说，您仍然需要明确地释放与所有范围内变量相关联的COM对象，因此您所做的只是减少而不是消除COM对象管理的需要。就个人而言，我宁愿去整个猪肉。 我还注意到在编写代码的许多答案中，一切都在方法结束时被释放，在一个大块的 ReleaseComObject 电话。这一切都很好，如果一切都按计划工作，但我会敦促任何人写严重的代码来考虑如果抛出异常会发生什么，或者如果该方法有几个退出点（代码不会执行，因此COM对象不会被释放）。这就是为什么我喜欢使用包装和使用 s。这是有用的，但它确实有防弹码。 EDIT2 ：我已经更新了上面的代码，以指出工作簿应该关闭的位置有或没有保存更改。以下是保存更改的代码： 对象saveChanges = Excel.XlSaveAction.xlSaveChanges; workbook.ComObject.Close（saveChanges，Type.Missing，Type.Missing）; ...并且不保存更改，只需更改 xlSaveChanges to 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; }}UpdateNo 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自动化 - 结束正在运行的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！