Microsoft KB Archive/319180

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

Article ID: 319180

Article Last Modified on 5/18/2007

-

APPLIES TO


 * Microsoft Excel 2002 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft .NET Framework 1.1 Service Pack 1
 * Microsoft Office Excel 2003
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q319180



IN THIS TASK

 * SUMMARY
 * Sample web application
 * Overview
 * Generate the DataSet
 * Transform the DataSet to Excel XML
 * Save the transformed XML in a file (optional)
 * Comments
 * REFERENCES



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

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  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.  Create an empty Web project named ExcelTransform. To do this, follow these steps:  On the File menu in Visual Studio. NET, click New, and then click Project. Click Visual Basic Projects, and then click the Empty Web Project template. Name the project http://localhost/ExcelTransform, and then click OK.  Add references to the project. To do this, follow these steps:  On the Project menu, click Add Reference.</li> In the list of components on the .NET tab, click System.data.dll, and then click Select.</li> Repeat the preceding step for System.dll, for System.Web.dll, and for System.XML.dll.</li> Click OK.</li></ol> </li> On the Project menu, click Add Class, name the class Getdata.vb, and then click OK.</li>  Replace the code in Getdata.vb with the following.

Note You must change User ID and 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 = _ &quot;User ID= ;Password= ;Initial Catalog=Northwind;Data Source=YourSQLServer;&quot;

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(&quot;OrderID&quot;)

If Not (sOrderRequested > &quot;&quot;) 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 = &quot;Select OrderID from Orders&quot; 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 child nodes for the drop-down lists. Dim sHTML As String, sOrderID As String Dim xmlDoc As New XmlDocument xmlDoc.Load(context.Server.MapPath(&quot;header.htm&quot;)) Dim oElem As XmlElement = _ xmlDoc.DocumentElement.GetElementsByTagName(&quot;select&quot;).Item(0) Dim oChild As XmlElement Do While rdr.Read sOrderID = rdr.GetInt32(0).ToString oChild = xmlDoc.CreateElement(&quot;option&quot;) oChild.SetAttribute(&quot;value&quot;, 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(&quot;Order&quot;) Dim CustDa As SqlDataAdapter = New SqlDataAdapter( _           &quot;SELECT OrderID, CompanyName, Address, City, Region, PostalCode, Country, Freight &quot; & _            &quot;FROM Customers &quot; & _            &quot;INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID &quot; & _            &quot;WHERE (((Orders.OrderID)=&quot; & sOrderRequested & &quot;))&quot;, conn) CustDa.Fill(ds, &quot;Customer&quot;) Dim ItemsDa As SqlDataAdapter = New SqlDataAdapter( _           &quot;SELECT Products.ProductName, [Order Details].Quantity, &quot; & _            &quot;  [Order Details].[UnitPrice]*[Quantity]*(1-[Discount]) AS ItemTotal &quot; & _            &quot;FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID &quot; & _            &quot;WHERE (([Order Details].[OrderID]) = &quot; & sOrderRequested & &quot;)&quot;, conn) ItemsDa.Fill(ds, &quot;Items&quot;) 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 = &quot;text/xml&quot; 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.

</li> On the Project menu, click Add New Item, click the Web Configuration File template, and then click OK.</li>  Replace the code in Web.config with the following: <?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?> <system.web> <httpHandlers> <add verb=&quot;*&quot; path=&quot;GetData.aspx&quot; type=&quot;ExcelTransform.GetData, ExcelTransform&quot; /> </httpHandlers> </system.web> </li> On the Project menu, click Add HTML Page, name the page Header.htm, and then click OK.</li> On the View menu, click HTML Source.</li>  Replace the code in Header.htm with the following: <script language=&quot;javascript&quot;> <select id=&quot;OrderSelect&quot; onchange=&quot;return OrderSelect_onchange&quot;> <option value=&quot;0&quot;>Select an Order 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.

</li> 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.</li> On the View menu, click HTML Source.</li>  In Frameset.htm, set the src and scrolling attributes for the header frame as follows: <frame name=&quot;header&quot; src=&quot;http://localhost/exceltransform/getdata.aspx&quot; scrolling=&quot;yes&quot; noresize> </li> In Solution Explorer, right-click Default.htm, and then click Set As Start Page.</li> Follow these steps to test the sample: <ol style="list-style-type: lower-alpha;"> <li>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.</li> <li>Select any order ID from the drop-down list.</li> <li>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.</li> <li>Select additional order IDs from the drop-down list to examine the results.</li> <li>When you are finished testing the Web application, quit Microsoft Internet Explorer.</li></ol> </li></ol>

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. <ol> <li>Open the ExcelTransform Web project that you just created.</li> <li>On the Project menu, click Add New Item, click the XSLT File template, name the project Transform.xslt, and then click OK.</li> <li> Replace the contents of Transform.xslt with the following: <xsl:stylesheet version=&quot;1.0&quot; xmlns=&quot;urn:schemas-microsoft-com:office:spreadsheet&quot; xmlns:xsl=&quot;http://www.w3.org/1999/XSL/Transform&quot; xmlns:msxsl=&quot;urn:schemas-microsoft-com:xslt&quot; xmlns:user=&quot;urn:my-scripts&quot; xmlns:o=&quot;urn:schemas-microsoft-com:office:office&quot; xmlns:x=&quot;urn:schemas-microsoft-com:office:excel&quot; xmlns:ss=&quot;urn:schemas-microsoft-com:office:spreadsheet&quot; >

<xsl:template match=&quot;Order&quot;>

<Workbook xmlns=&quot;urn:schemas-microsoft-com:office:spreadsheet&quot; xmlns:o=&quot;urn:schemas-microsoft-com:office:office&quot; xmlns:x=&quot;urn:schemas-microsoft-com:office:excel&quot; xmlns:ss=&quot;urn:schemas-microsoft-com:office:spreadsheet&quot; xmlns:html=&quot;http://www.w3.org/TR/REC-html40&quot;>

<Styles> <Style ss:ID=&quot;Default&quot; ss:Name=&quot;Normal&quot;> <Alignment ss:Vertical=&quot;Bottom&quot;/> <Borders/> <Font/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID=&quot;s21&quot;> <Font ss:Bold=&quot;1&quot;/> <Alignment ss:Horizontal=&quot;Center&quot; ss:Vertical=&quot;Bottom&quot;/> </Style> <Style ss:ID=&quot;s22&quot;> <Alignment ss:Horizontal=&quot;Center&quot; ss:Vertical=&quot;Bottom&quot;/> <Font ss:Bold=&quot;1&quot;/> <Interior ss:Color=&quot;#99CCFF&quot; ss:Pattern=&quot;Solid&quot;/> </Style> <Style ss:ID=&quot;s23&quot; ss:Name=&quot;Currency&quot;> <NumberFormat ss:Format=&quot;_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)&quot;/> </Style>  <Style ss:ID=&quot;s24&quot;>   <NumberFormat ss:Format=&quot;_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)&quot;/> </Style> <Style ss:ID=&quot;s25&quot;> <Alignment ss:Horizontal=&quot;Center&quot; ss:Vertical=&quot;Bottom&quot;/> </Style> </Styles>

<Worksheet> <xsl:attribute name=&quot;ss:Name&quot;> <xsl:value-of select='concat(&quot;Order #&quot;, Customer/OrderID)'/> </xsl:attribute> </Cell> <Cell ss:StyleID=&quot;s21&quot;> </Cell> <Cell ss:StyleID=&quot;s21&quot;> </Cell> </Row>

<xsl:apply-templates select=&quot;Items&quot;/> <Row> <Cell ss:Index=&quot;2&quot;> </Cell> <Cell ss:StyleID=&quot;s23&quot; ss:Formula=&quot;=SUM(R8C:R[-1]C)&quot;/> </Row> <Row> <Cell ss:Index=&quot;2&quot;> </Cell> <Cell ss:StyleID=&quot;s23&quot;> </Cell> </Row> <Row> <Cell ss:Index=&quot;2&quot;> </Cell> <Cell ss:StyleID=&quot;s23&quot; ss:Formula=&quot;=R[-2]C+R[-1]C&quot;/> </Row> </Table> </Worksheet> </Workbook>

</xsl:template>

<xsl:template match=&quot;Customer&quot;> <Row> <Cell> </Cell> </Row> <Row> <Cell> </Cell> </Row> <Row> <Cell> </Cell> </Row> <Row> <Cell> </Cell> </Row> <Row ss:Index=&quot;6&quot;> <Cell ss:MergeAcross=&quot;2&quot; ss:StyleID=&quot;s22&quot;> </Cell> </Row> </xsl:template>

<xsl:template match=&quot;Items&quot;> <Row> <Cell> </Cell> <Cell ss:StyleID=&quot;s25&quot;> </Cell> <Cell ss:StyleID=&quot;s24&quot;> </Cell> </Row> </xsl:template>

</xsl:stylesheet> </li> <li> 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 = &quot;application/vnd.ms-excel&quot; context.Response.Charset = &quot;&quot;

'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(&quot;transform.xslt&quot;)) xslTran.Transform(xmlDoc, Nothing, context.Response.OutputStream) context.Response.End End Sub </li> <li>Follow these steps to test the sample: <ol style="list-style-type: lower-alpha;"> <li>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.</li> <li>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.</li> <li>When you are finished testing the Web application, quit Internet Explorer.</li></ol> </li></ol>

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. <ol> <li>Set Write permissions on the folder for the ASP.NET process: <ol style="list-style-type: lower-alpha;"> <li>Start Windows Explorer.</li> <li>Locate the Web application folder. The default path is C:\Inetpub\Wwwroot\ExcelTransform.</li> <li>Right-click the ExcelTransform folder, and then click Properties.</li> <li>On the Security tab, click Add.</li> <li>Under Enter the object names to select, type the object name \aspnet, and then click OK.</li> <li>On the Security tab, click to select Write to allow Write permissions for the \aspnet account, and then click OK.</li></ol> </li> <li> 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(&quot;order&quot; & sOrderID & &quot;.xml&quot;), System.Text.Encoding.UTF8) tw.Formatting = Formatting.Indented tw.Indentation = 3 tw.WriteStartDocument tw.WriteProcessingInstruction(&quot;xml-stylesheet&quot;, _   &quot;type='text/xsl' href='http://localhost/ExcelTransform/transform.xslt'&quot;) 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(&quot;transform.xslt&quot;)) tw = New XmlTextWriter(context.Server.MapPath(&quot;order&quot; & sOrderID & &quot;.xls&quot;), 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( _       &quot;http://localhost/ExcelTransform/order&quot; & sOrderID & &quot;.xls&quot;) context.Response.End End Sub </li> <li>Follow these steps to test the sample: <ol style="list-style-type: lower-alpha;"> <li>On the Debug menu, click Start Without Debugging.</li> <li>Select an order in the drop-down list. The transformed XML appears in the main frame in Excel.</li> <li>Examine the raw DataSet XML, C:\Inetpub\Wwwroot\ExcelTransform\order .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.</li> <li>Examine the Spreadsheet XML, C:\Inetpub\Wwwroot\ExcelTransform\order .xls. You can open the XML in any text editor or in Excel.</li></ol> </li></ol>

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 &quot;used range&quot;). 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

<div class="references_section">