在Excel VSTO中，如何检查工作表是否属于已关闭的工作簿？

If I have a reference to Worksheet and I close it's parent Workbook, the reference doesn't go away. But I can't figure out how I should check to make sure these sheets don't exist. Checking for null doesn't work.

如果我有一个对工作表的引用，并且我关闭它的父工作簿，则引用不会消失。但我无法弄清楚我应该如何检查以确保这些表不存在。检查null不起作用。

Example:

例：

Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook;
Worksheet sheet = (Worksheet)book.Worksheets[1]; // Get first worksheet
book.Close(); // Close the workbook
bool isNull = sheet == null; // false, worksheet is not null
string name = sheet.Name; // throws a COM Exception

This is the exception I get when I try to access the sheet:

这是我尝试访问工作表时遇到的异常：

System.Runtime.InteropServices.COMException was caught
  HResult=-2147221080
  Message=Exception from HRESULT: 0x800401A8
  Source=MyProject
  ErrorCode=-2147221080
  StackTrace:
       at Microsoft.Office.Interop.Excel._Worksheet.get_Name()
       at MyCode.test_Click(Object sender, RibbonControlEventArgs e) in c:\MyCode.cs:line 413
  InnerException:

This wouldn't even be an issue if I could check for a workbook delete event, but Excel doesn't provide one (which is really annoying).

如果我可以检查工作簿删除事件，这甚至不是问题，但Excel不提供一个（这真的很烦人）。

Is there some convenient way to make sure I don't use these worksheets?

有没有方便的方法来确保我不使用这些工作表？

3 个解决方案

#1

If the other solutions fail, another way to handle this is to store the name of the workbook after it opens, then check to see if that name exists in the Workbooks collection before referencing the sheet. Referencing the workbooks by name will work since you can only have uniquely named workbooks in each instance of Excel.

如果其他解决方案失败，另一种处理方法是在工作簿打开后存储工作簿的名称，然后在引用工作表之前检查工作簿集合中是否存在该名称。按名称引用工作簿将起作用，因为在每个Excel实例中只能有唯一命名的工作簿。

public void Test()
{
    Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook;
    string wbkName = book.Name; //get and store the workbook name somewhere
    Worksheet sheet = (Worksheet)book.Worksheets[1]; // Get first worksheet
    book.Close(); // Close the workbook
    bool isNull = sheet == null; // false, worksheet is not null
    string name;

    if (WorkbookExists(wbkName))
    {
        name = sheet.Name; // will NOT throw a COM Exception
    }
}

private bool WorkbookExists(string name)
{
    foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks)
    {
        if (wbk.Name == name)
        {
            return true;
        }
    }

    return false;
}

Edit: for completeness, a helper extension method:

编辑：为了完整性，一个帮助扩展方法：

public static bool SheetExists(this Excel.Workbook wbk, string sheetName)
{
    for (int i = 1; i <= wbk.Worksheets.Count; i++)
    {
        if (((Excel.Worksheet)wbk.Worksheets[i]).Name == sheetName)
        {
            return true;
        }
    }

    return false;
}

#2

I use this method :

我用这个方法：

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

or you can try something like this:

或者您可以尝试这样的事情：

    static bool IsOpened(string wbook) 
{ 
    bool isOpened = true; 
    Excel.Application exApp; 
    exApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"); 
    try 
    { 
        exApp.Workbooks.get_Item(wbook); 
    } 
    catch (Exception) 
    { 
        isOpened = false; 
    } 
    return isOpened; 
}

#3

I've not tried this, but you could check if the Workbook sheet.Parent exists in the Application.Workbooks collection.

我没试过这个，但你可以检查Application.Workbooks集合中是否存在Workbook sheet.Parent。

#1

public void Test()
{
    Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook;
    string wbkName = book.Name; //get and store the workbook name somewhere
    Worksheet sheet = (Worksheet)book.Worksheets[1]; // Get first worksheet
    book.Close(); // Close the workbook
    bool isNull = sheet == null; // false, worksheet is not null
    string name;

    if (WorkbookExists(wbkName))
    {
        name = sheet.Name; // will NOT throw a COM Exception
    }
}

private bool WorkbookExists(string name)
{
    foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks)
    {
        if (wbk.Name == name)
        {
            return true;
        }
    }

    return false;
}