Using a tool like Excel is a great way to present data to a user. With Visual Studio Tools for Office (VSTO), you can automate the retrieval of that data, which can be from any location, even from a server, using a technology like Web services. You can also direct where the data will display in the workbook. In this article, I explain how to insert the data retrieved from a Web service method into an Excel worksheet.

Retrieve data for the workbook using Web services

To demonstrate how you can use VSTO to create an interactive application, I'll assume you have to access server-based data to populate a workbook. To access the server, you'll create a Web service to access the server-based data, then return that data as an XML string. You'll see that by returning the data back to VSTO as XML, you can take advantage of some of the new XML features in Excel. But let's not get ahead of ourselves here; you have to first build the Web service.

To build the Web service, create a Visual Studio .NET Web services project, specifically, a Web service that returns a list of Customers, called the CustomerService project. Because I'll create this Web service project and using it with the ExcelVSTO project from my last article (http://Advisor.com/doc/16502), I'll create a blank Visual Studio Solution first, then create the new Web service project. Next, I'll add the ExcelVSTO project to this solution.

To create the blank solution, select File > New > Blank Solution from the Visual Studio .NET menu. When the New Project dialog appears, type in the name of this new Solution (in this example, I'll call it VSTOWebServiceDemo). Clicking on OK creates a new, blank solution. Next, right-click on the new solution and select Add > New Project. The Add New Project dialog appears. To create a Web service project, select Visual Basic Projects in the Project Types list and ASP.NET Web service in the Templates list. Change the location to http://localhost/CustomerService, and click on OK (figure 1). Visual Studio .NET now creates the new Web service and makes a call Internet Information Services (IIS) to create a new virtual directory, because all Web services are hosted as ASP.NET Web sites. After Visual Studio .NET creates the new project, you have one sample Web service file called Service1.asmx. Because I want to call my Web service CustomerService, I'll rename both the file name and the Class name within the file to CustomerService.

Write the GetCustomers WebMethod

Now that you've created your CustomerService Web service, it's time to create your WebMethod. Fortunately, there's a template WebMethod commented out in the WebService file, so you'll simply modify that template. As I mentioned, you want to return XML back to the client as a string. You could retrieve the data from SQL Server and format the return data as XML, and although that would be easy, that's not the focus of this article. Instead, you'll simply return XML that you read from a file, using this Web Method:

    <WebMethod()> _
     Public Function GetCustomers() _
     As String
        Dim xd As New XmlDocument
        xd.Load("http://localhost/CustomerService/_
           Customers.xml")
        Return xd.InnerXml
    End Function

This Web Method uses the System.XML namespace methods to load an XML file from the file system into an XmlDocument variable. The Web Method then returns a string of the XmlDocument's InnerXML property back to the client. Figure 2 shows the completed Web service code, and listing 1 lists the contents of the file.

Listing 1: Contents of the Customers.xml file -- This is the XML file used as data to send back from the GetCustomers WebMethod.

<Customers>
  <Customer>
    <Number>123</Number>
    <Name>The Information Management Group</Name>
  </Customer>
  <Customer>
    <Number>456</Number>
    <Name>Tenibac Corporation</Name>
  </Customer>
  <Customer>
    <Number>789</Number>
    <Name>The Bismarck Group</Name>
  </Customer>
</Customers>

Add the Web Service Reference

Now that you've created the Web service and it's ready to use, let's add the ExcelVSTO project you've built in this article series to the VSTOWebServiceDemo Solution. To do that, right-click on the Solution in the Solution Explorer and select Add > Existing Project, find the ExcelVSTO project (mine is in c:\Advisor), and select the project. You'll now have two projects under the VSTOWebServiceDemo project. Right-click on the ExcelVSTO project and select Set as startup project. You're now ready to write code to access and display the Web service data in your Excel spreadsheet.

To use the CustomerService Web service, you must create a Web reference by right-clicking on the references item in the Solution Explorer and selecting Add Web Reference. The dialog that displays (figure 3), type http://localhost/CustomerService/ CustomerService.asmx in the URL box and click on the button to the right. In the Web Reference name, type Customer. When the dialog in figure 4 displays, click on Add Reference, and you can now code to the Web service.

Coding to the Web service in VSTO

In the ExcelVSTO project, you can now use the newly created Customer Web reference to call the GetCustomers method and the XML data returned to display in a list on the worksheet. Because you're in VSTO and, thus, the .NET Framework, you can take advantage of any .NET Framework call. Specifically, you can use the System.Xml namespace to create an XmlDocument object, and then populate the XmlDocument object with the return XML string from the Web service call. Here's the code to accomplish this:

  Dim ws As Customer.CustomerService = _
     New Customer.CustomerService
  Dim xd As XmlDocument = _
     New XmlDocument
  xd.InnerXml = ws.GetCustomers()

By simply setting the InnerXml property of the XmlDocument object, you now have a fully ready XmlDocument object. The next step is to create an XmlNodeList that iterates over the detail nodes in the XML file; in this case, it's the Customers node below the Customers root node (listing 1). From the XmlNodeList object, an enumerator is retrieved by calling the GetEnumerator method of the XmlNodeList object to let you write a while loop that iterates through each of the Customer nodes. Here'ss the code to do this:

Dim elemList As XmlNodeList = _
   xd.SelectNodes("/Customers/Customer")
Dim iEnum As IEnumerator = _
   elemList.GetEnumerator()

Finally, a while loop iterates through each node and writes a row of date, one column at a time:

Dim i As Integer = 2
While iEnum.MoveNext()

 ThisWorkbook.ActiveSheet.Cells(1, _
   5).Value2 = "Number"
 ThisWorkbook.ActiveSheet.Cells(1, _
   6).Value2 = "Name"

 Dim c1 As Excel.Range
 c1 = CType(sheet.Cells(i, _
   5), Excel.Range)
 c1.Value2 =  _
   (CType(iEnum.Current, _
   XmlNode)).SelectSingleNode(" _
   Number").InnerText

 Dim c2 As Excel.Range
 c2 = CType(sheet.Cells(i, 6), _
   Excel.Range)
 c2.Value2 = (CType(iEnum.Current,
 XmlNode)).SelectSingleNode("_
   Name").InnerText

 i = i + 1

End While

I have created a protected subroutine called ListCustomers that contains the code the cbSay button Click event calls. Figure 5 displays the finished code.


Run the code

With all the code in place, it's time to run the application. Select Debug > Start from the Visual Studio .NET menu, and the Visual Studio runtime will launch the ExcelVSTO.xls Excel workbook. If you click on the "Say Hi" button, the VSTO code for the button's Click event of the button will be called to retrieve the customer list from the server using the Web server, then the code in ListCustomers will iterate through the node list and display all the data in the sheet (figure 6).

Know your options

Although you accomplished the goal of calling a Web service to retrieve XML data, the actual writing of the data seemed way too difficult. Although you did take advantage of the Excel Object model in this approach, and it did get the job done, you had to 1) create am XML Document object, the 2) populate the XmlDocument object, 3) retrieve a NodeList 4) retrieve the enumerator from the node list, 5) create a while loop on the enumerator, 6) for each column, create a range object, select a single node, and assign it to the range object. Wow, now that's a lot of work; there must be a different (and better) way. Of course there is. The approach you used in this article was the only way to populate the spreadsheet with data, until Office 2003 shipped with it’s new XML Schema support. In my next article, I show you that different and better way, using some of Excel's new capabilities to assign XML Schemas to a workbook and then to "map" nodes in the schema to areas of the workbook. This new and better way reduces all the code to display the data in the spreadsheet that I showed you in this article to one line of code. Don't believe me? Stay tuned for my next article to see how it is done.

This article was originally published in the 2005 Week 19 issue of ADVISOR GUIDE to MICROSOFT VISUAL BASIC .NET. Subscribers can get more Advisor Answers, Advisor Tips, and how-to advice on succeeding with Microsoft .NET technology at http://msvb.advisorguide.com.