Consuming Web Services in Excel 2007

  • Article

Summary:  Learn how to call the Live Search Web service from an Excel 2007 document-level customization and insert search results into a worksheet.

Office Visual How To

Applies to:  2007 Microsoft Office system, Microsoft Office Excel 2007, Microsoft Visual Studio 2008, and Microsoft Visual Studio 2008 Tools for Office

Joel Krist, iSoftStone

May 2009

Overview

Previous versions of Microsoft Office applications were able to access Web services using the Microsoft Office Web Services Toolkit. However, the Web Services Toolkit is not a Microsoft supported product.

Microsoft Visual Studio 2008 Tools for Office provides support for creating Office 2007 application managed code extensions that allow developers access to the full power of the Microsoft .NET framework, including the ability to easily make calls to Web services.

 See It Consuming Web Services video splash screen

Watch the Video

Length: 07:32 | Size: 8.79 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Sample Code

This Visual How-to article illustrates calling the Live Search Web service from an Excel 2007 workbook solution created with Visual Studio 2008. The workbook extension provides a Fluent UI customization that allows users to provide a search term and perform a search of the Live Search business phone book. Search results are inserted into the current worksheet.

To illustrate how to consume Web services in Excel 2007, this section walks through the following steps:

  1. Creating an Excel 2007 workbook solution in Visual Studio 2008.

  2. Adding a reference to the Live Search Web service.

  3. Adding code that calls the Web service and inserts search results into the worksheet.

  4. Adding an Office Fluent UI customization.

Creating an Excel 2007 Workbook Solution in Visual Studio 2008

To create an Excel 2007 Workbook Solution in Visual Studio 2008

  1. Start Microsoft Visual Studio 2008.

  2. From the Project menu, click File, then click New.

  3. In the New Project dialog box, click Visual Basic Office 2007 or Visual C# Office 2007 in the Project types pane.

  4. In the Templates pane, click Excel 2007 Workbook. Name the solution and project WebServiceConsumer and click OK.

    Figure 1. Creating the Solution

    Creating the Solution

  5. Visual Studio displays the Visual Studio Tools for Office Project Wizard. Click the option to create a new document of type Excel Workbook and name it WebServiceConsumer.

    Figure 2. Creating a New Workbook

    Creating a New Workbook

  6. Click OK to create the solution.

Adding a Reference to the Live Search Web Service

This Visual How To uses the Live Search Application Programming Interface (API) Web service that allows applications to submit queries to, and return results from, the Live Search Engine using the SOAP protocol. Each application that uses the Live Search API requires a unique Application ID, which is supplied by the Developer Provisioning System. The following steps for adding a reference to the Live Search web service require an Application ID. To create a new application ID go to the Live Search Developer Center and click Create an AppId.

To Add a Reference to the Windows Live Search Web Service

  1. From the Visual Studio Project menu, click Add Service Reference. The Add Service Reference dialog box is displayed.

    Figure 3. Add Service Reference

    Add Service Reference

  2. In the Add Service Reference dialog box, click Advanced. The Service Reference Settings dialog box is displayed.

    Figure 4. Service Reference Settings

    Service Reference Settings

  3. Click Add Web Reference. The Add Web Reference dialog box is displayed.

    Figure 5. Add Web Reference

    Add Web Reference

  4. Type the following URL in the URL text box where ApplicationId is the Live Search Application ID that was acquired using the process described earlier: http://api.search.live.net/search.wsdl?AppID=\<ApplicationId>

  5. Click Go to retrieve the Web service description.

    Figure 6. Live Search Web Service Description

    Live Search Web Service Description

  6. Leave the Web reference name as net.live.search.api. Click Add Reference to add the Web reference to the project.

Adding Code that Calls the Web Service and Inserts Results

To Add Code that Calls the Web Service and Inserts Results

  1. Right-click Sheet1 of the WebServiceConsumer.xlsx file and click View Code.

    Figure 7. View Worksheet Code

    View Worksheet Code

  2. In the Sheet1.cs or Sheet1.vb file, add the following using statement or imports statement.

    Imports Microsoft.VisualStudio.Tools.Applications.Runtime
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports Tools = Microsoft.Office.Tools.Excel
Imports LiveSearch = WebServiceConsumer.net.live.search.api

    using Tools = Microsoft.Office.Tools.Excel;
using LiveSearch = WebServiceConsumer.net.live.search.api;

  3. In the Sheet1.cs or Sheet.vb file define the DisplayWebServiceResults method by adding the following code to the Sheet1 class. Replace the ApplicationId placeholder in the code with the Live Search Application ID that was acquired using the process described earlier.

    Sub DisplayWebServiceResults(ByVal searchTerm As String)

    ' Clear the current display.
    Dim range As Tools.NamedRange = Me.Controls("Data")
    range.Clear()

    ' Call the Web service.
    Dim service As LiveSearch.LiveSearchService = _
        New LiveSearch.LiveSearchService()

    Dim request As LiveSearch.SearchRequest = _
        New LiveSearch.SearchRequest()

    request.AppId = <ApplicationId>
    request.Sources = New LiveSearch.SourceType() _
        {LiveSearch.SourceType.Phonebook}
    request.Query = searchTerm
    request.Market = "en-us"
    request.UILanguage = "en"

    ' Phonebook specific search settings.
    request.Phonebook = New LiveSearch.PhonebookRequest()
    request.Phonebook.FileType = "YP"   ' Business only.
    request.Phonebook.Count = 25
    request.Phonebook.CountSpecified = True

    Dim response As LiveSearch.SearchResponse = service.Search(request)

    If Not (response.Phonebook Is Nothing) And _
        (response.Phonebook.Results.Length >= 0) Then
        ' Set the range.
        range.RefersToR1C1 = String.Format("=R1C1:R{0}C5", _
            response.Phonebook.Results.Length)

        For x = 0 To response.Phonebook.Results.Length - 1
            Dim item As LiveSearch.PhonebookResult = _
                response.Phonebook.Results(x)

            ' Update data range, Excel uses 1 as the base for index.
            range.Cells(x + 1, 1).Value2 = item.Business
            range.Cells(x + 1, 2).Value2 = item.PhoneNumber
            range.Cells(x + 1, 3).Value2 = item.Address
            range.Cells(x + 1, 4).Value2 = item.City
            range.Cells(x + 1, 5).Value2 = item.StateOrProvince
        Next x
    End If
End Sub

    public void DisplayWebServiceResults(string searchTerm)
{
    object paramMissing = System.Type.Missing;

    // Clear the current display.
    Tools.NamedRange range = (Tools.NamedRange)this.Controls["Data"];
    range.Clear();

    // Call the Web service.
    LiveSearch.LiveSearchService service =
        new LiveSearch.LiveSearchService();

    LiveSearch.SearchRequest request =
        new LiveSearch.SearchRequest();
    request.AppId = <ApplicationId>;
    request.Sources =
        new LiveSearch.SourceType[]{ LiveSearch.SourceType.Phonebook };
    request.Query = searchTerm;
    request.Market = "en-us";
    request.UILanguage = "en";

    // Set Phonebook specific search settings.
    request.Phonebook = new LiveSearch.PhonebookRequest();
    request.Phonebook.FileType = "YP";// business only
    request.Phonebook.Count = 25;
    request.Phonebook.CountSpecified = true;

    LiveSearch.SearchResponse response = service.Search(request);

    if (response.Phonebook != null &&
        response.Phonebook.Results.Length >= 0)
    {
        // Set the range.
        range.RefersToR1C1 = String.Format("=R1C1:R{0}C5",
            response.Phonebook.Results.Length);

        for (int x = 0; x < response.Phonebook.Results.Length; x++)
        {
            LiveSearch.PhonebookResult item = 
                response.Phonebook.Results[x];

            // Update data range, Excel uses 1 as the base for index.
            ((Excel.Range)range.Cells[x + 1, 1]).Value2 =
                item.Business;
            ((Excel.Range)range.Cells[x + 1, 2]).Value2 =
                item.PhoneNumber;
            ((Excel.Range)range.Cells[x + 1, 3]).Value2 =
                item.Address;
            ((Excel.Range)range.Cells[x + 1, 4]).Value2 = item.City;
            ((Excel.Range)range.Cells[x + 1, 5]).Value2 =
                item.StateOrProvince;
        }
    }
}

  4. Add the following code to the body of the Sheet1_Startup method in the Sheet1.cs or Sheet1.vb file.

    Me.Controls.AddNamedRange(Me.Range("A1", "A5"), "Data")

    this.Controls.AddNamedRange(this.Range["A1", "A5"], "Data");

Adding an Office Fluent UI Customization

To Add an Office Fluent UI Customization

  1. Right-click the WebServiceConsumer project in the Visual Studio Solution Explorer.

    Click Add and then click New Item.

    If the Solution Explorer is not visible, click View, then click Solution Explorer.

  2. In the Add New Item dialog box, in the Categories window, click Office and then, in the Templates window, click Ribbon (Visual Designer). Name the Fluent UI source file WebServiceRibbon.cs or WebServiceRibbon.vb depending on the language you use and click Add.

    Figure 8. Add Ribbon Customization

    Add Ribbon Customization

  3. From the Office Ribbon Controls group of the Visual Studio Toolbox, drag an EditBox and Button control to the Ribbon designer surface.

    Figure 9. Add Controls to Ribbon

    Add Controls to Ribbon

  4. Edit the properties of the EditBox, Button and RibbonGroup controls and set them to the following values:

    EditBox – Name = SearchTermBox, Label = Search Term

    Button – Name = SearchButton, Label = Search

    Ribbon Group – Name = WebServiceGroup, Label = Web Service

    Figure 10. Ribbon Controls

    Ribbon Controls

  5. To add a button-click handler, double-click Search. Visual Studio opens the WebServiceRibbon.vb or WebServiceRibbon.cs file and displays the SearchButton_Click method. Add the following code to the body of the SearchButton_Click method.

    Globals.Sheet1.DisplayWebServiceResults(Me.SearchTermBox.Text)

    Globals.Sheet1.DisplayWebServiceResults(this.SearchTermBox.Text);

  6. Press Ctrl-F5 to build and run the solution. Visual Studio launches Excel 2007 with the WebServiceConsumer.xlsx workbook loaded. Click the Add-ins tab. The Web Service tab group is visible. Type a search term in the edit box and click Search. Excel queries the Live Search Web service and displays the results in the current worksheet.

    Figure 11. Excel Workbook

    Excel Workbook

Read It

With Visual Studio 2008 Tools for Office, you can easily create managed code extensions to Office 2007 applications that incorporate Web service functionality. This Visual How-to article illustrates how to call the Live Search Web service from an Excel 2007 document-level customization and insert search results into a worksheet.

Explore It