[ExcelDNA] Batching web service calls in excel UDF

[kishor sharma]

Hi Excel DNA Users,

I have JSON based web service to get the data from server e.g. given a symbol and date it will return the price on the date. I have excel UDF written using ExcelDNA which calls the web service and gets the price in excel.  UDF seems to be working fine but when users refresh sheets (ctrl+alt+f9) then lot of calls (no of cells in excel sheet having formula) are made to the server which looks sub-optimal.

I was thinking about batching so instead of getting data for one date, I fetch data for a year  and keep it cache so that after one call to the server , next cell can pick data from cache instead of going to remote server. I implemented batching but it seems like on refreshing the excel still there are multiple calls to the server as excel uses multi-thread to refresh excel sheet. 

Any ideas on how to fix this ?  Basic problem is to have a cache which can be shared among all the cells during refresh. 

Thanks,

Kishor

[Govert van Drimmelen]

Your plan to pre-fetch a lot of data seems like a good idea.

If your functions are not marked as IsThreadSafe=true, then they will always run on the main Excel thread.

 

A good sample for a more sophisticated async / batch calls is this one:

https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs

Using that approach to populate a local cache should work well.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[kishor sharma]

Thanks Govert. I will try this and get back to you. 

One more question: Let's say I have an excel UDF Foo with IsThreaded=False then on refreshing worksheets will the call happen in sequential order? I am observing that calls are made in parallel on refresh instead of sequential calls which is causing multiple calls to the server.  

Thanks,

Kishor

[Govert van Drimmelen]

Hi Kishor,

 

UDFs that are not registered as IsThreadSafe=true will always execute on the main Excel thread one after the other (though the sequence in which the functions are evaluated might not be the same sequence as they appear in the worksheet).

 

If you are using some asynchronous execution mechanism like calling ExcelAsyncUtil.Run, the delegates you pass to ExcelAsyncUtil.Run would run on multiple thread on the .NET ThreadPool. But the actual UDF functions (the method marked with [ExcelFunction]) won’t run concurrently.