Using Visual Basic and ASP with XML to Generate Excel 2003 Workbooks

Article
07/11/2006

Frank Rice
Microsoft Corporation

March 2004

Applies to:
    Microsoft® Office Excel 2003
    Microsoft Visual Basic® 6.0
    Microsoft Office Access 2003

Summary: Learn ways to use XML files and XML style sheets to create formatted Excel workbooks. XML templates created using either Active Server Pages (ASP) or Microsoft Visual Basic gives you the flexibility to pick the method of creating templates that best suit your goals. (23 printed pages)

Introduction
Definition of XML
Definition of an XML style sheet
Translation of XML files
XML flattening
Advantages of XML style sheets
XML mapping
XML Spreadsheet (XMLSS) Format
Integrating XML in Excel solutions
Format an Excel Workbook While Streaming MIME Content
Create an XML Template for a Workbook Using Visual Basic or ASP
Use ASP to Transform an XML-Persisted ADO Recordset into an XML Spreadsheet
Conclusion

Introduction

You can use Microsoft® Office Excel 2003 to open, transform, and save files in the Extensible Markup Language (XML) format. This article reviews XML and XML style sheets and how you can use them with Excel 2003. Definitions of key words and concepts are provided, in addition to how you can use Active Server Pages (ASP) and Microsoft Visual Basic® to create formatted Excel workbooks, and how you can create an XML template that, when used with XSL transformations, generates a formatted workbook that can be opened directly in Excel.

Note This article is a consolidation of and update to various articles from the Microsoft Knowledge Base. For more information, search for the words "XML" and "Excel" in the Microsoft Knowledge Base.

Definition of XML

XML is a simple, standard way to delimit text data. It is analogous to creating an arbitrary data structure with an easy to read language, and then sharing it with anyone using any other language on any other computing platform. Because XML is easy to create and to interpret, it is used in a wide variety of programs. Correct, or well-formed, XML follows a set of guidelines that dictates the data structure yet gives the XML developer great flexibility to create an unlimited number of customized tags that best describe the data and the relationships that might exist in the data.

Like Hypertext Markup Language (HTML), XML is a markup language where text is organized with a combination of tags (words enclosed in angle brackets < and >) and attributes (of the form name ="value"). However, whereas HTML specifies what each tag and attribute means and how it should be rendered for display, XML uses the tags only to delineate elements of data. Because you can use any tag names with XML, it is at the sole discretion of a program to interpret the data and its meaning. Although XML developers are free to create whatever tags or hierarchy best fit their data, there is a set of specifications that should be followed to make sure that the XML is considered well-formed.

Figure 1. The parts of an XML document (Click picture to view larger image)

The syntax that make up an XML document are as follows:

Prolog. Signals the beginning of the XML data, describes the character encoding method, and provides other information to XML parsers and applications. The prolog consists of an optional XML declaration, zero or more comments, processing instructions, and white space characters.
XML declaration. All XML documents can (and should) begin with a single XML declaration. The XML declaration states that this is an XML document and gives the version number.
Comments. Optional entities used to increase the readability of the document. You can type any text between the  characters except "--."
Processing Instructions. Passes instructions to applications such as how to handle an element or its contents.
Attributes. Provides an alternative way to include additional information in an element. Typically, you store most of the information you intend to display in the element's context. Then, you store various properties of the element, not necessarily to be displayed, in an attribute.
Document element (Root element). The parent of all other elements in the XML document, meaning that it may not be contained in any other element.

Now that we've described the parts of an XML document, the characteristics that make up a well-formed document are as follows:

It contains exactly one root element with a unique name that does not appear in any other element in the document.
Elements are correctly nested so that no tags overlap between elements.
All element tags are closed.
Element start and end tags use a consistent case (XML is case-sensitive).
All element attributes are enclosed in quotation marks, either double or single.
Special characters (such as &, <, >) are defined as built-in entities (&, <, >).

Listing 1. Well-formed XML sample (Dictionary.xml)

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="Dictionary.xsl"?>
<Dictionary>
 <Entries>
    <Entry>
      <Word Type="1">Energetic</Word>
      <Definition>Having, exerting, or displaying energy</Definition>
    </Entry>
    <Entry>
      <Word Type="1">Happy</Word>
      <Definition>Enjoying, displaying, or characterized by pleasure or joy</Definition>
    </Entry>
    <Entry>
      <Word Type="2">Emotion</Word>
      <Definition>A complex, strong subjective response</Definition>
    </Entry>
 </Entries>
</Dictionary>

The XML in Listing 1 describes entries in a dictionary. The root element is <Dictionary>, and the root contains one child element named <Entries>. <Entries> contains three child elements, where each child element describes the data for an individual entry in the dictionary. For each entry, there are two child elements: <Word> and <Definition>. The value of each <Word> element is the word itself, <Word>. Elements also have a Type attribute that indicates whether the word is an adjective (1) or a noun (2). <Definition> elements have a text value and no attributes.

Definition of an XML Style Sheet

XML tags describe the data in a text file, but XML alone does not specify how to present the data to the user. Formatting rules for XML data are frequently contained in XML style sheets. XML style sheets are well-formed XML documents that use Extensible Style Language (XSL) to transform XML data for presentation. You can have many style sheets for the same XML data. While your actual XML data structure might remain the same, style sheets give you the flexibility to present the data in many different ways. The following example demonstrates how to format a dictionary entry by using XML tags:

Listing 2. XML style sheet (Dictionary.xsl)

<?xml version="1.0" encoding="ISO-8859-1"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/">

    <html>
      <body>
        <ul>
          <xsl:for-each select="Dictionary/Entries/Entry">
            <li>
              <B><xsl:value-of select="Word"/></B>
              <xsl:if test="Word[@Type='1']"><I>adj.</I></xsl:if>
              <xsl:if test="Word[@Type='2']"><I>n.</I></xsl:if>
              <BR/>
              <xsl:value-of select="Definition"/>
              <BR/><BR/>
            </li>
          </xsl:for-each>
        </ul>
      </body>
    </html>
 </xsl:template>
</xsl:stylesheet>

Dictionary.xsl, shown in Listing 2, is a style sheet that you can use to transform the sample Dictionary.xml. The transformation causes an HTML presentation of the data that is viewable in a Web browser. The XSL sorts the dictionary entries alphabetically in a bulleted list and formats each component of the dictionary entry. The transformed XML that is rendered in the browser looks similar to the following:

Emotion<n.> A complex, strong subjective response
Energetic<adj.> Having, exerting, or displaying energy
Happy<adj.> Enjoying, displaying, or characterized by pleasure or joy

To try it out, copy and paste Listing 1 into any text editor and save it as Dictionary.xml. Then, copy and paste the XML style sheet in Listing 2 and save it as Dictionary.xsl in the same folder as Dictionary.xml. Start Microsoft Internet Explorer and navigate to Dictionary.xml.

Translation of XML files

Excel can open any well-formed XML file. You can open XML files directly from the Open command on the File menu. You can also open XML files programmatically by using either the Open method or the OpenXML method of the Workbooks collection. Additionally, you can import XML files by using the Import XML Data button on the List toolbar. Finally, you can open XML files by clicking the Data menu, pointing to XML, and then clicking Import.

XML Flattening

In Excel, when you import XML as a read-only workbook, Excel uses a special flattening algorithm to load the data into rows and columns. Excel worksheets are two-dimensional entities made up of rows and columns; because XML can have more than two dimensions, flattening is required to interpret the XML so that Excel can load it into cells on a worksheet.

Consider the following two sets of XML that essentially represent the same data in different ways:

Listing 3. Customer XML with child elements

<?xml version="1.0"?>
<Customer>
    <CustomerID>1234</CustomerID>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
</Customer>

<?xml version="1.0"?>
<Customer CustomerID="1234" FirstName="John" LastName="Doe"/>

Listing 4. Customer XML with attributes

<?xml version="1.0"?>
<Customer CustomerID="1234" FirstName="John" LastName="Doe"/>

If you load the XML in Listing 3 into Excel, the data is imported into cells as follows:

Table 1. Flattened XML of Listing 3

Row /Column	A	B	C
1	/Customer
2	/Customerid	/FirstName	/LastName
3	1234	John	Doe

Looking at both Listings 3 and 4, Excel treats attributes exactly like child elements. To prevent name collision with existing elements, Excel puts the at sign (@) in front of the attribute names for consistency with the naming standards for XSL patterns. Therefore, the XML in Listing 4 is loaded into cells in the same way (by loading the file As a read-only workbook available from the Open menu on the File menu), except that cells A2, B2, and C2 contain"/@CustomerID","/@FirstName", and"/@LastName", respectively.

The Customer.xml file in Listings 3 and 4 follows a two-dimensional structure that is easy to see. But consider the following Customer.xml (Listing 5) that cannot be easily interpreted as two-dimensional because of the addition of the <Order> level:

Listing 5. Customer XML with two levels under the root element

<?xml version="1.0"?>
<Customers>
   <Customer>
      <CustomerID>1234</CustomerID>
      <FirstName>John</FirstName>
      <LastName>Doe</LastName>
      <Orders>
         <Order ProdID="AAA" Amt="10"/>
         <Order ProdID="BBB" Amt="3"/>
      </Orders>
   </Customer>
   <Customer>
      <CustomerID>5678</CustomerID>
      <FirstName>Alice</FirstName>
      <LastName>Smith</LastName>
      <Orders>
         <Order ProdID="AAA" Amt="5"/>
      </Orders>
   </Customer>
</Customers>

In the XML in Listing 5, there is one customer with two orders and another customer with one order. Excel must import the XML in such a way that it maintains the relationship between <Orders>and <Customers>. Excel imports the XML into the following three rows:

Figure 2. Spreadsheet with multiple dimensional representation (Click picture to view larger image)

Two additional columns are present: a #id column that associates the row with the <Customer> node, and a #agg column that represents a "FillDown." With #agg, the value of the first node in the level above it is listed one time and then omitted for remaining rows.

Also, note that the column for <Amt> comes before the column for <ProdID> even though it appears after <ProdID> in the actual XML. The flattener loads the elements in alphabetical order from left to right, not in the natural order that they appear in the original XML document.

Advantages of XML Style Sheets

Using style sheets provides significant advantages for opening XML files in Excel. By using a style sheet, you can better control the placement of data in cells and even provide some formatting for the cells. When you open an XML file in Excel and that file contains one or more XML style sheet processing instructions, Excel prompts you to select the style sheet that you want to use for transformation.

Again, consider the Customer.xml in Listing 5. By adding a processing instruction to the Customer.xml (<?xml-stylesheet type="text/xsl" href="Customer.xsl"?> ), you can control the columns in the worksheet that the data appears in and apply custom formatting. You can use the following style sheet sample as a transform for the Customer.xml sample:

Listing 6. Customer XSL style sheet

<?xml version="1.0" encoding="ISO-8859-1"?><xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/">

    <HTML>
      <HEAD>
        <STYLE>   
          .HDR { background-color:bisque;font-weight:bold }
          .CustomerRow { border-top:thin solid black }
        </STYLE>
      </HEAD>
   
      <BODY>
        <TABLE>
          <COLGROUP WIDTH="100" ALIGN="CENTER"></COLGROUP>
          <COLGROUP WIDTH="100" ALIGN="LEFT"></COLGROUP>
          <COLGROUP SPAN="2" WIDTH="80" ALIGN="CENTER"></COLGROUP>
          <TD CLASS="HDR">Customer ID</TD>
          <TD CLASS="HDR">Name</TD>
          <TD CLASS="HDR">Product ID</TD>
          <TD CLASS="HDR">Amount</TD>
          <xsl:for-each select="Customers/Customer">
            <TR>
              <TD CLASS = "CustomerRow">
                <xsl:value-of select="CustomerID"/>
              </TD>
              <TD CLASS = "CustomerRow">
                <xsl:value-of select="FirstName"/> <xsl:value-of 
      select="LastName"/>
              </TD>
              <TD CLASS = "CustomerRow">
                <xsl:value-of select="./Orders/Order/@ProdID"/>
              </TD>
              <TD CLASS = "CustomerRow">
                <xsl:value-of select="./Orders/Order/@Amt"/>
              </TD>
            </TR>
            <xsl:for-each select="./Orders/Order">

                <TR>
                  <TD> </TD><TD> </TD>
                  <TD><xsl:value-of select="@ProdID"/></TD>
                  <TD><xsl:value-of select="@Amt"/></TD>
                </TR>
 
            </xsl:for-each>
          </xsl:for-each>
        <TR><TD CLASS="CustomerRow" COLSPAN="4"> </TD></TR>
        </TABLE>
      </BODY>
    </HTML>
 </xsl:template>
</xsl:stylesheet>

The style sheet transforms the XML into valid HTML. Excel can translate valid HTML into its native format. When you open the Customer XML with this style sheet applied, Excel displays the data as a four-column table with headers (Customer ID, Name, Product ID, and Amount). Cell formatting such as font attributes, cell background colors, and borders are also present in the table. To try it out, copy and paste the XML in Listing 5 into any text editor and save it as Customer.xml. Then copy the XML style sheet in Listing 6 and save it as Customer.xsl in the same folder as Customer.xml. Start Internet Explorer, and then navigate to the file Customer.xml. In Figure 3, note that the file is formatted.

Figure 3. Formatted Customer.xml file

Next, remove the processing instruction ( <?xml-stylesheet type="text/xsl" href="Customer.xsl" ? > ) from Customer.xml and open the file in Internet Explorer. You can now see the difference between the Customer.xml file with and without formatting (see Figure 4).

Figure 4. Unformatted Customer.xml file

XML Mapping

Note The following functionality is available only in the Excel 2003 stand-alone program and in Microsoft Office 2003 Professional Edition. This functionality is not available in Microsoft Office 2003 Standard Edition.

The ability to open, edit, and save an XML file in the XML Spreadsheet schema was introduced In Microsoft Excel 2002 (available with Microsoft Office XP). Excel 2003 extends this ability, permitting you to open, edit, and save any XML data in any predefined XML schema (*.XSD), by using a user-defined map of the XML data in an Excel workbook.

In Excel 2003, you can map an XML schema to an Excel workbook. When mapped, you can import data from any XML source that complies with the mapped schema. In addition, the file is made available to the workbook for use; the mapped elements and subsequent imported data are displayed by using the Excel 2003 list interface.

When you add an XML schema to your workbook, Excel creates an object that is designated an XML map. With XML maps, you map cells or ranges to elements in the XML schema. Excel also uses these maps to relate the contents of a mapped range to elements in the schema when you import or export XML data. A workbook can contain many XML maps, with each one independent of all others. However, many maps can refer to the same underlying schema.

Excel displays a hierarchical representation of the data structure. You can then map the different elements in the representation to actual cells in the worksheet. The schema can constrain data hierarchically, or it can further constrain data types and other aspects of the data that you can extract from or import into the worksheet.

Additionally, you can export XML data from a mapped workbook back to its native schema if all the data in the mapped elements follows the schema and is exportable. The elements from the schema not mapped are not exported.

For additional information about mapping XML to a workbook, see the following articles:

If you try to open an XML file that is not well-formed, you receive an error message or a Script Parse Error. The error message describes why the XML did not open. The error message description may also provide details about the node that caused the translation to fail. In some cases where there is a parse error, you may receive a dialog box that lists errors instead of an error message; Excel logs script parse errors to a log file. The parse error message lists the location and name of the log file.

The Text Import Wizard may appear when you open your XML file. XML files are just text files that are structured in a very specific manner. For Excel to recognize that the file you are opening is XML and not plain text, the first item in your XML file must be an XML declaration that contains, at a minimum, the reserved name "xml" and a version number. The XML must also contain at least one element (the root element), in this case, the <MyXML> element:

Listing 7. Root element in an XML document

<?xml version="1.0"?>
<MyXML>
....
</MyXML>

XML Spreadsheet (XMLSS) Format

Excel can save workbooks as XML. You can save a workbook as XML by selecting XML Spreadsheet (*.xml) as the file type in the Save As dialog box. Excel saves the workbook in the XML Spreadsheet (XMLSS) format.

**Note **If you select "XML Data (*.xml)" as the file type, Excel exports only XML data in ranges mapped to the schema (as described in the XML mapping section of this article); if no schema is mapped to the workbook, you receive an error message.

The XML Spreadsheet format describes the contents of the workbook by persisting cell data and formulas, cell formats, worksheet settings, and workbook settings. The following Excel features cannot be persisted in XML:

Charts
OLE Objects
Drawing shapes or AutoShapes
VBA Projects
Group and Outline

XMLSS is a format that is common to Excel 2003 and Excel 2002. Therefore, you can share files in XMLSS between the two versions. You can also create your own style sheets for XMLSS files to transform the data to another format. This allows you to share the transformed file with any number of programs.

Integrating XML in Excel Solutions

Using XML data presents you with many opportunities for Excel 2003 solutions. For example, you can create multi-sheet workbooks in XMLSS at run time without automating Excel. Automating Excel requires loading the Excel executable file. Because Excel is an out-of-process automation server, calls to the Excel object model can be costly in terms of performance.

You can create XMLSS completely in process by using only the Microsoft XML Parser (MSXML) and XML style sheets. Or, you can use the Office XP Spreadsheet component (in process) to build a spreadsheet and to retrieve the XMLSS for the spreadsheet. In either case, you can open the resulting XMLSS directly in Excel as a native format. By streaming the XMLSS to client browsers by using the Multipurpose Internet Mail Exchange (MIME) (also known as Multimedia Internet Mail Exchange) content type in Excel, Web programs such as those that use ASP can create complex workbooks without the overhead of running Excel on the server.

Note MIME is a specification for formatting non-ASCII messages so that you can send them over the Internet. Many e-mail clients now support MIME, which enables them to send and receive graphics, audio, and video files via the Internet mail system. In addition to e-mail applications, Web browsers also support various MIME types. This enables the browser to display or output files that are not in HTML format. One of the types of files includes workbooks.

For sample code that demonstrates possible uses of XMLSS generation, see the following Microsoft Knowledge Base articles:

Extracting data from a range of cells in a format that you can easily share with other programs is another potential use of XML in an Excel solution. You can extract XMLSS from cells on a worksheet by calling the Value property of the Range object with the <xlRangeValueXMLSpreadsheet> parameter. You can use a style sheet to transform the XMLSS to any XML that you can use with another program. One possible use of this is to transform the XMLSS to custom XML that you can post and process by using a Web program.

Format an Excel Workbook While Streaming MIME Content

This section demonstrates how you can use ASP to create a formatted workbook that you can stream as MIME content to Microsoft Excel.

Excel 2003 can retain storage information in a format that is an HTML/XML hybrid. When you save an Excel 2003 workbook as a Web page, Excel creates a file that contains a combination of HTML tags and XML tags that have special meaning to Excel. A workbook saved in this format can go from Excel to the browser and back to Excel again (a round trip) without losing workbook integrity.

When you open a workbook that is saved as a Web page in Internet Explorer, the HTML tags render the document. When you open a workbook in this format in Excel, Excel uses the XML tags for settings that might pertain to the workbook, worksheets, rows, and columns.

Using ASP, you can create your own documents in an HTML/XML format in Excel so that when you stream the document to the client browser, it displays using Excel. Using the HTML/XML format, you control the cell placement of data and specify any workbook or worksheet settings that are supported in Excel.

To create an Excel workbook in the HTML/XML format and then stream it as MIME content to Excel on the client, follow these steps:

Paste the following code into Microsoft Notepad:

Listing 8. Procedure to format an Excel workbook and stream it MIME

<%@ Language=VBScript %>
<%
  ' Check for a value passed on the address bar.
  if (Request.QueryString("i")) = "" then bFirst = true
  ' If we have a value for "i", we know that we can display the
  ' data in Excel.
  if (bFirst = false) then
    ' Buffer the content and send it to Excel.
    Response.Buffer = true
    Response.ContentType = "application/vnd.ms-excel" 
%>
<HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
<HEAD>
<style>
  <!--table
  @page
     {mso-header-data:"&CMultiplication Table\000ADate\: &D\000APage &P";
mso-page-orientation:landscape;}
     br
     {mso-data-placement:same-cell;}

  -->
</style>
  <!--[if gte mso 9]><xml>
   <x:ExcelWorkbook>
    <x:ExcelWorksheets>
     <x:ExcelWorksheet>
      <x:Name>Sample Workbook</x:Name>
      <x:WorksheetOptions>
       <x:Print>
        <x:ValidPrinterInfo/>
       </x:Print>
      </x:WorksheetOptions>
     </x:ExcelWorksheet>
    </x:ExcelWorksheets>
   </x:ExcelWorkbook>
  </xml><![endif]--> 
</HEAD>
<BODY>








<TABLE>
<%
   ' Build a multiplication table from 1,1 to i,j.
   for i = 1 to CInt(Request.QueryString("i"))
     Response.Write "  <TR>" + vbCrLf
     for j = 1 to CInt(Request.QueryString("j"))
       if (j = 1) or (i = 1) then
         Response.Write "    <TD bgcolor=""#FFF8DC"">"
       else
         Response.Write "    <TD bgcolor=""#B0C4DE"">"
       end if
   Response.Write CStr(i*j) + "</TD>" + vbCrLf
     next
     Response.Write "  </TR>" + vbCrLf
   next
%>
</BODY>
</HTML>
<%
  else
  ' The user hasn't loaded the page yet. Prompt them for 
  ' values for the table.
%>
<HTML>
<BODY>
Please enter indices for the multiplication table:<BR>
<FORM action="xlmime.asp" method=GET>  
  i = <INPUT type="text" name=i style="WIDTH: 25px"><BR>
  j = <INPUT type="text" name=j style="WIDTH: 25px"><BR><BR/>
  <INPUT type="submit" value="Submit"><BR/>
</FORM>
</BODY>
</HTML>
<%
  end if
%>

Save the file as XLMime.asp in the virtual root directory of your web server. (The default virtual root is C:\Inetpub\Wwwroot.)
Start Internet Explorer and browse to http://Server_Name/xlmime.asp, where Server_Name is the name of your Web server.

Figure 5. The introductory screen for xlmime.asp file
In the Web page that appears (see Figure 5), supply numeric values in each of the text boxes provided, and then click Submit. Excel activates in the browser with a new workbook. The new workbook (see Figure 6) contains formatted data in the number of rows (i) and the number of columns (j) that you specified. Also, if you check the page setup information for the worksheet, you will notice that the orientation is set to landscape and that a custom header exists.

Figure 6. The formatted resultant screen

Create an XML Template for a Workbook Using Visual Basic or ASP

This next section illustrates how you can create an XML template that, when used with XSL transformations, generates a formatted workbook that you can open directly in Excel. The XML transformation is demonstrated for both ASP and Visual Basic. With regard to the Visual Basic code sample, Excel generates the spreadsheet data solely with XML/XSL; the example uses minimal automation to open the results in Excel.

Create a folder, C:\ExcelXML.
In Excel, create a workbook.
Add the following data to cells A1:F2 of the workbook in the cells indicated. Add the Cell F2 data as a formula.

Table 2. Example data for workbook

A1: Order ID B1: Product ID C1: Unit Price D1: Quantity E1: Discount F1: Total

A2: aaa B2: 111 C2: 222 D2: 333 E2: 0 F2: =C2*D2*(1-E2)
In cell F3, type the following formula:
```
=SUM(F$2:F2)
```
Select cells A1:F1.
On the Format menu, click Cells.
Apply a bold font, a bottom border, and a solid color for cell shading. Click OK.
Select columns A:F.
On the Format menu, point to Column and then click Width.
Type 15 for the new column width and then click OK.
With columns A:F still selected, on the Format menu, click Cells.
On the Alignment tab, select Center from the horizontal alignment list, and then click OK.
Select column E. On the Format menu, click Cells.
On the Number tab, click Percentage and specify 0 decimal places, and then click OK.
Select column F.
On the Format menu, click Cells.
On the Number tab, click Accounting, and then click OK.
Select cells A3:F3.
On the Format menu, click Row and then Height, type 25, and then click OK.
With A3:F3 still selected, on the Format menu, click Cells.
Apply a top border to the cells and click OK.
On the Tools menu, click Options.
On the View tab, clear the Gridlines check box and click OK.
Select row 2. On the Window menu, click Freeze Panes.
Select cell A1.
On the File menu, click Save As.
Browse to the C:\ExcelXML folder you created, and save the workbook as Orders.xsl in the XML Spreadsheet format.

Note In the File Name box in the Save As dialog box, enclose the file name in double quotes so that Excel does not add an .xml extension to your file name.
Exit Excel.
Open Orders.xsl in any text editor, such as Notepad.

A1: Order ID	B1: Product ID	C1: Unit Price	D1: Quantity	E1: Discount	F1: Total
A2: aaa	B2: 111	C2: 222	D2: 333	E2: 0	F2: =C2D2(1-E2)

Insert the following between the <?xml version="1.0"?> and <Workbook> tags:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
<xsl:pi name="xml">version="1.0"</xsl:pi>

At the end of Orders.xsl, append the following two lines:
```
</xsl:template>
</xsl:stylesheet>
```

Locate the following set of tags in Orders.xsl (this set of tags represents cells A2:F2 in the worksheet)

<Row ss:Height="14.25">
<Cell><Data ss:Type="String">aaa</Data></Cell>
<Cell><Data ss:Type="Number">111</Data></Cell>
<Cell><Data ss:Type="Number">222</Data></Cell>
<Cell><Data ss:Type="Number">333</Data></Cell>
<Cell><Data ss:Type="Number">0</Data></Cell>
<Cell ss:Formula="=RC[-3]*RC[-2]*1*(1-RC[-1])"><Data ss:Type="Number">73926</Data></Cell>
</Row>

Note The Height attribute may vary depending on the border setting applied in step 8.

Replace the preceding code with the following XSL code

<xsl:for-each select="xml/rs:data/z:row">
<Row ss:AutoFitHeight="0" ss:Height="13.5">
    <Cell><Data ss:Type="String"><xsl:value-of select="@OrderID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@ProductID"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@UnitPrice"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Quantity"/></Data></Cell>
    <Cell><Data ss:Type="Number"><xsl:value-of select="@Discount"/></Data></Cell>
    <Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data ss:Type="Number">0</Data></Cell>
</Row>
</xsl:for-each>

Save your changes to Orders.xsl, and close the file.

Use Visual Basic to Transform an XML-Persisted ADO Recordset into an XML Spreadsheet

In Visual Basic, create a standard EXE project.
On the Project menu, click References. Select the type libraries for Microsoft ActiveX Data Object 2.5 (or later) and Microsoft XML 3.0.

Add a CommandButton to Form1, and add the following code to the button's Click event:

Listing 9. Procedure to transform ADO recordset to XML spreadsheet with Visual Basic

    Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
         "C:\program files\microsoft office\office11\samples\Northwind.mdb"
    Const sOutXML = "C:\ExcelXML\Orders.xml"
    Const sXSL = "C:\ExcelXML\Orders.xsl"

    'Retrieve an ADO recordset of the Orders Detail table in Northwind.
    Dim nRecords As Long, nFields As Long
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM [Order Details]", sConn, adOpenStatic, adLockOptimistic

    'Persist the recordset to a new DOMDocument and store the record count.
    Dim oXML As New DOMDocument
    rs.Save oXML, adPersistXML
    nRecords = rs.RecordCount
    nFields = rs.Fields.Count
    rs.Close

    'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
    Dim oXSL As New DOMDocument
    oXSL.Load sXSL

    'Transform the XML using the style sheet.
    Dim oResults As New DOMDocument
    oXML.transformNodeToObject oXSL, oResults

    If oXSL.parseError.errorCode <> 0 Then
        MsgBox "Parse Error: " & oResults.parseError.reason
    Else
       'Modify the ss:ExpandedRowCount attribute for the <table> node to
       'indicate the correct number of rows (count of records + 1 row for
       'the header + 1 row for the total).
        Dim oTable As MSXML2.IXMLDOMElement
        Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
        oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2
        '***************************************
        'Save the results to a file.
        Open sOutXML For Output As #1
        Print #1, oResults.xml
        Close #1
        'Open the XML in Excel.
        Dim oExcel As Object
        Set oExcel = CreateObject("Excel.Application")
        oExcel.Workbooks.Open sOutXML
        oExcel.Visible = True
        oExcel.UserControl = True
        '***************************************
    End If

Note If needed, modify the connection string (Const sConn) so that it contains the correct installation path to the sample Access Northwind sample database.

Press F5 to run the program.
Click the button on Form1. The data from the Orders Detail table in the Northwind sample database appears in the Excel workbook template that you created (see Figure 7).

Figure 7. Formatted data from the Order Detail table

The sample generates the XML Spreadsheet and automates Excel using the Open method of the Workbooks collection to open the XML as a new workbook. As an alternative approach, you can directly insert the XML into an existing worksheet at a specific cell. To illustrate, replace the code that is contained between the asterisks with the following:
```
        'Display the data in a workbook starting at cell B2.
        Dim oExcel As Object, oBook As Object
        Set oExcel = CreateObject("Excel.Application")
        Set oBook = oExcel.Workbooks.Add
        oBook.Worksheets(1).Range("B2").Resize(nRecords + 2, nFields + 1).Value(11) = _
            oResults.xml 'Note: xlRangeValueXMLSpreadsheet=11
        oExcel.Visible = True
        oExcel.UserControl = True
```

After you make this change, run the program again. Note that this time, Excel inserts the XML Spreadsheet data into a new workbook starting at cell B2 of the first worksheet. Excel applies cell data and formats to the range; however, using this approach, Excel does not carry over settings specific to the rows, columns, worksheet, and workbook.

Use ASP to Transform an XML-Persisted ADO Recordset into an XML Spreadsheet

Paste the following code into Notepad. Save the code as ExcelXML.asp in the virtual root folder of your Web server.

Note The default virtual root folder is C:\inetpub\wwwroot.

Listing 10. Code to transform ADO recordset to XML spreadsheet with ASP

<%@ Language="vbscript"%>
<%
    Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft
 office\office11\samples\Northwind.mdb"
    Const sXSL = "C:\ExcelXML\Orders.xsl"

    Response.Buffer = True
    'Retrieve an ADO recordset of the Orders Detail table in Northwind.
    Dim rs, nRecords
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM [Order Details]", sConn, 3, 3

    'Persist the recordset to a new DOMDocument and store the record count.
    Dim oXML
    Set oXML = CreateObject("Microsoft.XMLDOM")
    rs.Save oXML, 1
    nRecords = rs.RecordCount
    rs.Close

    'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
    Dim oXSL
    Set oXSL = CreateObject("Microsoft.XMLDOM")
    oXSL.Load sXSL

    'Transform the XML using the style sheet.
    Dim oResults
    Set oResults = CreateObject("Microsoft.XMLDOM")
    oXML.transformNodeToObject oXSL, oResults

    If oXSL.parseError.errorCode <> 0 Then
       Response.Write "Parse Error: " & oResults.parseError.reason
    Else
       'Modify the ss:ExpandedRowCount attribute for the <table> node in the XSL.
        Dim oTable
        Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
        oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2
        'Return the resulting XML Spreadsheet for display in Excel.
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = "ISO-8859-1"
        Response.Write oResults.XML
        Response.Flush
    End If
%>

Note If needed, modify the connection string (Const sConn) so that it contains the correct installation path to the sample Access Northwind sample database.

Start Internet Explorer and browse to http://Server_name/ExcelXML.asp, where Server_name is the name of your Web server. The data from the Orders Detail table in the Northwind sample database appears in the Excel workbook template that you created.

Conclusion

In this article, you saw different ways to use XML formatted files and XML style sheets to work with Excel workbooks. In addition, you read an overview of XML files and XML style sheets. Additionally, the article demonstrated how to use ASP and Visual Basic to create formatted Excel workbooks. Finally, the article illustrated steps to use ASP to create MIME messages that you can use to stream an Excel workbook. Combining Visual Basic, ASP, and XML can lead to a variety of ways to create Excel solutions.

Using Visual Basic and ASP with XML to Generate Excel 2003 Workbooks

Contents

Introduction

Definition of XML

Definition of an XML Style Sheet

Translation of XML files

XML Flattening

Advantages of XML Style Sheets

XML Mapping

XML Spreadsheet (XMLSS) Format

Integrating XML in Excel Solutions

Format an Excel Workbook While Streaming MIME Content

Create an XML Template for a Workbook Using Visual Basic or ASP

Use Visual Basic to Transform an XML-Persisted ADO Recordset into an XML Spreadsheet

Use ASP to Transform an XML-Persisted ADO Recordset into an XML Spreadsheet

Conclusion

Additional resources