Testing - develop a testing strategy for async and real-time streaming functions

[govert]

RTD-based async and streaming functions are easy to develop with Excel-DNA, but remain hard to test. We need a plan for these and perhaps some helpers in the Testing framework. It would be a start idea to see and document how async and IObservable functions are tested in .NET elsewhere, and see if there's a way to adapt this to the Excel environment. Behaviour of these features under 'manual' calculation mode is also quite different to automatic mode, and can perhaps be some help in knowing when to check values.

[govert]

Some examples from @eeaston:

Given a UDF like this:

       [ExcelFunction(Name = "MyUDF",
           Description = "Test for ExcelDNA.Testing",
           Category = "Test")]
       public static string MyUDF()
       {
           dynamic app = ExcelDnaUtil.Application;
           ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
           ExcelAsyncUtil.QueueAsMacro(() =>
           {
               caller.SetValue(XlCall.Excel(XlCall.xlfGetWorkbook, 16).ToString());
           });
           return "Running ..";
       }

When running this normally, the function sets the calling cell’s value initially to ‘OK’, and then shortly after to the name of the workbook.

Here’s a little test to try and capture this behaviour :

       [ExcelFact(Workbook = "MyTest.xlsx")]
       public void MacroTest()
       {
           Workbook wb = Util.Workbook;
           var ws = wb.Sheets[1];
           ws.Range["A1"].Formula = "=MyUDF()";

           Thread.Sleep(3000);

           var result = ws.Range["A1"].Value;
           Assert.Equal("MyText.xlsx", result);    // This fails, instead result = “Running ..” 
       } 

No matter how long I sleep in the middle here, the queued macro function never runs within the life of the test. If however I set the test to run in OutOfProcess mode, it seems to works OK running C-api as well as the COM API functions.

[govert]

Maybe a start could be to support async tests, and have some primitives helpers that can be used inside a tests:

       [ExcelFact(Workbook = "MyTest.xlsx")]
       public async Task MacroTest()
       {
           Workbook wb = Util.Workbook;
           var ws = wb.Sheets[1];
           ws.Range["A1"].Formula = "=MyUDF()";

           await Util.AfterCalculate();  // maybe with a timeout?

           var result = ws.Range["A1"].Value;
           Assert.Equal("MyText.xlsx", result);    // This fails, instead result = “Running ..” 
       } 

An implementation for Util.AfterCalculate() might set up some Task and then pass a TaskCompletionSource to a call to ExcelAsyncUtil.QueueAsMacro which we think will run after calculate?

We might even nest the QueueAsMacro twice (enqueue the one with the completion only when the first one runs), to make sure that we let queued macros that are called from function run first, before our completion.

Similarly we might imagine

    await Util.AfterRangeChange(myRange);

where we watch SheetChange events for a range that intersects with myRange.
Though I'm not sure what to do if it doesn't change - I guess there's a timeout and the test fails?

The simplest one might just be a delay.

    await Task.Delay(3000);

I think streaming functions would be harder to deal with, so we can consider these later.

[Sergey-Vlasov]

The initial example works if instead of Thread.Sleep(3000); we call System.Windows.Forms.Application.DoEvents();

[Sergey-Vlasov]

I've added Automation.Wait and Automation.WaitFor methods to wait for an async function to complete, internally synchronously calling Sleep() and DoEvents():
6ad823f