ExcelAsyncUtil.Observe

Excel 2010 and later. Available with .NET 4.0
Asynchronous function can then be implemented as an IObservable that returns a single value before completing.
Use the .NET 4.0 Thread class.
Excel-DNA defines an interface called IExcelObservable that matches the semantics of IObservable
You cannot interact with the Excel UI while the function executes.
Excel can just continue with different parts of the sheet calculation while the function is executing.
Cancellation (triggered when the user removes a real-time or async formula) is supported via the standard IDisposable mechanism
These take an ExcelAsyncHandle as the parameter and have a return type of "void".


ExcelAsyncUtil.Observe - Parameters

functionName (string) - identifies this async function.
parameters (object) - includes all the parameters to the function.
This can be a single object (string, or an object array of parameters.
function (function/delegate) - a delegate that will be evaluated asynchronously.


public static string udf_CLOCK(string sArgument1) 
{
    return System.Convert.ToString(ExcelDna.Integration.ExcelAsyncUtil.Observe(
        "udf_SOMETEXT2",
        sArgument1,
        () => new ExcelObservableClock() )
    );
}

public class ExcelObservableClock : ExcelDna.Integration.IExcelObservable
{
    System.Threading.Timer _timer;
    System.Collections.Generic.List<ExcelDna.Integration.IExcelObserver> _observers;

    public ExcelObservableClock()
    {
       _timer = new System.Threading.Timer(method_TimerTick, null, 0, 1000);
       _observers = new System.Collections.Generic.List<ExcelDna.Integration.IExcelObserver>();
    }

    public System.IDisposable Subscribe(ExcelDna.Integration.IExcelObserver observer)
    {
       _observers.Add(observer);
       observer.OnNext(DateTime.Now.ToString("HH:mm:ss.fff") + " (Subscriber)");
       return new ActionDisposable(() => _observers.Remove(observer));
    }

    private void method_TimerTick(object oo)
    {
        string sNow = DateTime.Now.ToString("HH:mm:ss.fff");
        foreach (var obj in _observers)
        {
            obj.OnNext(sNow);
        }
    }
}

public class ActionDisposable : System.IDisposable
{
    System.Action _disposeAction;

    public ActionDisposable(System.Action disposeAction)
    {
        _disposeAction = disposeAction;
    }
    public void Dispose()
    {
        _disposeAction();
    }
}



The main helper function is AsyncTaskUtil, which takes the async call identifiers (the callerFunctionName and callerParameters) as well as an Action that will create the async Task on the first call.
Internally, an ExcelTaskObservable is created, which converts the Task completion result into the appropriate IObservable interface to register with Excel-DNA.
There is also an overload that supports cancellation.


namespace ExcelDNALibrary 
{
   public class Class1 : ExcelDna.Integration.IExcelAddIn
   {
      void ExcelDna.Integration.IExcelAddIn.AutoOpen()
      {
         ExcelDna.Integration.ExcelIntegration.RegisterUnhandledExceptionHandler();
         ExcelDna.Integration.ExcelIntegration.Initialize();
      }

      void ExcelDna.Integration.IExcelAddIn.AutoClose()
      {
         ExcelDna.Integration.ExcelIntegration.Uninitialize();
      }

      public static string MyWebsiteString(string sURL)
      {
         return ExcelDna.Integration.ExcelAsyncUtil.Observe("MyWebsiteString",
                                                            sURL,
                                                            delegate {
                value = MyHelperClass.GetStringAsync(sURL).ToExcelObservable();
                return value;
             }
      }
   }
}


    [ExcelFunction] 
    public static object MyExcelTicker()
    {
        return ExcelAsyncUtil.Observe("MyExcelTicker", new object[] { }, TickerFunction());
    }

    public static ExcelObservableSource TickerFunction()
    {
        ExcelObservableSource source = new ExcelObservableSource(() => new TickerObservable());
        return source;
    }

In order to allow compatibility with .NET 2.0, the interfaces IExcelObservable / IExcelObserver are used by Excel-DNA, but the semantics is the same as the .NET 4.0 interfaces IObservable / IObserver.
link - github.com/Excel-DNA/Samples/tree/master/RtdClocks

public static object udf_MyFunction(string sText1) 
{
   return ExcelAsyncUtil.Observe(
      functionName, // "name"
      paramInfo, // new object[] {param1, param2}
      () => new ExcelObservableClock() );
}

class ExcelObservableClock : ExcelDna.Integration.IExcelObservable //System.IObservable
{
   Timer _timer;
   List<ExcelDna.Integration.IExcelObserver> _observers;

   public ExcelObservableClock() {
      _timer = new Timer(method_TimerTick, null, 0, 1000);
      _observers = new List(<ExcelDna.Integration.IExcelObserver>();
   }

   public IDisposable Subscribe(ExcelDna.Integration.IExcelObserver observer)
   {
      _observers.Add(observer);
      observer.OnNext(DateTime.Now.ToString("HH:mm:ss.fff") + " (Subscribe)" );
      return new ActionDisposable( () => _observers.Remove(observer) );
   }

   void method_TimerTick(object ??)
   {
      string now = DateTime.Now.ToString("HH:mm:ss.fff");
      foreach(var obj in _observers)
      {
         obj.OnNext(now);
      }
   }
}

class ActionDisposable : IDisposable
{
   System.Action _disposeAction;
   public ActionDisposable(System.Action disposeAction)
   {
      _disposeAction = disposeAction;
   }
   public void Dispose()
   {
      _disposeAction();
   }
}


public class TickerObservable : IExcelObservable 
{
    public IDisposable Subscribe(IExcelObserver observer)
    {
        var timer = new System.Timers.Timer();
        timer.Interval = 1000;
        timer.Elapsed += (s, e) => observer.OnNext(DateTime.Now.ToString());
        timer.Start();

        return new TickerDisposable(timer);
    }
}

public class TickerDisposable : IDisposable
{
    private Timer ticky;
    public TickerDisposable(Timer timer)
    {
        ticky = timer;
    }

    public void Dispose()
    {
        if(ticky != null)
        {
            ticky.Dispose(); // or Stop, or etc..
        }
    }
}


public static void Run2() 
{
    System.Threading.Tasks.Task.Factory.StartNew( () => {
    System.Threading.Thread.Sleep(2000);
    var sc = new ExcelSynchronizationContext();

    sc.Post(delegate {
         var app = (Application)ExcelDna.Util.Application;
         app.Cells[1,1] = String.Format("Done at {0}", System.DateTime.Now);
      }, null);

      System.Threading.Thread.Sleep(2000);

      sc.Post(delegate {
         new ExcelReference(3,3,3,3).SetValue(String.Format("Done at {0}", System.DateTime.Now));

         // update the status bar
         ExcelDna.Integration.XlCall.Excel(ExcelDna.Integration.XlCall.xlcMessage, true, "Hello");
      }, null);

      System.Threading.Thread.Sleep(2000);

      sc.Post(delegate {
         // cancel the status bar
         ExcelDna.Integration.XlCall.Excel(ExcelDna.Integration.XlCall.xlcMessage, false);
      }, null);

}


Asnc / Await (.NET 4.5)



Links

link - docs.microsoft.com/en-us/office/client-developer/excel/developing-excel-xlls
link - docs.microsoft.com/en-us/office/client-developer/excel/asynchronous-user-defined-functions
link - docs.excel-dna.net/asynchronous-functions-with-tasks/
link - github.com/Excel-DNA/Samples/tree/master/LimitedConcurrencyAsync


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext