Excel Services UDF, be not synchronous
In a previous post, I show you how to create a UDF that resolves internet names by using a DNS lookup web service. In this post, we will discuss how we can make potential uses of this UDF even faster.
Continuing the example of the DNS look up, what if we wanted to look up many values all at the same time? We could use the function multiple times – but remember, it makes a web service call – that can potentially take a long time and doesn’t really have any significant usage of the CPU. Instead, we can create another function that will be used in the cases of multiple lookups. In this case, the UDF will return an Array which will be used by Excel Services just like any other array function. The parameter to our new function will be an array of names we want to look up. The signature will look like this:
public object[,] ResolveHostNameArray(object[,] names)
When passing array into UDFs, you always use object arrays (all other array types are unsupported). You can either use vectors (single dimension) or two-dimensional arrays – what you use will govern what types of calls can be made.
Vectors (single-dimension arrays)
If the range your UDF expects from Excel is a single row, you can use a vector as the parameter. The left-most item in the range passed will be the 0th item in the array. For example, a UDF method that needs to return the index of a string in a row of number:
[UdfMethod]
public int GetStringIndex(string find, object[] singleRow)
{
for (int i = 0; i < singleRow.Length; i++)
{
if (find.Equals(singleRow[i].ToString(), StringComparison.InvariantCultureIgnoreCase))
{
return i;
}
}
return -1;
}
Calls made to this UDF with more than one row (even if there’s only one column) will fail – the UDF won’t even get executed.
Two-dimensional arrays
When you need a range that spans more than one row, you will need to use a two dimensional array (as described above, in the ResolveHostNameArray() signature).
The first index of the array is the row index, the second one will be the column index. The next table shows you a range of numbers passed in and the tuple of indices for each element in the range:
[0,0] |
[0,1] |
[1,0] |
[1,1] |
[2,0] |
[2,1] |
Back to our example
Now that we have the signature, we actually need to write the code for the method. As a reminder, this was the code in our prior Dns Lookup example:
[UdfMethod]
public string ResolveHostName(string name)
{
string st;
using (DnsLookup.DNSLookupService dns = new Blog_DNSUdf.DnsLookup.DNSLookupService())
{
st = dns.GetDNSInfoByWebAddress(name).IPAddress[0].ToString();
}
return st;
}
Simple Array Udf
The simple Array Udf will look similar to the single-lookup one:
[UdfMethod]
public object[,] ResolveHostNameArraySync(object[,] names)
{
int nameCount = names.GetLength(0);
object[,] result = new object[nameCount, 1];
for (int i = 0; i < nameCount; i++)
{
using (DnsLookup.DNSLookupService dns = new Blog_DNSUdf.DnsLookup.DNSLookupService())
{
result[i, 0] = dns.GetDNSInfoByWebAddress((string)names[i, 0]).IPAddress[0].ToString();
}
}
return result;
}
As you can see, we are iterating the names array we got and for each value, we make a call to the web-service, asking for the IP address. We then take each address and place it in a result array which we then return to Excel Services.
This works and it’s all nice and dandy. The only problem is that it is highly inefficient. As explained before, calling Web-Services takes a relative long period of time while taking a relatively small amount of computing power. If a single DNS lookup call takes 400ms, making 10 of those calls serially will take 4 seconds and change. However, if you can make all those calls in parallel, the entire ordeal will take 400ms+. That means that this is a prime candidate for parallelization.
Parallel calls example
To call into the web-service multiple times, we will want to shoot off multiple asynchronous calls and then wait for all of them to get back to us. When the last one comes back, we will “free” our UDF method and let it return the combined array.
[UdfMethod]
public object[,] ResolveHostNameArray(object[,] names)
{
if (System.Net.ServicePointManager.DefaultConnectionLimit < 20)
System.Net.ServicePointManager.DefaultConnectionLimit = 20;
int nameCount = names.GetLength(0);
object[,] result = new object[nameCount, 1];
int returnCount = nameCount;
using (ManualResetEvent m_event = new ManualResetEvent(false))
{
for (int i = 0; i < nameCount; i++)
{
DnsLookup.DNSLookupService dns = new Blog_DNSUdf.DnsLookup.DNSLookupService();
dns.GetDNSInfoByWebAddressCompleted +=
delegate(object sender, Blog_DNSUdf.DnsLookup.GetDNSInfoByWebAddressCompletedEventArgs e)
{
int index = (int)e.UserState;
if (e.Error == null)
{
result[index, 0] = e.Result.IPAddress[0].ToString();
}
else
{
result[index, 0] = "";
}
Interlocked.Decrement(ref returnCount);
if (returnCount == 0)
{
m_event.Set();
}
};
dns.GetDNSInfoByWebAddressAsync((string)names[i, 0], (object)i);
}
m_event.WaitOne();
return result;
}
}
Let us dissect the method and see what’s going on here.
- We create an event – we use it towards the end of the method to know when we are done processing.
- We make an async call – dns.GetDNSInfoByWebAddressAsync() – this call was generated for us by VS2005 and it will make a call to the GetDNSInfoByWebAddressCompleted() event when the call is complete and the data ready.
- Note that into the async call, we pass in the index where the result will need to reside – this index will be passed to the complete event.
- For the GetDNSInfoByWebAddressCompleted() event, we create an anonymous delegate. This delegate places the result in the correct location in the array.
- Each delegate decrements the returnCount local variable* - if it sees that it’s the last one (returnCount is zero), it will signal the event.
- Finally, towards the end of the method, we wait for the event to signal. Once it does, we know all async calls have come back home and we can return the result.
You may have also noticed the first call I make to the ServicePointManager. By default, .NET will allow only two concurrent connections to the same server at the same time. In our case, we want more (otherwise, our method will again be blocked).
There are quite a few changes we could make so that this method would be safer and cleaner. For one, we could give the event a timeout so that if Something Bad happens, we don’t hang the server in a thread for eternity. Second, we could have a less sweeping change when using the ServicePointManager and only target the server we are actually using.
* C# is very crafty when it comes to anonymous functions – once the local variable is modified inside the anonymous delegate, it ceases to really be a local variable and turns into a member in an anonymous class. Luckily, C# takes care of all that goo for us, so we don’t actually need to, like, think hard, you know?
Comments
Anonymous
September 01, 2006
Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on...Anonymous
December 13, 2006
Hi, I'm returning a 2-dimensional object to the Excel using UDF method. But in Excel I can display the results using Array. For that array i'm assigning this UDF formula. Here I can display only a Fixed size of data. For example I declared an array(which contains 100 rows) using Ctrl+Shift+Enter with UDF formula in Excel. But if I got more than 100 rows from the UDF method those rows are not displayed in the Excel sheet. How do I solve this problem. Please suggest me a solution. Thanks & Regards, Deepak. email: madugulad@yahoo.comAnonymous
December 14, 2006
That's an Excel limitation - Array Functions in Excel can only have a pre-determined size. The only solution is to figure what the absolute maximum the range can be and use that as the size of the returned array.Anonymous
December 14, 2006
Hi Shahar, Thanks for the reply. Is there any other way for diaplaying the records from UDF ? Because the data may be more... And other thing is If I assign maximum array the excel file size will be increased(The Excel is in sharepoint server). For that I have to make it dynamic. Please give me a suggestion how to do this. Thanks & Regards, Deepak. email:madugulad@yahoo.comAnonymous
December 14, 2006
There is one more way which is sub-optimal for almost all cases. It involves "Paging" of sort, where you tell the UDF what row to start at and how many rows to display. Then you can use the Parameters pane to tell the UDF which "page" to show. Note that, depending on the amount of potential data you will have, placing a very big Array formula may take less disk-space than you think.Anonymous
December 18, 2006
Hi Shahar, Thanks alot for giving me the suggestions. As you said that we need to tell the UDF what row to start and how many rows to display. How do I tell the UDF where to start & How many rows to display? I'm unable to get the reference to the Excel Cells from my UDF. Please tell me the way how to do. Thanks & Regards, Deepak.Anonymous
December 19, 2006
You just use 2 parameters: GetData(int start, int count) And when the UDF is called, you use the start parameter to figure out which element you need to start from.Anonymous
January 15, 2007
Hi Shahar, will excel services supports Digital Signatures? I have created one workflow in sharepoint 2007. I have added the workflow in one of the Excel 2007 file(which published to sharepoint). I have added Digital Signature in that Excel and trying to view that file using Excel Web Access. But it is giving an error saying that Digital Signature is not a supported feature kind of message. please let me know whether Digital Signature is not supported by Excel Services.. If supported, do I have to do any settings. Thanks & Regards, Deepak.Anonymous
January 15, 2007
Excel Services does not support digital signatures. Sorry about that.Anonymous
January 21, 2007
Hi shahar, I have tried to use function oveloading in my UDF class. I have created 2 udf function with the same name and different parameters. I called those 2 udf functions from my Excel. But if I see the result using Excel Web Access, It gives the results only for the first method. For the 2nd call its giving #value. If I comment the 1st udf function in my code then 2nd one is working. Does UDFs supports Method Overloading? Please let me know. Thanks & Regards, Deepak.Anonymous
January 22, 2007
Good question. UDFs do not support overloading of methods. The closest you can get is to use optional parameters. Not the best thing in the world, but can solve most scenarios. If you look at your Event Log, it will tell you that 2 methods with the same name were found in your class and that only the first one will be used.Anonymous
January 24, 2007
Hi shahar, Thank you for the support. Regards, Deepak.Anonymous
March 05, 2007
Hi Shahar, I tried to implement the method that takes an array as a parameter, but I always get the #VALUE! error in Excel. My method has the following code (very simple) code: [UdfMethod] public int arrayPassing(string inString, object[] inRow) { return 1; } In an Excel cell I type in =arrayPassing(A1, A2:F2) and press ctrl+shift+enter, but I always get the #VALUE! error. When I try debugging it doesn't even enter in to the method. The values in the cells (A1, A2:F2) are all strngs. I was able to create a method that returns an array with no problems. Any ideas about what I'm doing wrong? Thanks, CynthiaAnonymous
March 05, 2007
Cynthia, First of all - are you trying to make this work on the client or on the server? Second - Why are you hitting Ctrl-Shift-Enter? That's useful only when the UDF returns an array. Not when it takes an array. I will try it (hopefuly later today) to make sure that it actually works, though, from what I am seeing, it should work. Could it be that you are not using the correct binary? If you go to the "Loaded Modules" window in VS, does it show the correct path where the file gets compiled into (or copied into)?Anonymous
March 05, 2007
Shahar, Thank you for such a quick response. I'm trying to make this work in Excel client. I'm working on the server where I have SharePoint installed. When I view the spreadsheet in a browser via Excel Services it works correctly. I should have mentioned - I'm pretty new to all things Excel other than the most basic spreadsheet. I read somewhere online that you had to press Ctrl+Shift+Enter for passing an array in as well as receiving an array. I stopped doing that so thanks for the tip. It does show the correct path that the file is compiled to and just as a sanity check I added a new UDF method in the same class and I was able to call it and debug it correctly. Thanks, CynthiaAnonymous
March 05, 2007
Cynthia, I am a bit confused. So bear with me. You say it all works perfectly on the server. Good. You say: <<< It does show the correct path that the file is compiled to and just as a sanity check I added a new UDF method in the same class and I was able to call it and debug it correctly. >>> So all the problems we are talking about are in the client? So you followed my instructions (or some other) on how to make a server UDF work on the server? sAnonymous
March 05, 2007
Yes. I am just having a problem on the client. I followed the instructions at http://msdn2.microsoft.com/en-us/library/bb267252.aspx#Office2007ExcelServicesUnlimited_ExternalRefs to write and use a UDF on the server. I also followed the instructions at the end of the above link about Deploying a UDF Assembly on the Excel Client. The instructions worked for all the other methods I wrote, but for some reason I can't get this one to work on the client. I know there must be some little step that I forgot, but I have gone through it a few times and I can't figure it out. Thanks again, CynthiaAnonymous
March 05, 2007
Okay. I see now. The problem is that when Excel passes in a range to a UDF, it does not do the work for converting it into an array. Instead, it passes in a Range COM object. To make this work, you will need to turn that Range instance into an array. For a complete example on how to make this work: http://blogs.msdn.com/cumgranosalis/archive/2006/08/30/ServerClientUDFsCompat2.aspx sAnonymous
March 06, 2007
Thank you for all your help. That worked perfectly. -CynthiaAnonymous
March 25, 2007
Hi shahar, I need to generate Excel Reports on a timely basis. Please let me know how this kind of scheduling jobs in sharepoint 2007 can be achieved? We downloaded compatibility pack for Office 2000 to work with Excel Services. Here we installed 2002 & 2003 compatibility packs and those are working fine. But the same thing we couldn't achieve with 2000 compatibility pack. Can you please give suggestion on this issue? Thanks & Regards, DeepakAnonymous
April 02, 2007
Hi Shahar, I created one cube, from that I'll get the data and displayed it in an Excel Pivot Table. I'm calling this Excel file from Excel Web Access. Here I can able to filter the pivot table values using Sql Server 2005 Analysis Filter. But the requirement is I need to pass the value from one sql server 2005 analysis filter and it should filter two pivot tables. Here I can connect to one pivot table. Please let me know how to connect to two pivot table filters? Thanks & Regards, Deepak.Anonymous
April 05, 2007
Just got this reply in our internal alias: <<< Its not possible. The possible solution in the problem below is to send the MDX to a non-pivot parameter. Then use that MDX expression in pivot table page field to filter data. >>>Anonymous
May 16, 2007
The comment has been removedAnonymous
September 09, 2007
Hi, I have created a .net Excel Service UDF dll in 32 bit machine, which calls c++ and c libraries. If I register the UDF in 32 bit Server machine,and call it from Sharepoint Excel WebBrowser it is working , But if I register the UDF Dll on a 64 bit Sharepoint server machine, Its not working. I tested with this dll from Windows Application, and its not working. I have created that .net Dll by setting the compileoptions-> target cpu to x86 in the project properties. Then I'm able to get the result if I call the Dll from Windows Application. But if I create the UDF Dll using x86, its not recognised by SharePoint and its displaying #NAME in the Excel Web Browser. please help me in doing this.. Thanks & Regards, DeepakAnonymous
September 09, 2007
I posted an answer on the blog: http://blogs.msdn.com/cumgranosalis/archive/2007/09/10/udfs-in-the-world-of-64bit-excel-services-installations.aspxAnonymous
February 18, 2008
I can seem to be able to set my Qry Parameters. It's not an option on my setting...Why?Anonymous
February 19, 2008
What qry? Can you elaborate your question? I am not sure what it is you are asking.Anonymous
September 23, 2009
Shahir, While calculation Is it possible to accumulate the parameters of each call to UDF in the excel sheet to a collection and calculate it in a multithreaded way and when thread returns , each value is returned as the result of each UDFAnonymous
September 23, 2009
Shahar, While calculation Is it possible to accumulate the parameters of each call to UDF in the excel sheet to a collection and calculate it in a multithreaded way and when thread returns , each value is returned as the result of each UDF