如何使用 Visual Basic .NET 和 ASP.NET 将数据集转换为 Excel 的电子表格 XML

本任务的内容

•

概要

•

Web 应用程序示例

•	概述
•	生成数据集
•	将数据集转换为 Excel XML
•	将转换后的 XML 保存成文件（可选）

•

注解

•

参考

本页内容

	概要
	参考

概要

本分步指南介绍了如何将数据集转换为可以在 Excel 中呈现的电子表格 XML。Excel 电子表格 XML 格式支持 Excel 功能（如多表格工作簿、公式和单元格格式）所用的元素标记和属性。

本文假定您熟悉下列主题：

•	XSL 转换
•	Excel 电子表格 XML
•	ASP.NET

返回页首

Web 应用程序示例

概述

本示例逐步阐释了服务器端组件和客户端组件是如何协同工作以提供和显示 Excel 电子表格 XML 的。

•	Default.htm 是一个有两个框架的框架集。页眉框架包含订单 ID 的列表，而主框架则在从列表中选择订单 ID 后显示订单信息。
•	Header.htm 包括用于页眉框架的 HTML 元素标记和脚本。
•	Getdata.vb 是一个具有双重作用的 HTTP 处理程序。该处理程序为页眉框架添加了订单 ID 列表，并处理对显示在主框架中订单信息的请求。当 HTTP 处理程序收到对订单信息的请求后，它将为该订单创建一个数据集，并向调用方返回该数据集的 XML 表示形式。
•	Transform.xslt 是一个 XSLT 文件，它用于将数据集 XML 转换成 Excel 电子表格 XML。

示例代码将使用 Microsoft SQL Server Northwind 示例数据库中的订单数据。该示例假定您在本地计算机 (http://localhost) 中创建和测试 Web 应用程序。如果您不打算在本地计算机上使用 Web 服务器，则请使用您的 Web 服务器名称替换示例 URL 中的 localhost。

返回页首

生成数据集

在本节中，您将创建检索订单信息所需的 HTTP 处理程序和客户端组件。数据集作为纯 XML 返回到主框架；未转换数据集。

1.

创建名为 ExcelTransform 的空 Web 项目。为此，请按照下列步骤操作：

a.	在 Visual Studio .NET 的文件菜单上，单击新建，然后单击项目。
b.	单击 Visual Basic 项目，然后单击空 Web 项目模板。
c.	将该项目命名为 http://localhost/ExcelTransform，然后单击确定。

2.

向项目中添加引用。为此，请按照下列步骤操作：

a.	在项目菜单上，单击添加引用。
b.	在 .NET 选项卡上的组件列表中，单击 System.data.dll，然后单击选择。
c.	对 System.dll、System.Web.dll 和 System.XML.dll 重复上述步骤。
d.	单击确定。

3.

在项目菜单上，单击添加类，将类命名为 Getdata.vb，然后单击确定。

4.

将 Getdata.vb 中的代码替换为以下代码。

注意：在运行此代码之前，必须将 User ID <username> 和 password =<strong password> 更改为正确的值。请确保该用户 ID 具有在数据库中执行此操作所需的适当权限。

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

注意：在 Getdata.vb 中，将对 sConn 的赋值修改为一个运行 SQL Server 并且包含 Northwind 示例数据库的计算机的有效连接字符串。

5.

在项目菜单上，单击添加新项，单击 Web 配置文件模板，然后单击确定。

6.

将 Web.config 中的代码替换为以下代码：

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

在项目菜单上，单击添加 HTML 页，将页命名为 Header.htm，然后单击确定。

8.

在查看菜单上，单击 HTML 源代码。

9.

将 Header.htm 中的代码替换为以下代码：

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

注意：HTTP 处理程序将 Header.htm 作为 XML 文档加载。要想在加载时不出错，Header.htm 必须是格式正确的 XML。如果修改 Header.htm，请确保它的格式正确；所有元素都必须具有正确的开始和结束标记，而且所有属性赋值都必须用引号引起来。

10.

在项目菜单上，单击添加 HTML 页，单击框架集模板，将文件命名为 Default.htm，然后单击确定。得到提示时，请单击页眉框架集类型，然后单击确定。

11.

在查看菜单上，单击 HTML 源代码。

12.

在 Frameset.htm 中，按照以下步骤设置页眉框架的 src 和 scrolling 属性：

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

13.

在解决方案资源管理器中，右键单击 Default.htm，然后单击设为起始页。

14.

按照下列步骤测试该示例：

a.	在调试菜单上，单击开始执行（不调试）。框架集在浏览器中打开，页眉框架中有一个下拉列表，其中包含一个订单 ID 列表。
b.	从下拉列表中选择任何订单 ID。
c.	检查主框架中出现的 XML。XML 是 Getdata HTTP 处理程序根据您的请求创建的数据集的一种表示形式。
d.	从下拉列表选择其他订单 ID 以检查结果。
e.	Web 应用程序测试完毕后，请退出 Microsoft Internet Explorer。

返回页首

将数据集转换为 Excel XML

在本节中，将添加附加代码来转换数据集以便在 Excel 中显示。

1.

打开刚创建的 ExcelTransform Web 项目。

2.

在项目菜单上，单击添加新项，单击 XSLT 文件模板，将项目命名为 Transform.xslt，然后单击确定。

3.

将 Transform.xslt 中的内容替换为以下内容：

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

在 Getdata.vb 中，将 SendResults 函数替换为以下内容：

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.

按照下列步骤测试该示例：

a.	在调试菜单上，单击开始执行（不调试）。框架集在浏览器中打开，页眉框架中有一个下拉列表，其中包含一个订单 ID 列表。
b.	从下拉列表中选择任何订单 ID。您会注意到您请求的数据集已创建出来，并转换成电子表格 XML 显示在 Excel 中。Excel 中的数据包含格式设置和计算。
c.	Web 应用程序测试完毕后，退出 Internet Explorer。

返回页首

将转换后的 XML 保存成文件（可选）

在上一节中，HTTP 处理程序将转换后的 XML 传输到客户端。您可能需要将数据集 XML 和转换结果保存到文件中。如果您发现转换没有产生预期的效果，则这可能是一个很有用的故障排查步骤。您可以使用这种方法检查数据集 XML 和转换后的 XML 以找到潜在错误。

注意：该示例将 XML 文件保存到 Web 应用程序文件夹中。您可能必须更改该文件夹的权限，如下列步骤所示。

1.

对于 ASP.NET 进程，将对该文件夹的权限设置为“写”：

a.	启动 Windows 资源管理器。
b.	找到 Web 应用程序文件夹。默认路径为 C:\Inetpub\Wwwroot\ExcelTransform。
c.	右键单击 ExcelTransform 文件夹，然后单击属性。
d.	在安全选项卡上，单击添加。
e.	在输入要选择的对象名称下，键入对象名称您计算机的名称\aspnet，然后单击确定。
f.	在安全选项卡上，单击以选中写入以授予您计算机的名称\aspnet 帐户“写”权限，然后单击确定。

2.

在 Getdata.vb 中，将 SendResults 函数替换为以下内容：

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.

按照下列步骤测试该示例：

a.	在调试菜单上，单击开始执行（不调试）。
b.	从下拉列表中选择一个订单。转换后的 XML 将出现在 Excel 中的主框架中。
c.	检查原始数据集 XML，C:\Inetpub\Wwwroot\ExcelTransform\orderNNNNN.xml。您可以在任何文本编辑器或者在 Excel 中打开 XML。如果您在 Excel 中打开 Order.xml，将提示您应用样式表。
d.	检查电子表格 XML，C:\Inetpub\Wwwroot\ExcelTransform\orderNNNNN.xls。您可以在任何文本编辑器或者在 Excel 中打开 XML。

返回页首

注解

当您创建自己的 Excel XSLT 文件时，首先要在 Excel 中创建一个工作簿模板，让它包含您需要的格式设置和公式，之后将该工作簿保存为电子表格 XML。然后，您可以修改该 XML，以便它包含正确地转换数据集 XML 所需的 XSL 表达式和元素。在修改从 Excel 中保存的 XML 时，应注意以下事项：

•	在工作表中的单元格将由 XML 中的 <表> 元素表示。<表> 有两个属性，ss:ExpandedColumnCount 和 ss:ExpandedRowCount，它们指定使用工作表中的哪些单元格（亦即“使用区域”）。如果工作表的行数或列数（或两者）不停变化，则应在转换期间设置这两个属性。请注意，transform.xslt 中的 ss:ExpandedRowCount 属性是根据数据集 XML 中特定节点的计数而设置的。
•	单元格中的公式以 RC 标记而不是以 A1 标记表示。
•	您对工作表中单元格应用的各种不同的格式组合都有它自己的样式。样式存储在电子表格 XML 的 <样式> 元素中。

返回页首