问题描述
我通过C＃试图Excel自动化。我按照所有从微软如何去这个指示，但我仍然在努力放弃最后的参考（S）到Excel它关闭，以使GC收集它。
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.
一个code样品如下。当我注释掉包含类似行code座：
A code sample follows. When I comment out the code block that contains lines similar to:
Sheet.Cells[iRowCount, 1] = data["fullname"].ToString();
然后保存文件和Excel退出。否则，文件保存，但Excel中留下作为进程运行。这code运行时，它下一次创建一个新的实例，他们最终建立。
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.
任何帮助是AP preciated。谢谢你。
Any help is appreciated. Thanks.
这是我的code的准系统：
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;
}
}
不管我怎么努力，在'干净'方法或'丑陋'的方法（见下面的答案），Excel的实例仍一旦该行被击中徘徊：
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();
如果我评论说，线路输出（和它下面的其他类似的，很明显）Excel的应用程序将会自动退出。只要按照上面一行是注释掉时，Excel枝左右。
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.
我想我将不得不检查是否有之前分配XL变量的运行实例，并挂接到该网址。我忘了提，这是一个窗口服务，但是这不应该的问题，应该吗？
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?
推荐答案
由于迈克说，在他的回答中，有一个简单的方法和一个硬盘的方式来处理这个问题。麦克建议使用简单的方法，因为......它更容易。我不个人认为这是一个好足够的理由，我不相信这是正确的方式。它意味的将其关闭并重新打开给我。
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.
我在开发.NET中办公自动化应用程序的数年经验，而这些COM互操作问题的困扰我的头几个星期和放大器;个月的时候我第一次遇到了这个问题，这不仅是因为微软是关于承认有摆在首位的问题，并在当时很好的建议很难在网上找到很腼腆。
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.
我有工作，我现在几乎不使用思考它的一种方式，它的年，因为我有一个问题。它仍然是重要的是要活到所有隐藏的对象，你可能会创造 - 是的，如果你错过了一个，你可能有泄漏，只有变得明显要晚得多。但它不逊于用过的东西是在的malloc / 免费的坏日子。
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.
我不觉得有什么东西为好自己清理，当您去，而不是在结束时说。如果你刚开始的Excel填补了几格，那么也许它并不重要 - 但如果你打算做一些繁重的，那么这是一个不同的问题。
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.
反正我用的方法是使用一个包装类实现的IDisposable ，并在的Dispose 方法调用 ReleaseComObject的。这样，我可以使用使用语句以尽快确保对象布置（与COM对象发布）因为我完成它。
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.
重要的是，它会得到处理/释放，即使我的功能月初返回，或有一个异常等。此外，它会的只有的获取处理/释放，如果它实际上是在创建第一名 - 叫我书呆子，但建议code，试图释放实际上可能没有被创建的对象在我看来就像马虎code。我有一个类似的反对使用FinalReleaseComObject - 你应该知道你有多少次引发了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.
我的code的典型片段可能会是这样的（或将它，如果我是用C＃v2和可以使用泛型： - ））：
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();
}
}
现在，我不会为pretend这是不罗嗦了，通过创建对象造成压痕的可以的失控，如果你不分裂的东西分成更小的方法。这个例子是一个最坏情况的东西，因为所有我们正在做的是创建对象。通常有很多更括号和开销之间的事情要少得多。
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.
请注意，根据上面的例子中我总是会传递方式，从来没有一个赤裸裸的COM对象之间的'包裹'的对象，这将是调用者的责任与使用语句）。同样，我总是返回一个包装的对象，从来没有一个赤身裸体之一，它再次将调用者释放它的责任。你可以使用不同的协议，但有明确的规则是很重要的，只是因为它是当我们曾经有过做我们自己的内存管理。
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.
的 ComWrapper＆LT; T＆gt;此处使用类希望需要一点解释。它只是存储对封装的COM对象的引用，并明确释放它在其的Dispose 方法（使用 ReleaseComObject的）。在 ComObject 方法只返回一个类型引用包装的COM对象。
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.
希望这有助于！
修改：我现在才跟着到Mike's回答另一个问题，我看到另一个问题的答案有有一个链接到一个包装类，就像我建议之上。
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.
另外，对于小李的回答这个问题，其他的，我必须说，我是非常接近的只使用 GC.Collect的的说法诱惑。不过，我主要是吸引到了一个错误的premise;它乍一看好像就没有必要担心在所有的COM引用。然而，由于迈克说你仍然需要明确地释放你的所有调查范围内的变量相关的COM对象 - 等你所做的是减少而不是消除对COM对象管理的必要性。就个人而言，我宁愿去整猪。
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.
我也注意到在很多答案倾向于写code其中一切都被在方法月底公布，在 ReleaseComObject的呼叫的大块。这一切都非常好，如果一切按计划进行，但我会呼吁任何书面严重code考虑，如果该方法有几个出口点（code将不会被执行，如果一个异常被抛出会发生什么，或，因此，COM对象将不被释放）。这就是为什么我喜欢用包装，用取值的。这是罗嗦，但它确实使防弹code。
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 ：我已经更新上面的code以指示工作簿要带或不保存更改关闭。这里的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);
...和的不的保存更改，只需更改 xlSaveChanges 到 xlDoNotSaveChanges
...and to not save changes, simply change xlSaveChanges to xlDoNotSaveChanges.
这篇关于C＃和Excel自动化 - 结束正在运行的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！