How to add support for External Workbook References to Excel Services


In v1, Excel Services will not support External Workbook References. This is due to various reasons, not the least of which is that the model itself does not lend itself in exactly the same manner on the server as it does on the client side.


That said, I will show you a relatively simple UDF that will allow you to have simulated External Workbook References in your Excel Services workbooks.


 


A few important things to notice about this example:


1. From a security stand-point, this solution may not be ideal for you. The “External Workbook Reference” queries will be done via the machine account which may actually have more access than you want to give your user. On a later post I will show how you can (in some cases) impersonate the user to achieve user-specific results.


2. Some servers will disallow loopbacks (i.e. A server connecting back to itself via HTTP). In those cases, the method employed to call back into the server may actually not work. For these cases, you should be able to employ what we call “Direct Linking” of the API DLL. At some point I will also give an example of how to do that.


3.  There are a few features that would work in Excel client and not in Excel Services. Namely, Excel has a global calculation chain that is shared by all workbooks. This means that if two workbooks are opened at the same time and two workbooks reference each other, Excel client will calculate the various cells in both workbooks in the correct order to give coherent results. In Excel Services, the UDF I am presenting is not designed to allow you to have such a “shared calc chain”.


 


To write this “External Workbook Reference” UDF for Excel Services, we will need knowledge we acquired from previous posts. Namely, how to use the API and how to write a UDF.


 


Our UDF will have the following signature:


 


public object ExternalWorkbookRef(string workbookName, string sheetName, string range)


 


As you can see, the signature is self explanatory – we take the location of the workbook, the name of the sheet and the name of the range. In the actual implementation, we will simply make a call through the Excel Services API, open the requested workbook and get the requested range. The code looks like this:


 


[UdfClass]


public class ExternalWorkbookUdfClass


{


       private string ServerUrl


       {


              get { return String.Format(“http://{0}/_vti_bin/ExcelService.asmx”, Environment.MachineName); }


       }


 


       [UdfMethod]


       public object ExternalWorkbookRef(string workbookName,


              string sheetName,


              string range)


       {


              ES.ExcelService s = new Blog_ExternalWBRef.ES.ExcelService();


              s.Credentials = System.Net.CredentialCache.DefaultCredentials;


              s.Url = ServerUrl;


 


              object result = null;


              string sessionId = null;


              try


              {


                     ES.Status[] stati;


                     sessionId = s.OpenWorkbook(workbookName, “”, “”, out stati);


                     result = s.GetCellA1(sessionId,


                           sheetName,


                           range,


                           false,


                            out stati);


              }


              catch


              {


                     throw new InvalidOperationException(“Could not open workbook.”);


              }


              finally


              {


                     if (sessionId != null)


                     {


                           s.CloseWorkbookAsync(sessionId);


                     }


              }


              return result;


       }


}


 


As you can see, the first part creates the Excel Web Service proxy, it then simply makes a call to the OpenWorkbook() API call and gets back a session ID. Then a call is made to GetCellA1() which retrieves the value we want. Finally, we close the session id asynchronously.


 


And that’s it. If you follow the instructions on how to install and use a UDF in the UDF primer, you should be able to see the results of your labor.


Example


For an example, we will take two workbooks – one called ExternalWB.xlsx which will contain the cell information we are looking for and one called RefWB.xlsx which will contain the call to the external workbook information:



(ExternalWB.xlsx)


 


 


(RefWB.xlsx)


 


As you can see, we are calling the UDF with the following parameters:


 


=ExternalWorkbookRef(“\\127.0.0.1\xlfiles\ExternalWB.xlsx”, “Sheet1”, “A1”)


 


(In this case, my trusted location is on the local machine, on a share called xlfiles)


If you open the RefWB.xlsx workbook on the server, you will see the information from ExternalWB.xlsx. To see that this is indeed the case, try changing the value in A1 in the ExternalWB.xlsx workbook and resaving it. Then reopen the RefWB.xlsx in EWA* – notice how the values change:


 



(RefWB.xlsx opened in EWA through Excel Services)


 


* Note: Depending on your Excel Service admin settings, it may take time for you to see the changes reflect in EWA.

Comments (27)

  1. mgr says:

    I was a little disappointed to see that excel services do not support external links in workbooks. I was looking forward to use this feature since my client uses a lot of external links in his wbks and i have to design a application to load and calculate these wbks on the server through browsers. I have not tried the above example yet abut I am hoping to find more infomation like this to make my life easier.

  2. newbie says:

    Does excel services support SSL. I noticed that the networkcredentials class does not support SSL so how do I connect using SSL or is this another thing that is not supported?

  3. Ira says:

    Yes, Excel Services supports SSL. It’s supported between the client application and Excel Services API, and it’s also supported between Excel Services API and Excel Services Calculation backend service.

    To enable SSL to Excel Services API, you’ll need to configure your SharePoint site to accept SSL connections. You can configure that using the Central Administration site. Once you’ve done that, you can issue requests to Excel Services API using https protocol.

    Please note, that you’ll need to install a certificate that is trusted by your client application.

  4. Nidhi Yadav says:

    Can somebody please tell me that : if i intend to use a web serice built using WCF

    how do i do that .. I added the references in the module i have tried creating a

    soapclient  but then i cannot see the web methods which by web service exposes

    Thanks

    Nidhi

  5. TS says:

    I have this example working, but need it to do a little more. I want to get a table from another workbook and use a column from it in a formula.

    Getting it thru the method described above seems easy except its not a single value. if I put that in my formula, it will blow up. Since the object returned from this method call is a jagged array, i would have to have some kind of formula to access an element in that array in excel language.

    thinking about it, I don’t know that its possible because i’m referring to a .Net object and accessing it in an Excel workbook.

    right now i have a query table and in one of the columns, each row is trying to get at a column from the table that exists in the named range i am accessing externally.

    What are some options to accomplish this.

    any chance your

  6. Shahar says:

    TS:

    I am not sure I understand what it you are trying to do.

    UDF supports ranges, but differently than the way the API does (due to SOAP limitations).

    If you could elborate, I may be able to give you a hand with this.

  7. mgr says:

    I am trying to implement this udf for use in excel client as well as the server. I am trying to find a way to connect to the excel services no matter where i use excel cleint. Other wise the udf will fail right? Any ideas on this? thanks.

  8. mgr says:

    Thanks for the quick response. I was able to implement with the help of those blogs. I was wondering what I should to if this udf has to work on the client when I am working outside the current  n/w . I am now in the office n/w where sharepoint server is installed and everything works great.

  9. Shahar says:

    I am not sure what you are asking about.

    Are youa sking about off-line? What to do when you do not have access to your server?

  10. mgr says:

    can I  apply functions like vlookup and hlookup on the cell having this udf?

    eg:

    cell B1=ExternalWorkbookRef( workbookName, sheetName,  range)

    cell f1=vlookup(number,indirect(B1),2,false)

  11. Shahar says:

    Mgr:

    (Is this question related to the previous?)

    Yeah, you can use both though I am not sure why you are using Indirect there. Why not just use B1?

    s

  12. mgr says:

    Yes in a way it is. I have to figure out a way of making the udf work offline on the client.

    The vlookup and hlookup functions retrive values from "table_array" which is a multidimensional array? Is this right? I am trying to conver the jagged array returned by getRangeA1 to a table_array and see if it works. As of now i am getting a either a "#REF!" or "#NA" for these and few other fucntions I am trying to use on this udf. Indirect was part of soemthing else I was trying to do and I have removed the indirect and am using something like f1=vlookup(number, B1 ,2,false).

  13. mgr says:

    some how none of the look up functions seem to work. I tried to return a 2 dimensional array instead of jagged array. I alway get #NA.

  14. mgr says:

    sorry spoke too soon, the formatted parameter in getrangeA1 was set to true in my function so all the problems. Its working fine now. Thanks

  15. mgr says:

    Hi Shahar,

    The UDF here is working fine as long as I use it about 25 times in one worksheet. I have a workbook here which has hlookup and vlookup functions referencing a named range in an externalworkbook which I have to replace with this UDF in about 3000 cells and I start getting #value errors. can you suggest ways of making this more robust function?

  16. Shahar says:

    Mgr:

    Can you try replacing the CloseSessionAsync() call with CloseSession()?

    Let me know if that solved your problem.

  17. mgr says:

    You mean CloseWorkbook() instead of the the asyncronous call? Yes I tried that. Made no difference.

  18. Shahar says:

    Okay. I think I know what the issue is.

    When Excel Services closes a session, it doesn’t close it immediatly – the session gets really closed up to a few seconds after the whole thing starts.

    I have 3 suggestions for you:

    1. Increase the amount of opened sessions that are allowed per user – this is an Excel Services option and is settable from the SharePoint admin site.

    2. If the cells coming from the external workbook are contigous, you can add another UDF that does a get-range and returns that to the workbook in the form of an Array function (I can go into more details if you want – it’s pretty simple – "hardest" part is to translate the jagged array you get back from the web-services into a 2-dimensional array.)

    3. A more efficient way of doing what you want here would be to open one session, get the infromation you want and then close it. This should also solve the problem you are seeing here (you are opening a lot of sessions as it currently stands and that’s a somewhat expensive process.

    To do that, you will need to separate this UDF into three UDFs. One that opens a session (and returns the session ID), one that does a get cell and one that closes the session. Then you do the following:

    a. In one helper cell (Say, A1) you type =OpenWorkbook(….).

    b. In the cells you want to get information into, you type =GetCell(A1, ….) – this will guarantee the OpenWorkbook UDF gets called first.

    c. In another helper cell, you call your CloseWorkbook UDF – this guy gets the range of GetCell() as a parameter so that it only gets calculated after they all are.

  19. mgr says:

    Thank you very much Shahar. Your suggestions were very helpful. I went for your suggestion number 3. It cut down performance time by 50%. Again Thank you very much.

  20. mgr says:

    HI,

    I had posted earlier here that I am having problems with vlookup and hlookup functions and UDF’s. I have not yet solved this problem and got side tracked into getting udfs to work on client first. Although everything is working fine on the client, sever still comes up with a 0 and #NA for getrangeA1 and lookup functions respectively. When I debug the UDF running in excel on server, I see that the UDF is returning the array with the named range from the GetRangeA1 method. I even tried changing the array to a 2 dimensional array but did not change the result. I saw this post http://msdn2.microsoft.com/en-us/library/bb226682.aspx and wondering whats going on. I am trying out different things here but can you please point me to a direction where to go from here. Your suggetions have always worked thanks a lot.

  21. Shahar says:

    Mgr,

    I would like to help, but am not sure I understand what the problem is. Can you elaborate?

    What do you mean that the server comes up with 0 and #NA? Where? Under what conditions? What UDF are you running? How is it defined? Where are you calling it from? Is it different on client and server etc.

  22. mgr says:

    My UDF looks like this

    [UdfMethod(IsVolatile = true)]

           public object[] ExternalWorkbookRangeRef(string workbookName, string sheetName, string range)

           {

               ES.ExcelService s = new ExternalRef.ES.ExcelService();

               s.Credentials = System.Net.CredentialCache.DefaultCredentials;

               s.Url = ServerUrl;

               object[] result = null;

               string sessionId = null;

               try

               {

                   ES.Status[] stati;

                   sessionId = s.OpenWorkbook(workbookName, "", "", out stati);

                   result = s.GetRangeA1(sessionId, sheetName, range, false, out stati);                                

               }

               catch(Exception e)

               {

                   throw new InvalidOperationException("Could not open workbook.");

               }

               finally

               {

                   if (sessionId != null)

                   {

                       s.CloseWorkbookAsync(sessionId);

                   }

               }

               return result;

           }        

    Book1.xlsx has named range "rangeValues" having some strings in cells A8:C14.

    Book2.xlsx has UDF calls in A10 to C16

    {=ExternalWorkbookRangeRef("path to Book1.xlsx","Sheet1","rangeValues")}

    I also have the follwoing calls in Book2.xlsx

    =VLOOKUP("string 1",ExternalWorkbookRangeRef("path to Book1.xlsx","Sheet1","rangeValues"),2,TRUE)

    =HLOOKUP("string 1",ExternalWorkbookRangeRef("path to Book1.xlsx","Sheet1","rangeValues"),2,TRUE)

    The UDF works fine on the excel cleint. I get all correct values in all the cells.

    On the server, The ExternalWorkbookRangeRef UDF call comes up as 0 in the workbook cells and the vlookup and Hlookup comes up as #NA?. When I run the code in Debug mode by attaching to the server process, I see that the array is returned fine by the UDF but for some reason the workbook is not showing the returned results!!?? on the server.

  23. mgr says:

    Never mind my questions. I was able to make the udf work on the server by converting the jagged array to 2 dimensional array and returning it. There was a bug in my code before and I fixed it. Thanks

  24. Ecl says:

    ES.ExcelService s = new Blog_ExternalWBRef.ES.ExcelService();

    Does this code work well?

  25. Shahar says:

    What do you mean by well?

    It wont do anything – it just creates an instance of the proxy.

  26. raj says:

    How  will we use vloolup option between two workbook especially when data is in two separate workbook.