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("https://{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.