Como transformar um DataSet a folha de cálculo XML para o Excel utilizando o Visual Basic .NET e ASP.NET

Este artigo passo a passo descreve como transformar um DataSet a folha de cálculo XML que pode ser apresentado no Excel. O XML de folha de cálculo do Excel formato suporta elemento tags e atributos para funcionalidades tais como livros multi-sheet, fórmulas, o Excel e formatação de células.

Este artigo pressupõe que está familiarizado com os seguintes tópicos:

• Transformações XSL
• Folha de cálculo XML do Excel
• ASP.NET

Aplicação de web de exemplo

Descrição geral

Este passo a passo exemplo ilustra como lado do servidor e do lado do cliente componentes trabalho em conjunto para entregar e apresentar XML de folha de cálculo do Excel.

• default.htm é um conjunto de frames que tem duas molduras. A frame de cabeçalho contém uma lista de ordem de ID e a frame principal apresenta informações sobre encomendas depois de seleccionar um código da encomenda a partir da lista.
• Header.htm contém os códigos de elemento HTML e script para a moldura de cabeçalho.
• getdata.VB é um processador HTTP que tem duas finalidades. A rotina de tratamento adiciona à lista de ordem de ID da moldura de cabeçalho e processa pedidos de informações de ordem que são apresentadas na frame principal. Quando o processador de HTTP recebe um pedido de informação sobre a encomenda, cria um DataSet para a encomenda e devolve uma representação XML de DataSet que o autor da chamada.
• Transform.XSLT é um ficheiro XSLT que é utilizado para transformar o DataSet XML ao Excel folha de cálculo XML.

O código de exemplo utiliza dados da ordem no Microsoft SQL Server base de dados de exemplo Adamastor. O exemplo assume que vai criar e testar a aplicação Web no computador local (http://localhost). Se vai não utilizar um servidor Web no computador local, substitua localhost nos URLs do exemplo pelo nome do servidor Web.

Gerar o DataSet

Esta secção, pode criar o processador de HTTP e os componentes lado do cliente que são necessárias para obter informações sobre encomendas. DataSet é devolvida a frame principal como XML simples; não é transformados DataSet .

1. Crie um projecto Web vazio denominado ExcelTransform. Para o fazer, siga estes passos:
   1. No menu ficheiro no Visual Studio. NET, clique em Novo e, em seguida, clique em projecto .
   2. Clique em Projectos do Visual Basic e clique em modelo de Projecto da Web vazia .
   3. Atribua um nome http://localhost/ExcelTransform projecto e, em seguida, clique em OK .
2. Adicione referências ao projecto. Para o fazer, siga estes passos:
   1. No menu projecto , clique em Add Reference .
   2. Na lista de componentes no separador .NET , clique System.data.dll e, em seguida, clique em Seleccionar .
   3. Repita o passo anterior para System.dll , de System.Web.dll e para System.XML.dll .
   4. Clique em OK .
3. No menu Project , clique em Adicionar classe , nome da classe Getdata.vb e, em seguida, clique em OK .
4. Substitua o código de getdata.vb com o seguinte.
   
   Nota <username>Tem de alterar ID de utilizador <utilizador> e palavra-passe = < palavra-passe segura > para os valores correctos antes de executar este código. Certifique-se que o ID de utilizador tem as permissões adequadas para efectuar esta operação na base de dados.

   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

   Nota : em Getdata.vb , modificar a atribuição sConn para uma cadeia de ligação válida para um computador que esteja a executar o SQL Server e que contém a base de dados de exemplo Adamastor.
   
   
5. No menu projecto , clique em Adicionar novo item , faça clique sobre o modelo de Ficheiro de configuração Web e, em seguida, clique em OK .
6. Substitua o código em Web.config com o seguinte:

   <?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. No menu Project , clique em Adicionar página de HTML , a página Header.htm, nome e, em seguida, clique em OK .
8. No menu Ver , clique em Código HTML .
9. Substitua o código de Header.htm com o seguinte:

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

   Nota : processador de HTTP A carrega Header.htm como um documento XML. Para carregar sem erros, Header.htm tem de ser correctamente formado XML. Se modificar Header.htm, certifique-se de que está correctamente formado; todos os elementos tem de ter tags de início e de fim adequadas e, todas as atribuições de atributos tem de ser colocadas entre aspas.
   
   
10. No menu Project , clique em Adicionar página de HTML , faça clique sobre o modelo de conjunto de frames , o arquivo default.htm, nome e, em seguida, clique em OK . Quando lhe for pedido, faça clique sobre o tipo de conjunto de frames de cabeçalho e, em seguida, clique em OK .
11. No menu Ver , clique em Código HTML .
12. Em Frameset.htm, defina os atributos de src e deslocamento para a moldura de cabeçalho da seguinte forma:

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

13. No Solution Explorer, clique com o botão direito do rato ' default.htm ' e, em seguida, clique em Definir como página inicial .
14. Siga estes passos para testar o exemplo:
    1. No menu Debug , clique em Iniciar sem depuração . O conjunto de frames é aberta no browser com uma lista pendente na frame de cabeçalho que contém uma lista de IDs de encomenda.
    2. Seleccione qualquer código da encomenda na lista pendente.
    3. Examine o XML que aparece na frame principal. O XML é uma representação do DataSet que a rotina de tratamento Getdata HTTP criada acordo com a seu pedido.
    4. Seleccione ordem adicional ID na lista pendente para examinar os resultados.
    5. Quando tiver terminado de testar a aplicação da Web, saia do Microsoft Internet Explorer.

Transformar o DataSet para o Excel XML

Nesta secção, é adicionar código adicional para transformar o DataSet para apresentação no Excel.

1. Abra o projecto ExcelTransform Web que acabou de criar.
2. No menu Project , clique em Adicionar novo item , faça clique sobre o modelo de Ficheiro XSLT , nome do projecto Transform.xslt e, em seguida, clique em 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. Nos Getdata.vb , substitua a função SendResults o seguinte:

   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. Siga estes passos para testar o exemplo:
   1. No menu Debug , clique em Iniciar sem depuração . O conjunto de frames é aberta no browser com uma lista pendente na frame de cabeçalho que contém uma lista de IDs de encomenda.
   2. Seleccione qualquer código da encomenda na lista pendente. Note que o DataSet que pediu foi criado e é transformado folha de cálculo XML e apresentado no Excel. Os dados no Excel contém formatação e cálculos.
   3. Quando tiver terminado de testar a aplicação da Web, saia do Internet Explorer.

Guarde o XML transformado num ficheiro (opcional)

Na secção anterior, o processador de HTTP sequências o XML transformado ao cliente. Poderá guardar o XML de DataSet e os resultados da transformação num ficheiro. Isto pode ser um passo de resolução de problemas útil se achar que a transformação não lhe os resultados que esperava. Pode utilizar esta abordagem para examinar o XML de DataSet e o XML transformado erros potenciais.

Nota : O exemplo guarda os ficheiros XML na pasta de aplicação Web. Poderá ter de alterar permissões para a pasta, tal como demonstrado nos passos seguintes.

1. Definir permissões de escrita na pasta para o ASP.NET processo:
   1. Inicie o Explorador do Windows.
   2. Localize a pasta de aplicação Web. O caminho predefinido é C:\Inetpub\Wwwroot\ExcelTransform.
   3. Clique com o botão direito do rato na pasta ExcelTransform e, em seguida, clique em Propriedades .
   4. No separador segurança , clique em Adicionar .
   5. Em Introduza os nomes de objectos para seleccionar , escreva o objecto nome YourComputerName \aspnet e, em seguida, clique em OK .
   6. No separador segurança , clique para seleccionar menos conceda permissões de escrita para a conta de \aspnet YourComputerName e, em seguida, clique em OK .
2. Nos Getdata.vb , substitua a função SendResults o seguinte:

   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. Siga estes passos para testar o exemplo:
   1. No menu Debug , clique em Iniciar sem depuração .
   2. Seleccione uma ordem na lista pendente. O XML transformado aparece na frame principal no Excel.
   3. Examinar o XML de DataSet não processados, C:\Inetpub\Wwwroot\ExcelTransform\order NNNNN .XML. É possível abrir o XML em qualquer editor de texto ou no Excel. Se abrir Order.xml no Excel, é solicitado para aplicar a folha de estilos.
   4. Examine a folha de cálculo XML, C:\Inetpub\Wwwroot\ExcelTransform\order NNNNN .xls. É possível abrir o XML em qualquer editor de texto ou no Excel.

Comentários

Quando cria o seu ficheiro XSLT para o Excel, crie primeiro um modelo de livro no Excel que contém a formatação e fórmulas que necessita e, em seguida, guardar o livro na folha de cálculo XML. Em seguida, é possível modificar o XML para que contenha o XSL expressões e os elementos que pretende transformar correctamente o DataSet XML. Quando modifica o XML que guardou a partir do Excel, tenha em atenção o seguinte:

• As células na folha de cálculo são representadas por um <table> elemento XML. O <table> tem dois atributos, ss:ExpandedColumnCount e ss:ExpandedRowCount , designar quais as células que são utilizados na folha de cálculo (ou seja, "intervalo utilizado"). Se a folha de cálculo tiver um número variável de linhas ou colunas (ou ambos), definir estes atributos durante a transformação. Note que o atributo de ss:ExpandedRowCount transform.xslt é definido numa contagem de determinados nós no DataSet XML.
• Fórmulas em células são representadas na notação RC e não no notação A1.
• Cada combinação de formatação diferente aplicados a células numa folha de cálculo é dado o respectivo estilo. Estilos são armazenados na <style> elemento de folha de cálculo XML.

Nota Os programadores que utilizam o .NET Framework 1.1 devem estar ciente das alterações efectuadas à classe XslTransform entre o .NET Framework 1.0 e .NET Framework 1.1. Por exemplo, várias versões dos carregar e transformação de métodos (incluindo a versão que é utilizada neste artigo) sobrecarregadas são consideradas obsoletas começando com o .NET Framework 1.1. Para obter informações adicionais sobre o método XslTransform.Transform , visite o seguinte Web site da Microsoft Developer Network (MSDN):Para obter informações adicionais, clique números de artigo que se seguem para visualizar os artigos na base de dados de conhecimento da Microsoft: