XML Web Service-Enabled Office Documents

Chris Lovett

Microsoft Corporation

March 19, 2001

Download or browse the xml03192001.exe in the MSDN Online Code Center.

Are you ready for a marriage of Microsoft Office XP and .NET Web Services? In a networked world of B2B e-commerce, why not deliver the power of Web Services to the end user by integrating business process workflow right into everything people do from their desktop? What am I talking about? Well, an Excel spreadsheet that looks something like Figure 1.

Figure 1. Web Services-enabled Excel spreadsheet

This is not just an ordinary spreadsheet. It uses UDDI to find company addresses and it uses a Catalog Web Service to find product information. It also does an XML transform on the XML spreadsheet format to generate a RosettaNet PIP 3 A4 Purchase Order Request format when you click the Send button.

When you type in the name of the company you are purchasing from, and then click on the Find button, some VBA code behind the spreadsheet makes a UDDI call and fills out the rest of the address section. For example, type in Microsoft, click Find, and you should see the following in the Purchase From fields:

Figure 2. Purchase From field

When you type in a quantity of, say, 23, and then the term Pear in the description field, then press the TAB key, some VBA code queries a SOAP Catalog Web Service to see if it can find a matching product, then it fills out the details. In this case, I have wired the Catalog Web Service to the Northwind database, so it returns the following:

Figure 3. Detailed look at the order portion of the spreadsheet

In this case, it has also filled out the description and turned it into a link which takes you to an HTML page that tells you all about that product.

If more than one product is found and none of them matches exactly to what you typed, then a drop down list of choices is provided. For example, if you type tofu, you'll 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 the specific details are provided.

When you're done, you click the Send button and the RosettaNet PIP 3 A4 XML Purchase Order format is generated, and the order is sent.

How Does All This Work?

You can browse the VBA code behind the spreadsheet by going to the Tools menu, select Macro, then Visual Basic Editor. There's a bit of code behind ThisWorkbook that reacts to changes in the spreadsheet, in particular the Workbook_SheetChange event clears out a line item when you delete the description and the Workbook_SheetSelectionChange event calls FindProduct() when you tab out of the Description field into the SKU field. If FindProduct returns an XMLNode, then the relevant fields are pulled out of that node to populate the rest of the line item details.

How the UDDI find_business call works can be found in my earlier article UDDI: An XML Web Service. If a business is found, the addressLines found in the /businessInfo/contacts/contact/address/ part of the UDDI response are used to populate the Purchase From address block.

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 crack open the <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 page outside of the visible area. You can see how the drop-down list works by going to the Data menu and selecting Validation.

The Catalog Web Service is very simple. The .aspx entry point simply creates a CatalogSearch object which is defined in search.cs and calls Execute, passing the HttpResponse 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 very simple SQL Managed Provider code wrapped in an XmlTextWriter that returns the specific fields from the SQL SELECT statement. So it is basically a while loop through the DataReader, writing to the XmlTextWriter 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=sa;pwd=;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 about the most efficient way you can get XML out of SQL Server using the .NET frameworks. With a very rough measurement, I got about 80 to 90 of these per second on my 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 magic lines of VBA code:

With ActiveSheet 

Set sourcexml = New MSXML2.DOMDocument
 
sourcexml.loadXML .Range("B1:N34").value(xlRangeValueXMLSpreadsheet)

End With

This returns a huge chunk of XML that fully describes everything about that range of cells in the spreadsheet. The following is a snippet from the chunk 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.asp?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 we use XSL 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-03-15T00:00:00.000</DateTimeStamp>
  
</thisDocumentGenerationDateTime>

</PurchaseOrder>

Note   This is probably not a technically complete Request according to RosettaNet PIP 3 A4 Purchase Order Request specification, but you get the idea.
The trick to making this transformation somewhat robust is to name the important cells that we want to pull the data out of. 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 that is Named with the name City. The rest of the stylesheet is pretty straight forward. See XLToPO.xsl for additional information.

Try It Out

To get this running, all you need to do is install MSXML 3.0 and get a hold of a Northwind database. The demo code is wired to SQL Server as follows:

SQLConnection("server=localhost;uid=sa;pwd=;database=northwind");

You may need to change this bit of code if your Northwind database is elsewhere.

The PO.xsl spreadsheet is expecting the Catalog Service to be located at:

http://localhost/catalog/search.aspx

You will need to install the Web Service search.aspx, search.cs, and XLToPO.xsl in a virtual directory called catalog on your local machine, or change the spreadsheet to point elsewhere.

To edit the spreadsheet you will have to turn off protection, which can be done using the Tools/Protection submenu.

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 I'm using in this demo is not registered as a known service type in UDDI.

It would be a fun exercise to make use of Office Smart Tags to do similar things. See the Smart Tags SDK on http://msdn.microsoft.com/office/ for some additional information.