How To Use XSL to Transform Excel XML Spreadsheet for Server-Side Use

Article translations Article translations
Article ID: 278976 - View products that this article applies to.
This article was previously published under Q278976
Expand all | Collapse all

SUMMARY

Excel 2002 introduces new XML functionality that enables developers to extract formatted data from worksheets in an XML Spreadsheet (XMLSS) format. The Value property of a Range object can return cell formatting and contents in XMLSS. XMLSS is well-formed XML that can be loaded by the Microsoft XML Parser for manipulation at runtime. One potential use of this feature is to post XML data to a server application for processing. This article illustrates one such example, in which an Excel workbook is used as an interface for an Active Server Pages (ASP) script that processes XML data that is extracted from a range in a workbook.

This article guides you through the creation of an Excel workbook, an ASP script, and a stylesheet that all work together as an order processing system. Throughout the steps, explanations of each component and how it contributes to the whole solution are given. The sample described in this article is also available for download; for download instructions, see the "Download" section at the end of this article.

MORE INFORMATION

In this sample, several different components work together to function as an order processor:
  • ASP Script. The server-side component is an ASP script that takes client requests for new orders. The script expects requests to be in a specific XML format. The script takes information from the XML that it receives and adds a new order to the Northwind sample database by using ActiveX Data Objects (ADO).
  • Excel Workbook. The client-side component is an Excel workbook that contains a worksheet for order entry and a macro that interacts with the ASP script to process the user's new order.
  • XSL Stylesheet. A stylesheet transforms the Excel XMLSS to the custom XML format that the ASP script requires. The Excel macro loads the stylesheet and transforms the XMLSS to the custom XML prior to posting the data to the ASP script on the Web server.

Step 1: Create the ASP Order Processing Script

Create a new folder named OrderProc in the virtual root folder of your Web server (the default root folder is C:\Inetpub\Wwwroot). In the OrderProc folder, create a new file named OrderEntry.asp with the script below. The script uses the sample Northwind Access database; you may need to modify the path to Northwind.mdb in the connection string (sConn) to match your Office installation.
<%@ Language="vbscript" CodePage="65001"%>

<%
    Response.Buffer = True
    Response.ContentType = "text/xml"
  
    Dim oDataXML                    'Custom Data XML passed in by caller.
    Dim oConn                       'ADO Connection to Northwind database.
    Dim oOrdersRS, oDetailsRS       'ADO Recordsets for the Orders table and Order Details Table.
    Dim oItems                      'Collection of nodes meeting the match. "Order/Items/Item"
    Dim oItem                       'Single node in oItems.
    Dim sCustID                     'Customer ID for the new order.
    Dim sOrderID                    'Order ID of the newly created record. in Orders table
    Dim sStatus                     'Status of order processing.
    Dim bContinue                   'Flag that indicates whether or not to continue processing the order.

    Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\samples\northwind.mdb"
    On Error Resume Next

    'Load the XML passed into the request.
    Set oDataXML = Server.CreateObject("Microsoft.XMLDOM")
    oDataXML.Load Request
    bContinue = True

    'Obtain the Customer ID. If no customer id is provided, report an error.
    sCustID = oDataXML.selectSingleNode("Order/CustomerID").Text
    If sCustID="" Then
       sStatus = "There is no customer specified for the order"
       bContinue = False
    End If

    'Obtain collection of "items" for this order. If the item count = 0, report an error.
    If bContinue Then
       Set oItems = oDataXML.SelectNodes("Order/Items/Item")
       if oItems.length = 0 Then
          sStatus = "There are no items to process for this order"
          bContinue = False
       End If
    End If

    'Open a connection to the Northwind database.
    If bContinue Then
       Set oConn = CreateObject("ADODB.Connection")
       oConn.Open sConn
       if err.Number <> 0 Then
          sStatus = err.Description
          bContinue = False
       end if

    End If

    'Open the Orders and Order Details tables and add the new records.
    If bContinue Then

       Set oOrdersRS = CreateObject("ADODB.Recordset")
       oOrdersRS.Open "SELECT * FROM Orders", oConn, 2, 3
       Set oDetailsRS = CreateObject("ADODB.Recordset")
       oDetailsRS.Open "SELECT * FROM [Order Details]", oConn, 2, 3

       'Add a new entry in the Orders table.
       oOrdersRS.AddNew
       oOrdersRS.Fields("CustomerID").Value = sCustID
       oOrdersRS.Fields("OrderDate").Value = CDate(Now)
       oOrdersRS.Update
       sOrderID = oOrdersRS.Fields("OrderID").Value
        
       'And a new record for each item in the order XML to the Order Details table.
       If err.number = 0 Then
          For Each oItem In oItems
              oDetailsRS.AddNew
              oDetailsRS.Fields("OrderID").Value = sOrderID
              oDetailsRS.Fields("ProductID").Value = oItem.childnodes(0).Text
              oDetailsRS.Fields("Quantity").Value = CLng(oItem.childnodes(1).Text)
              oDetailsRS.Fields("UnitPrice").Value = CLng(oItem.childnodes(2).Text)
              oDetailsRS.Fields("Discount").Value = 0
              oDetailsRS.Update
          Next
       End If

       if err.Number <> 0 Then
          sStatus = err.Description
       else
          sStatus = "Success"
       end if

       'Close the recordsets and connection.
       oDetailsRS.Close
       oOrdersRS.Close
       oConn.Close

    End If
  
    'Return the resulting XML (the Order status).
    Dim sResult
    sResult = "<?xml version=""1.0""?>"
    sResult = sResult & "<OrderProcessed>" 
    sResult = sResult & "<Status>" & sStatus & "</Status>"
    sResult = sResult & "<OrderID>" & sOrderID & "</OrderID>"
    sResult = sResult & "</OrderProcessed>"
    Response.Write sResult
    Response.End
 
%> 
				
To successfully process an order request, this ASP script expects XML data structured as follows:

<?xml version="1.0"?>
<Order>
    <CustomerID>BOTTM</CustomerID>
    <Items>
        <Item>
            <ProductID>4</ProductID>
            <Quantity>11</Quantity>
            <Price>20.25</Price>
        </Item>
        <Item>
            <ProductID>18</ProductID>
            <Quantity>2</Quantity>
            <Price>63.7</Price>
        </Item>
    </Items>
</Order>
					
This XML designates an order for the customer with the ID of "BOTTM". The order contains two items: 11 units of the product with the ID of 4, and 2 units of the product with the ID of 18.

The ASP script does some error handling to ensure that clients have provided it with valid order information. Clients must provide a Customer ID and at least one item; if these criteria are not met, the ASP script does not process the order and returns an error.

The ASP script returns XML data to clients that have made an order request. This XML indicates the success or failure of the order processing and also provides the order number on success:

<?xml version="1.0"?>
<OrderProcessed>
    <Status>Success</Status>
    <OrderID>11078</OrderID>
</OrderProcessed>
					

Step 2: Create the Workbook Interface for Order Entry

  1. In Excel, start a new workbook.
  2. Enter labels in cells A1, A3, B3, and C3 as follows:
    A1:   Customer ID    B1:                C1:
    A2:                  B2:                C2:
    A3:   Product ID     B3:   Quantity     C3:   Price
    					
  3. Select cells A1:B1. On the Insert menu, point to Name and then click Create. In the Create Names dialog box, select Left Column and click OK. This creates the defined name Customer_ID for cell B1.
  4. Select cells A3:C8. On the Insert menu, point to Name and then click Create. In the Create Names dialog box, select Top Row and click OK. This creates the defined names Product_ID, Quantity and Price for cells A4:A8, B4:B8, and C4:C8 respectively.
  5. Press ALT+F11 to start the Visual Basic Editor.
  6. In the Visual Basic Editor, on the Insert menu, click Module. Add the following macro to the code module:
    Sub ProcessOrder()
    
        Const sFolder = "http://YourWebServer/OrdrProc/"
    
        'Load a new DOMDocument based on the XMLSS of the range A1:C8.
        Dim oRangeXML
        Set oRangeXML = CreateObject("Microsoft.XMLDOM")
        oRangeXML.LoadXML Range("A1:C8").Value(xlRangeValueXMLSpreadsheet)
            
        'Transform the XMLSS to custom XML that the ASP can
        'interpret as a new "order".
        Dim oXSL, oOrderXML
        Set oXSL = CreateObject("Microsoft.XMLDOM")
        oXSL.Load ThisWorkbook.Path & "\OrderEntry.xsl"
        Set oOrderXML = CreateObject("Microsoft.XMLDOM")
        oRangeXML.transformNodeToObject oXSL, oOrderXML
        
        'Submit the XMLSS to the ASP page for processing.
        Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
        oXMLHTTP.Open "Post", sFolder  & "/OrderEntry.asp", False
        oXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
        oXMLHTTP.send oOrderXML
        
        'Retrieve the results of the processing by the ASP page.
        Dim oResult
        Set oResult = CreateObject("Microsoft.XMLDOM")
        oResult.Load oXMLHTTP.responseXML
    
        'Check the returned XML -- if the Status is "Success", fill in the
        'Order # and display a message. If the Status is not "Success",
        'report the error.
        Dim sStatus As String
        sStatus = oResult.selectsinglenode("OrderProcessed/Status").Text
        If sStatus = "Success" Then
            MsgBox "Thank you. Your order number is " & _
                oResult.selectsinglenode("OrderProcessed/OrderID").Text
        Else
            MsgBox sStatus
        End If
    
    End Sub
    					
    NOTE: Change YourWebServer in the sFolder constant to the name of your Web server.

  7. Close the Visual Basic Editor and return to Excel.
  8. Save the workbook in the OrdrProc folder you previously created as Invoice1.xls.
The "order form" is essentially cells A1:C8. The macro retrieves the XMLSS for the order form and loads it into a new DOMDocument object. It then loads the Extensible Stylesheet Language (XSL) stylesheet into another DOMDocument and transforms the XMLSS into an XML structure that the ASP can interpret as a new order. The macro uses the XMLHTTP object to post the order XML to the ASP script on the Web server. The ASP script processes the order and then returns more XML to the macro for order status information.

NOTE: As a design choice, the task of transforming the XMLSS to custom Order XML is given to the client-side macro code. You can also post the XMLSS to the ASP script and let ASP perform the transformation for you.

Step 3: Create the Stylesheet

In the OrdrProc folder, create a new file named OrderEntry.xsl and paste the following XSL code:
<?xml version='1.0'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">

  <xsl:template match="/">
    <xsl:pi name="xml">version="1.0"</xsl:pi>

    <Order>
      <CustomerID><xsl:value-of select="Workbook/Worksheet/Table/Row/Cell[NamedCell[@ss:Name='Customer_ID'] $and$ Data[@ss:Type!='Error']]"/></CustomerID>
      <Items>
        <xsl:for-each select="Workbook/Worksheet/Table/Row[Cell[NamedCell[@ss:Name='Product_ID'] $and$ Data[@ss:Type!='Error']]]">
          <Item>
            <xsl:apply-templates/>
          </Item>
        </xsl:for-each>
      </Items>
    </Order>

  </xsl:template>

  <xsl:template match="Cell[NamedCell[@ss:Name='Product_ID']]">
    <ProductID><xsl:value-of select="Data"/></ProductID>
  </xsl:template>

  <xsl:template match="Cell[NamedCell[@ss:Name='Quantity']]">
    <Quantity><xsl:value-of select="Data"/></Quantity>
  </xsl:template>

  <xsl:template match="Cell[NamedCell[@ss:Name='Price']]">
    <Price><xsl:value-of select="Data"/></Price>
  </xsl:template>

</xsl:stylesheet>
				
The XSL transforms the XMLSS to the custom Order XML (illustrated in Step 1) as follows:
  • The XSL locates the Customer ID by traversing the node hierarchy until it encounters a <Cell> node that contains the <NamedCell> and <Data> child nodes. It checks that <NamedCell> has an ss:Name attribute equal to 'Customer_ID' and that <Data> has an ss:Type attribute that does not equal 'Error'.
  • To locate items for the order, the XSL finds all <Row> nodes with a <Cell> node that contain a <NamedCell> child node with an ss:Name attribute of 'Product_ID' and a <Data> child node with an ss:Type attribute that is not 'Error'.
  • For each <Row> that the XSL determines is an item for the order, the XSL uses templates to match <NamedCell> nodes that have an ss:Name attribute of 'Product_ID', 'Quantity' and 'Price'.

Step 4: Run the Sample Code to Process a New Order

  1. Return to Invoice1.xls in Excel.
  2. To simulate order entry, update Sheet1 with customer and product information as shown below:
    A1:   Customer ID    B1:   BOTTM        C1:
    A2:                  B2:                C2:
    A3:   Product ID     B3:   Quantity     C3:   Price
    A4:   4              B4:   11           C4:   20.25
    A5:   18             B5:   2            C5:   63.70
    					
    NOTE: For this sample, you must enter valid customer and product IDs for the ASP script to successfully process the order.

  3. On the Tools menu, point to Macro and click Macros. Select the ProcessOrder macro in the list and click Run.
  4. If the ASP script successfully processes the order, you receive a message with the new order number. Start Microsoft Access and open the Northwind sample database. You should see a new entry in the Orders table with the Order ID returned from the ASP. You also see two new entries in the Order Details table for the same Order ID. Quit Access and return to Invoice1.xls in Excel.
  5. Clear the Customer ID in cell B1 and run the macro again. This time, the server is unable to process the order and it returns an error indicating that no Customer ID has been provided.

Download

ExcelXML.exe contains the sample described in this article as well as an enhanced version of Invoice1.xls. Invoice2.xls uses the same ASP script and XSL stylesheet as Invoice1.xls. However, Invoice2.xls demonstrates additional Excel features, such as worksheet protection, data validation and VLOOKUP formulas, that you can use to provide a more user-friendly order form.

The following file is available for download from the Microsoft Download Center:
Excelxml.exe
Release Date: April 3, 2001

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

REFERENCES

For additional information on using XML and ASP to build server-side solutions, see the following Microsoft Developer Network (MSDN) Web sites:
Introduction to XML
http://www.microsoft.com/learning/en/us/syllabi/2500AFinal.mspx

XML Tutorial
http://msdn.microsoft.com/en-us/library/ms950712.aspx

XSL Developer's Guide
http://msdn.microsoft.com/en-us/library/ms862738.aspx

Sending XML Between the Client and Server
http://msdn.microsoft.com/en-us/library/ms763733.aspx

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
288215 INFO: Excel 2002 and XML
285891 How To Use Visual Basic or ASP to Create an XML Spreadsheet for Excel 2002
288130 How To Use ASP to Build Spreadsheet XML for Client-Side Display
(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Lori B. Turner, Microsoft Corporation.

Properties

Article ID: 278976 - Last Review: January 29, 2007 - Revision: 4.8
APPLIES TO
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Excel 2002 Standard Edition
Keywords: 
kbdownload kbdownload kbfile kbhowto KB278976

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com