Excel对象有时无法处理
本文关键字:处理 对象 Excel | 更新日期: 2023-09-27 18:23:54
我有一段从excel单元格中读取的代码:
public T GetValue<T>(string testsheet, string range)
{
Application excelApplication = null;
Workbooks workBooks = null;
Workbook activeWorkBook = null;
Worksheet activeWorkSheet = null;
try
{
excelApplication = new Application();
workBooks = excelApplication.Workbooks;
activeWorkBook = workBooks.Open(workBook);
activeWorkSheet = activeWorkBook.ActiveSheet;
var cells = activeWorkSheet.get_Range(range);
return cells.Value2;
}
catch (Exception theError)
{
Console.WriteLine(theError.Message);
throw theError;
}
finally
{
ReleaseComObject(activeWorkSheet);
ReleaseComObject(activeWorkBook);
ReleaseComObject(workBooks);
ReleaseComObject(excelApplication);
}
}
此外,我有一个Set value方法,它将值设置为如下单元格:
public void SetValue<T>(string testsheet, string range, T value)
{
Application excelApplication = null;
Workbooks workBooks = null;
Workbook activeWorkBook = null;
Worksheet activeWorkSheet = null;
try
{
excelApplication = new Application();
workBooks = excelApplication.Workbooks;
activeWorkBook = workBooks.Open(workBook);
activeWorkSheet = activeWorkBook.ActiveSheet;
var cells = activeWorkSheet.get_Range(range);
cells.Value2 = value;
activeWorkBook.Save();
}
catch (Exception theError)
{
Console.WriteLine(theError.Message);
throw theError;
}
finally
{
if (activeWorkBook != null)
activeWorkBook.Close();
ReleaseComObject(excelApplication);
ReleaseComObject(workBooks);
ReleaseComObject(activeWorkBook);
ReleaseComObject(activeWorkSheet);
}
}
这是我的ReleaseComObject:
private static void ReleaseComObject<T>(T comObject) where T : class
{
if (comObject != null)
Marshal.ReleaseComObject(comObject);
}
我写了一个测试来确保excel对象被正确处理,如下所示:
[Test]
public void Should_dispose_excel_objects_created_after_io_operation()
{
var expected = Process.GetProcesses().Count(process => process.ProcessName.ToLower() == "excel");
var automationClient = new ExcelAutomation.ExcelAutomationClient(ExcelSheet);
automationClient.SetValue("Sheet1", "A1", 1200);
automationClient.GetValue<double>("Sheet1", "A1");
var actual = Process.GetProcesses().Count(process => process.ProcessName.ToLower() == "excel");
actual.Should().Be(expected, "Excel workbook is not disposed properly. There are still excel processess in memory");
}
如果我只调用SetValue方法,那么这个测试就通过了,但是在调用GetValue时它会失败。但是,我在任务管理器中看不到Excel.exe。你知道为什么会这样吗?我的GetValue函数有问题吗?
处理完对象后,使用
GC.Collect();
这就是我处理Excel对象的方式
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}