清理Excel Interop
清理Excel Interop
提问于 2013-05-29 04:05:33
因此，我有一个Winforms应用程序，它需要从Excel工作表中读取数据以填充某些字段。为了使UI响应，我决定创建一个从Excel工作表读取的线程，这样主线程就不必等待了。如果读取完成，然后应用程序退出，EXCEL.EXE将正常运行并退出。但是，如果我在读操作仍在进行时关闭主应用程序，则EXCEL.EXE任务将保持活动状态。
我猜这是因为ExcelReader在关闭之前没有时间调用destructor吗？
一种可能的解决方案是让主窗体在其FormClosing事件中调用ExcelReader.Cleanup。但这似乎是对封装的可怕违反。
对此还有什么其他可能的解决方案？下面是我的ExcelReader代码：
using Excel = Microsoft.Office.Interop.Excel;
class ExcelReader
{
private int sheetNum { get ; set; }
public int rowCount { get; private set; }
public int colCount { get; private set; }
public List<string> sheetValues { get; private set; }
public List<string> sheetNames { get; private set; }
Excel.Application xlApp;
Excel.Workbooks workBooks;
Excel.Workbook xlWorkbook;
Excel.Worksheet xlWorkSheet;
Excel.Range xlRange;
Excel.Range row;
Excel.Range col;
public ExcelReader(string path){
//initialize values
this.sheetNum = 1;
sheetNames = new List<string>();
sheetValues = new List<string>();
//read from excel blackmagic here
xlApp = new Excel.Application();
workBooks = xlApp.Workbooks;
xlWorkbook = workBooks.Open(path);
xlWorkSheet = xlWorkbook.Sheets[sheetNum];
xlRange = xlWorkSheet.UsedRange;
row = xlRange.Rows;
col = xlRange.Columns;
int rowCount = row.Count;
int colCount = col.Count;
this.getSheetNames(xlWorkbook);
this.getValues(xlRange, rowCount, colCount);
CleanUp();
}
~ExcelReader()
{
CleanUp();
}
private void getSheetNames(Excel.Workbook xlWorkbook)
{
var workSheets = xlWorkbook.Sheets;
int numberOfSheets = workSheets.Count;
for (int i = 1; i < numberOfSheets+1; i++)
{
sheetNames.Add(xlWorkbook.Sheets[i].Name);
}
Marshal.FinalReleaseComObject(workSheets);
}
private void getValues(Excel.Range xlRange, int rowCount, int colCount)
{
for (int i = 1; i < rowCount; i++)
{
for (int j = 1; j < colCount; j++)
{
var cells = xlRange.Cells[i, j];
var value = cells.Value2;
sheetValues.Add(value);
Marshal.FinalReleaseComObject(cells);
}
}
}
private void CleanUp()
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
col.Clear();
row.Clear();
Marshal.FinalReleaseComObject(col);
Marshal.FinalReleaseComObject(row);
xlRange.Clear();
Marshal.FinalReleaseComObject(xlRange);
//close book without saving
xlWorkbook.Close(false);
workBooks.Close();
Marshal.FinalReleaseComObject(xlWorkbook);
Marshal.FinalReleaseComObject(workBooks);
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
}
}回答 1
Stack Overflow用户
发布于 2013-05-29 04:43:24
在Interop会话期间，在某些情况下关闭Excel可能是一件痛苦的事情。有时，您可能需要使用Windows API调用来强制关闭您正在使用的Excel实例。
下面是我在几个项目中使用的一个类，用于在Interop中管理Excel。这里的关键点是使用唯一的标题(在本例中是GUID )来标识您正在使用的特定Excel实例，并在Quit方法不起作用的情况下使用此信息强制关闭该实例。
using System;
using System.Runtime.InteropServices;
namespace ExcelSupport
{
public class ExcelController : IDisposable
{
private Microsoft.Office.Interop.Excel.Application _ExcelApplication;
private bool disposedValue = false; // To detect redundant calls
private string _caption; //used to uniquely identify hidden Excel instance
//
// Windows API used to help close Excel instance
//
[DllImport("user32.dll")]
private static extern int EndTask(IntPtr hWnd);
[DllImport("user32.dll")]
private static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
[DllImport("kernel32.dll")]
private static extern IntPtr SetLastError(int dwErrCode);
public ExcelController()
{
OpenExcel(null);
}
public ExcelController(string workbookName)
{
OpenExcel(workbookName);
}
private void OpenExcel(string workbookName)
{
_ExcelApplication = new Microsoft.Office.Interop.Excel.Application();
_caption = System.Guid.NewGuid().ToString().ToUpper();
_ExcelApplication.Caption = _caption;
_ExcelApplication.Visible = false;
_ExcelApplication.DisplayAlerts = false;
if(workbookName != null)
_ExcelApplication.Workbooks.Open(workbookName);
}
public Microsoft.Office.Interop.Excel.Application Application
{
get { return _ExcelApplication; }
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected void Dispose(bool disposing)
{
if (!this.disposedValue)
{
try
{
if (_ExcelApplication != null)
{
foreach (Microsoft.Office.Interop.Excel.Workbook WorkBookName in _ExcelApplication.Workbooks)
{
WorkBookName.Close(false);
}
IntPtr iHandle = IntPtr.Zero;
iHandle = new IntPtr(_ExcelApplication.Parent.Hwnd);
_ExcelApplication.DisplayAlerts = false;
_ExcelApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(_ExcelApplication);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(_ExcelApplication);
SetLastError(0);
if (IntPtr.Equals(iHandle, IntPtr.Zero))
iHandle = FindWindow(null, _caption);
if (!IntPtr.Equals(iHandle, IntPtr.Zero))
{
int iRes;
uint iProcId;
iRes = (int)GetWindowThreadProcessId(iHandle, out iProcId);
if (iProcId == 0)
{
if (EndTask(iHandle) == 0)
throw new ApplicationException("Excel Instance Could Not Be Closed");
}
else
{
System.Diagnostics.Process proc = System.Diagnostics.Process.GetProcessById((int)iProcId);
proc.CloseMainWindow();
proc.Refresh();
if (!proc.HasExited)
proc.Kill();
}
}
}
}
finally
{
_ExcelApplication = null;
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
}
this.disposedValue = true;
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/16800533
复制相关文章
相似问题
腾讯云开发者
