实现可以异步调用Web方法的Excel UDF
最初，我实现了一个可以同步调用 Web 方法的 UDF，但是遇到了很多麻烦，特别是当我尝试请求大量数据时 -简而言之，Excel 将挂起并冻结，直到检索到所有数据，这严重损害了用户体验。
我猜同步使用 Web 服务才是真正的杀手。所以我想知道是否有什么方法可以异步做到这一点？有人能给我一些指点吗？有什么工具或平台是更优选的？ （我个人还是更喜欢用C#）
Initially, I implemented an UDF that would call web methods Synchronously, but had quite a lot of troubles with that, especially when I tried to request huge amount of data -- in short, Excel would hang and freeze until all data was retrieved, which severely jeopardizes the user experience.
I am guessing consuming web service synchronously is the real killer here. So I wonder if there is any way that I can do it Asynchronously? Can anyone give me some pointers? Any tools or platform is more preferred? (Personally I still prefer using C#)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
RTD 会起作用。您必须实现 IRtdServer 接口。当加载项启动时，Excel 会为您提供一个函数指针。对于每个单元格，您将获得一个“excel id”和一个参数列表。发送您的异步网络请求。当响应到达时，您调用 Excel 提供的 notfiy 函数。当 Excel 准备就绪时，它将调用您的 GetData 方法来实际获取数据。
请参阅如何使用 RtdServer 在 C# 中创建实时 Excel 自动化插件？ 有关如何在 C# 中执行此操作的示例。
一旦您了解了 RTD 的工作原理，Excel-DNA 就可以很好地抽象出 COM 管道……强烈推荐。
RTD would work. You have to implement the IRtdServer interface. When your add-in is started, Excel gives you a function pointer. For each cell you will be given an "excel id" and a list of arguments. Send your async web request. When the response arrives, you call the notfiy function that Excel gave you. When Excel is ready it will call your GetData method to actually get the data.
See How do I create a real-time Excel automation add-in in C# using RtdServer? for an example of how to do this in C#.
Once you understand how RTD works, Excel-DNA does a nice job of abstracting away the COM plumbing...highly recommended.
我没有该技术的经验，但似乎 Excel RTD（实时数据）可能是一种方法。查看与 ExcelDNA 相关的此帖子，其中概述了一种方法。希望这有帮助！
I have no experience with the technology, but it seems that Excel RTD (Real Time Data) might be an approach. Check out this thread related to ExcelDNA which outlines an approach. Hope this helps!
我不使用 C# 和 Excel，只使用 VBA，但我想也存在类似的限制。通过 udf 远程调用某些内容（特别是当您经常这样做时）的问题是工作簿开始变得无响应，并且您无法控制计算的发生方式。但是，即使您能够弄清楚如何异步运行 udf，它也可能会扰乱 Excel 对如何计算工作簿以处理单元格依赖性的内部跟踪。
服务器上正在进行哪些计算？您能否将它们移至外接程序中，从而提高 UDF 的性能？
如果您做不到这一点，那么您可以尝试事件驱动的方法（例如，使用前面提到的工作表更改事件），但您仍然面临可能需要确保整个工作表最终完全计算的困难，并且这样做可能会带来更多的工作量而不是其价值。还不要忘记，即使异步运行，操作系统一次可以打开的连接数量也是有限的，因此您不可能同时发送数十个单元的调用......
I don't use c# with Excel, just VBA, but I imagine similar constraints apply. The problem with calling something remote via a udf (particularly when you're doing that a lot) is the workbook starts getting unresponsive, and you don't have any control over how calculation occurs. However, even if you could figure out how to run your udf asynchronously, it would likely disrupt Excel's internal tracking of how to calculate the workbook to take care of cell dependencies.
What calculations are being done on the server? Could you just move them into an add-in and so improve the performance of the UDF?
If you can't do that, then you could try an event-driven approach (eg. using worksheet change event as mentioned before), but you still have the difficulty that you might need to ensure the whole sheet ends up fully calculated, and doing that might be more work than it's worth. Don't forget also that even when running asynchronously there's a limit to how many connections your OS can open in one go, so it's not like you could send off the calls for dozens of cells all at once...
提供了一个带有示例的出色教程
An excellent tutorial with examples is available here.