浅谈Excel开发：六 Excel 异步自定义函数

时间：2021-02-26 02:02:59

上文介绍了Excel中的自定义函数(UDF )，它极大地扩展了Excel插件的功能，使得我们可以将业务逻辑以Excel函数的形式表示，并可以根据这些细粒度的自定义函数，构建各种复杂的分析报表。

普通的UDF自定义函数的基本执行逻辑是，Excel接受用户输入的函数表达式，然后通过UDF函数的处理逻辑进行处理，在处理过程中，Excel 的UI界面会一直等待函数体执行完成之后更新单元格数据。和大多数同步应用一样，同步的UDF函数会阻塞Excel UI线程，并且不方便动态扩展计算能力，在处理逻辑比较复杂、进行耗时的计算逻辑的时候，会造成较差的用户体验。所以我们需要开发异步的UDF函数。

一 问题的提出

通常，当用户在Excel中输入自定义函数的时候，我们希望实现以下表现：

  1. 开启另外一根线程或者在线程池中处理函数计算逻辑(不同于Excel UI线程)。
  2. 同时立即返回“Calculating”,“Fetching”或“Loading”等值提示用户正在计算。
  3. 待计算完成之后，通知Excel重新计算该单元格。
  4. 返回真正的计算结果。

以上步骤中，难点在于第三步，一般的函数在第二步返回值之后，整个过程就结束了。由于函数计算不在Excel主线程中，而刷新Excel单元格重新计算时要在Excel 主线程中进行，并且要刷新哪一个单元格还需要指明，而且在异常处理上比较麻烦。

在Excel 2010中，提供了直接编写异步UDF的能力，不过只有在使用C/C++开发的XLL Addin中才能使用，并且不向下兼容。要编写适用于大多数版本的Excel(03及以上版本)必须使用Excel已经提供的编程机制。我们可以利用在上文介绍的Excel RTD函数来解决以上问题，Excel RTD机制是Excel 2002就引入的，因此向上兼容性良好。使用Excel RTD来实现异步UDF函数的基本原理是，当用户在单元格输入函数时：

  1. 将函数以RTD函数请求，记录下TopicID，以及请求的表达式及参数，并返回“Calculating”提示用户正在处理
  2. 开辟另外线程处理该TopicID对应的Excel请求，并计算出结果，保存。
  3. 调用UpdateNotify方式，请求Excel重新计算单元，在重新计算的单元格中，根据TopicID，以及计算的结果值，返回给Excel
  4. Excel根据返回的计算结果值，刷新单元格。

根据以上分析，为Excel编写异步UDF函数的可用的解决方案为:

  • 在Excel 2010及以上版本，可以利用C/C++调用Excel API，开发XLL类型插件实现。
  • 在Excel2002 及以上版本，可以利用Excel RTD函数，来实现
  • 通过第三方类库，如Excel-DNA中引入的ReactiveExtension来实现。

考虑到Excel个版本最大限度的兼容性以及本系列文章主要关注.NET 下的Excel插件开发，所以将重点介绍第2种方法。

二 如何使用RTD实现异步UDF

正如前文所述，RTD函数主要是用来作为实时数据更新来使用的，但是我们可以利用RTD函数的这种特殊的Push-Pull机制来开发异步的UDF函数。异步UDF函数的主要实现框架如下图：

浅谈Excel开发：六 Excel 异步自定义函数

基于RTD的异步UDF函数的实现大致流程如上：

  1. 用户输入UDF函数，在VBA函数层面上，将UDF函数的函数名，及参数作为RTD函数的参数数组，在内部发起RTD函数调用。
  2. 在RTD函数的ConnectData方法中，将该次请求Excel分配的TopicID，以及请求的参数，包括函数名，参数拼接成Formula的样式，作为Key保存到一个全局的Dictionary中，Value为一个实体类，该实体类记录了此次的TopicID，以及该Key的请求返回的值Result(Object类型的)以及其他一些字段。
  3. ConnectData返回Loading，或者其他提示符，在单元格中显示，提示正在后台运算。
  4. 同时，另开一个线程对全局的请求Dictionary里面的请求进行处理，这个过程涉及到同步的处理，因为有可能在处理请求的时候，又有新的请求添加进来。
  5. 取出key，解析方法名及参数，使用反射或者一些高效率的方法获取该请求对应的值，存储到key对应的Value的实体的Result字段中。
  6. 同时调用RTD的UpdateNotify方法，通知Excel有数据要更新。
  7. Excel调用RTD的RefreshData方法，然后将全局请求的Dictionary中有结果值的数据项及TopicID，放到二维数组中，并返回。
  8. Excel单元格显示为该请求实际返回的结果值。

三、实现

下面就按照以上的逻辑编写一个简单的异步UDF的实现。

1. 实现UDF集中处理实体类

在最开始之前，我们要定义一个基于Dictionary自定义一个RequestDictionary实体类，用于存储所有的请求以及请求结果，该类继承自Dictionary<String, Request>并实现了IDictionary<string, Request>方法。

internal sealed class RequestsDictionary : Dictionary<String, Request>, IDictionary<string, Request>
{

}

在该类中，我们定义了一个枚举型的状态变量，分为Idle，Processing，Waiting三个状态，并定义了一个一步集合AsyncList，用来保存与处理的请求集合。

private AsyncList<Request> processRequestCollection;
private int state;
public int State { get { return state; }

然后，我们的实例内部，对pocessRequestCollection进行处理，处理完成之后，回调 RequestProcessCompletedHandle事件。

public event EventHandler<ProcessCompletedArgs> RequestProcessCompletedHandle;

我们重写了该类的Add方法，当在RTD中往该实体类中添加请求时：

public new void Add(string key, Request value)
{
    if (!base.ContainsKey(key))
    {
        base[key] = value;
        if (Interlocked.Exchange(ref this.state, (int)ProcessState.Waiting) == (int)ProcessState.Idle)
        {
            this.processRequestCollection.Add(value);
            OnRequestAdded(null);
        }
        else
        {
            Interlocked.Exchange(ref this.state, (int)ProcessState.Waiting);
            this.processRequestCollection.Add(value);
        }
    }
}

如果当前状态属于空闲，则将请求加载到待处理集合中，并将状态改为等待，并发起请求处理命令。后面如果有请求过来了，直接添加到待处理集合中。而不再次发起处理请求，只有当前状态变为idle了才会再次对集合中的待处理命令进行处理。

private void OnRequestAdded(Request[] formulas)
{
    RequestProcessor processor = new RequestProcessor(realProcessMethod);
    string[] refeshedFormulas;
    processor.BeginInvoke(formulas, out refeshedFormulas, new AsyncCallback(RequestProcessCompletedCallback), processor);
}

在OnRequestAdd方法中，我们使用异步代理，发起了一个真正的对请求处理的方法realProcessMethod方法。该方法的实现如下：

bool realProcessMethod(Request[] request, out string[] refeshedIds)
{
    if (request == null)
    {
        Thread.Sleep(10);
        refeshedIds = null;
        return true;
    }
    else
    {
        try
        {
            List<string> requestList = new List<string>(request.Length);
            refeshedIds = new string[request.Length];
            for (int i = 0; i < request.Length; i++)
            {
                requestList.Add(request[i].Formula);
                refeshedIds[i] = request[i].Formula;
            }
            //GetValues method get the process the real request;
            Dictionary<string, object> dictionaryWithValues = GetValues(requestList);
            Request requestInstance;
            foreach (KeyValuePair<string, Object> item in dictionaryWithValues)
            {
                requestInstance = this[item.Key];
                requestInstance.Result = item.Value;
                requestInstance.InCache = true;
            }
            return true;
        }
        catch
        {
            refeshedIds = null;
            return false;
        }
    }
}

该方法中，我们所有请求的ID放到refreshId中，当计算完成之后，我们就可以通知Excel仅刷新这些refreshId的数据了。方法中，我们真正处理请求的是在GetValue方法中，该方法接受所有请求的表达式，然后返回以该表达式为key，结果值为value的Dictionary对象。这里为了演示，我们让每个方法请求睡眠1秒，以模拟耗时操作，并直接返回函数中的参数作为返回值。在真实的场景中，我们通常将requestList拿出来解析，解析出函数名词，函数参数，然后利用反射，或者是约定的处理方式对这些请求进行计算并求得结果，就拿前面我们看过的天气预报的例子来说，如果用户在Excel中输入 =YY_Weather_Condition(“Shanghai”, “2013-09-28”),那么我们的requestList中就有一个名为 =YY_Weather_Condition(“Shanghai”, “2013-09-28”)的字符串，解析第一部分，就知道是函数名称，括号里面的就是参数， 然后动态的去调用相应的方法，或者直接对该逻辑进行判断处理，一般滴，我们的函数都是约定好的，所以可以直接采用if的方式进行判断然后进行处理。

bool realProcessMethod(Request[] request, out string[] refeshedIds)
{
    if (request == null)
    {
        Thread.Sleep(10);
        refeshedIds = null;
        return true;
    }
    else
    {
        try
        {
            List<string> requestList = new List<string>(request.Length);
            refeshedIds = new string[request.Length];
            for (int i = 0; i < request.Length; i++)
            {
                requestList.Add(request[i].Formula);
                refeshedIds[i] = request[i].Formula;
            }
            //GetValues method get the process the real request;
            Dictionary<string, object> dictionaryWithValues = GetValues(requestList);
            Request requestInstance;
            foreach (KeyValuePair<string, Object> item in dictionaryWithValues)
            {
                requestInstance = this[item.Key];
                requestInstance.Result = item.Value;
                requestInstance.InCache = true;
            }
            return true;
        }
        catch
        {
            refeshedIds = null;
            return false;
        }
    }
}

本例中，我们对之前的天气函数进行改造，将其改造为异步的UDF，所以这里的GetValue方法我们去获取天气情况：

//Fake method
//获取天气情况
private Dictionary<string, object> GetValues(List<string> requestList)
{
    Dictionary<string, object> results = new Dictionary<string, object>();
    for (int i = 0; i < requestList.Count; i++)
    {
        string request = requestList[i];
        string functionName = request.Substring(0, request.LastIndexOf('('));
        string[] parameters = request.Substring(request.LastIndexOf('(') + 1, request.Length - request.LastIndexOf('(') - 2).Split(',');
        Object returnValue = "Paramters Error!";
        //Thread.Sleep(1000);
        if (functionName.Equals("YY_Weather_Condition") ||
            functionName.Equals("YY_Weather_Temperature") ||
            functionName.Equals("YY_Weather_WindSpeed"))
        {

            if (parameters.Length == 2)
            {
                string city = parameters[0];
                string date = parameters[1];
                returnValue = GetWeather(functionName, city, date);
            }
        }
        else //return parameters
        {
            returnValue = request.Substring(request.LastIndexOf(','));
        }
        results.Add(requestList[i], returnValue);
    }
    return results;
}

private Object GetWeather(string functionName, string city, string date)
{
    Object result = null;
    Weather weather = new Weather(city, Weather.TemperatureUnits.Celcius);
    switch (functionName)
    {
        case "YY_Weather_Condition":
            result = weather.Condition.Text;
            break;
        case "YY_Weather_Temperature":
            result = weather.Condition.Temperature;
            break;
        case "YY_Weather_WindSpeed":
            result = weather.Wind.Speed;
            break;
    }
    return result;
}

该方法执行完成之后，会调用RequestProcessCompletedCallback这个回调方法， 在该回调方法中：

void RequestProcessCompletedCallback(IAsyncResult asyncResult)
{
    RequestProcessor processor = (RequestProcessor)asyncResult.AsyncState;
    string[] refeshedFormulas;
    bool isCompleted = processor.EndInvoke(out refeshedFormulas, asyncResult);
    if (isCompleted)
    {
        if (Interlocked.Exchange(ref this.state, (int)ProcessState.Idle) == (int)ProcessState.Waiting)
        {
            Interlocked.Exchange(ref this.state, (int)ProcessState.Processing);
            OnRequestAdded(this.processRequestCollection.PopupRest());
        }
        else  // which means no entry added yet
        {
            this.processRequestCollection.Clear();
        }
        OnRequestCompleted(new ProcessCompletedArgs(refeshedFormulas));
    }
}

private void OnRequestCompleted(ProcessCompletedArgs args)
{
    if (this.RequestProcessCompletedHandle != null)
        this.RequestProcessCompletedHandle(this, args);
}

我们查看当前的状态， 如果当前处于等待状态，则将状态改为空闲表示我们的已经处理完了。 然后将后面添加的请求继续进行处理，否则，表示所有的请求都处理完了，清空本地的待处理的请求队列。最后调用回调方法，传入待刷新的单元格formulas通知Excel刷新相应的单元格。

该类是异步UDF 的核心处理逻辑， 因为要涉及到不停的处理所有添加的请求，对请求集合的处理，一边在添加，一边在读取处理，为了减轻加锁对系统性能造成的影响，方法中大量使用了无锁编程的技巧，这一点对于提升性能有很大帮助，详细使用方法在后面讲到性能优化的时候我会再写一篇文章详细讲解，当然了，在CLR Via C# 4th中对该技术有详细的介绍。为了实现该类能够对请求进行不停的处理，将之前的使用timer控件的定时检查，然后处理逻辑，转换成了效率更高的三状态处理机制，极大的提高了性能和响应性。

写完了我们的请求处理类之后，我们需要在RTD 函数中对接收的请求进行统一的处理。和第四篇文章介绍如何创建RTD函数一样，我们新建一个RTD的类，然后实现IRtdServer接口及相应的方法。

2. 对RTD 函数进行改造

第一部分写完处理函数之后，接下来需要编写RTD 函数了，将普通的函数请求添加到之前的处理类中来。如何创建RTD 函数这里就不多讲了，您可以直接看前面的文章，这里不再赘述。

[Guid("BB007B7A-2647-4A09-A230-0CD8A2BDD489")]
[ProgId("YYAsyncRTD.Func")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class YYAsyncRTD : IRtdServer
{
    private SynchronizationContext ExcelContext;
    private static RequestsDictionary Requests = new RequestsDictionary();
    private static IRTDUpdateEvent xlRTDUpdater;

    public int ServerStart(IRTDUpdateEvent CallbackObject)
    {
        this.ExcelContext = new SynchronizationContext();
        xlRTDUpdater = CallbackObject;
        Requests.RequestProcessCompletedHandle += new EventHandler<ProcessCompletedArgs>(Requests_RequestProcessCompletedHandle);
        return 1;
    }

    public void ServerTerminate()
    {
        Requests.Clear();
        xlRTDUpdater = null;
    }
}

首先我们定义了三个对象，RequestsDictionary类型的Requests对象用来存储和处理所有传进来的请求，IRTDUpdateEvent对象用来保存ServerStart中的CallBack对象，用于后面调用UpdateNotify方法。这里还定义了一个同步上下文环境，用来保存Excel主线程的上下文环境，因为我们再调用RTD的UpdateNotify方法时，必须在主线程上操作。

在ServerStart函数中，我们注册了RequestsDictionary对象的处理完成事件。在该回调方法中我们向Excel发出消息，提示调用UpdateNotify方法：

void Requests_RequestProcessCompletedHandle(object sender, ProcessCompletedArgs e)
{
    if (e.Formulas != null && e.Formulas.Length != 0)
    {
        ExcelContext.Send(delegate(object obj) { xlRTDUpdater.UpdateNotify(); }, null);
    }
}

一切初始化好之后，我们开始编写ConnectData方法：

public dynamic ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)
{
    string formula = GetFormula(Strings);
    Request tmpRequest;
    if (Requests.ContainsKey(formula))
    {
        tmpRequest = Requests[formula];
        tmpRequest.IsValid = true;
        if (tmpRequest.InCache)
        {
            return tmpRequest.Result.ToString();
        }
        else
        {
            return "Fetching...";
        }
    }
    else
    {
        Request requestObj = new Request();
        requestObj.Formula = formula;
        requestObj.TopicID = TopicID;
        requestObj.InCache = false;
        requestObj.IsValid = true;
        Requests.Add(formula, requestObj);
        return "Fetching...";
    }
}

在该方法中，我们对于每一个请求，新建了一个Request对象，并将该请求的TopicID,formula记下来，然后加入到处理集合中，然后返回Processing… 提示用户后台正在处理。

在Refresh方法中，我们遍历集合中所有已经处理完成了的请求，然后赋给二维数组返回供Excel刷新使用：

public Array RefreshData(ref int TopicCount)
{
    object[,] valueArray = new object[2, Requests.Count];

    int count = 0;
    foreach (Request item in Requests.Values)
    {
        if (item.IsValid && item.InCache)
        {
            valueArray[0, count] = item.TopicID;
            valueArray[1, count++] = item.Result;
        }
    }
    TopicCount = count;
    return valueArray;
}

在Refresh方法中，我们遍历集合中所有已经处理完成了的请求，然后赋给二维数组返回供Excel刷新使用。

现在我们的RTD 函数写完了，用户如果要查询天气，输入=YY_Weather_Condition(“Shanghai”, “2013-09-28”)，我们可以在VBA中发起响应的RTD 调用=RTD(“YYAsyncRTD.Func”,,“YY_Weather_Condition”,”2”,“Shanghai”,“ 2013-09-28”)，我们约定RTD中，第一个参数为函数名称，第二个参数为该函数的参数个数，后面的都是函数的具体参数。这样我们就可以在请求的逻辑处还原YY_Weather_Condition(“Shanghai”, “2013-09-28”)这一请求。这样我们的函数就是异步的UDF 函数了。

3. 实际效果

写完上面的实现之后，将该项目设置为面向com可见，然后编译整个类库，我们在该RTD中可以异步调用我们之前的天气函数了。比如我们可以在单元格中输入函数：

=RTD("YYAsyncRTD.Func",,"YY_Weather_Condition",2, "Shanghai",TODAY())

该函数首先返回Fetching通知Excel正在处理，然后待处理完成之后，将Shanghai今天的天气情况刷新返回出来。我录制了一个动画，效果如下:

浅谈Excel开发：六 Excel 异步自定义函数

可以看到，整个用户界面要比同步的UDF函数要好很多，而且在单元格Fetching的时候，Excel单元格还是能够响应其他的操作，比如选中其他单元格等等，Excel的UI界面一点儿没有阻塞。

四、实现

本文首先介绍了异步自定义函数相较于传统的同步UDF的具有良好扩展性，用户体验等优点，然后介绍了在Excel中实现异步UDF的步骤及解决方案，最后通过自定义Dictionary类，然后基于RTD函数实现了一个简单的异步UDF函数，并通过该异步UDF函数实现了对第四篇文章中的天气函数的调用。

本文所有代码点击此处下载，Excel 异步UDF函数编写较为复杂，希望本文对您了解及如何编写Excel异步UDF函数有所帮助。

相关文章