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.
最初，我实现了一个可以同步调用web方法的UDF，但是遇到了很多麻烦，特别是当我试图请求大量数据时 - 简而言之，Excel会挂起并冻结，直到检索到所有数据，严重危害用户体验。
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服务是真正的杀手。所以我想知道是否有任何方法可以异步进行？任何人都可以给我一些指示吗？更优选任何工具或平台？ （我个人还是喜欢使用C＃）
4 个解决方案
#1
5
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.
RTD会起作用。您必须实现IRtdServer接口。启动加载项后，Excel会为您提供一个函数指针。对于每个单元格，您将获得“excel id”和参数列表。发送异步Web请求。当响应到达时，您调用Excel给您的notfiy函数。当Excel准备就绪时，它将调用您的GetData方法来实际获取数据。
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#.
请参阅如何使用RtdServer在C＃中创建实时Excel自动化加载项？有关如何在C＃中执行此操作的示例。
Once you understand how RTD works, Excel-DNA does a nice job of abstracting away the COM plumbing...highly recommended.
一旦你理解了RTD如何工作，Excel-DNA就可以很好地抽象出COM管道......强烈推荐。
#2
1
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!
我没有这项技术的经验，但似乎Excel RTD（实时数据）可能是一种方法。查看与ExcelDNA相关的此主题，其中概述了一种方法。希望这可以帮助！
#3
0
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.
我不使用c＃和Excel，只使用VBA，但我想类似的约束适用。通过udf调用远程调用的问题（特别是当你做了很多）时，工作簿开始变得没有响应，并且你无法控制计算的发生方式。但是，即使您可以弄清楚如何异步运行udf，也可能会破坏Excel内部跟踪如何计算工作簿以处理单元依赖性。
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?
在服务器上进行了哪些计算？你能将它们移动到一个加载项中，从而提高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...
如果你不能这样做，那么你可以尝试一种事件驱动的方法（例如，使用前面提到的工作表更改事件），但是你仍然有困难，你可能需要确保整个工作表最终完全计算，并且这样做可能比它的价值更多。不要忘记，即使在异步运行时，操作系统可以一次打开多少连接也是一个限制，所以不像你可以同时发送几十个单元格的调用...
#1
5
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.
RTD会起作用。您必须实现IRtdServer接口。启动加载项后，Excel会为您提供一个函数指针。对于每个单元格，您将获得“excel id”和参数列表。发送异步Web请求。当响应到达时，您调用Excel给您的notfiy函数。当Excel准备就绪时，它将调用您的GetData方法来实际获取数据。
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#.
请参阅如何使用RtdServer在C＃中创建实时Excel自动化加载项？有关如何在C＃中执行此操作的示例。
Once you understand how RTD works, Excel-DNA does a nice job of abstracting away the COM plumbing...highly recommended.
一旦你理解了RTD如何工作，Excel-DNA就可以很好地抽象出COM管道......强烈推荐。
#2
1
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!
我没有这项技术的经验，但似乎Excel RTD（实时数据）可能是一种方法。查看与ExcelDNA相关的此主题，其中概述了一种方法。希望这可以帮助！
#3
0
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.
我不使用c＃和Excel，只使用VBA，但我想类似的约束适用。通过udf调用远程调用的问题（特别是当你做了很多）时，工作簿开始变得没有响应，并且你无法控制计算的发生方式。但是，即使您可以弄清楚如何异步运行udf，也可能会破坏Excel内部跟踪如何计算工作簿以处理单元依赖性。
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?
在服务器上进行了哪些计算？你能将它们移动到一个加载项中，从而提高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...
如果你不能这样做，那么你可以尝试一种事件驱动的方法（例如，使用前面提到的工作表更改事件），但是你仍然有困难，你可能需要确保整个工作表最终完全计算，并且这样做可能比它的价值更多。不要忘记，即使在异步运行时，操作系统可以一次打开多少连接也是一个限制，所以不像你可以同时发送几十个单元格的调用...