C# excel add in - how to directly update sheet UI from event handler on long processing tasks

Question

Hello_ everyone. First apologize if the question is not ask correctly and please correct me if I am wrong.

I am developing excel add-in using Visual Studio 2015. The project type is Excel 2013 and 2016 VSTO Add-in and my problem is that I want to show to users some indication that application is loading data at button click but UI is being updated when data is loaded and not before that ...

For example:

private void MyCustomMenuItem_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
        {

            Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
            Excel.Shapes shapes = sheet.Shapes;
            shapes.AddTextEffect(Office.MsoPresetTextEffect.msoTextEffect1,
                "Loading please wait ...",
                "Arial",
                16,
                Office.MsoTriState.msoFalse,
                Office.MsoTriState.msoFalse,
                10,
                10
                );

            // assume that on the following line is a long processing calculation
            Thread.Sleep(3000);
            // And the problem is that the Shape will show after 3 seconds
}

My problem is that the shape element will show after the thread is ready with its job in this case after 3 seconds. If on the place of Thread.Sleep(3000) I have some code that will run 40 seconds it will show the text after 40 seconds ...

I know I'm doing something wrong and not updating the UI thread correctly ...

Question: How to update UI thread before a long processing task ?

Note: I'm using Microsoft.Office.Interop.Excel namespace for interacting with the excel application.

I did try to show a custom form with nothing but the text "Loading please wait" but again the form is showing after the long process ends ... Other thing that I tried is rising a custom event and trying to show something from the event handler but again no success ...

I want to avoid using Globals.ThisAddIn.Application.StatusBar = "Loading please wait ..." method and do something more user friendly.

If someone can point me what am I doing wrong it will be great.

Thanks in advance_

score 1 · Accepted Answer · edited May 23 '17 at 12:17

You're UI is "freezing" because the UI thread is busy doing other things.

If you're using WPF, read this question.

If you're using Win32 Forms, read this question.

This is not a vsto or excel-interop problem. You just need to be careful with threads. If you want your UI to update while a long process is executing, then you'll want that process to be on a background thread. However, here are a few rules to keep in mind for your case (this is not a complete list):

Don't update the UI directly from a background thread.
Don't modify the Excel objects from a thread other than the one from which it was created.

If you're wanting an older/simpler way to update the UI, check out BackgroundWorker and the ReportProgress method. If you want to keep up with the more modern Task, check out the links above.

After some study and implementation, please post some code examples if you have any trouble.

score 0 · Answer 2 · answered Feb 16 '17 at 17:27

Hello_ friend and sorry for the late answer.

You were totally right and with the help of the link that you posted I succeed to complete what I was trying to do.

I am posting this answer in case someone else gets in this problem and finds this discussion.

This is what I've did:

private void ShowLoadingTextMenuItem_Click(Office.CommandBarButton Ctrl, ref bool CancelDefault)
{
    // This part is very important because on the following call  
    // to FromCurrentSynchronizationContext() you can get an exception
    if (SynchronizationContext.Current == null)
    {
        SynchronizationContext.SetSynchronizationContext(new SynchronizationContext());
    }

    var context = TaskScheduler.FromCurrentSynchronizationContext();
    var task = new Task(() =>
    {
        // Disable excel application interactivity
        // you can avoid this and user will be able to use the UI
        // but there is the possibility to get HRESULT: 0x800AC472 Exception
        // when the long processing ends and you try to update the UI
        // which is probably coming because main UI thread is busy  
        Globals.ThisAddIn.Application.Interactive = false;

        // This is a simple class that is just showing WordArt text
        // in the middle of screen to indicate the loading process
        LoadingOverlayHelper.StartLoading();
    });

    Task continuation = task.ContinueWith(t =>
    {
        Globals.ThisAddIn.Application.StatusBar = "Doing some long processing task ...";

        // At this point you start the long processing task
        Thread.Sleep(4000);

        // And when its over remove the processing text
        LoadingOverlayHelper.StopLoading();

        Globals.ThisAddIn.Application.StatusBar = "Ready";

        Globals.ThisAddIn.Application.Interactive = true;

    }, CancellationToken.None, TaskContinuationOptions.OnlyOnRanToCompletion, context);

    // Execute the task !
    task.Start();
}

And also I made a github repository with the code that I was playing with, so anybody can get it and play with it too - https://github.com/research-and-develop/ExcelAddInPlayground/

Thanks

C# excel add in - how to directly update sheet UI from event handler on long processing tasks

2 Answers2