5

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#)

woodykiddy
  • 6,074
  • 16
  • 59
  • 100
  • Seems like the udf would need to return twice for that to work: once after kicking off the web call, and again to return the result when it arrived. Might be better to use a worksheet change event to make the calls instead? – Tim Williams Sep 19 '11 at 15:18
  • @Tim Can you explain it in a bit more detail? Is there any examples for that? – woodykiddy Sep 20 '11 at 04:09

4 Answers4

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.

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.

Community
  • 1
  • 1
Frank
  • 3,029
  • 5
  • 34
  • 43
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!

Mathias
  • 15,191
  • 9
  • 60
  • 92
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.

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...

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

An excellent tutorial with examples is available here.

iDevlop
  • 24,841
  • 11
  • 90
  • 149