logo标签列表

在C#中等待Excel Interlop退出

c#excelwaitexit
4
我正在开发一款工具，可以自动执行多项操作。其中之一是下载Excel文件，运行宏，然后将文件发送邮件。在某些情况下，我希望该工具仅运行Excel宏，等待x秒后退出Excel并发送电子邮件。这些都很顺利，因为我可以等待固定的时间。但是，在某些情况下，我希望能够检查和更改Excel宏检索到的数据。当出现这种情况时，我希望保持Excel打开状态，直到用户手动关闭Excel。当我检测到Excel不再打开时，我想要发送文件。
这个最后一种情况给我带来了一些麻烦。因为我使用Excel交互来打开Excel，所以我无法像普通进程一样使用WaitForExit()。当我手动关闭Excel时，进程也会在进程资源管理器中继续运行。
我已经在互联网上搜索了解决方案，但没有一个真正有效。有没有简单的方法可以实现这个？
更新： 非常感谢您的回复，这真的帮助了我。现在等待Excel的工作正常进行。我使用了以下代码：
if (Settings.ExitExcel)
{
  System.Threading.Thread.Sleep(Settings.ExcelTimeout * 1000);

  //Close Excel
  excelWorkbook.Close();
  excelApp.Quit();
}
else
{
  Excel.AppEvents_WorkbookBeforeCloseEventHandler Event_BeforeBookClose;

  Event_BeforeBookClose = new Excel.AppEvents_WorkbookBeforeCloseEventHandler(WorkbookBeforeClose);
  excelApp.WorkbookBeforeClose += Event_BeforeBookClose;

  //Wait until excel is closed
  while (!isClosed)
  {
    Thread.Sleep(1000);
  }

  //Show message               
  MessageBox.Show("excel closed");
}

//Clean up excel.
Marshal.FinalReleaseComObject(excelSheets);
Marshal.FinalReleaseComObject(excelWorksheet);
Marshal.FinalReleaseComObject(excelWorkbook);
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
excelWorkbook = null;
excelWorksheet = null;
excelSheets = null;
GC.Collect();

现在唯一的问题是Excel仍在进程中运行。我没有正确关闭它吗？

如果你无法进行Interop，请尝试Elope。最终你会回到Interop。 - John Alexiou
2个回答
4
您可以使用BeforeClose事件来实现此操作。基本上，您需要按照以下步骤进行操作：
  1. 创建一个变量来跟踪状态，即Excel是打开还是关闭。
  2. 为WorkbookBeforeClose事件创建一个事件处理程序。
  3. 在处理程序中，检查工作簿是否已关闭，并相应地更新您的状态跟踪变量的值。
  4. 如果工作簿已关闭，请执行您的邮件发送操作。
代码可能如下所示：
//Outside main
private static bool isClosed = false;

....

//Let's say your Workbook object is named book
book.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(app_WorkbookBeforeClose);

if(isClosed)
sendMailMethod();

...

private static void app_WorkbookBeforeClose(Excel.Workbook wb, ref bool cancel)
    {
        closed = !cancel;
    }

这种方法不需要你先结束进程。但是，在完成Excel工作后，应释放COM对象并结束进程。

编辑：要关闭进程，请尝试使用Application.Quit()方法。

非常感谢您的回复。它对我很有帮助。然而，我还有一个问题没有解决，所以我更新了我的帖子。 - Roger
再次感谢您的快速回复。我尝试了几次添加excelApp.Quit()，但似乎无法关闭该进程。 - Roger
啊，好的，我的错！你已经在发布它了。抱歉。看起来你可能需要使用Process.Kill。 - shree.pat18
理论上是的。然而，我的感觉是这并不总是奏效 - 可能是因为使用了COM，它与Interop包装类和其他托管的C#代码一起使用时是不受管理的。 - shree.pat18
我会尝试寻找解决方案。非常感谢你的帮助！ - Roger
显示剩余2条评论
0

如果您还没有找到解决方案，这是我为自己整理的有效方法。

private async void OnOpenWorkSheetImportCommand(object sender, ExecutedRoutedEventArgs e)
{
    try
    {
        if (e.Parameter is IWorkSheetImportViewModel model)
        {
            if (WorksheetDictionaries.OfType<WorksheetDictionary>().FirstOrDefault(a => a.Header.Equals(model.Header)) is WorksheetDictionary worksheetDictionary)
            {
                Type officeType = Type.GetTypeFromProgID("Excel.Application", true);

                if (officeType != null)
                {
                    BeforeWorkbookDictionary = worksheetDictionary.WorkbookDictionary;
                    OpenInteropWorkbookDictionary(worksheetDictionary.WorkbookDictionary, worksheetDictionary, true);
                }
                else
                {
                    throw new ArgumentException("Microsoft Excel is not installed!");
                }
            }
        }
    }
    ...
    ..
    .
}

调用 OpenInteropWorkbookDictionary 是我设置 Microsoft.Office.Interop.Excel 的地方

private void OpenInteropWorkbookDictionary(WorkbookDictionary workbookDictionary, WorksheetDictionary worksheetDictionary, bool activateWorksheet = false)
{
    if (ExcelApplication != null && ExcelApplication.Visible == true)
    {
        ExcelApplication.ActiveWindow.Activate();
        IntPtr handler = FindWindow(null, ExcelApplication.Caption);
        SetForegroundWindow(handler);
    }
    else
    {
        if (ExcelApplication != null) Marshal.FinalReleaseComObject(ExcelApplication);
        if (ExcelWorkbook != null) Marshal.FinalReleaseComObject(ExcelWorkbook);
        if (ExcelWorksheet != null) Marshal.FinalReleaseComObject(ExcelWorksheet);
        if (ExcelWorksheets != null) Marshal.FinalReleaseComObject(ExcelWorksheets);

        ExcelApplication = null;
        ExcelWorkbook = null;
        ExcelWorksheet = null;
        ExcelWorksheets = null;

        ExcelApplication = new Microsoft.Office.Interop.Excel.Application
        {
            // if you want to make excel visible to user, set this property to true, false by default
            Visible = true,
            WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized
        };

        ExcelApplication.WindowActivate += new Microsoft.Office.Interop.Excel.AppEvents_WindowActivateEventHandler(OnWindowActivate);
        ExcelApplication.WindowDeactivate += new Microsoft.Office.Interop.Excel.AppEvents_WindowDeactivateEventHandler(OnWindowDeactivate);

        ExcelApplication.WorkbookOpen += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookOpenEventHandler(OnWorkbookOpen);
        ExcelApplication.WorkbookActivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookActivateEventHandler(OnWorkbookActivate);
        ExcelApplication.WorkbookDeactivate += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookDeactivateEventHandler(OnWorkbookDeactivate);

        // open an existing workbook
        ExcelWorkbook = ExcelApplication.Workbooks.Open(BeforeWorkbookDictionary.FilePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
        ExcelWorkbook.BeforeClose += new Microsoft.Office.Interop.Excel.WorkbookEvents_BeforeCloseEventHandler(OnBeforeClose);
        ExcelWorkbook.AfterSave += new Microsoft.Office.Interop.Excel.WorkbookEvents_AfterSaveEventHandler(OnAfterSave);
        ExcelWorkbook.BeforeSave += new Microsoft.Office.Interop.Excel.WorkbookEvents_BeforeSaveEventHandler(OnBeforeSave);

        // SheetEvents
        ExcelWorkbook.SheetChange += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetChangeEventHandler(OnSheetChange);
        ExcelWorkbook.SheetSelectionChange += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetSelectionChangeEventHandler(OnSheetSelectionChange);
        ExcelWorkbook.NewSheet += new Microsoft.Office.Interop.Excel.WorkbookEvents_NewSheetEventHandler(OnNewSheet);
        ExcelWorkbook.SheetBeforeDelete += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetBeforeDeleteEventHandler(OnSheetBeforeDelete);
        ExcelWorkbook.SheetActivate += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetActivateEventHandler(OnSheetActivate);
        ExcelWorkbook.SheetDeactivate += new Microsoft.Office.Interop.Excel.WorkbookEvents_SheetDeactivateEventHandler(OnSheetDeactivate);

        //// get all sheets in workbook
        ExcelWorksheets = ExcelWorkbook.Worksheets;
    }

    if (activateWorksheet)
    {
        ExcelWorksheet = ExcelWorkbook.Sheets[worksheetDictionary.Name];
        ExcelWorksheet.Activate();
    }
}

注意：

  1. 首先调用ExcelWorkbook.BeforeClose，然后再调用ExcelApplication.WorkbookDeactivate，最后才完全关闭Excel。
  2. 因此，在ExcelApplication.WorkbookDeactivate中，我设置并使用Process来等待其自然终止。
  3. 额外信息：我使用Reactive来捕获OnClose事件。
  4. 重要提示：当用户关闭Workbook时，我关闭Excel！

所以在OnWorkbookDeactivate中：

private async void OnWorkbookDeactivate(Microsoft.Office.Interop.Excel.Workbook Wb)
{
    try
    {
        ProgressController = default(ProgressDialogController);
        if (ExcelApplication != null && ExcelApplication.Visible == true)
        {
            int excelProcessId = -1;
            GetWindowThreadProcessId(new IntPtr(ExcelApplication.Hwnd), ref excelProcessId);
            Process ExcelProc = Process.GetProcessById(excelProcessId);

            if (ExcelProc != null)
            {
                ExitedEventArgs = Observable.FromEventPattern<object, EventArgs>(ExcelProc, MethodParameter.Exited);
                DisposableExited = ExitedEventArgs.Subscribe(evt => OnExitedEvent(evt.Sender, evt.EventArgs));
                ExcelProc.EnableRaisingEvents = true;

                ProgressController = await _dialogCoordinator.ShowProgressAsync(this, string.Format("Workbook {0}", BeforeWorkbookDictionary.ExcelName), "Please wait! Processing any changes...");
                ProgressController.SetIndeterminate();

                ExcelApplication.Quit();

                if (ExcelApplication != null) Marshal.FinalReleaseComObject(ExcelApplication);
                if (ExcelWorkbook != null) Marshal.FinalReleaseComObject(ExcelWorkbook);
                if (ExcelWorksheet != null) Marshal.FinalReleaseComObject(ExcelWorksheet);
                if (ExcelWorksheets != null) Marshal.FinalReleaseComObject(ExcelWorksheets);

                ExcelApplication = null;
                ExcelWorkbook = null;
                ExcelWorksheet = null;
                ExcelWorksheets = null;

                ExcelProc.WaitForExit();
                ExcelProc.Refresh();
            }
        }
    }
    catch (Exception msg)
    {
    ...
    ..
    .
    }
}

我在课堂上设置了一些东西，以使所有这些工作正常：

#region DllImports

[DllImport("user32.dll")]
[return: MarshalAs(UnmanagedType.Bool)]
private static extern bool SetForegroundWindow(IntPtr hWnd);
[DllImport("user32.dll", SetLastError = true)]
private static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
[DllImport("user32.dll", SetLastError = true)]
private static extern int GetWindowThreadProcessId(IntPtr hwnd, ref int lpdwProcessId);

#endregion

并且：

#region RoutedEventArgs Properties

private IDisposable disposableClosingEvent;
public IDisposable DisposableClosingEvent
{
    get => this.disposableClosingEvent; set => SetProperty(ref disposableClosingEvent, value, nameof(DisposableClosingEvent));
}

private IObservable<EventPattern<object, EventArgs>> exitedEventArgs;
public IObservable<EventPattern<object, EventArgs>> ExitedEventArgs
{
    get => exitedEventArgs; set => SetProperty(ref exitedEventArgs, value, nameof(ExitedEventArgs));
}
private IDisposable disposableExited;
public IDisposable DisposableExited
{
    get => this.disposableExited; set => SetProperty(ref disposableExited, value, nameof(DisposableExited));
}

#endregion

最后: 使用OnExitedEvent进行一些最后的处理。

private async void OnExitedEvent(object sender, EventArgs e)
{
    try
    {
        DisposableExited.Dispose();
        using (SpreadsheetDocument s = SpreadsheetDocument.Open(SelectedWorkbookDictionaryImport.FilePath, false))
        {
            foreach (Sheet workbookSheet in s.WorkbookPart.Workbook.Sheets)
            {
                if (WorkbookDictionaryImports.OfType<WorkbookDictionary>().FirstOrDefault(d => d.ExcelName.Equals(SelectedWorkbookDictionaryImport.ExcelName)) is WorkbookDictionary workbookDictionary)
                {
                    if (workbookDictionary.WorksheetDictionaryItems.OfType<WorksheetDictionary>().FirstOrDefault(d => d.Id.Equals(workbookSheet.Id)) == null)
                    {
                        Application.Current.Dispatcher.Invoke(() =>
                        {
                            WorksheetDictionary worksheetDictionary = new WorksheetDictionary()
                            {
                                WorksheetDictionaryId = Guid.NewGuid().ToString(),
                                IsSelected = false,
                                Id = workbookSheet.Id,
                                SheetId = workbookSheet.SheetId,
                                Name = workbookSheet.Name,
                                Header = workbookSheet.Name,
                                HeaderInfo = GetSheetStateValues(workbookSheet.State),
                                Code = GetSheetCodeValues(workbookSheet.Name.ToString()).ToString(),
                                Description = BeforeWorkbookDictionary.Subject,
                                WorkbookDictionary = BeforeWorkbookDictionary
                            };
                            workbookDictionary.WorksheetDictionaryItems.Add(worksheetDictionary);
                        });
                    }
                }
            }
        }
    }
    catch (Exception msg)
    {
    ...
    ..
    .
    }
    finally
    {
        if (ProgressController != null)
        {
            await ProgressController.CloseAsync();
            ProgressController = default(ProgressDialogController);
        }
    }
}

希望这篇文章能帮到你和其他人，当然要根据你的处理需求进行更改。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接