Creating a C# User Defined Function in Excel that Communicates with a WCF Service

Here is the business case: Your accounting staff wishes to query some figures from the financial system in Excel. To prevent a litany of copying and pasting, creating custom analysis services cubes, or taking the rather drastic measure of granting SQL access, it’s decided to create a custom User Defined Function in Excel. This UDF will query a WCF service that retrieves the information.

It is very easy to query a WCF service using an Excel VSTO add-in. Typically there is a custom ribbon with a button that, when clicked, goes off and retrieves the information. Unfortunately, VSTO add-ins built using Visual Studio do not have the ability to define custom User Defined Functions. The end result being targeted here is something like so:

Untitled

In order to do this, a Class Library project must be created and the result registered with Excel. There are a good number of examples of how to do this on the web, here is the summary:

 namespace YourNamespace
{
  [ClassInterface(ClassInterfaceType.AutoDual)]
  [ComVisible(true)]
  public class Functions
  {
    public Functions()
    {
    }

    /// <summary>
    /// A simple test function to ensure all is working
    /// </summary>
    /// <returns>A string</returns>
    public string Test()
    {
      return "Hello World";
    }
    
    /// <summary>
    /// Registration information
    /// </summary>
    /// <param name="type"></param>
    [ComRegisterFunctionAttribute]
    public static void RegisterFunction(Type type)
    {
      Registry.ClassesRoot.CreateSubKey(GetClsIdSubKeyName(type, "Programmable"));
      
      var key = Registry.ClassesRoot.OpenSubKey(GetClsIdSubKeyName(type, "InprocServer32"), true);
      if (key == null)
      {
        return;
      }
      key.SetValue("", String.Format("{0}\\mscoree.dll", Environment.SystemDirectory), RegistryValueKind.String);
    }

    [ComUnregisterFunctionAttribute]
    public static void UnregisterFunction(Type t)
    {
      Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
          "CLSID\\{" + t.GUID.ToString().ToUpper() +
            "}\\Programmable");
    }

    private static string GetClsIdSubKeyName(Type type, String subKeyName)
    {
      return string.Format("CLSID\\{{{0}}}\\{1}",
          type.GUID.ToString().ToUpper(), subKeyName);
    }
  } // end class
}

 

So far, so good. The next step is to create a new UDF other than the Hello World one that connects to the web service. Standard procedure would be to add a Service Reference, create an instance of the client, call the method and be done with it. The problem is, you cannot call a Service Reference from a Class Library project easily. Using the above method, you will likely receive this message:

Could not find default endpoint element that references contract ‘UserService.UserService’ in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.

This occurs because the service definitions are stored in the app.config file of your class library project. Being a class library project, this does not get included when deployed. A number of solutions on the web suggest copying the service definitions from your app.config into the app.config of the application that calls your compiled class library. Unfortunately, when the calling application is Excel, this is not an option.

The solution to this is the revert to the “old school” method of dealing with WCF services and use the SVCUtil function to generate the WCF definition as a .cs file to be included in your project. This utility is most often installed here: C:\Program Files (x86)\Microsoft SDKs\Windows\vXXX\Bin. The command I ran is:

svcutil.exe http://ServerName/ServiceName/ServiceName.svc

Take the generated cs file, copy to your project folder, and include it in your project.

The straightforward method of connecting to a basic WCF service would be something like so:

 BasicHttpBinding binding = new BasicHttpBinding();

EndpointAddress remoteAddress = new EndpointAddress(“http://ServerName/ServiceName/ServiceName.svc”);
        
ServiceNameClient theService = new ServiceNameClient(binding, remoteAddress);
double result = theService.FinancialFigure(AccountNumber, StartDate, EndDate);

return result.ToString();
 

If however you are operating in a corporate environment, there is a good chance you will receive a message similar to this:

The HTTP request is unauthorized with client authentication scheme ‘Anonymous’. The authentication header received from the server was ‘Negotiate,NTLM’.

This is because the class library is attempting to connect to a WCF service that required authentication – in the example above, NTLM authentication.

Expand the C# code to include security settings:

 BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly);
binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;

EndpointAddress remoteAddress = new EndpointAddress(“http://ServerName/ServiceName/ServiceName.svc”);
ServiceNameClient theService = new ServiceNameClient(binding, remoteAddress);
 

Unfortunately that still did not work for me and I received this message:

System.ServiceModel.EndpointNotFoundException: There was no endpoint listening at http://ServerName/ServiceName/ServiceName.svc that could accept the message. This is often caused by an incorrect address or SOAP action.

This was an easy fix – just add /basic to the end of the service URL:

EndpointAddress remoteAddress = new EndpointAddress(“http://ServerName/ServiceName/ServiceName.svc/basic”);

That’s it! It took a while to put all the pieces together, but the result is a UDF in Excel that can call an NTLM secured WCF service.

Advertisements

About John Winford

Based in Vancouver, Canada I’m an IT professional with a number of specialties. First and foremost I am the front-line between the business users I enable and the technical team I represent. Not content to simply push the paper I also get my ‘hands dirty’ when required. I have an extensive amount of experience with technical project management, ERP implementations, BI work, and development across SharePoint, CRM and generic Widows applications.
This entry was posted in Business Intelligence, Windows Apps. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s