XML Web Service-Enabled Office Documents
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Chris Lovett
Siew-Moi Khor
Microsoft Corporation
First published: March 2001
Article and samples updated: November 2001
Applies to:
Microsoft® Excel 2002
Download Odc_xlws.exe.
Summary: This article describes an example of how you can integrate XML Web services into Microsoft Excel 2002. (12 printed pages)
Contents
Introduction
How Does All of This Work?
Catalog Web Service
Send Button
Try It Out
Running the Sample on ASP.NET or the .NET Framework SDK
Running the Sample Without Either ASP.NET or the .NET Framework SDK
Code to Connect to the Access Northwind Database Instead of SQL Server
Next Steps
Introduction
Are you ready for a marriage of Microsoft® Office XP and XML Web services? In a networked world of business-to-business (B2B) e-commerce, why not deliver the power of XML Web services to end users by integrating business process workflow right into their desktop computers?
What are we talking about? Take a look at the Microsoft Excel 2002 spreadsheet in Figure 1.
.gif "Click here to see larger image")
Figure 1. XML Web services-enabled Excel spreadsheet (click image to see larger picture)
This is not an ordinary spreadsheet. It uses Universal Description, Discovery, and Integration (UDDI) (http://www.uddi.org/) to find company addresses and an XML Web service called the Catalog Web service to find product information. It also does an XML transform on the XML spreadsheet format to generate a RosettaNet (http://www.rosettanet.org/rosettanet/Rooms/DisplayPages/LayoutInitial) PIP3A4 Purchase Order Request format when you click the Send button.
When you type in the name of the company that you are purchasing from, and then click the Find button, some Microsoft® Visual Basic® for Applications (VBA) code behind the spreadsheet makes a UDDI call and fills out the rest of the address section for you. For example, in the Purchase From field, type Microsoft, and click Find. You should see the following in the Purchase From field:
.gif "Aa140065.odc_xlws02(en-us,office.10).gif")
Figure 2. Purchase From field
When you type in a quantity of, for example, 23, and then the term Pear in the Description field, then press TAB, some VBA code queries the Catalog Web service using SOAP to see if it can find a matching product. Then the Catalog Web service fills out the details. In this example, the Catalog Web service connects to the Northwind database, which returns the following:
.gif "Click here to see larger image")
Figure 3. Detailed look at the order portion of the spreadsheet (click image to see larger picture)
It has also filled out the Description field and turned it into a link, which will take you to an HTML page that tells you all about that particular product if you click on it.
If more than one product is found and none of them matches exactly what you typed in the Description field, a drop down list of choices is provided. For example, if you type tofu, you will see the following choices:
Figure 4. Example of multiple choices provided when an exact match is not found
When you select one of these choices, then tab to the SKU field, the specific corresponding details for the SKU, Unit Price, Package, and Total fields will be automatically filled. When you have finished the purchase order and are ready to place the order, click the Send button. The RosettaNet PIP3A4 XML Purchase Order format is generated, and the order is sent.
How Does All of This Work?
You can browse the VBA code behind the spreadsheet by going to the Tools menu, pointing to Macro, and clicking Visual Basic Editor.
There is a bit of code behind the ThisWorkbook module that reacts to changes in the spreadsheet. In particular:
- Workbook_SheetChange event
This event clears out a line item when you delete the description in the Description field. - Workbook_SheetSelectionChange event
This event calls the FindProduct method when you tab out of the Description field into the SKU field. - FindProduct method
If the FindProduct method returns an XMLNode object, the relevant fields are pulled out of that node to populate the rest of the line item details.
In the UDDI module, how the UDDI find_business call works has been discussed in detailed in Chris Lovett's article, UDDI: An XML Web Service. In this particular example, if a business is found, the addressLines node in the /businessInfo/contacts/contact/address/ part of the UDDI response is used to populate the Purchase From spreadsheet field.
Catalog Web Service
The FindProduct function in the Catalogs module calls the Catalog Service URL with a url parameter containing the search term to lookup. It expects to get a SOAP response back and first checks to see if it matches /Envelope/Body/Fault. If it is not a Fault, it proceeds to open <CatalogQueryResult>, checking to see if the ProductName attribute in any returned items match the given term.
It also builds the drop-down list of choices further down the purchase order page, outside of the visible area (cells A40 to A50). To see how the drop-down list works, first turn off protection on the spreadsheet by going to the Tools menu, pointing to Protection, and then clicking Unprotect Sheet.
Tab to the Description field. Next, go to the Data menu and click Validation to display the Data Validation dialog box. On the Settings tab in the Validation criteria area, the Allow list is set to List, and the Source list is set to =$A$40:$A$50. What this does (if, for example you type lager, then tab to the SKU field) is give you two choices listed in cells A40 and A41.
The Catalog Web service is very simple. The .aspx entry point simply creates a CatalogSearch object, which is defined in search.cs, and calls the Execute method, passing the HttpResponse object output stream as follows:
<%@Language="C#" src="search.cs" Debug="true" %>
<%
Response.ContentType = "text/xml";
string term = Request.QueryString["term"];
if (term != null) {
CatalogSearch s = new CatalogSearch(term);
s.execute(output);
}
else {
Response.Write("<Empty/>");
}
%>
The Execute method is where the fun begins. This is a very simple SQL Managed Provider code wrapped in an XmlTextWriter object that returns the specific fields from the SQL SELECT statement. So it is basically a while loop through the DataReader object, writing to the XmlTextWriter object as follows:
public void Execute(TextWriter stm)
{
XmlTextWriter xw = new XmlTextWriter(stm);
xw.WriteStartElement("Envelope", "http://schemas..../envelope/");
xw.WriteStartElement("Body", "http://schemas..../envelope/");
try {
String const =
"server=localhost;uid=user_name;pwd=your_password;database=Northwind";
SQLConnection con = new SQLConnection(constr);
con.Open();
IDataReader reader;
String query = "SELECT ProductName,UnitPrice,QuantityPerUnit," +
"SupplierID,ProductID FROM Products WHERE " +
"ProductName LIKE '%" + term + "%'";
SQLCommand cmd = new SQLCommand(query, con);
cmd.Execute(out reader);
string funNamespace = "urn:schemas-b2b-fun:catalogs";
xw.WriteStartElement("CatalogQueryResult", funNamespace);
while (reader.Read())
{
xw.WriteStartElement("item");
xw.WriteAttribute("ProductName", reader.GetString(0));
xw.WriteAttrDecimal("UnitPrice", reader.GetDecimal(1));
xw.WriteAttribute("UnitOfMeasure", reader.GetString(2));
xw.WriteAttribute("SKU", "S"+reader.GetInt32(3)+
"-P"+reader.GetInt32(4));
xw.WriteEndElement();
}
xw.WriteEndElement();
con.Close();
}
catch (Exception e) {
xw.WriteStartElement("Fault");
xw.WriteElementString("faultcode","500");
xw.WriteElementString("faultstring",e.ToString());
xw.WriteEndElement();
}
xw.WriteEndElement();
xw.WriteEndElement();
xw.Close();
}
The URL http://localhost/catalog/search.aspx?term=tofu returns the following result:
<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<CatalogQueryResult xmlns="urn:schemas-b2b-fun:catalogs">
<item ProductName="Tofu" UnitPrice="23.25"
UnitOfMeasure="40 - 100 g pkgs." SKU="S6-P14"/>
<item ProductName="Longlife Tofu" UnitPrice="10"
UnitOfMeasure="5 kg pkg." SKU="S4-P74"/>
</CatalogQueryResult>
</Body>
</Envelope>
This is the most efficient way to get XML out of Microsoft® SQL Server™ using the .NET Framework. With a very rough measurement, about 80 to 90 of these results per second can be obtained on a Dell PowerEdge 2400.
Send Button
The SendOrder function loads an XML document from an XML representation of a selected range of cells in the spreadsheet. This is done with the following lines of VBA code:
With ActiveSheet
Set sourcexml = New MSXML.DOMDocument
sourcexml.loadXML _
.Range("B1:N34").value(xlRangeValueXMLSpreadsheet)
End With
This returns a large piece of XML that fully describes everything about that range of cells in the spreadsheet. The following is a snippet from the piece of XML:
<Workbook>
<Worksheet>
<Table>
<Row>
<Cell ss:StyleID="s23">
<Data ss:Type="Number">23</Data>
<NamedCell ss:Name="Item"/>
</Cell>
<Cell ss:MergeAcross="4" ss:StyleID="m31209522"
ss: HREF =
"http://eshop.msn.com/category.htm?catId=170">
<Data ss:Type="String">Uncle Bob's Organic Dried
Pears</Data>
</Cell>
<Cell ss:StyleID="s52">
<Data ss:Type="String">S3-P7</Data>
</Cell>
<Cell ss:StyleID="s26">
<Data ss:Type="Number">30</Data>
<NamedCell ss:Name="UnitPrice"/>
</Cell>
<Cell ss:StyleID="s27">
<Data ss:Type="String">12 - 1 lb pkgs.</Data>
</Cell>
<Cell ss:StyleID="s37">
<Data ss:Type="Number">690</Data>
</Cell>
<Cell ss:StyleID="s49"/>
</Row>
</Table>
</Worksheet>
</Workbook>
Then XSL is used to turn this into the following format:
<PurchaseOrder xmlns="http://www.rosettanet.org">
<deliverTo>
<PhysicalAddress>
<cityName>Seattle, WA, USA 98111</cityName>
<addressLine1>Airport Chocolates</addressLine1>
<addressLine2>2711 Alaskan Way</addressLine2>
<regionName>USA</regionName>
</PhysicalAddress>
</deliverTo>
<ProductLineItem>
<ProductQuantity>23</ProductQuantity>
<productUnit>
<ProductPackageDescription>
<ProductIdentification>
<GlobalProductIdentifier>S3-
P7</GlobalProductIdentifier>
</ProductIdentification>
</ProductPackageDescription>
</productUnit>
<Description>Uncle Bob's Organic Dried Pears</Description>
<requestedPrice>
<FinancialAmount>
<GlobalCurrencyCode>USD</GlobalCurrencyCode>
<MonetaryAmount>30</MonetaryAmount>
</FinancialAmount>
</requestedPrice>
</ProductLineItem>
<thisDocumentGenerationDateTime>
<DateTimeStamp>2001-10-08T00:00:00.000</DateTimeStamp>
</thisDocumentGenerationDateTime>
</PurchaseOrder>
**Note **This is probably not a technically complete request according to the Rosetta PIP3A4 Purchase Order Request specification, but you get the idea.
The trick to making this transformation somewhat robust is to name the important cells from which we want to pull the data. This is done with the following style of XPath expression in the XSLT transform:
select="/Workbook/Worksheet/Table/Row/Cell[NamedCell[@ss:Name='City']]
This particular expression finds the cell with the name City. The rest of the stylesheet is straightforward. See the XLToPO.xsl file in the download for additional information.
Try It Out
We have put together two samples for you to try out. One requires either ASP.NET or the .NET Framework SDK to run. The other does not.
Running the Sample on ASP.NET or the .NET Framework SDK
To run the sample, do the following:
Install MSXML 3.0 on the client computer.
Install either ASP.NET Premium Edition or the .NET Framework SDK on the local computer (if you are planning to use only one computer to run this sample) or a server.
**Note **The local computer or server must have Microsoft Internet Information Server (IIS) 5.0 or later already running on it.
Download this article's sample files. You will find the following files extracted onto your computer: PO.xsl, PO2.xsl, search.aspx, search.asp, search.cs, and XLToPO.xsl. For this scenario, you only need the following files: PO.xsl, search.aspx, search.cs, and XLToPO.xsl. The other files are for the second scenario (see the next section, Running the Sample Without Either ASP.NET or the .NET Framework SDK) and are not needed here.
The PO.xsl spreadsheet is expecting the Catalog Service to be located at http://localhost/catalog/search.aspx
You will need to install the XML Web service files search.aspx, search.cs, and XLToPO.xsl in a virtual directory called catalog on your local machine (for example, C:\Inetpub\wwwroot\catalog).
Alternatively, change the spreadsheet to point to a server that has IIS and either ASP.NET or the .NET Framework SDK running on it (for example, http://shareserver/catalog/search.aspx). If you do this, install the search.aspx, search.cs, and XLToPO.xsl files in that server's virtual directory instead.
As the PO.xsl file is a purchase order for customers, it must reside on the client's computer. However, it can be saved in any folder on the client's computer.
Make sure the Northwind database is installed. The sample code (see the search.cs file which you can open using Notepad) is wired to SQL Server as follows:
SQLConnection("server=localhost;uid=user_name;pwd=your_password;database=Northwind");Make sure you change the
uidandpwdto match your own.You may need to change this bit of code if your Northwind database is located elsewhere.
**Note **If you do not have SQL Server installed but have the Microsoft Access Northwind database installed, see the Code to Connect to the Access Northwind Database Instead of SQL Server section on how to change the code to point to the Access Northwind database.
To edit the spreadsheet you must turn off protection, which can be done by clicking the Tools menu, pointing to Protection, and then clicking Unprotect Sheet.
To fill out the Purchase From field, type Microsoft, press ENTER, and click the Find button. A UDDI call is made and the address section is then populated with the returned address information.
To fill out the purchase order, type a numerical quantity in the QTY field, then tab to the next column, which is the Description field. Type either apple or biscuit, then tab to the next column, which is the SKU field. You will find that the SKU, Unit Price, Package, and Total fields will automatically be populated with the relevant data.
Also note that after typing a product in the Description field, and tabbing to the SKU field, if the product is available for ordering, a list of choices will appear from cell A40 onwards. For example, apple and biscuit will list only one choice each, whereas hot and tofu will have two choices listed each. If there is more than one choice, you have to go back to that particular cell in the Description field to make your selection from the drop-down list and then tab back to the SKU field again.
After you have filled out the purchase order, select the Send button. This will generate a RosettaNet PIP3A4 XML Purchase Order format (saved as test.xml), and the order is sent. The test.xml file automatically opens up in your browser when you click the Send button. It is also saved automatically under that file name in the same folder as your PO.xsl file.
Running the Sample Without Either ASP.NET or the .NET Framework SDK
To get this sample running, do the following:
Install MSXML 3.0 on the client computer.
Download this article's sample files. You will find the following files extracted onto your computer: PO.xsl, PO2.xsl, search.aspx, search.asp, search.cs, and XLToPO.xsl.
For this scenario, you only need the following files: PO2.xsl, search.asp, and XLToPO.xsl. The other files are for the first scenario (see the section above, Running the Sample on Either ASP.NET or the .NET Framework SDK) and are not needed for this scenario.
The PO2.xsl spreadsheet is expecting the Catalog Service to be located at http://localhost/catalog/search.asp.
You will need to install the XML Web service files search.asp and XLToPO.xsl in a virtual directory called catalog on your local machine (for example, C:\Inetpub\wwwroot\catalog). This means you must have Microsoft Internet Information Server (IIS) 5.0 or later running on your computer.
Alternatively, change the spreadsheet to point to a server that has IIS running on it (for example, http://shareserver/catalog/search.asp). If you do this, install the search.asp and XLToPO.xsl files in that server's virtual directory instead.
As the PO2.xsl file is a purchase order for customers, it must reside on the client's computer. However, you may save it in any folder.
Acquire the Northwind database. The sample code (see the search.asp file) is wired to SQL Server as follows:
objCon.ConnectionString = "DRIVER={SQL Server}; SERVER=localhost;UID=user_name;pwd=your_password;database=Northwind"Make sure you change the uid and pwd parameters accordingly to match your own user name and password. You may need to change this bit of code if your Northwind database is located elsewhere.
**Note **If you do not have SQL Server installed but have the Microsoft Access Northwind database installed, see the Code to Connect to the Access Northwind Database Instead of SQL Server section on how to change the code to point to the Access Northwind database.
Follow steps 6 through 9 in the Running the Sample on Either ASP.NET or the .NET Framework SDK section above.
Code to Connect to the Access Northwind Database Instead of SQL Server
If you are running the sample on either ASP.NET or the .NET Framework SDK, go to item 1 for the code to connect to the Access Northwind database; otherwise see item 2.
- Running the Sample on Either ASP.NET or the .NET FrameworkSDK
Open the search.cs file in Notepad.
**Note **Be careful not to accidentally break a string up by pressing ENTER or reformatting the search.cs file.
Replace
using System.Data.SqlClient;with
using System.Data.OleDb;Replace
SqlConnection con = new SqlConnection ("server=localhost;uid=user_name;pwd=your_password;database=Northwind");with
OleDbConnection con = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;User ID=user_name;Password=your_password;Data Source=C:\Program Files \Microsoft Office\Office10 \Samples\Northwind.mdb");Make sure you change the password to match your own.
Replace
SqlCommand cmd = new SqlCommand(query, con);with
OleDbCommand cmd = new OleDbCommand(query, con);Save the search.cs file before exiting.
- Running the Sample Without Either ASP.NET or the .NET FrameworkSDK
Open the search.asp file. Replace the following code in the search.asp file.
objCon.ConnectionString = "DRIVER={SQL Server}; SERVER=localhost;UID=user_name;pwd=your_password;database=Northwind"with
objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Password=your_password;User ID=user_name;Data Source=C:\Program Files \Microsoft Office\Office10\Samples\Northwind.mdb"Make sure you change the password to match your own. Save the search.asp file before exiting.
Next Steps
Ideally, you would want to store the supplier's Catalog service bindings in UDDI. There is some VBA code commented out that will do this for you. It looks for a recognized Catalog Service serviceInfo (by serviceKey) and, if it finds it, it then uses the accessPoint contained within the serviceDetails. The pseudo-catalog API used in this demo is not registered as a known service type in UDDI.
It would be a fun exercise to make use of smart tags to do similar things. See the Microsoft Office XP Smart Tag SDK 1.1 for more information on smart tags.