Friday, December 24, 2010

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:
    2010-12-05_091921
  2. Accept the default settings for Create a new document and click OK:
    2010-12-05_091937
    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.
    2010-12-05_091947
    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:
    2010-12-05_092357
  4. Type in the web address of the web service we just created:
    2010-12-05_092441
    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:
    2010-12-05_092507
  6. With the Excel Workbook Designer displayed from within Visual Studio right-click anywhere on the worksheet and choose View Code:
    2010-12-05_092756
  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:
    2010-12-05_093805
  9. Select Office under the Visual C# Installed Templates then choose Ribbon (Visual Designer) and click Add:
    2010-12-05_093910
  10. From the Toolbox drag an EditBox control then a Button control onto the designer surface:
    2010-12-05_094125
    Resulting in the following:
    2010-12-05_094212
  11. Clean up the Labels to make the Ribbon more meaningful and useful:
    2010-12-05_094313
    2010-12-05_094332
  12. The completed Ribbon should look similar to this:
    2010-12-05_094451
  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:
    2010-12-05_094538
  15. Here is the completed method:
    2010-12-05_094721
    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:
    2010-12-05_094814
  17. Simply type in a customer number then click Search, the results should look something like this:
    2010-12-05_110725
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.

No comments:

Post a Comment