Excel 2010 asynchronous UDFs

Feb 16, 2011 at 4:09 PM

Hi,

Is it possible to write asynchronous UDFs for excel 2010 using excel-DNA?

I couldn't tell from the source or discussions whether this was definitely possible or not.

Thanks!

 

Coordinator
Feb 19, 2011 at 7:02 AM

Hi,

At the moment, there is no special support in Excel-DNA for asynchronous UDFs. But it is definitely possible - in fact users have been making async functions in various ways from the start of the Excel-DNA project.

However, the RTD support I added last year was put in mainly to improve support for these scenarios. Although there is some built-in support for async UDFs in Excel 2010, I would still start by doing it in terms of an RTD server, and then maybe make a special case for Excel 2010 later. Getting the implementation right will be about as much work, and an RTD-based implementation will work for all versions of Excel since Excel 2002. 

The basic idea for an RTD-based implementation is to create an RTD server that would have a topic for each outstanding call. When the async function is called, register the work with the RTD server, start the work on another thread, and 'return' the RTD function call. When the async call finishes, call UpdateNotify (with some care to get the threading right - maybe use the ExcelSynchronizationContext from the newsgroup). Then excel will call your function again - check if the work is done and return the result.

All of this can be implemented today with no changes to Excel-DNA. And it should be fast and scale well. Once we have this implemented in user code, I can look at how to make it a built-in part of Excel-DNA that is wired up automatically, maybe just with an [Async] attribute.

If you'd like to give this a try, I'm happy to help.

-Govert

Feb 22, 2011 at 6:01 PM

Hi Govert,

Thanks for the reply.  I managed to get an RTD server working along these lines using the examples.  Basically each function call is queued and processed by a threadpool: when completed, the results are passed back to the RTD server which then calls back to excel, and excel then requests the updates.

However one thing is working strangely.  Instead of updating the cells with the results of my calls, it appears as if excel just drops the results it gets from RefreshData, and instead calls the function afresh.  I don't think this is what is expected - have you any ideas as to why it might do this and/or can you confirm that's definitely NOT what it normally does?!

I've attached the source code for your use

using System;
using System.Collections.Generic;
using System.Threading;
using ExcelDna.Integration.Rtd;
using ExcelDna.Integration;

namespace ClassLibrary6
{

    /// <summary>
    /// The actual excel functions that we want to call
    /// </summary>
    public static class ExcelFunctions
    {
        public static int cnt = 1;

        public static object yada(string name)
        {
            // we are really slow
            Thread.Sleep(10000);
            return String.Format("fgdfgfdg! {0}", cnt++);
        }       
    }

    public delegate object QueuedExcelFunction();
    public delegate void RequestCompletionHandler(int topicId, object result);

    /// <summary>
    /// Call the actual excel functions, but asynchronously via the RTD
    /// </summary>
    public static class ExcelFunctionAsyncWrapper
    {
        private const string RTDFullName = "ClassLibrary6.StaticRtd";

        [ExcelFunction(Description="yada")]
        public static object yada(string name)
        {
            var req = new RequestInfo {method = () => ExcelFunctions.yada(name)};
            QueueHandler.addRequest(req);
            return XlCall.RTD(RTDFullName, null, req.identifier.ToString());
        }   
    }

    /// <summary>
    /// Information about the specific queued requests
    /// </summary>
    public class RequestInfo
    {
        public Guid identifier = Guid.NewGuid();

        public int topicId { get; set; }

        private QueuedExcelFunction _method;
        public QueuedExcelFunction method { set { _method = value; } }

        public bool completed { get; private set; }
        public object result { get; private set; }

        public void callQueuedFunction()
        {
            result = _method();
            completed = true;
        }
    }

    /// <summary>
    /// Simple threadpool that queues and runs requests, and passes results back to the RTD server
    /// </summary>
    public static class QueueHandler
    {
        // requests waiting
        private static readonly Queue<Guid?> QueuedRequests = new Queue<Guid?>();

        // requests in progress
        private static readonly Dictionary<Guid, RequestInfo> Requests = new Dictionary<Guid, RequestInfo>();

        public static void addRequest(RequestInfo req)
        {
            Requests.Add(req.identifier, req);
        }

        #region calledByExcelRTD

        public static void removeRtd(int topicId)
        {
            // noop - we only ever notify excel once about the output
            // so we just leave this
        }

        // we receive a topicId from the RTD server, so the request
        // can now be put onto the queue for processing.  otherwise we need
        // to deal with possibility that a request could be completed before
        // RTD has a callback
        public static void addRtd(Guid requestId, int topicId)
        {
            if (!Requests.ContainsKey(requestId))
                throw new Exception("Request not found");
            Requests[requestId].topicId = topicId;
            enqueue(requestId);
        }

        #endregion

        private static void enqueue(Guid? reqId)
        {
            lock (QueuedRequests)
            {
                QueuedRequests.Enqueue(reqId);
                Monitor.Pulse(QueuedRequests);
            }
        }

        #region QueueProcessingByThreadPool

        // start the next request, return the GUID of the request
        // this is used by the RTD server. null kills the thread
        public static void serviceNextRequest()
        {
            while (true)
            {
                Guid? reqId;
                lock (QueuedRequests)
                {
                    while (QueuedRequests.Count == 0) Monitor.Wait(QueuedRequests);
                    reqId = QueuedRequests.Dequeue();
                }

                if (reqId == null)
                    return;

                var req = Requests[(Guid) reqId];
                req.callQueuedFunction();
                requestCompleted(req);
            }
        }

        private static Thread[] _workers;
        private static int _maxThreads;
        public static void start(int maxThreads, RequestCompletionHandler requestQueueUpdated)
        {
            _maxThreads = maxThreads;
            onRequestCompleted += requestQueueUpdated;
            _workers = new Thread[_maxThreads];
            for (var i = 0; i < maxThreads; i++)
                (_workers[i] = new Thread(serviceNextRequest)).Start();
        }

        public static void shutdown()
        {
            for (var i = 0; i < _maxThreads; i++)
                enqueue(null);
            
            // wait for the threads to finish
            foreach (var worker in _workers)
                worker.Join();
        }

        // when a thread has serviced a request, it needs to cleanup and notify
        public static event RequestCompletionHandler onRequestCompleted;
        public static void requestCompleted(RequestInfo req)
        {
            Requests.Remove(req.identifier);
            if (onRequestCompleted != null)
            {
                onRequestCompleted(req.topicId, req.result);
            }
        }

        #endregion

    }

    /// <summary>
    /// RTD server for excel, processes requests using a queue
    /// Topics are GUIDs which point to queued requests set up separately
    /// </summary>
    public class StaticRtd : IRtdServer
    {
        private IRTDUpdateEvent _callback;
        private readonly List<Tuple<int, object>> _readyData = new List<Tuple<int, object>>();

        public object ConnectData(int topicId, ref Array strings, ref bool getNewValues)
        {
            if (strings.Length != 1)
            {
                throw new Exception("Expecting REQUESTINFO.IDENTIFIER");
            }
            var name = strings.GetValue(0) as string;
            if (string.IsNullOrEmpty(name))
            {
                throw new Exception("Expecting REQUESTINFO.IDENTIFIER");
            }
            Guid threadId;
            if (!Guid.TryParse(name, out threadId))
            {
                throw new Exception("REQUESTINFO.IDENTIFIER invalid");
            }

            // associate the RTD call with the request
            QueueHandler.addRtd(threadId, topicId);
            return "WAIT";
        }

        public void DisconnectData(int topicId)
        {
            // we only ever notify excel once about updated date,
            // when the async call returned
            QueueHandler.removeRtd(topicId);
        }

        public int ServerStart(IRTDUpdateEvent callbackObject)
        {
            QueueHandler.start(4, requestQueueUpdated);
            _callback = callbackObject;
            return 1;
        }

        public void ServerTerminate()
        {
            QueueHandler.shutdown();
        }

        public int Heartbeat()
        {
            return 1;
        }

        public Array RefreshData(ref int topicCount)
        {
            // topicIds and values
            object[,] res;
            lock (_readyData)
            {
                topicCount = _readyData.Count;
                res = new object[2,topicCount];
                for (var i = 0; i < topicCount; i++)
                {
                    var ob = _readyData[i];
                    res[0,i] = ob.Item1;
                    res[1,i] = ob.Item2;
                }
                _readyData.Clear();
            }
            return res;
        }

        private void requestQueueUpdated(int topicId, object result)
        {
            lock (_readyData)
            {
                _readyData.Add(new Tuple<int, object>(topicId, result));
            }
            _callback.UpdateNotify();
        }
    } 
}



Feb 22, 2011 at 6:03 PM

oh yes, i'm using office 2010

Coordinator
Feb 22, 2011 at 9:13 PM

Hi,

This looks very promising!

I think we should move the thread to the Excel-DNA Google group ...?

I'd expect Excel to call your function again. Support your function had said:  return XlCall.RTD(...) + "Something else";

I think you need to remember the function parameters and the async result, so that you return the async result value instead of calling XlCall.RTD again.

So the async yada looks like this:

object yada(string value)

if (hasResultFor(value))

return value;

else

// Set up async work and cal XlCall.RTD(...)

 

Once that works, we'll need to pay some attention to the UpdateNotify call. There are some indicators that this is much safer if called from the Excel thread which created the RTD server. For this you might need something like the ExcelSynchronizationContext that was discussed in the Google group on this thread: http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9

-Govert

 

Jan 23, 2012 at 3:44 PM

Hi Govert

This is the most complete example of RTD usage I have been able to find. So this indeed looks very promising. Do you know whether any progress has been made during the past 12 months? - I was not able to find anything in the ExcelDNA Google group.

My intention is to asynchronously return a result fetched from a Webservice to a UDF that resides in Excel cells of a worksheet.

Would you still recommend going this way?

All the best & thanks

Mario

Coordinator
Jan 23, 2012 at 3:59 PM

Hi Mario,

Yes - I still think this is the right approach (though I'd also like to support the Excel 2010 queued async function in future).

I don't have more than is discussed in this thread, but I should have some time to work on it in the next few weeks.

If you're interested and want to help/encourage me along, I suggest you contact me directly.

Regards,

Govert

Jan 30, 2012 at 9:21 PM

Hi Govert,

 

I am new to using Excel DNA and am trying to follow the sample mr_miles posted. When I try to run the code I get the WAIT msg and it just keeps looping ExcelFunctionAsyncWrapper.yada. Your reply described how to properly fix it but I cant quite seem to understand what you mean by checking if there is a value otherwise setup and call the RTD method. When I debut I can see the proper value being found in the RefreshData() method but after that it loops back to ExcelFunctionAsyncWrapper.yada.

Thanks in advance for any feedback you may have, thanks.

Coordinator
Jan 30, 2012 at 9:34 PM

Hi Joe,

What I meant in the comment is that you store somewhere (in some static variable or accessible data structure) the result of the function, so that you can directly return the result when Excel calls the function after the RefreshData, instead of making the RTD call again.

I've not really figured out async function myself, I'll be having a look at how to implement these soon, with the plan to add this as a sample or feature in a future version of Excel-DNA.

Regards,

Govert

Jan 31, 2012 at 1:56 PM

Govert,

Thanks for your prompt reply. I was able to get the code working by changing the ConnectData method as follows

private object[,] result;
public object ConnectData(int topicId, ref Array strings, ref bool getNewValues)
{
    if (result != null)
    {
        string foo = result[1, 0].ToString();
        result = null;
        return foo;
    }
    else
    {
         #region unchanged from before
         #endregion

        // associate the RTD call with the request
        QueueHandler.addRtd(threadId, topicId);
        return "WAIT";
    }
}

public Array RefreshData(ref int topicCount)
{
    #region unchanged from before
    #endregion
    result = res;
    return res;
}

My first question is, if this is a correct solution or not? My second concern was that despite now being able to see a result, the process doesn't seem to be multi-threaded like I would have imagined. I can clearly see the call to QueueHandler.start with 4 worker threads being spawned, all running serviceNextRequest; but when I try to run multiple cells of yada() I see them process 1 at a time, not 4 at a time. Is there something I am doing wrong?

 

Coordinator
Jan 31, 2012 at 2:25 PM

Hi,

I don't think you want to change the ConnectData - that's called by Excel when a new topic on the RTD server is called.

Rather in the ExcelFunctionAsyncWrapper.yada function iteself, you want to return the result directly if it is available.

-Govert

 

Feb 1, 2012 at 11:39 AM

Hi,

This worked for me pretty much as-is, I have an app in production that uses the approach.  Since writing the snippet and getting to production, a few things subtle came up in excel that might help you out, and made my approach very slightly different.

First of all, when you call XlCall.RTD, you're going back "into" excel, which then calls back out to the RTD server.  Whilst you're "in" excel, it examines the call to work out whether it has already mapped the request to a topicId - if it has done so, then it doesn't call the RTD server, just returns the last value it received for that topicId.  The main impact is that if you're function is non-deterministic (like the example) then you won't see the non-deterministic behaviour.  Also, in the example code I believe the item gets added to the queu but the XLCall.RTD probably returns immediately with a value.

Excel assumes that the RTD server will handle getting the new values, and informing it of changes - excel then updates all the cells that refer to that topicId.  If you use the same function in multiple cells, then excel also assumes it's taking care of ensuring you only get one call for new data, and that updates are propogated to all the cells.

Secondly, when you have an ExcelDNA function in a cell, that in turn calls XLCall.RTD, excel re-evaluates the cell value in response to receiving new data by calling your function again (it has no way to know that your function is just returning the RTD value, rather than doing something else).  There isn't a way to tell whether the RTD call will return a cached value or if you need to put a new request on the queue.  If your function returns a value directly and does not call RTD, then excel "disconnects" that topicId from the RTD.

This means that this bit 

"What I meant in the comment is that you store somewhere (in some static variable or accessible data structure) the result of the function, so that you can directly return the result when Excel calls the function after the RefreshData, instead of making the RTD call again."

is a bit unnecessary - excel will return the value immediately when you call RTD if it has received a value already.

Similarly, you shouldn't need to modify connect data to get a return value, as that's too late (excel will have returned if there is a return value).

For my purposes, I provided a toolbar button to force all the RTD values to be re-evaluated by excel.  It did this by pushing through "updated" values of NA, and queueing up new requests, and so accomplished a "refresh".

Hope that all makes sense.  I'll try to help with any other queries

Feb 1, 2012 at 1:48 PM
Edited Feb 1, 2012 at 1:52 PM

mr_miles,

Thanks for the thorough reply. I am able to run your sample, and I even see it hit RefreshData and build the correct array (see link below), but it never seems to refresh my excel spreadsheet. Any ideas why that would be happening?

http://i.imgur.com/HEUGe.png

Feb 1, 2012 at 3:12 PM

hmm. do you see excel call your function again after RefreshData?  When it retrieves the data via RefreshData, it's just updating it's internal values.  The actual cell value is updated when it calls your function again.

You could try changing the code so the RTD calls the function directly, rather than through the delegate?

Also, try a breakpoint on the DisconnectData method - perhaps excel is disconnecting the cell from the RTD updates?

I vaguely remember the UpdateNotify call not triggering excel to do anything when I started writing it, but not that it retrieved the values and just ignored them

Aug 11, 2014 at 7:37 AM
Hi mr-miles and others.

Your code is exactly what i need, i changed 2 or 3 things to adapt it to my project but there is one thing i do not understand :
on this line of code :
var req = new RequestInfo {method = () => ExcelFunctions.yada(name)};

My function is :
  public static object Histo(String lSheetName, int row, int colum, String name)//, DateTime _date, String _typedata)
        {
            SqlDataReader rd;
            SqlConnection _MaConn = new SqlConnection("ytiopm");

            _MaConn.Open();
            SqlCommand cd = new SqlCommand("psGetHistoBond", _MaConn);
            cd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = cd.Parameters.Add("@isin", SqlDbType.VarChar, 12);
            param.Value = name;
      

            dico = new Dictionary<int, string>();
       
            Excel.Application app = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;
            Excel.Worksheet xlSheet = (Excel.Worksheet)app.ActiveWorkbook.Worksheets[lSheetName];

                while (rd.Read())
                {
                    String tempres;
                    tempres = rd.GetDateTime(0).ToString("dd/MM/yy");
                    dico[n] = tempres;
                    n++;
                    
                }
             for l, c    //...... ( i have sthg which is okay there)
                 xlSheet.Cells[l, c + 1].FormulaLocal = dico[k];
            finally ...
         
My excel function is :
 [ExcelFunction(Description = "récupère l'historique")]
        public static void getHisto(String name)  //, DateTime _debut, String typedata)
        {
            Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;
            int l;
            int c;
            l = xlApp.ActiveCell.Row;
            c = xlApp.ActiveCell.Column;

            ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
            string lFullSheetName = (string)XlCall.Excel(XlCall.xlSheetNm, cell);
            int lIndex = lFullSheetName.IndexOf("]");
            string lSheetName = lFullSheetName.Substring(lIndex + 1);

            __var req = new RequestInfo { method = () => ExcelFunctions.Histo(lSheetName, l, c, name) }; 
            QueueHandler.addRequest(req);
            XlCall.RTD("StaticRtd", null, req.identifier.ToString());__
The other parts (rtd, queuehandler...) are the same as in mr miles' project (just above)

The problem is that my function is never called (XlCall does not seem to work there) i must be doing something wrong.

Thank you in advance for your help,

Best regards,

Paul