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.



  1. We create an event – we use it towards the end of the method to know when we are done processing.
  2. 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.
  3. 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.
  4. For the GetDNSInfoByWebAddressCompleted() event, we create an anonymous delegate. This delegate places the result in the correct location in the array.
  5. Each delegate decrements the returnCount local variable* – if it sees that it’s the last one (returnCount is zero), it will signal the event.
  6.  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 (29)

  1. Shahar Prish, one of the developers on the Excel Services team, has recently posted a few entries on…

  2. Deepak says:

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

  3. Shahar says:

    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.

  4. Deepak says:

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

  5. Shahar says:

    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.

  6. Deepak says:

    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.

  7. Shahar says:

    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.

  8. madugulad says:

    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.

  9. Shahar says:

    Excel Services does not support digital signatures. Sorry about that.

  10. madugulad says:

    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.

  11. Shahar says:

    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.

  12. Deepak says:

    Hi shahar,

       Thank you for the support.

    Regards,

    Deepak.

  13. Cynthia says:

    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,

    Cynthia

  14. Shahar says:

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

  15. Cynthia says:

    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,

    Cynthia

  16. Shahar says:

    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?

    s

  17. Cynthia says:

    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,

    Cynthia

  18. Shahar says:

    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

    s

  19. Cynthia says:

    Thank you for all your help.  That worked perfectly.

    -Cynthia

  20. Deepak says:

    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,

    Deepak

  21. madugulad says:

    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.

  22. Shahar says:

    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.

    >>>

  23. madugulad says:

    Hi shahar,

     We are trying to solve an issue with Excel Services, but are not able to hit upon a right solution, requesting your help on this.

    What I am trying is some thing like this.

    We have 4 UDFs, each taking some parameters.

    Each function returns 2 columns as of now, but could return more the 2 columns in the future.

    But the number of rows each function would return is not fixed; it could just return one row or 200 rows.

    What we want to do here is to ensure that there are no blanks rows between the two function calls or I could have 1 or 2 blank rows between them, basically 4 UDFs are getting called one below the other.

               We tried to write a

    function concat() {

       [native code]

    }

    UDF which would basically execute this 4 UDFs and build an array and the same can be show to the user while he prints the document as report, but there are some issues with this approach,

    Like what if each page has varying number of cells.

    What if we have merged cells in some places out side the excel array boundary etc., our calculations are becoming very complex.

              Could you please suggest an alternate and a better approach of this problem in Excel Services.

    Thanks & Regards,

    Deepak

  24. Deepak says:

    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,

    Deepak

  25. Irv says:

    I can seem to be able to set my Qry Parameters. It’s not an option on my setting…Why?

  26. Shahar says:

    What qry? Can you elaborate your question? I am not sure what it is you are asking.

  27. KK says:

    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 UDF

  28. KK says:

    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