How to transform a DataSet to spreadsheet XML for Excel by using Visual Basic .NET and ASP.NET (319180)

SUMMARY

This step-by-step article describes how to transform a DataSet to Spreadsheet XML that can be rendered in Excel. The Excel Spreadsheet XML format supports element tags and attributes for Excel functionality such as multi-sheet workbooks, formulas, and cell formatting.

This article assumes that you are familiar with the following topics:

• XSL Transformations
• Excel Spreadsheet XML
• ASP.NET

back to the top

Sample web application

Overview

This step-by-step sample illustrates how server-side and client-side components work together to deliver and display Excel Spreadsheet XML.

• Default.htm is a frameset that has two frames. The header frame contains a list of order IDs and the main frame displays order information after an order ID is selected from the list.
• Header.htm contains the HTML element tags and script for the header frame.
• Getdata.vb is an HTTP handler that serves two purposes. The handler adds the list of order IDs for the header frame, and it processes requests for order information that is displayed in the main frame. When the HTTP handler receives a request for order information, it creates a DataSet for the order and returns an XML representation of that DataSet to the caller.
• Transform.xslt is an XSLT file that is used to transform the DataSet XML to Excel Spreadsheet XML.

The sample code uses order data in the Microsoft SQL Server Northwind sample database. The sample assumes that you will create and test the Web application on your local computer (http://localhost). If you will not be using a Web server on your local computer, replace localhost in the URLs of the sample with the name of your Web server.

back to the top

Generate the DataSet

In this section, you create the HTTP handler and the client-side components that are needed to retrieve order information. The DataSet is returned to the main frame as plain XML; the DataSet is not transformed.

1. Create an empty Web project named ExcelTransform. To do this, follow these steps:
   1. On the File menu in Visual Studio. NET, click New, and then click Project.
   2. Click Visual Basic Projects, and then click the Empty Web Project template.
   3. Name the project http://localhost/ExcelTransform, and then click OK.
2. Add references to the project. To do this, follow these steps:
   1. On the Project menu, click Add Reference.
   2. In the list of components on the .NET tab, click System.data.dll, and then click Select.
   3. Repeat the preceding step for System.dll, for System.Web.dll, and for System.XML.dll.
   4. Click OK.
3. On the Project menu, click Add Class, name the class Getdata.vb, and then click OK.
4. Replace the code in Getdata.vb with the following.
   
   Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

   Imports System.Web
   Imports System.Xml
   Imports System.Xml.Xsl
   Imports System.Data
   Imports System.Data.SqlClient
   
   Public Class GetData
      Implements IHttpHandler
   
      Private sConn As String = _
         "User ID=<username>;Password=<strong password>;Initial Catalog=Northwind;Data Source=YourSQLServer;"
   
      Public ReadOnly Property IsReusable() As Boolean _
      Implements IHttpHandler.IsReusable
         Get
            Return False
         End Get
      End Property
   
      Public Sub ProcessRequest(ByVal context As HttpContext) _
      Implements IHttpHandler.ProcessRequest
   
         Dim conn As SqlConnection
         Dim sOrderRequested As String
         sOrderRequested = context.Request.Item("OrderID")
   
         If Not (sOrderRequested > "") Then
   
            '=== If no order is requested, assume that this is a request
            '=== to fill the drop-down list in the Header.htm template
            '=== with the list of OrderIDs.
   
            'Get a DataSet for a list of OrderIDs.
            Dim sSQL As String = "Select OrderID from Orders"
            conn = New SqlConnection(sConn)
            conn.Open()
            Dim cmd As New SqlCommand(sSQL, conn)
            Dim rdr As SqlDataReader = cmd.ExecuteReader
   
            'Open the header template for the frameset and fill
            'in the <option> child nodes for the drop-down lists.
            Dim sHTML As String, sOrderID As String
            Dim xmlDoc As New XmlDocument()
            xmlDoc.Load(context.Server.MapPath("header.htm"))
            Dim oElem As XmlElement = _
               xmlDoc.DocumentElement.GetElementsByTagName("select").Item(0)
            Dim oChild As XmlElement
            Do While rdr.Read
               sOrderID = rdr.GetInt32(0).ToString
               oChild = xmlDoc.CreateElement("option")
               oChild.SetAttribute("value", sOrderID)
               oChild.InnerText = sOrderID
               oElem.AppendChild(oChild)
            Loop
            rdr.Close()
            conn.Close()
   
            'Return the modified header template.
            context.Response.Write(xmlDoc.InnerXml)
   
         Else
   
            '=== If an order is requested, create a DataSet for that
            '=== order and return the results to the client browser.
   
            'Build a DataSet for the order.
            conn = New SqlConnection(sConn)
            conn.Open()
            Dim ds As DataSet = New DataSet("Order")
            Dim CustDa As SqlDataAdapter = New SqlDataAdapter( _
               "SELECT OrderID, CompanyName, Address, City, Region, PostalCode, Country, Freight " & _
               "FROM Customers " & _
               "INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID " & _
               "WHERE (((Orders.OrderID)=" & sOrderRequested & "))", conn)
            CustDa.Fill(ds, "Customer")
            Dim ItemsDa As SqlDataAdapter = New SqlDataAdapter( _
               "SELECT Products.ProductName, [Order Details].Quantity, " & _
               "  [Order Details].[UnitPrice]*[Quantity]*(1-[Discount]) AS ItemTotal " & _
               "FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID " & _
               "WHERE (([Order Details].[OrderID]) = " & sOrderRequested & ")", conn)
            ItemsDa.Fill(ds, "Items")
            conn.Close()
   
            SendResults(context, ds)
            context.Response.End()
   
         End If
   
      End Sub
   
       Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
            'Write the XML for the DataSet.
            context.Response.ContentType = "text/xml"
            context.Response.Output.Write(ds.GetXml)
            context.Response.End()
       End Sub
   End Class

   NOTE: In Getdata.vb, modify the assignment to sConn to a valid connection string for a computer that is running SQL Server and that contains the Northwind sample database.
   
   
5. On the Project menu, click Add New Item, click the Web Configuration File template, and then click OK.
6. Replace the code in Web.config with the following:

   <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
      <system.web>
         <httpHandlers>
            <add verb="*" path="GetData.aspx" type="ExcelTransform.GetData, ExcelTransform" />
         </httpHandlers>
      </system.web>
   </configuration> 

7. On the Project menu, click Add HTML Page, name the page Header.htm, and then click OK.
8. On the View menu, click HTML Source.
9. Replace the code in Header.htm with the following:

   <html>
      <script language="javascript">
      <!--
      function OrderSelect_onchange() {
         window.parent.frames("main").location.replace("about:blank");
         if(OrderSelect.selectedIndex>0) {
            window.setTimeout("OpenOrder()", 50); 
         }
      }
      function OpenOrder() {
         var order = OrderSelect.options(OrderSelect.selectedIndex).value;
         window.parent.frames("main").location.href=
            "http://localhost/exceltransform/getdata.aspx?orderid=" + order;
      }
      //-->
      </script>
      <body>
         <select id="OrderSelect" onchange="return OrderSelect_onchange()">
            <option value="0">Select an Order</option>
         </select>
      </body>
   </html>

   NOTE: The HTTP handler loads Header.htm as an XML document. To load without error, Header.htm must be well-formed XML. If you modify Header.htm, make sure that it is well-formed; all elements must have proper start and end tags, and all attribute assignments must be enclosed in quotation marks.
   
   
10. On the Project menu, click Add HTML Page, click the Frameset template, name the file Default.htm, and then click OK. When you are prompted, click the Header frameset type, and then click OK.
11. On the View menu, click HTML Source.
12. In Frameset.htm, set the src and scrolling attributes for the header frame as follows:

    <frame name="header" src="http://localhost/exceltransform/getdata.aspx" scrolling="yes" noresize>

13. In Solution Explorer, right-click Default.htm, and then click Set As Start Page.
14. Follow these steps to test the sample:
    1. On the Debug menu, click Start without Debugging. The frameset opens in the browser with a drop-down list in the header frame that contains a list of order IDs.
    2. Select any order ID from the drop-down list.
    3. Examine the XML that appears in the main frame. The XML is a representation of the DataSet that the Getdata HTTP handler created according to your request.
    4. Select additional order IDs from the drop-down list to examine the results.
    5. When you are finished testing the Web application, quit Microsoft Internet Explorer.

back to the top

Transform the DataSet to Excel XML

In this section, you add additional code to transform the DataSet for display in Excel.

1. Open the ExcelTransform Web project that you just created.
2. On the Project menu, click Add New Item, click the XSLT File template, name the project Transform.xslt, and then click OK.
3. Replace the contents of Transform.xslt with the following:

   <xsl:stylesheet version="1.0"
       xmlns="urn:schemas-microsoft-com:office:spreadsheet"
       xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
   	xmlns:msxsl="urn:schemas-microsoft-com:xslt"
   	xmlns:user="urn:my-scripts"
   	xmlns:o="urn:schemas-microsoft-com:office:office"
   	xmlns:x="urn:schemas-microsoft-com:office:excel"
   	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >   
   
   <xsl:template match="Order">
   
   <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
   
    <Styles>
     <Style ss:ID="Default" ss:Name="Normal">
      <Alignment ss:Vertical="Bottom"/>
      <Borders/>
      <Font/>
      <Interior/>
      <NumberFormat/>
      <Protection/>
     </Style>
     <Style ss:ID="s21">
      <Font ss:Bold="1"/>
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
     </Style>
     <Style ss:ID="s22">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
      <Font ss:Bold="1"/>
      <Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
     </Style>
     <Style ss:ID="s23" ss:Name="Currency">
      <NumberFormat
       ss:Format="_(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;* \(#,##0.00\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)"/>
     </Style>
     <Style ss:ID="s24">
      <NumberFormat ss:Format="_(* #,##0.00_);_(* \(#,##0.00\);_(* &quot;-&quot;??_);_(@_)"/>
     </Style>
     <Style ss:ID="s25">
      <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
     </Style>
    </Styles>
   
    <Worksheet>
    <xsl:attribute name="ss:Name">
      <xsl:value-of select='concat("Order #", Customer/OrderID)'/>
    </xsl:attribute>
     <Table ss:ExpandedColumnCount="3">
     <xsl:attribute name="ss:ExpandedRowCount" >
   	<xsl:value-of select="count(Items)+10"/>
     </xsl:attribute>
   
      <Column ss:AutoFitWidth="0" ss:Width="150"/>
      <Column ss:AutoFitWidth="0" ss:Width="100"/>
      <Column ss:AutoFitWidth="0" ss:Width="75"/>
      
      <xsl:apply-templates select="Customer"/>
   
      <Row>
       <Cell ss:StyleID="s21"><Data ss:Type="String">Item</Data></Cell>
       <Cell ss:StyleID="s21"><Data ss:Type="String">Quantity</Data></Cell>
       <Cell ss:StyleID="s21"><Data ss:Type="String">Total</Data></Cell>
      </Row>
   
      <xsl:apply-templates select="Items"/>
      
      <Row>
       <Cell ss:Index="2"><Data ss:Type="String">Subtotal</Data></Cell>
       <Cell ss:StyleID="s23" ss:Formula="=SUM(R8C:R[-1]C)"/>
      </Row>
      <Row>
       <Cell ss:Index="2"><Data ss:Type="String">Freight</Data></Cell>
       <Cell ss:StyleID="s23"><Data ss:Type="Number"><xsl:value-of select="Customer/Freight"/></Data></Cell>
      </Row>
      <Row>
       <Cell ss:Index="2"><Data ss:Type="String">Total</Data></Cell>
       <Cell ss:StyleID="s23" ss:Formula="=R[-2]C+R[-1]C"/>
      </Row>
     </Table>
    </Worksheet>
   </Workbook>
   
   </xsl:template>
   
   <xsl:template match="Customer">
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="CompanyName"/></Data></Cell>
      </Row>
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="Address"/></Data></Cell>
      </Row>
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select='concat(City, ", ", Region, " ", PostalCode)'/></Data></Cell>
      </Row>
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="Country"/></Data></Cell>
      </Row>
      <Row ss:Index="6">
       <Cell ss:MergeAcross="2" ss:StyleID="s22">
        <Data ss:Type="String">Order #<xsl:value-of select="OrderID"/></Data>
       </Cell>
      </Row>   
   </xsl:template>
   
   <xsl:template match="Items">
      <Row>
       <Cell><Data ss:Type="String"><xsl:value-of select="ProductName"/></Data></Cell>
       <Cell ss:StyleID="s25"><Data ss:Type="Number"><xsl:value-of select="Quantity"/></Data></Cell>
       <Cell ss:StyleID="s24"><Data ss:Type="Number"><xsl:value-of select="ItemTotal"/></Data></Cell>
      </Row>
   </xsl:template>
   
   </xsl:stylesheet>

4. In Getdata.vb, replace the SendResults function with the following :

   Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
       Dim sOrderID As String = ds.Tables(0).Rows(0).Item(0)
   
       'Set up the response for Excel.
       context.Response.ContentType = "application/vnd.ms-excel"
       context.Response.Charset = ""
   
       'Transform the DataSet XML using transform.xslt 
       'and return the results to the client in Response.Outputstream.
       Dim tw As XmlTextWriter
       Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
       Dim xslTran As XslTransform = New XslTransform()
       xslTran.Load(context.Server.MapPath("transform.xslt"))
       xslTran.Transform(xmlDoc, Nothing, context.Response.OutputStream)
       context.Response.End()
   End Sub

5. Follow these steps to test the sample:
   1. On the Debug menu, click Start without Debugging. The frameset opens in the browser with a drop-down list in the header frame that contains a list of order IDs.
   2. Select any order ID from the drop-down list. Note that the DataSet that you requested was built, and is transformed into Spreadsheet XML and displayed in Excel. The data in Excel contains formatting and calculations.
   3. When you are finished testing the Web application, quit Internet Explorer.

back to the top

Save the transformed XML in a file (optional)

In the previous section, the HTTP handler streams the transformed XML to the client. You may want to save the DataSet XML and the results of the transformation in a file. This can be a useful troubleshooting step if you find that the transformation does not give you the results that you expect. You can use this approach to examine the DataSet XML and the transformed XML for potential errors.

NOTE: The sample saves the XML files in the Web application folder. You may have to change permissions for the folder, as demonstrated in the following steps.

1. Set Write permissions on the folder for the ASP.NET process:
   1. Start Windows Explorer.
   2. Locate the Web application folder. The default path is C:\Inetpub\Wwwroot\ExcelTransform.
   3. Right-click the ExcelTransform folder, and then click Properties.
   4. On the Security tab, click Add.
   5. Under Enter the object names to select, type the object name YourComputerName\aspnet, and then click OK.
   6. On the Security tab, click to select Write to allow Write permissions for the YourComputerName\aspnet account, and then click OK.
2. In Getdata.vb, replace the SendResults function with the following:

   Private Sub SendResults(ByVal context As HttpContext, ByVal ds As DataSet)
       Dim sOrderID As String = ds.Tables(0).Rows(0).Item(0)
   
       'First, save the XML representation of the DataSet in a file
       'and add a processing instruction to the XML so that it can be
       'transformed client-side.
       Dim tw As XmlTextWriter
       tw = New XmlTextWriter(context.Server.MapPath("order" & sOrderID & ".xml"), System.Text.Encoding.UTF8)
       tw.Formatting = Formatting.Indented
       tw.Indentation = 3
       tw.WriteStartDocument()
       tw.WriteProcessingInstruction("xml-stylesheet", _
   	"type='text/xsl' href='http://localhost/ExcelTransform/transform.xslt'")
       ds.WriteXml(tw)
       tw.Close()
   
       'Second, transform the DataSet XML and save it to a file.
       Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds)
       Dim xslTran As XslTransform = New XslTransform()
       xslTran.Load(context.Server.MapPath("transform.xslt"))
       tw = New XmlTextWriter(context.Server.MapPath("order" & sOrderID & ".xls"), System.Text.Encoding.UTF8)
       tw.Formatting = Formatting.Indented
       tw.Indentation = 3
       tw.WriteStartDocument()
       xslTran.Transform(xmlDoc, Nothing, tw)
       tw.Close()
   
       'Optionally, redirect to the saved transformation.
       context.Response.Redirect( _
   		"http://localhost/ExcelTransform/order" & sOrderID & ".xls")
       context.Response.End()
   End Sub

3. Follow these steps to test the sample:
   1. On the Debug menu, click Start Without Debugging.
   2. Select an order in the drop-down list. The transformed XML appears in the main frame in Excel.
   3. Examine the raw DataSet XML, C:\Inetpub\Wwwroot\ExcelTransform\orderNNNNN.xml. You can open the XML in any text editor or in Excel. If you open Order.xml in Excel, you are prompted to apply the stylesheet.
   4. Examine the Spreadsheet XML, C:\Inetpub\Wwwroot\ExcelTransform\orderNNNNN.xls. You can open the XML in any text editor or in Excel.

back to the top

Comments

When you build your own XSLT file for Excel, first create a workbook template in Excel that contains the formatting and formulas that you need, and then save the workbook in Spreadsheet XML. You can then modify the XML so that it contains the XSL expressions and elements that you need to correctly transform your DataSet XML. When you modify the XML that you saved from Excel, note the following:

• The cells in a worksheet are represented by a <Table> element in XML. The <Table> has two attributes, ss:ExpandedColumnCount and ss:ExpandedRowCount, that designate which cells are used in the worksheet (that is, the "used range"). If the worksheet has a varying number of rows or columns (or both), set these attributes during transformation. Note that the ss:ExpandedRowCount attribute in transform.xslt is set based on a count of certain nodes in the DataSet XML.
• Formulas in cells are represented in RC notation, and not in A1 notation.
• Each different formatting combination that you apply to cells on a worksheet is given its own style. Styles are stored in the <Style> element of the Spreadsheet XML.

back to the top