We are developing a kind of stock lookup UDF in c# for Excel. Our UDF ends up calling a web service, which can be very slow if a sheet has, say 1000 calls to our web service.
我们正在c#中为Excel开发一种股票查询UDF。我们的UDF最终会调用一个Web服务,如果一个工作表有1000个调用我们的Web服务,这可能会非常慢。
I noticed that Excel 2010 has introduced Asynchronous UDFs to help with this http://msdn.microsoft.com/en-us/library/ff955606.aspx#odc_office14_ta_WhatsNewforOffice2010Developers_Excel2010
我注意到Excel 2010引入了异步UDF来帮助解决这个问题http://msdn.microsoft.com/en-us/library/ff955606.aspx#odc_office14_ta_WhatsNewforOffice2010Developers_Excel2010
Has anyone tried this and have any c# examples, or have any other tips on improving the performance of web service type UDFs in Excel?
有没有人尝试过这个并且有任何c#示例,或者有任何其他提示来改进Excel中的Web服务类型UDF的性能?
2 个解决方案
#1
0
Whenever I've had this sort of issue, I've always decided to come up with a different approach to solve the problem.
每当我遇到这种问题时,我总是决定采用不同的方法来解决问题。
In this case, if there's any way you can batch up the calls and just make one webservice call then that would be the best thing as you'd only have one round trip instead of 1000 or whatever.
在这种情况下,如果有任何方法可以批量调用并只进行一次webservice调用,那么这将是最好的事情,因为你只有一次往返而不是1000或其他什么。
In other words, can you iterate through the 1000 cells and build up a (fairly big) request to send to the webservice and retrieve all the info at once? Then unpack it all when it returns.
换句话说,你可以遍历1000个单元格并建立一个(相当大的)请求发送到Web服务并立即检索所有信息吗?然后在它返回时将其全部解压缩。
If it's your own webservice (which I'm hoping it is, since you said our webservice), then you should be able to modify it to do this. If you are calling an external webservice with no batch functionality then I'm afraid I'm not sure how best to approach it, other than to fire of numerous asynchronous UDFs on separate threads.
如果它是你自己的webservice(我希望它是,因为你说我们的webservice),那么你应该能够修改它来做到这一点。如果您正在调用没有批处理功能的外部Web服务,那么我担心我不确定如何最好地处理它,除了在单独的线程上激发大量异步UDF。
#2
2
You can use Excel RTD to provide asynchronous data retrieval. RTD allows you to push updates to Excel on demand, which means you can return quickly from the UDF call and asynchronously retrieve the data in the background. When the request is complete, you then notify Excel that updated values are available. You can also quite easily batch multiple UDF calls into a single web service request by simply queuing the RTD topics as they arrive and periodically forming a batched request.
您可以使用Excel RTD提供异步数据检索。 RTD允许您按需将更新推送到Excel,这意味着您可以从UDF调用快速返回并在后台异步检索数据。请求完成后,您通知Excel已更新的值可用。您还可以轻松地将多个UDF调用批量处理为单个Web服务请求,只需在RTD主题到达时对其进行排队并定期形成批量请求。
Here is a link that describes how to create a basic RTD server in C#. To make the solution more user friendly, you can wrap the RTD worksheet function call in a UDF.
这是一个描述如何在C#中创建基本RTD服务器的链接。为了使解决方案更加用户友好,您可以将RTD工作表函数调用包装在UDF中。
#1
0
Whenever I've had this sort of issue, I've always decided to come up with a different approach to solve the problem.
每当我遇到这种问题时,我总是决定采用不同的方法来解决问题。
In this case, if there's any way you can batch up the calls and just make one webservice call then that would be the best thing as you'd only have one round trip instead of 1000 or whatever.
在这种情况下,如果有任何方法可以批量调用并只进行一次webservice调用,那么这将是最好的事情,因为你只有一次往返而不是1000或其他什么。
In other words, can you iterate through the 1000 cells and build up a (fairly big) request to send to the webservice and retrieve all the info at once? Then unpack it all when it returns.
换句话说,你可以遍历1000个单元格并建立一个(相当大的)请求发送到Web服务并立即检索所有信息吗?然后在它返回时将其全部解压缩。
If it's your own webservice (which I'm hoping it is, since you said our webservice), then you should be able to modify it to do this. If you are calling an external webservice with no batch functionality then I'm afraid I'm not sure how best to approach it, other than to fire of numerous asynchronous UDFs on separate threads.
如果它是你自己的webservice(我希望它是,因为你说我们的webservice),那么你应该能够修改它来做到这一点。如果您正在调用没有批处理功能的外部Web服务,那么我担心我不确定如何最好地处理它,除了在单独的线程上激发大量异步UDF。
#2
2
You can use Excel RTD to provide asynchronous data retrieval. RTD allows you to push updates to Excel on demand, which means you can return quickly from the UDF call and asynchronously retrieve the data in the background. When the request is complete, you then notify Excel that updated values are available. You can also quite easily batch multiple UDF calls into a single web service request by simply queuing the RTD topics as they arrive and periodically forming a batched request.
您可以使用Excel RTD提供异步数据检索。 RTD允许您按需将更新推送到Excel,这意味着您可以从UDF调用快速返回并在后台异步检索数据。请求完成后,您通知Excel已更新的值可用。您还可以轻松地将多个UDF调用批量处理为单个Web服务请求,只需在RTD主题到达时对其进行排队并定期形成批量请求。
Here is a link that describes how to create a basic RTD server in C#. To make the solution more user friendly, you can wrap the RTD worksheet function call in a UDF.
这是一个描述如何在C#中创建基本RTD服务器的链接。为了使解决方案更加用户友好,您可以将RTD工作表函数调用包装在UDF中。