Excel interop COM doesn't close
I have some code that opens a spreadsheet, reads some values, and then closes the sheet. I need to do this for multiple files. The problem I'm having is that the Excel application instance is not exiting, therefore when I run my process for several files, I end up with several excel.exe processes running. Any ideas how I get Excel to close?
static void ParseFile(string file)
{
try
{
System.Console.WriteLine("parsing:" + file);
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = excel.Workbooks.Open(file);
Excel.Worksheet ws = wb.Worksheets[1];
for (int i = 2; i < 27; i++)
{
log(ws.Cells[i, 1].Text);
}
wb.Close(false);
excel.Quit();
excel = null;
ws = null;
wb = null;
}
catch (Exception ex)
{
log(ex.Message);
}
}
------Update 12/11/12--------Still leaving excel instances open-------
static void ParseFile(string file)
{
try
{
log("parsing:" + file);
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = excel.Workbooks.Open(file);
Excel.Worksheet ws = wb.Worksheets[1];
//do some stuff here
wb.Close(false);
excel.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
Marshal.FinalReleaseComObject(excel);
excel = null;
ws = null;
wb = null;
System.GC.Collect();
}
catch (Exception ex)
{
log(ex.Message);
}
}
Use Marshal.FinalReleaseComObject() to release the resources
If you read multiple files one after the other then performance wise it is much better to open/close Excel Application only once and move the open/close application part to separate functions
Refactored Code:
static Excel.Application OpenExcel(){
Excel.Application excel = null;
try{
excel = new Excel.Application();
return excel;
}
catch(Exception ex){
log(ex.Message);
return null;
}
}
static void ParseFile(string file)
{
try
{
System.Console.WriteLine("parsing:" + file);
Excel.Workbook wb = excel.Workbooks.Open(file);
Excel.Worksheet ws = wb.Worksheets[1];
for (int i = 2; i < 27; i++)
{
log(ws.Cells[i, 1].Text);
}
wb.Close(false);
}
catch (Exception ex)
{
log(ex.Message);
}
finally{
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
ws = null;
wb = null;
}
}
static void CloseExcel(Excel.Application excel){
try{
excel.Quit();
}
catch(Exception ex){
log(ex.Message);
}
finally{
Marshal.FinalReleaseComObject(excel);
excel = null;
}
}
Usage:
Excel.Application excel = OpenExcel();
if(excel != null){
// Parse files in a loop
ParseFile("fileName");
// Close excel after parsing all files
CloseExcel(excel);
}
You can use a wrapper object around the actual COM object that implements IDisposable , so that it can be used with C#'s using statement.
The benefit of this is that it'll promote readable code and it'll work for any COM object. Here's an example with runtime dispatching:
using System;
using System.Reflection;
using System.Runtime.InteropServices;
public class ComRef<T> : IDisposable where T : class
{
private T reference;
public T Reference
{
get
{
return reference;
}
}
public ComRef(T o)
{
reference = o;
}
public void Dispose()
{
if (reference != null)
{
Marshal.ReleaseComObject(reference);
reference = null;
}
}
}
public class Test
{
static void Main()
{
Type excelAppType = Type.GetTypeFromProgID("Excel.Application");
using (var comRef = new ComRef<object>(Activator.CreateInstance(excelAppType)))
{
var excel = comRef.Reference;
// ...
excel.GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, excel, null);
}
}
}
If, however, you already have imported Excel's type library, or any other type library for that matter, you might want something a bit more friendly:
public class CoClassComRef<T> : ComRef<T> where T : class, new()
{
public CoClassComRef() : base(new T())
{
}
}
public class Test
{
static void Main()
{
using (var comRef = new CoClassComRef<Excel.Application>())
{
var excel = comRef.Reference;
// ...
excel.Quit();
}
}
}
You should just make sure that you don't capture comRef.Reference to some field or variable that outlives the using statement.
Note that I haven't given much thought about thread safety and a proper Dispose implementation. Thread safety isn't important if you only use a ComRef with using statements. A proper Dispose implementation would colaborate with a finalizer, but there's no need for that here, as using is basically a try-finally . If you use a ComRef not in a using statement and Dispose is not called, the ComRef will simply be garbage collected, and with it the underlying COM object, which will be released if only this ComRef was referencing it.
Finally, I didn't use Marshal.FinalReleaseComObject , because that is made when you're absolutely sure you want to release the underlying COM object (at least, all references from the managed environment) no matter how many times it has (re)entered the managed world. However, if you feel lucky, you may just create a new constructor which also receives a boolean stating if FinalReleaseComObject should be called instead of ReleaseComObject . The first results on a web search for any of these methods will point to articles and blog posts detailing why they are usually evil, one more than the other.
结束了杀死进程，这是唯一有效的工作。
[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
/// <summary> Tries to find and kill process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <returns>True if process was found and killed. False if process was not found by hWnd or if it could not be killed.</returns>
public static bool TryKillProcessByMainWindowHwnd(int hWnd)
{
uint processID;
GetWindowThreadProcessId((IntPtr)hWnd, out processID);
if (processID == 0) return false;
try
{
Process.GetProcessById((int)processID).Kill();
}
catch (ArgumentException)
{
return false;
}
catch (Exception ex)
{
return false;
}
return true;
}
static void ParseFile(string file)
{
try
{
log("parsing:" + file);
Excel.Application excel = new Excel.Application();
Excel.Workbook wb = excel.Workbooks.Open(file);
Excel.Worksheet ws = wb.Worksheets[1];
//do some stuff here
wb.Close(false);
int hWnd = excel.Application.Hwnd;
excel.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(ws);
Marshal.FinalReleaseComObject(wb);
Marshal.FinalReleaseComObject(excel);
excel = null;
ws = null;
wb = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
TryKillProcessByMainWindowHwnd(hWnd);
}
catch (Exception ex)
{
log(ex.Message);
}
}
上一篇: 当使用COM Interop时，EXCEL.EXE进程立即关闭
下一篇: Excel互操作COM不关闭