Asynchronous functions that calls synchronous function
Eduardo Amaral
[ExcelFunction(HelpTopic = "https://github.com/edugca/xlRcode")]
public static object XLRCODE_RTD(string commands, params object[] paramsList)
{
// Check whether function wizard is open
if (ExcelDnaUtil.IsInFunctionWizard()) return "# Function wizard is open";
string environment = "XLRCODE_TEMP_" + ((Microsoft.Office.Interop.Excel.Range)xlApp.Caller).AddressLocal.Replace("$", "_");
var parameters = new object[] { environment, commands, paramsList };
var result = ExcelAsyncUtil.Run("XLRCODE_RTD", parameters, delegate
{
return xlRcode.MyFunctions.XLRCODE_ENV(environment, commands, paramsList);
});
if (Equals(result, ExcelError.ExcelErrorNA))
{
return "# WAIT";
}
// Clear environment
_engine.Evaluate("rm(" + environment + ")");
return result;
}
Govert van Drimmelen
Hi Eduardo,
I suggest using the C APi to get the caller information, rather than Application.Caller, which is not reliable in this context.
var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
// get the caller local address for your environment name somehow – I’ve forgotten now, some XlCall. options.
// note that caller might sometimes be null (e.g. if function called from VBA or COM)
var environment = …..
// use the caller object directly in your parameters array
var parameters = new object[] { caller, commands, paramsList };
ExcelAsyncUtil.Run will run the delegates on a ThreadPool thread. If it’s OK to call XLRCODE_ENV from different threads, but must just ensure that the calls are done one at a time, then just adding a lock will be the simplest plan. The disadvantage is that you are blocking one ThreadPool thread for every outstanding call, so that won’t scale well. But it might be a good start to test things.
Somewhere in your class:
static object lockObject = new object[];
Then
var result = ExcelAsyncUtil.Run("XLRCODE_RTD", parameters, delegate
{
lock(lockObject)
{
return xlRcode.MyFunctions.XLRCODE_ENV(environment, commands, paramsList);
}
});
The more sophisticated and scalable plan would be to
- add a TaskScheduler class that runs Tasks on a single thread (Google can find this code)
- make a Task<T> returning method that calls XLRCODE_ENV, using the single-thread TaskScheduler (you might need two Task<T> methods, one for the real call, one for the version of Task.Run that uses the particular scheduler
- Change from ExcelAsyncUtil.Run to AsyncTaskUtil.RunTask in the UDF wrapper
I’d suggest start by trying the lock first. Then think about the more complicated plan.
-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/a577e54c-6847-4f03-a3f7-fe0fb0b669b3n%40googlegroups.com.
Govert van Drimmelen
ChatGPT offered this implementation for the TaskScheduler approach:
private TaskScheduler customScheduler;
private TaskFactory customTaskFactory;
private void XLRCODE_ENV(string environment, string commands, object[] paramList)
{
// implementation of the XLRCODE_ENV function
}
private void XLRCODE_ENV_CALL(string environment, string commands, object[] paramList)
{
customTaskFactory.StartNew(() => XLRCODE_ENV(environment, commands, paramList), CancellationToken.None, TaskCreationOptions.None, customScheduler);
}
public CustomTaskScheduler()
{
customScheduler = new LimitedConcurrencyLevelTaskScheduler(1);
customTaskFactory = new TaskFactory(customScheduler);
}
private sealed class LimitedConcurrencyLevelTaskScheduler : TaskScheduler
{
private readonly int maxDegreeOfParallelism;
private readonly ConcurrentQueue<Task> tasks = new ConcurrentQueue<Task>();
public LimitedConcurrencyLevelTaskScheduler(int maxDegreeOfParallelism)
{
if (maxDegreeOfParallelism < 1) throw new ArgumentOutOfRangeException("maxDegreeOfParallelism");
this.maxDegreeOfParallelism = maxDegreeOfParallelism;
}
protected override IEnumerable<Task> GetScheduledTasks()
{
return tasks;
}
protected override void QueueTask(Task task)
{
tasks.Enqueue(task);
NotifyThreadPoolOfPendingWork();
}
protected override bool TryExecuteTaskInline(Task task, bool taskWasPreviouslyQueued)
{
return false;
}
private void NotifyThreadPoolOfPendingWork()
{
ThreadPool.UnsafeQueueUserWorkItem(ExecuteTask, null);
}
private void ExecuteTask(object state)
{
Task task;
while (tasks.Count > 0 && maxDegreeOfParallelism > 0)
{
if (tasks.TryDequeue(out task))
{
maxDegreeOfParallelism--;
TryExecuteTask(task);
maxDegreeOfParallelism++;
}
}
}
}
I suppose you might need to wrap in a class, make some bits static, and figure out how to initialize. But at least that shows the main idea.
-Govert