REST Web services
SOAP Web services
Excel Object Model
In Chapter 7, “Using XML data in spreadsheets”, on page 132, we learned how to share XML data between Excel worksheets and local files. But there are other sources for XML data and one of the most important is Web services. Not just public services offered to all Web users, but services your company might deploy to facilitate sharing of its own data among its employees.
In this chapter we’ll show you how to share data between Excel worksheets and Web services by writing macros or Excel-based code. SOAP Web services are accessed with essentially the same techniques that we employed for Word in 6.2, “SOAP Web services”, on page 121, but incorporating the results is somewhat different.
Skills required
What we covered in the previous chapter on Excel, plus the above-mentioned material in the Word chapter, and the basics of Web services (Chapter 19, “Web services introduction”, on page 414 and Chapter 23, “Web services technologies”, on page 484).
Worldwide Widget Corporation is known for its award-winning pension plan. Each month, N. Ron Ponzi, the plan’s investment advisor, analyzes dozens of securities before investing 100% of the pension fund in Worldwide’s own stock.
Ron would like us to create an Excel worksheet to help him analyze stock price information. He wants to be able to enter a stock symbol, month and year, and retrieve the quotes for that month into the worksheet. These quotes can easily be retrieved from a Web service.
An example of the stock quote worksheet is shown in Figure 8-1.
Importing the quote data into Excel allows Ron to take advantage of Excel’s data analysis features, creating charts and graphs of the data such as the one shown in Figure 8-2.
Our solution requires several steps:
Save a sample of the Web service output as a local XML file.
Create a worksheet that is mapped to the schema that Excel infers from the sample service output.
Write some Visual Basic for Applications (VBA) code that accesses the Web service and reimports the map with the XML data returned by the Web service. This code will be executed every time the user clicks a “Refresh” button that we will add to the worksheet.
To access the data, we will use the Web service located at http://www.xignite.com/xquotes.asmx. This Web service consists of a set of operations that retrieve stock quote data. If you browse the above URL, you will see a list of those operations. We will use one called GetQuotesHistorical.
Selecting that operation will bring up a page that describes the operation and provides examples. About half way down the page is the syntax for calling the service using the HTTP GET method, which is a REST interface. It says:
Example 8-1. Syntax for Web service request
GET http://www.xignite.com/xquotes.asmx/GetQuotesHistorical?
Symbol=string&Month=string&Year=string HTTP/1.1
Host: www.xignite.com
As we are using a REST interface to this Web service, rather than its SOAP interface, we can invoke the service simply by specifying a URL that includes the necessary parameters. For example, if we want the quotes for Microsoft Corporation, for March of 2003, we can specify the following URL:
Example 8-2. Web service request URL for MSFT data
http://www.xignite.com/xquotes.asmx/GetQuotesHistorical? Symbol=MSFT&Month=3&Year=2003
The result will be returned as an XML document, as shown on that page. An example is shown in Example 8-3.
Example 8-3. Sample output of the stock quote Web service
<?xml version="1.0" encoding="utf-8"?>
<ArrayOfHistoricalQuote
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.xignite.com/services/">
<HistoricalQuote>
<Outcome>Success</Outcome>
<Date>3/31/2003</Date>
<Open>24.25</Open>
<High>24.71</High>
<Low>24.01</Low>
<Last>24.21</Last>
<Volume>71384000</Volume>
<PercentChange>-0.165</PercentChange>
<SplitRatio>1</SplitRatio>
<OpenAdjusted>24.25</OpenAdjusted>
<HighAdjusted>24.71</HighAdjusted>
<LowAdjusted>24.01</LowAdjusted>
<LastAdjusted>24.21</LastAdjusted>
<ChangeAdjusted>-0.04</ChangeAdjusted>
<VolumeAdjusted>71384000</VolumeAdjusted>
<Change>-0.04</Change>
</HistoricalQuote>
<HistoricalQuote>
<Outcome>Success</Outcome>
<Date>3/28/2003</Date>
<Open>24.67</Open>
<!-- ... -->
</HistoricalQuote>
</ArrayOfHistoricalQuote>
You can test this out yourself by entering the URL in Example 8-2 into your browser and viewing the source.
To create the XML map in the worksheet, we will need a sample of the XML data that will be returned from the Web service. This can be obtained by executing the Web service in a browser and copying the result, or by copying the sample output from the page describing the Web service. We have provided sample output in the file sampledata.xml. It does not matter which ticker symbol or month you choose right now; we simply care about the structure.
To set up the map:
Open the worksheet
stock quotes start.xls. This is a mostly empty worksheet.On the Data menu, point to XML, then click XML Source. This brings up the XML Source task pane.
Click XML Maps to bring up a list of XML maps in the worksheet (currently there are none).
Click Add and select
sampledata.xmlfrom the list. Alternatively, you can enter a URL request to execute the Web service and return a result; e.g. the URL in Example 8-2.Click Open.
Click OK when Excel warns you that it will infer a schema, then OK again to return to the main dialog.
This brings up the XML Source task pane. From it, drag the
nsl:HistoricalQuoteelement type to the top left corner of the location where you want to put the quote data in the worksheet; for example, cell A7.
You can customize the map to meet your needs. For example, you can change the column headers, or delete columns that contain data that you do not need (for example, Outcome). You now have a map that will import the data returned by the GetQuotesHistorical operation of the Web service.
Because the user wants to pass different parameters to the Web service each time he refreshes the data in the map, he cannot simply use the Refresh XML Data command on the XML submenu. He could use the Import command on the XML submenu, but only if he were willing to enter a new URL (with different parameters) for each import. However, N. Ron Ponzi felt (not unreasonably) that constructing a URL would be cumbersome and error-prone.
Instead, we decided to write a macro that constructed the URL from parameters entered in the worksheet. Clicking a “Refresh” button would call the macro, which would invoke the Web service with the constructed URL and refresh the data.
To create the button and macro:
Right-click the toolbar at the top of the window and click Control Toolbox. This will display the small Control Toolbox toolbar shown in Figure 8-3.
Click the picture that looks like a button.
Click the location in the worksheet where you want the button placed.
Select the button and right-click. On the shortcut menu, click Properties.
Change both the (Name) and the Caption properties to
Refresh.Close the Properties dialog.
Right-click the button again. On the context menu, click View Code. This will bring up the Visual Basic Editor with the cursor positioned in a function that will be executed each time the button is clicked. This is shown in Figure 8-4.
The code entered into the Visual Basic Editor should look like that shown in Example 8-4. You can either type it in or insert it from the example file refresh_quotes.txt.
Example 8-4. Function to import from a constructed URL
Private Sub Refresh_Click()
Dim theURL, ticker, month, year, beginningOFURL As String
Dim theMap As XmlMap
Set theMap = ActiveWorkbook.XmlMaps(1)
beginningOFURL =
"http://www.xignite.com/xquotes.asmx/GetQuoteHistorical?Symbol="
ticker = Range("B3").Text
month = Range("E3").Text
year = Range("G3").Text
theURL = beginningOFURL+ticker+"&Month="+month+"&Year="+year
theMap.Import (theURL)
End Sub
The code takes the parameters entered by the user from the worksheet, strings them together to construct the URL, and then refreshes the data in the map with the results.
Lines 3-5 of the example declare the variables and assign the appropriate (first and only) map to the variable
theMap.Lines 7 to 12 concatenate the various parts of the URL based on the contents of the cells that contain the parameters.
Line 14 performs the actual import, using the constructed URL.
To test your solution, you must first exit design mode by clicking the top left icon on the Control Toolbox toolbar.
A complete working version of this example can be found in the sample file stock quotes.xls.
Ellen would like to extend the XML expense report system for foreign travel and thinks that automatic capture of currency conversion rates would be a requirement. Fortunately, there is a Web service available for the purpose.
We will create a simple worksheet to develop and test the use of the Web service. Eventually, we can move the code to the expense report worksheet.
For pedagogical reasons we will use the SOAP interface to the service (it has REST interfaces as well) and we will again use the Office Web Services Toolkit to hide the complexity of SOAP (as we did in 6.2, “SOAP Web services”, on page 121).
Also for pedagogical reasons we will use code to update individual cells instead of simply creating an XML map and importing the Web service output. Using code will allow us to see how the toolkit returns the data.[1]
The worksheet is shown in Figure 8-5. It has cells for the parameters – the “from” currency (“Expense Currency”) and the “to” currency (“Expense Report Currency”) – and a Refresh button that retrieves the exchange rate from the Web service.
To create this example, open the worksheet currency converter start.xls. It is a skeleton worksheet to which we will add the currency lookup capability.
The Web service is located at: http://www.webservicex.net/CurrencyConvertor.asmx. It has one operation, named ConversionRate, that will provide the current exchange rate, given a “from” currency and a “to” currency.
If you browse the above URL, you will see a brief description of the operation. Clicking on ConversionRate will bring up a page that describes the operation fully and provides examples. Although the page shows the input and output messages, we do not need to concern ourselves with them because of our use of the Office Web Services Toolkit.
Generating the necessary classes in Excel is identical to the process described in 6.2.3, “Using the Web Services Reference Tool”, on page 126. We execute that process, this time using the URL: http://www.webservicex.net/CurrencyConvertor.asmx?wsdl The name of the Web service and its methods is shown on the right side of the dialog in Figure 8-6.
The procedure generates a single class named clsws_CurrencyConvertor which allows access to the ConversionRate operation.
As in the previous example, we will use a Refresh button to update the data in the worksheet. Follow the instructions in 8.1.3, “Refreshing the data”, on page 167 to create the Refresh button. This time, use the code shown in Example 8-5, which can be copied from the example file refresh_currency.txt.
Example 8-5. Refresh code for symbol lookup example
Private Sub Refresh_Click()
Dim objLookup As New clsws_CurrencyConvertor
Dim fromCurrency As String
Dim toCurrency As String
Dim rate As Double
fromCurrency = Range("B5").Text
toCurrency = Range("B6").Text
Set rateRange = Range("B8")
rate = objLookup.wsm_ConversionRate(fromCurrency, toCurrency)
If (rate) Then
rateRange.Value = rate
Else
rateRange.Value = 0
End If
End Sub
Line 3 creates a new instance of the generated class
clsws_CurrencyConvertor.Lines 5 through 7 declare the other variables used.
Lines 8 and 9 assign the text entered by the user in cells B5 and B6 to the variables
fromCurrencyandtoCurrency.Line 10 sets the range to be filled in, namely cell B8.
Line 12 calls the method
wsm_ConversionRateand assigns the result to the variablerate. Ifrateis not null, the value is assigned to the appropriate cell.
Note that, unlike the previous example, there is no XML Map involved. The data returned from the Web service is placed into the cells by the code, rather than through XML import. This technique allows more control over which data is mapped and where it is placed in the worksheet.
A complete working version of this example can be found in the sample file currency converter.xls.
There are several approaches to accessing Web services with Excel. The two scenarios in this chapter are just examples of what is possible. You could, for example, combine those two, using the Office Web Services toolkit with an XML Map, or using REST and manually parsing the XML result and placing the contents into the worksheet.
If you will be calling the Web service with no parameters, or with the same parameters every time, your task is simplified. You can simply create an XML map for the output from the appropriate URL and use the Refresh XML Data command on the XML submenu to refresh the data.
For example, if the Web service is designed to return current information on a fixed set of market indices, you can create a map to the URL: http://www.xignite.com/xquotes.asmx?GetIndices. This approach does not require any code to be written.
You can write an application outside Excel that retrieves information from a Web service and writes it to an XML document on the file system. This application could be run on a daily basis, for example. The map in the Excel worksheet could then be set up to retrieve data from the local document.
Finally, you can also use an XML Map in a worksheet to post an XML document to a Web service. For example a Web service could be created for accepting expense report submissions, and users could create their expense reports in Excel and submit them to the Web service for approval.
The ability to write code that is integrated with Excel means that the possibilities are almost endless. In this chapter, we used examples written in Visual Basic for Applications (VBA). However, you can also write separate applications in Visual Basic, C# or C++ that are integrated with Excel.
The Excel Object Model exists to allow programmers to write code to perform Excel-related functions. The Object Model includes classes for such things as worksheets, cells, cell ranges, charts, and many other Excel constructs. These classes allow you to write code that inserts, modifies, formats or deletes data in the cells, creates new worksheets, adds charts, and performs almost any other function that can be performed by an Excel user.
In Office 2003 the Excel Object Model was extended to support XML. It now has classes for things like the XmlMap object.
Complete coverage of the Excel Object Model is outside the scope of this book, but this section will provide some pointers on understanding the XML-specific aspects of it.
Unlike the Word or InfoPath object models, which represent the underlying document as XML, the Excel object model does not treat the underlying worksheet as an XML document. Instead, it keeps track, by means of XPath expressions, of the relationship between cells and their locations in the mapped schema. When the map is imported or exported, Excel performs the appropriate transformation based on the XPath expressions.
Two terms are important to understanding the XML Object Model: maps and lists. Maps were introduced in Chapter 7, “Using XML data in spreadsheets”, on page 132. A map is the relationship between an Excel workbook and a schema. Our stock quote example has a single map, which ties stock quotes.xls to the schema of the documents returned by the stock quote Web service. It is possible for a single workbook to have multiple maps, with different schema definitions. It is also possible for a single map to have multiple schemas associated with it, if the “main” schema document includes others.
Lists represent the repeating element types of the map. For example, our stock quote example has one list, which represents the repeating set of stock quotes. This list has multiple columns associated with it.
Maps in the object model are represented by the XmlMap class. You can retrieve the maps associated with a workbook through the XmlMaps collection. For example, the following code will return the first map of the active workbook:
You can then use the XmlMap object to import or export the XML data. For example, you can export the data to the XML file C:expenses.xml with:
You can also use the ExportXML method to export the XML data as a string. For example, you can assign a string containing the XML data to the variable xmlString with:
Example 8-8. Assign XML data string to variable
Dim xmlString as String firstMap.ExportXML(xmlString)
To import, you can use the Import method. For example, to import the XML data from the file C:expenses.xml:
In addition to these methods, the XmlMap class has a number of properties that allow you to set various options on the map; for example:
AdjustColumnWidth
indicates whether to adjust column widths to fit the data when importing.
AppendOnImport
specifies whether to append to the existing data in the map or overwrite it.
Lists, which represent repeating XML data, are represented by the ListObject class. For example, our stock quotes example has one list that represents all the repeating stock quote information. The relationship between lists, columns and their maps and XPath expressions is depicted in Figure 8-7.
You can retrieve the lists associated with a worksheet through the ListObjects collection. A ListObject may be associated with one or more ListColumn objects. In our stock quote example, the ListObject is associated with eight ListColumn objects, one for each column of data.
Each list column has an XPath expression associated with it. In our stock quotes example, the first column in the list is associated with the XPath expression:
Individual cells can have XPath expressions associated with them as well. In our expense report example in Chapter 7, “Using XML data in spreadsheets”, on page 132, there were several cells that contained non-repeating data, such as the employee name and the charge code. These cells have their own XPath expressions associated with them to locate their data. For example, the following code will display a dialog containing the XPath expression associated with cell E5:
Example 8-11. Display associated with XPath expression
Set myCell = Range("E5")
MsgBox (myCell.XPath)
It is also possible to determine which range of cells is mapped to the data located by a given XPath. This is accomplished using the XmlDataQuery method of the Worksheet class. For example, the following code will return a range representing the first column of the stock quotes worksheet.
Example 8-12. Return cells mapped to located data
Set myRange=myWorkSheet.XmlDataQuery(
"ArrayOfHistoricalQuote/HistoricalQuote/Date")
This section has given you a taste of the capabilities of the Excel Object Model. Basically, any XML-related task that can be performed manually in Excel can also be performed using the object model, and then some. This includes creating, modifying and deleting maps, setting all the XML-related options, and performing imports and exports.
The Excel Object Model can be viewed and navigated using Object Browser in the Visual Basic Editor.
[1] In real life we would only use code if we needed more control over the placement of the data in the worksheet; for example, for complex structures that we needed to flatten or rearrange in some way.