How to use Visual Basic or ASP to create an XML spreadsheet for Excel 2002 and Excel 2003

Create the XML template for the workbook

1. Create a new folder, C:\ExcelXML.
2. In Excel, start a new workbook. Add the following data to cells A1:F2 of the workbook in the cells indicated. Cell F2 should be entered as a formula.

   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)

3. In cell F3, type the following formula:
   =SUM(F$2:F2)
4. 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.
5. 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.
6. Select column E. On the Format menu, click Cells. On the Number tab, click Percentage and specify 0 decimal places, and then click OK.
7. Select column F. On the Format menu, click Cells. On the Number tab, click Accounting and then click OK.
8. Select cells A3:F3. On the Format menu, click Row 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 then click OK.
9. On the Tools menu, click Options. On the View tab, clear the Gridlines check box and click OK.
10. Select row 2. On the Window menu, click Freeze Panes.
11. Select cell A1.
12. 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 an .xml extension is not added to your file name.
13. Quit Excel.
14. Open Orders.xsl in any text editor, such as Notepad.
15. Insert the following between the <?xml version="1.0"?> tag and the <Workbook> tag:

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

16. At the very end of Orders.xsl, append the following two lines:

    </xsl:template>
    </xsl:stylesheet>
    					

17. 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>
    					

    and replace it 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>
    					

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

Use Visual Basic to transform an XML-persisted ADO recordset into an XML spreadsheet

1. In Visual Basic, create a new Standard EXE project.
2. On the Project menu, click References. Select the type libraries for Microsoft ActiveX Data Object 2.5 (or later) and Microsoft XML 3.0.
3. Add a CommandButton to Form1, and add the following code to the button's Click event:

       Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                     "C:\program files\microsoft office\office10\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 database.
4. Press F5 to run the program.
5. Click the button on Form1. The data from the Orders Detail table in the Northwind Database appears in the Excel workbook template that you created.

        '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
				

Use ASP to transform an XML-persisted ADO recordset into an XML spreadsheet

1. 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.

   <%@ Language="vbscript"%>
   
   <%
   
       Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\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 database.
   
   
2. Start Internet Explorer and browse to http://YourWebServer/ExcelXML.asp, where YourWebServer is the name of your Web server. The data from the Orders Detail table in the Northwind Database appears in the Excel workbook template that you created.

Additional notes

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

Download