Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Incorrect async/await working, Excel events in Excel Application Level Add-in

I faced a problem with using async /await in my add-in application, I have custom task pane and user can download some data with it, so there are problems:

  1. After awaited operation completed the control is not returned to previous execution context. I created example with windows forms and paste the same method with awaiting and it works fine - I can use form controls after await operation, but not in add-in, I have to call custom task pane Invoke method. Simple example:

    private void SearchPaneButton_Click(object sender, EventArgs e)
    {
        Search();
    }
    
    private async void Search()
    {
        var searchText = SearchTextBox.Text;
        SearchPaneButton.Text = "Loading…";
        var data = await new DataServiceClient().GetDataAsync(searchText);
        this.Invoke((MethodInvoker)(() =>
        {
            SearchPaneButton.Text = "Search";
            ToggleWorkbookEvents();
        }));
    }
    
  2. In my add-in I need to handle some events - worksheet changed, worksheet activated/deactivated, but these events disappear and isn't fired after await operation and I have to use something like "toggling"(remove+add) events after each await using. Simple example:

    private void ToggleWorkbookEvents()
    {
        var excel = Globals.MyAddIn.Application;
        //need to ensure event will not be set twice
        excel.SheetActivate -= CheckSheetForData;
        excel.SheetActivate += CheckSheetForData;
        if (excel.ActiveWorkbook != null)
        {
            var activeSheet = Globals.MyAddIn.GetActiveWorksheet();
            //need to ensure event will not be set twice
            activeSheet.Change -= Worksheet_Changed;
            activeSheet.Change += Worksheet_Changed;
        }
    }
    

So maybe vsto framework can not support new features(like async await) of last versions .NET Framework?

like image 851
Max Sinev Avatar asked Sep 30 '15 11:09

Max Sinev


1 Answers

This is a long-standing issue with Office plugins: they don't provide a SynchronizationContext.

As I mention on my blog, you can work around this by ensuring you have a proper SynchronizationContext. This is a bit hacky, but it works:

private async void SearchPaneButton_Click(object sender, EventArgs e)
{
  if (SynchronizationContext.Current == null)
    SynchronizationContext.SetSynchronizationContext(new WindowsFormsSynchronizationContext());
  await SearchAsync();
}

private async Task SearchAsync()
{
  var searchText = SearchTextBox.Text;
  SearchPaneButton.Text = "Loading…";
  var data = await new DataServiceClient().GetDataAsync(searchText);
  SearchPaneButton.Text = "Search";
  ToggleWorkbookEvents();
}

As far as the "toggling events" problem goes, I have no idea.

like image 180
Stephen Cleary Avatar answered Oct 18 '22 13:10

Stephen Cleary