Dans Excel VSTO, comment puis-je vérifier si une feuille de travail appartient à un classur fermé?

Si j'ai une reference à la Worksheet de Worksheet et je ferme le Workbook parent, la reference ne disparaît pas. Mais je ne peux pas comprendre comment je devrais vérifier pour s'assurer que ces feuilles n'existent pas. La vérification de null ne fonctionne pas.

Exemple:

 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 ssortingng name = sheet.Name; // throws a COM Exception

C'est l'exception que j'obtiens lorsque j'essaie d'accéder à la fiche:

 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:

Cela ne serait même pas un problème si je pouvais vérifier un événement de suppression de classur, mais Excel ne fournit pas un (ce qui est vraiment ennuyant).

Y a-t-il un moyen pratique de m'assurer que je n'utilise pas ces feuilles de travail?

Si les autres solutions échouent, une autre façon de gérer cela est de stocker le nom du classur après l'ouverture, puis de vérifier si ce nom existe dans la collection des Workbooks avant de referencer la feuille. Le referencement des classurs par nom fonctionnera car vous ne pouvez avoir que des classurs nommés de manière unique dans chaque instance d'Excel .

 public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } { public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } { public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } } public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } } public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } { public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } { public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } { public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } } public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } } public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; } renvoyer faux; public void Test() { Workbook book = Globals.ThisAddIn.Application.ActiveWorkbook; ssortingng 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 ssortingng name; if (WorkbookExists(wbkName)) { name = sheet.Name; // will NOT throw a COM Exception } } private bool WorkbookExists(ssortingng name) { foreach (Microsoft.Office.Interop.Excel.Workbook wbk in Globals.ThisAddIn.Application.Workbooks) { if (wbk.Name == name) { return true; } } return false; }

Modifier: pour l'exhaustivité, une méthode d'extension auxiliaire:

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

J'utilise cette méthode:

  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.ToSsortingng()); } finally { GC.Collect(); } } {  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.ToSsortingng()); } finally { GC.Collect(); } } {  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.ToSsortingng()); } finally { GC.Collect(); } } }  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.ToSsortingng()); } finally { GC.Collect(); } } {  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.ToSsortingng()); } finally { GC.Collect(); } } }  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.ToSsortingng()); } finally { GC.Collect(); } } {  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.ToSsortingng()); } finally { GC.Collect(); } } }  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.ToSsortingng()); } finally { GC.Collect(); } }

ou vous pouvez essayer quelque chose comme ceci:

  static bool IsOpened(ssortingng 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; } {  static bool IsOpened(ssortingng 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; } {  static bool IsOpened(ssortingng 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; } }  static bool IsOpened(ssortingng 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; } {  static bool IsOpened(ssortingng 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; } }  static bool IsOpened(ssortingng 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; }

Je n'ai pas essayé cela, mais vous pouvez vérifier si la sheet.Parent Workbook sheet.Parent existe dans la collection Application.Workbooks.