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函数有问题吗?

Excel对象有时无法处理

处理完对象后,使用

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();
        }
    }