Exercise 3: Consuming Azure WCF Service Data in Excel

In the previous exercise you saw how you could use WCF Data Services to easily work with the RESTful data exposed by Azure’s Table Storage service. Another way that you can interact with Azure is by creating WCF Services that are hosted on the Azure platform. WCF Services can expose a wide range of functionality from abstracting a data tier to implementing business logic and more. In this exercise, you will create an Excel User-Defined Function that integrates with a WCF Service on Azure to retrieve summarized sales totals.

Although it is outside of the scope of this exercise, UDF’s can be an interesting way to expose external data in Excel, particularly if Excel Services is part of your solution as user-defined functions are one of the primary ways you can extend workbooks destined for Excel Services. See https://msdn.microsoft.com/en-us/library/ms493934.aspx for more information. For this exercise you will create a managed code UDF for use on the client (Excel rather than Excel Services).

Task 1 – Creating the Project

Creating a UDF is slightly different from a traditional Office document or application add-in solution created using Visual Studio. Rather than starting with one of the Visual Studio Office templates, you start using a standard class library template.

1. Open Visual Studio 2010 (run as administrator).
2. Select File>>New Project
3. Select the Class Library template found under Visual C#
   1. Name: SalesFunctions
   2. Location: C:\Student\Azure
   3. Solution Name: NorthwindSalesFunctions

   4. Click OK

      Figure 13

      Creating a Class Library

4. Add necessary references
   1. Right-click on References in the Solution Explorer and choose Add Reference
   2. On the .NET tab locate and select the following components (hold the CTRL key to select multiple non-contiguous items)
      1. System.Runtime.Serialization
      2. System.ServiceModel
   3. Click OK to add the references
5. Right-click on the SalesFunctions project in the Solution Explorer and choose Add Existing Item
6. Locate the SaleObservationService.cs file in the folder C:\Student\Azure\SaleObservationServiceTestClient\SaleObservationServiceTestClient and click Add
7. Right-click on the project and choose Properties
8. On the Build tab, put a check next to Register for COM interop
9. Press CTRL+S to save your changes
10. Close the Properties window
11. Click on Class1.cs in the Solution Explorer
12. Change the File Name to SalesFunctions.cs
13. Choose Yes to perform a rename in the project of all references to the code element ‘Class1’

Task 2 – Modifying the Class so That it Can Interoperate With COM

Since Excel is a COM-based application, in order to use the class library within Excel you need to be sure that class is COM callable.

1. Import the necessary namespaces in the SalesFunctions class

   C#

   using System.Runtime.InteropServices; using Microsoft.Win32;

2. Decorate the SalesFunctions class with the following attributes

   C#

   namespace SalesFunctions { [Guid("7C69C8CF-747C-4855-B897-AF0710109816")] [ClassInterface(ClassInterfaceType.AutoDual)] [ComVisible(true)] public class SalesFunctions

3. Replace the supplied Guid above with a new Guid
   1. Select Tools>>Create GUID

   2. Select the 5th GUID Format and choose Copy

      Figure 14

      Create GUID dialog

   3. Select the [Guid("7C69C8CF-747C-4855-B897-AF0710109816")] statement
   4. Press CTRL+V to replace it with the new GUID
4. Add the following functions to the SalesFunction class to handle COM registration and un-registration and the class constructor

   C#

   public SalesFunctions() { } [ComRegisterFunctionAttribute] public static void RegisterFunction(Type type) { Registry.ClassesRoot.CreateSubKey( GetSubKeyName(type, "Programmable")); RegistryKey key = Registry.ClassesRoot.OpenSubKey( GetSubKeyName(type, "InprocServer32"), true); key.SetValue("", System.Environment.SystemDirectory + @"\mscoree.dll", RegistryValueKind.String); } [ComUnregisterFunctionAttribute] public static void UnregisterFunction(Type type) { Registry.ClassesRoot.DeleteSubKey( GetSubKeyName(type, "Programmable"), false); } private static string GetSubKeyName(Type type, string subKeyName) { System.Text.StringBuilder s = new System.Text.StringBuilder(); s.Append(@"CLSID\{"); s.Append(type.GUID.ToString().ToUpper()); s.Append(@"}\"); s.Append(subKeyName); return s.ToString(); }

Task 3 – Implementing the Call to the WCF Service

The final task is to make the call to the WCF Service.

1. Add the necessary namespaces to SalesFunctions.cs

   C#

   using System.ServiceModel; using System.Xml;

2. Add a method to the SalesFunctions class to create a new instance of the SaleObservationService

   C#

   private SaleObservationServiceClient GetClient() { //Specify the binding to be used for the client. BasicHttpBinding binding = new BasicHttpBinding(); //Specify the address to be used for the client. EndpointAddress address = new EndpointAddress( "http://127.0.0.1:81/SaleObservationService.svc"); return new SaleObservationServiceClient(binding, address); }

3. Implement the GetSales function in the SalesFunctions class

   C#

   public double GetSales(string category) { double result = 0.0; SaleObservationServiceClient client = GetClient(); result = client.CategorySales(category); client.Close(); return result; }
4. Press F6 to build the project

Exercise 3 Verification

To verify your work, perform the following actions.

1. Verify your WCF SaleObservationService from Exercise 1 is running.
   1. Open the Development Fabric UI by right-clicking on the Windows Azure icon in the system tray

   2. Choose Show Development Fabric UI

      Figure 15

      Windows Azure context menu

   3. Verify that the WCFSaleObservationService is running

      Figure 16

      Running WCFSaleObservationService service

   4. If it is not running, restart it by debugging the SaleObservationService project (from Exercise 1) in Visual Studio
2. Open up MicrosoftExcel
3. Activate the SalesFunctions add-in in Excel
   1. In Excel, select File>>Options
   2. In the Excel Options window click on Add-Ins
   3. Down near the bottom, select Excel Add-ins in the Manage drop-down box and

   4. Click Go

      Figure 17

      Activating SalesFunction add-in

   5. In the Add-Ins window, click Automation or click the Automation… button

      Figure 18

      AutomationAddin.MyFunctions selected for activation

   6. Locate and select the SalesFunctions.SalesFunctions Automation server and click OK

      Figure 19

      SalesFunctions.SalesFunctions automation server

   7. Click OK to close the Add-Ins window
4. Create a function using the user-defined function

   1. In a blank worksheet, enter the values as shown in the screenshot below

      Figure 20

      Product Category values

   2. In cell C4, enter the formula =GetSales(B4)
   3. Fill the formula down to cell C9

   4. (Optional) Format the cells using the comma style

      Figure 21

      Using the formula to get Sales Totals over Product Category values