Excel自动化：关闭事件丢失 [英] Excel automation: Close event missing

问题描述

另一个你好，

我在C＃中通过Interop进行Excel自动化，我想在工作簿关闭时被通知。但是，工作簿上没有关闭事件，也没有在应用程序上退出事件。

以前有人做过吗？ 如何编写一段对正在关闭的工作簿作出反应的代码（仅当工作簿真的关闭时才执行）？理想情况下，应该发生

关闭工作簿，所以我可以依靠该文件来反映所有更改。

有关我到目前为止发现的详细信息：

有一个BeforeClose（）事件，但是如果存在未保存的更改，则会在用户询问是否保存该事件之前引发此事件，因此我可以处理该事件，我不会有最终的文件，我无法释放COM对象，我需要/做的两件事情。我甚至不知道工作簿是否实际关闭，因为用户可能会选择中止关闭。

然后有一个BeforeSave（）事件。因此，如果用户选择是来保存未保存的更改，则BeforeSave（）将在 BeforeClose（）之后执行。但是，如果用户选择中止，则点击file-> save，执行完全相同的事件顺序。此外，如果用户选择否，则完全不执行BeforeSave（）。只要用户没有点击任何这些选项，这一点也是一样。

解决方案

我创建了一个使用投票方式，它的工作原理：

给定工作簿要观察，我创建一个线程，定期尝试在工作簿集合中找到该工作簿。

（DisposableCom类是我目前的解决方案正确清理COM对象。）

  Excel.Application app = wbWorkbook.Application; 
 string sWorkbookName = wbWorkbook.Name; 
 
线程overseeWorkbooksThread = new Thread（new ThreadStart（
 delegate（）
 {
 bool bOpened = false; 
 
 Excel.Workbooks wbsWorkbooks = app.Workbooks; 
 using（new DisposableCom< Excel.Workbooks>（wbsWorkbooks））
 {
 while（true）
 {
 Thread.Sleep（1000） ; 
 
 if（wbsWorkbooks.ContainsWorkbookProperly（sWorkbookName））
 bOpened = true; 
 else 
 if（bOpened）
 //工作簿打开，所以它已被关闭
 break; 
 else 
 {
 //工作簿根本没有完成打开，不执行任何
} 
} 
 
 //工作簿关闭
 RunTheCodeToBeRunAfterWorkbookIsClosed（）; 
} 
}））; 
 
 overseeWorkbooksThread.Start（）; 
  

ContainsWorkbookProperly扩展方法如下所示：

  public static bool ContainsWorkbookProperly（此Excel.Workbooks excelWbs，
 string sWorkbookName）
 {
 Excel.Workbook wbTemp = null; 
 try 
 wbTemp = excelWbs.Item（sWorkbookName）; 
 catch（异常）
 {
 //忽略
} 
 
如果（wbTemp！= null）
 {
 new DisposableCom< Excel.Workbook>（wbTemp）.Dispose（）; 
返回true; 
} 
 
 return false; 
} 
  

如果有更简单或更好的解决方案，仍然会感兴趣。 p>

Another hi all,

I am doing Excel automation via Interop in C#, and I want to be informed when a workbook is closed. However, there is no Close event on the workbook nor a Quit event on the application.

Has anybody done that before? How can I write a piece of code which reacts to the workbook being closed (which is only executed if the workbook is really closed)? Ideally that should happen after closing the workbook, so I can rely on the file to reflect all changes.

Details about what I found so far:

There is a BeforeClose() event, but if there are unsaved changes this event is raised before the user being asked whether to save them, so at the moment I can process the event, I don't have the final file and I cannot release the COM objects, both things that I need to have/do. I do not even know whether the workbook will actually be closed, since the user might choose to abort closing.

Then there is a BeforeSave() event. So, if the user chooses "Yes" to save unsaved changes, then BeforeSave() is executed after BeforeClose(). However, if the user chooses to "Abort", then hits "file->save", the exact same order of events is executed. Further, if the user chooses "No", the BeforeSave() isn't executed at all. The same holds as long as the user doesn't click any of these options.

解决方案

I've created a hack using a polling-like approach, and it works:

Given the workbook to observe, I create a thread which periodically tries to find that workbook in the workbooks collection.

(The DisposableCom class is my current solution to properly cleanup COM objects.)

Excel.Application app = wbWorkbook.Application;
string sWorkbookName = wbWorkbook.Name;

Thread overseeWorkbooksThread = new Thread(new ThreadStart(
    delegate()
    {
        bool bOpened = false;

        Excel.Workbooks wbsWorkbooks = app.Workbooks;
        using (new DisposableCom<Excel.Workbooks>(wbsWorkbooks))
        {
            while (true)
            {
                Thread.Sleep(1000);

                if (wbsWorkbooks.ContainsWorkbookProperly(sWorkbookName))
                    bOpened = true;
                else
                    if (bOpened)
                        // Workbook was open, so it has been closed.
                        break;
                    else
                    {
                        // Workbook simply not finished opening, do nothing
                    }
            }

            // Workbook closed
            RunTheCodeToBeRunAfterWorkbookIsClosed();
        }
    }));

overseeWorkbooksThread.Start();

The "ContainsWorkbookProperly" extension methods looks like this:

public static bool ContainsWorkbookProperly(this Excel.Workbooks excelWbs,
    string sWorkbookName)
{
    Excel.Workbook wbTemp = null;
    try
        wbTemp = excelWbs.Item(sWorkbookName);
    catch (Exception)
    {
        // ignore
    }

    if (wbTemp != null)
    {
        new DisposableCom<Excel.Workbook>(wbTemp).Dispose();
        return true;
    }

    return false;
}

Still I would be interested if there is a simpler or better solution.

这篇关于Excel自动化：关闭事件丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！