Return DataTable
Hagai David
Govert van Drimmelen
Hagai David
Govert van Drimmelen
Hi Hagai,
Does your version of Excel support Dynamic Arrays?
If it does not (e.g. Excel 2019 or earlier) then you cannot safely return arrays from an RTD Server.
You also cannot mix the ArrayResizer with an RTD function.
You can optimize the implementation of the RTD server to do some kind of batching, but your RTD topics should return simple values only.
Also, when you are implementing the RTD server (so you are writing the RTD code like ConnectData) you have to follow the RTD specification exactly.
If your version Excel does support Dynamic Arrays, your RTD server should still return only a simple value for each topic, but you can safely make wrapper functions that return arrays.
I know that might not answer all your questions, but implementing an RTD server from scratch as is not so easy as there is very little documentation (besides what I have sent you in previous discussions).
-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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/5f559b17-5350-4298-932b-84e5848a091fn%40googlegroups.com.
Hagai David
Hagai David
Govert van Drimmelen
Hi Hagai,
I would do something like the code below, which uses an internal object cache and returning a handle to the cell.
There are two UDF functions:
- afFetchUrl – this is the async function that retrieves the information from the back-end. The result is stored internally, and a handle returned to the calling cell.
- afGetSubstring – this is an example of a function that takes the handle and does some computation or extracts a part of it.
The access function (here afGetString) could return an array.
Since you do not have Dynamic Arrays available, you could use the ‘ArrayResizer’ hack to expand the calling array formula if required – some code here: https://groups.google.com/g/exceldna/c/WK6RfD5cym0/m/KaoZR6KPAwAJ
-Govert
using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Threading.Tasks;
using ExcelDna.Integration;
namespace AsyncFetch
{
public static class Functions
{
static HttpClient _client = new HttpClient();
// This UDF will do some async work and when done will return a 'handle' as its result
public static object afFetchUrl(string url)
{
var handleType = "FetchResult"; // Will be the name for our handles
var args = new object[] { url };
return HandleManager.GetHandle(handleType, args, async (args) =>
{
// Here is the async work that will return the target object referred to by the handle
return await _client.GetStringAsync((string)args[0]);
});
}
// This UDF can take the 'handle' as input, retrieve the the target object and use it to return some result
public static object afGetSubstring(string handle, int start, int length)
{
if (HandleManager.TryGetObject(handle, out var response))
{
return ((string)response).Substring(start, length);
}
return ExcelError.ExcelErrorValue;
}
}
class HandleManager
{
static int _handleIndex;
static readonly Dictionary<string, HandleInfo> _objects = new();
// Tries to get an existing handle for the given object type and parameters.
// If there is no existing handle, creates a new handle with the target provided by evaluating the delegate 'getTarget'
// (with the given object type and parameters).
public static object GetHandle(string handleType, object[] args, Func<object[], Task<object>> getTargetAsync)
{
return ExcelAsyncUtil.Observe(handleType, args, () =>
{
var handle = $"{handleType}:{_handleIndex++}";
var handleInfo = new HandleInfo(handle, getTargetAsync(args));
_objects.Add(handle, handleInfo);
return handleInfo;
});
}
public static bool TryGetObject(string handle, out object value)
{
if (_objects.TryGetValue(handle, out var handleInfo))
{
value = handleInfo.Target;
return true;
}
value = null;
return false;
}
public static void Remove(HandleInfo handleInfo)
{
if (TryGetObject(handleInfo.Handle, out var value))
{
_objects.Remove(handleInfo.Handle);
if (value is IDisposable disp)
{
disp.Dispose();
}
}
}
}
class HandleInfo : IExcelObservable, IDisposable
{
public readonly string Handle;
public object Target;
// Set internally when hooked up to Excel
public IExcelObserver Observer;
public HandleInfo(string handle, Task<object> getTarget)
{
Handle = handle;
getTarget.ContinueWith(t =>
{
if (t.IsCompletedSuccessfully)
{
Target = t.Result;
if (Observer != null)
Observer.OnNext(Handle);
}
else
{
// Not sure what to do here
Observer.OnNext(ExcelError.ExcelErrorValue);
Observer.OnCompleted();
}
});
}
// This call is made (once) from Excel to subscribe to the topic.
public IDisposable Subscribe(IExcelObserver observer)
{
// We know this will only be called once, so we take some adventurous shortcuts (like returning 'this')
Observer = observer;
if (Target == null)
Observer.OnNext(ExcelError.ExcelErrorGettingData);
else
Observer.OnNext(Handle);
return this;
}
public void Dispose()
{
HandleManager.Remove(this); // Called when last instance of this topic is removed from the current session
}
}
}
Hagai David
Kay
https://groups.google.com/g/exceldna/c/r0aQG-tPYFc/m/NlQNhjtFBQAJ
https://groups.google.com/g/exceldna/c/gYL6rhj2psU/m/q7O37DqLBQAJ
//using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Concurrent;
using System.Runtime.InteropServices;
using System.Threading;
using System.Threading.Tasks;
namespace Sample
{
[ComVisible(true)]
public class Rtm : IRtdServer
{
private IRTDUpdateEvent callback;
private CancellationTokenSource cts;
private string d1 = "d1";
private string[] d2 = new string[] { "d2.1", "d2.2" };
private string[,] d3 = new string[,] { { "d3.1", "d3.2" } , { "d3.4", "d3.4" } };
private ConcurrentDictionary<int, string> topics = new ConcurrentDictionary<int, string>();
public int ServerStart(IRTDUpdateEvent CallbackObject)
{
callback = CallbackObject;
cts = new CancellationTokenSource();
Task.Run(async () => {
while (!cts.IsCancellationRequested)
{
await Task.Delay(1000, cts.Token);
if (cts.IsCancellationRequested) return;
var nows = " " + DateTime.Now.ToString("O");
d1 = "d1.1" + nows;
d2 = new string[] { "d2.1" + nows, "d2.2" + nows };
d3 = new string[,] { { "d3.1" + nows, "d3.2" + nows }, { "d3.4" + nows, "d3.4" + nows } };
callback.UpdateNotify();
}
}).ContinueWith(t => {
//if (t.IsFaulted) logger.LogError(t.Exception, "server exception");
//else if (t.IsCanceled) logger.LogWarning("server task cancelled");
});
return 1;
}
public object ConnectData(int topicId, ref Array pars, ref bool newValues)
{
newValues = true;
if (pars.Length == 0) return null;
var sourceNameObj = pars.GetValue(0);
if (!(sourceNameObj is string sourceName)) return null;
if (sourceName == "D1" || sourceName == "D2" || sourceName == "D3")
{
topics[topicId] = sourceName;
return "connect" + sourceName;
}
return null;
}
public Array RefreshData(ref int topicCount)
{
topicCount = topics.Count;
object[,] result = new object[2, topicCount];
int i = 0;
foreach (var kv in topics)
{
object val = "fckp";
switch (kv.Value)
{
case "D1":
val = d1;
break;
case "D2":
val = d2;
break;
case "D3":
val = d3;
break;
}
result[0, i] = kv.Key;
result[1, i] = val;
i++;
}
return result;
}
public void DisconnectData(int topicID)
{
topics.TryRemove(topicID, out _);
}
public int Heartbeat() => 1;
public void ServerTerminate()
{
cts.Cancel();
cts.Dispose();
callback = null;
}
}
public static class Functions
{
//[ExcelFunction(IsVolatile = false, IsThreadSafe = true)]
public static object Doneone()
{
return ExcelDna.Integration.XlCall.RTD("sample.rtm", null, "D1");
}
//[ExcelFunction(IsVolatile = false, IsThreadSafe = true)]
public static object Dtwo()
{
return ExcelDna.Integration.XlCall.RTD("sample.rtm", null, "D2");
}
//[ExcelFunction(IsVolatile = false, IsThreadSafe = true)]
public static object Dthree()
{
return ExcelDna.Integration.XlCall.RTD("sample.rtm", null, "D3");
}
}
}
Govert van Drimmelen
Excel RTD function cannot return an array (archive.org)
You can make a wrapper UDF that takes the scalar value returned from the RTD call, and based on that return an array.
One approach is to return serialized information in a string from the RTD call, the parse or unpack the string in your wrapper UDF. Note that the RTD strings are limited to 255 characters though.
Another approach is to keep some internal storage in memory, and set the RTD value to the key of your store. Then the wrapper UDF can take the key, and return the value from the store.
In Excel-DNA I implement the second mechanism for the async UDF and streaming IObservable support. This is built on top of an ExcelRtdServer which uses a Dictionary and Guid keys to do this. So your IExcelObservable can pass through large array values when called with ExcelAsyncUtil.Observe. In versions of Excel which support dynamic arrays, this works great.
In older Excel versions (2019 and earlier) which do not have Dynamic Array support, there are some memory leaks when using this pattern with CSE arrays.
So in that case the recommendation is to use an extra cell to store the key that comes from the RTD topic, and then expand the key with a different function called from different cells. Note that the example from the Microsoft KB article above where the RTD and unpacking functions are nested in a single cell is an example of unsafe usage that you should avoid.
-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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/aa5c1313-b5c2-4a41-b3ce-7964724618e8n%40googlegroups.com.