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.
- Open Visual Studio 2010 (run as administrator).
- Select File>>New Project
- Select the Class Library template found under Visual C#
- Name: SalesFunctions
- Location: C:\Student\Azure
- Solution Name: NorthwindSalesFunctions
Click OK
Figure 13
Creating a Class Library
- Add necessary references
- Right-click on References in the Solution Explorer and choose Add Reference
- On the .NET tab locate and select the following components (hold the CTRL key to select multiple non-contiguous items)
- System.Runtime.Serialization
- System.ServiceModel
- Click OK to add the references
- Right-click on the SalesFunctions project in the Solution Explorer and choose Add Existing Item
- Locate the SaleObservationService.cs file in the folder C:\Student\Azure\SaleObservationServiceTestClient\SaleObservationServiceTestClient and click Add
- Right-click on the project and choose Properties
- On the Build tab, put a check next to Register for COM interop
- Press CTRL+S to save your changes
- Close the Properties window
- Click on Class1.cs in the Solution Explorer
- Change the File Name to SalesFunctions.cs
- 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.
Import the necessary namespaces in the SalesFunctions class
using System.Runtime.InteropServices; using Microsoft.Win32;
Decorate the SalesFunctions class with the following attributes
namespace SalesFunctions { [Guid("7C69C8CF-747C-4855-B897-AF0710109816")] [ClassInterface(ClassInterfaceType.AutoDual)] [ComVisible(true)] public class SalesFunctions
- Replace the supplied Guid above with a new Guid
- Select Tools>>Create GUID
Select the 5th GUID Format and choose Copy
Figure 14
Create GUID dialog
- Select the [Guid("7C69C8CF-747C-4855-B897-AF0710109816")] statement
- Press CTRL+V to replace it with the new GUID
- Add the following functions to the SalesFunction class to handle COM registration and un-registration and the class constructor
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.
Add the necessary namespaces to SalesFunctions.cs
using System.ServiceModel; using System.Xml;
Add a method to the SalesFunctions class to create a new instance of the SaleObservationService
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); }
- Implement the GetSales function in the SalesFunctions class
public double GetSales(string category) { double result = 0.0; SaleObservationServiceClient client = GetClient(); result = client.CategorySales(category); client.Close(); return result; }
- Press F6 to build the project
Exercise 3 Verification
To verify your work, perform the following actions.
- Verify your WCF SaleObservationService from Exercise 1 is running.
- Open the Development Fabric UI by right-clicking on the Windows Azure icon in the system tray
Choose Show Development Fabric UI
Figure 15
Windows Azure context menu
Verify that the WCFSaleObservationService is running
Figure 16
Running WCFSaleObservationService service
- If it is not running, restart it by debugging the SaleObservationService project (from Exercise 1) in Visual Studio
- Open up MicrosoftExcel
- Activate the SalesFunctions add-in in Excel
- In Excel, select File>>Options
- In the Excel Options window click on Add-Ins
- Down near the bottom, select Excel Add-ins in the Manage drop-down box and
Click Go
Figure 17
Activating SalesFunction add-in
In the Add-Ins window, click Automation or click the Automation… button
Figure 18
AutomationAddin.MyFunctions selected for activation
Locate and select the SalesFunctions.SalesFunctions Automation server and click OK
Figure 19
SalesFunctions.SalesFunctions automation server
- Click OK to close the Add-Ins window
- Create a function using the user-defined function
In a blank worksheet, enter the values as shown in the screenshot below
Figure 20
Product Category values
- In cell C4, enter the formula =GetSales(B4)
- Fill the formula down to cell C9
(Optional) Format the cells using the comma style
Figure 21
Using the formula to get Sales Totals over Product Category values