相关文章:
浅谈Excel开发(1) Excel开发概述
浅谈Excel开发(二) Excel 菜单系统
浅谈Excel开发(三) Excel 对象模型
上文介绍了Excel中的自定义函数(UDF ),它极大地扩展了Excel插件的功能,使得我们可以将业务逻 辑以Excel函数的形式表示,并可以根据这些细粒度的自定义函数,构建各种复杂的分析报表。
普通的UDF自定义函数的基本执行逻辑是,Excel接受用户输入的函数表达式,然后通过UDF函数的处理 逻辑进行处理,在处理过程中,Excel 的UI界面会一直等待函数体执行完成之后更新单元格数据。和大多 数同步应用一样,同步的UDF函数会阻塞Excel UI线程,并且不方便动态扩展计算能力,在处理逻辑比较 复杂、进行耗时的计算逻辑的时候,会造成较差的用户体验。所以我们需要开发异步的UDF函数。
一 问题的提出
通常,当用户在Excel中输入自定义函数的时候,我们希望实现以下表现:
开启另外一根线程或者在线程池中处理函数计算逻辑(不同于Excel UI线程)。
同时立即返回“Calculating”,“Fetching”或“Loading”等值提 示用户正在计算。
待计算完成之后,通知Excel重新计算该单元格。
返回真正的计算结果。
以上步骤中,难点在于第三步,一般的函数在第二步返回值之后,整个过程就结束了。由于函数计算 不在Excel主线程中,而刷新Excel单元格重新计算时要在Excel 主线程中进行,并且要刷新哪一个单元格 还需要指明,而且在异常处理上比较麻烦。
在Excel 2010中,提供了直接编写异步UDF的能力,不过只有在使用C/C++开发的XLL Addin中才能使用 ,并且不向下兼容。要编写适用于大多数版本的Excel(03及以上版本)必须使用Excel已经提供的编程机制 。我们可以利用在上文介绍的Excel RTD函数来解决以上问题,Excel RTD机制是Excel 2002就引入的,因 此向上兼容性良好。使用Excel RTD来实现异步UDF函数的基本原理是,当用户在单元格输入函数时:
将函数以RTD函数请求,记录下TopicID,以及请求的表达式及参数,并返回 “Calculating”提示用户正在处理
开辟另外线程处理该TopicID对应的Excel请求,并计算出结果,保存。
调用UpdateNotify方式,请求Excel重新计算单元,在重新计算的单元格中,根据TopicID,以及计算 的结果值,返回给Excel
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函数的主要实现框架如下图:
基于RTD的异步UDF函数的实现大致流程如上:
用户输入UDF函数,在VBA函数层面上,将UDF函数的函数名,及参数作为RTD函数的参数数组,在内部 发起RTD函数调用。
在RTD函数的ConnectData方法中,将该次请求Excel分配的TopicID,以及请求的参数,包括函数名, 参数拼接成Formula的样式,作为Key保存到一个全局的Dictionary中,Value为一个实体类,该实体类记 录了此次的TopicID,以及该Key的请求返回的值Result(Object类型的)以及其他一些字段。
ConnectData返回Loading,或者其他提示符,在单元格中显示,提示正在后台运算。
同时,另开一个线程对全局的请求Dictionary里面的请求进行处理,这个过程涉及到同步的处理,因 为有可能在处理请求的时候,又有新的请求添加进来。
取出key,解析方法名及参数,使用反射或者一些高效率的方法获取该请求对应的值,存储到key对应 的Value的实体的Result字段中。
同时调用RTD的UpdateNotify方法,通知Excel有数据要更新。
Excel调用RTD的RefreshData方法,然后将全局请求的Dictionary中有结果值的数据项及TopicID,放 到二维数组中,并返回。
Excel单元格显示为该请求实际返回的结果值。