How To Build an ASP.NET 4.0 Web Service and Consume It With Excel 2007-Part 2

With a freshly built web service we can now have a bit of fun.

1. Create a new project utilizing the Visual Studio Tools for Office template for Excel 2007 Workbook:
   
   
2. Accept the default settings for Create a new document and click OK:
   
   Note: if you encounter errors while creating a new project it is most likely because the VSTO add-ins are not configured until first used.
   
   The quickest way to resolve the issue is to exit out of Visual Studio and use the right-click Run as administrator trick to launch with elevated permissions.
   
3. From the Solution Explorer, right-click on the References folder then choose Add Service Reference:
   
   
4. Type in the web address of the web service we just created:
   
   Hint: You may want to switch (Alt-Tab) over to the browser window where we tested the web service against IIS after deploying then copy the address from the address bar.
   
5. After pressing Go the deployed web service will be queried and the results displayed for confirmation:
   
   
6. With the Excel Workbook Designer displayed from within Visual Studio right-click anywhere on the worksheet and choose View Code:
   
   
7. Create a new method called DisplayCustomer containing the following code:

   public void DisplayCustomer(int customerKey)
   {
   // Call the Web service.
   using (var service = new
   Assignment3Service.WebServiceSoapClient())
   {
   var result = service.GetCustomerByKey(customerKey);
   if (result != null)
   {
   var items = result.Tables["Customer"]
   .Rows[0].ItemArray;
   var limit = items.GetLength(0);
   for (int i = 0; i < limit; i++)
   {
   var item = items[i];
   this.Cells[1, i + 1] = item;
   }
   }
   }
   }

   
   
8. Right-click on the Excel Workbook project and choose Add New Item:
   
   
9. Select Office under the Visual C# Installed Templates then choose Ribbon (Visual Designer) and click Add:
   
   
10. From the Toolbox drag an EditBox control then a Button control onto the designer surface:
    
    Resulting in the following:
    
    
11. Clean up the Labels to make the Ribbon more meaningful and useful:
    
    
    
12. The completed Ribbon should look similar to this:
    
    
13. Double-click on the Search button (text) on the Ribbon to jump to the event-handler section. Put the following code to call the add-in method DisplayCustomer previously created:
    

    private void btnSearch_Click(object sender, RibbonControlEventArgs e)
    {
    Globals.Sheet1.DisplayCustomer(int.Parse(CustomerKeyBox.Text));
    }

    
    
14. Notice as you start typing that Intellisense will provide filtered statement completion:
    
    
15. Here is the completed method:
    
    Pay attention to the name of the control (shown as “CustomerKeyBox” above) – this name must match the name you gave to the EditBox control immediately preceding this step.
    
16. Now press F5 to debug the solution. Doing so will launch Excel 2007 (or Excel 2010) and you’ll notice the Add-Ins tab of the Ribbon UI displays your custom fields:
    
    
17. Simply type in a customer number then click Search, the results should look something like this:
    

At this point we have used a custom add-in running on a client desktop to invoke a web service published and deployed in IIS which in turn connects to a SQL database to execute a query using the data supplied by the client (customer key value) and the results are returned and displayed in Excel.