ExcelDNA RDT Server connecting to a WebSocket data provider

[Iron Mask]

Hello Govert,

I have multiple real-time data sources that I need to connect to on the same Spreadsheet.

Each data source provided Data Table via a sql-like query ("Select X,Y,Z from Table" or "Select * from Table")

Returning any number of rows and columns completely dynamic.

The RTD Formula should allow on any cell =DataProvider("host:port", "sql-like querty")

"host:port" specifying any data source end-point which could be one of many.

How would you suggest going about implementing this without formula interfering with one another and allowing the same in multiple instances of Books/Spreadsheet?

Thanks,

Iron

[Govert van Drimmelen]

This is not so easy to implement. RTD functions should not return array results (this is an Excel limitation).

So the suggested design is to have one function that does the web request query as an asynchronous function using RTD  (or the higher level wrapper ExcelAsyncUtil.Observe) and have these return a handle to one Excel cell and internally store the handle to array map. Then another non-RTD function takes the handle as input and returns the result array, perhaps with auto-resizing of the results.

For examples you can look at the ACQ add-in to learn about the object handle implementation https://excel-dna.net/2016/06/10/add-in-spotlight-acq-for-interpolation/

You might also find some open-source projects if you search on GitHub. But I don't know of a coherent open-source example that puts the whole thing together.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Iron Mask]

Hi Govert,

Thanks for the suggestions.

You are right, it should be an asynchronous operation.

Going along with your suggestion, I was thinking of instantiating a separate WebSocket Manager Class from the 

ConnectData override method, having the topic unique combining both: "host:port" and the "sql"

Handle the communication with the external Manager class. 

And then use ExcelFuntion ShowResult to resize and load the data to the specific cell.

The only question is how do I pass the specific cell to the Manager Class and ultimately to the ExcelFunction ShowResult ?

Thanks,

Iron

[Govert van Drimmelen]

Not sure what your ShowResult function does, or how you'd hook it up to RTD reliably.

The separate handle cell + ArrayResizer approach is the only plan I have a good grip on myself.

-Govert

[Iron Mask]

Hi Govert;

By ShowResult I meant:

 public static void ShowResult(object result)

    {

      try

      {

        ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

        dynamic Excel = ExcelDnaUtil.Application;

        var matrix = result as object[,];

        int rows = matrix.GetLength(0);

        int columns = matrix.GetLength(1);

        Range callerRange = ReferenceToRange(caller);

     

        Excel.Range[callerRange].Resize[rows, columns] = matrix;

      }

      catch (Exception e)

      {

        MessageBox.Show(e.Message.ToString());

      }

    }

    

    private static Range ReferenceToRange(ExcelReference xlref)

    {

      Application app = (Application)ExcelDnaUtil.Application;

      string refText = (string)XlCall.Excel(XlCall.xlfReftext,

        xlref, true);

      Range range = app.get_Range(refText);

      return range;

    }

On Thursday, March 7, 2019 at 6:19:37 AM UTC-5, Iron Mask wrote:

[Iron Mask]

But that did not work for me;

Then I tried the resizer using observable as follows;

The excel just hung up;

public class RTDArrayAddIn : IExcelAddIn
 {
   // connection to server
   private static Timer _timer;
   private readonly List<ExcelRtdServer.Topic> topics = new List<ExcelRtdServer.Topic>();
 
   private  Dictionary<string, Timer> endpointToTimer = new Dictionary<string, Timer>();
 
   public void AutoOpen()
   {
     // setup support for RTD functions
     ExcelAsyncUtil.Initialize();
 
     // setup error handler
     ExcelIntegration.RegisterUnhandledExceptionHandler(ex => "!!! EXCEPTION: " + ex.ToString());
 
     // increase RTD refresh rate since the 2 seconds default is too slow (move as setting to ribbon later)
     object app;
     object rtd;
     app = ExcelDnaUtil.Application;
     rtd = app.GetType().InvokeMember("RTD", BindingFlags.GetProperty, null, app, null);
     rtd.GetType().InvokeMember("ThrottleInterval", BindingFlags.SetProperty, null, rtd, new object[] { 100 });
   }
 
   public void AutoClose()
   {
     ExcelAsyncUtil.Uninitialize();
   }
 
   // event boilerplate stuff
   public delegate void ValueSentHandler(TableValueSentEventArgs args);
   public static event ValueSentHandler OnValueSent;
   public class TableValueSentEventArgs : EventArgs
   {
     public object[,] Value { get; private set; }
     public TableValueSentEventArgs(object[,] Value)
     {
       this.Value = Value;
     }
   }
 
   // this gets called by the server if there is a new value
   public static void UpdateArrayValue(object[,] x)
   {
     // invert method call from WCF into event for Rx
     if (OnValueSent != null)
     {
       OnValueSent(new TableValueSentEventArgs(x));
     }
   }
 
   private static void Start()
   {
     _timer.Change(1500, 1500);
   }
 
   private static void Stop()
   {
     _timer.Change(-1, -1);
   }
 
   private static void Callback(object o)
   {
     Stop();
 
     UpdateArrayValue(GetNewValues());
 
     Start();
   }
 
   private static  object[,] GetNewValues()
   {
     Random random = new Random(Guid.NewGuid().GetHashCode());
 
     int rows = random.Next(3, 15);
     int cols = random.Next(3, 15);
 
     object[,] matrix = new object[rows, cols];
     //
     // fill matrix with random numbers
     for (int i = 0; i < rows; i++)
     {
       for (int j = 0; j < cols; j++)
       {
         matrix[i, j] = random.NextDouble();
       }
     }
 
     return matrix;
   }
 
   // this function accepts an observer and calls OnNext on it every time the OnValueSent event is raised
   // this is the part that chains individual events into streams
   // it returns an IDisposable that can be called after the stream is complete 
   static Func<IObserver<object[,]>, IDisposable> EventToObservable = observer =>
   {
     OnValueSent += d => observer.OnNext(d.Value);   // when a new value was sent, call OnNext on the Observers.
     return Disposable.Empty;                        // we'll keep listening until Excel is closed, there's no end to this stream
   };
 
   // this calls the wrapper to actually put it all together
   [ExcelFunction("Gets realtime values from server")]
   public static object GetArrayValues(string endpointAndQuery)
   {
     if (_timer == null)
     {
       _timer = new Timer(Callback); //, null, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(1));
       Start();
     }
 
     // a delegate that creates an observable over Event2Observable
     Func<IObservable<object[,]>> funcArray2 = () => Observable.Create<object[,]>(EventToObservable);
 
     //  pass that to Excel wrapper   
     var result = RxExcel.Observe("GetArrayValues", null, funcArray2);
     if (result.Equals(ExcelError.ExcelErrorNA)) return result;
 
     return XlCall.Excel(XlCall.xlUDF, "Resize", result);
 
   
   }

[Iron Mask]

Hi Govert,

I thank you for you help; I find that your Excel-DNA is amazing. I just have to learn and understand the various ways of if doing things with it.

So far, I managed to make the ShowResult work.

But in a bit funky way. Instead of using a Excel Reference of a cell, I assigned a random Range Name to the cell below the UDF cell

and Show the Results of dynamic table right there essentially row below my function call =GetValues(B2). In B2 I provide the "host:port" and the "query" info in one string.

Excel.Range["SomeRandomRangeName"].Resize[rows, columns] = matrix;

I believe this should be okay; because in my situation, I just need real time data without any formula.

I also want to try your second suggestion when I get a chance.

Thanks so much for your help.

Iron

On Thursday, March 7, 2019 at 6:19:37 AM UTC-5, Iron Mask wrote: