Como transformar um DataSet a planilha XML para o Excel usando o Visual Basic .NET e ASP.NET

Este artigo passo a passo descreve como transformar um DataSet a planilha XML que pode ser renderizado no Excel. O XML de planilha do Excel formato oferece suporte a marcas de elemento e os atributos para Excel funcionalidades, como pastas de trabalho multi-sheet, fórmulas e formatação de célula.

Este artigo pressupõe que você esteja familiarizado com os seguintes tópicos:

• Transformações em XSL
• Planilha XML do Excel
• ASP.NET

Exemplo de aplicativo da web

Visão geral

Este exemplo passo a passo ilustra componentes de cliente e como servidor trabalham em conjunto para entregar e exibir XML de planilha do Excel.

• default.htm é um conjunto de quadros que tem dois quadros. O quadro de cabeçalho contém uma lista de identificações de ordem e o quadro principal exibe informações de ordem depois que um código do pedido é selecionado na lista.
• Header.htm contém as marcas de elemento HTML e script para o quadro de cabeçalho.
• getdata.vb é um manipulador HTTP que serve a dois propósitos. O manipulador adiciona a lista de ordem de identificações para o quadro de cabeçalho e processa solicitações de informações de ordem que são exibidas no quadro principal. Quando o manipulador HTTP recebe uma solicitação de informações da ordem, ele cria um DataSet para a ordem e retorna uma representação XML do que DataSet para o chamador.
• Transform.XSLT é um arquivo XSLT que é usado para transformar o DataSet XML para XML de planilha do Excel.

O código de exemplo usa dados de pedidos no Microsoft SQL Server banco de dados de exemplo Northwind. O exemplo supõe que você irá criar e testar o aplicativo da Web no computador local (http://localhost). Se você não usar um servidor Web no seu computador local, substitua localhost em URLs do exemplo de nome do seu servidor Web.

Gere o DataSet

Nesta seção, você cria o manipulador HTTP e os componentes do lado do cliente que são necessários para recuperar informações do pedido. O DataSet é retornado para o quadro principal como XML simples; o DataSet não é transformado.

1. Crie um projeto da Web vazio chamado ExcelTransform. Para fazer isso, execute as seguintes etapas:
   1. No menu arquivo no Visual Studio. NET, clique em novo e, em seguida, clique em Project .
   2. Clique em Projetos do Visual Basic e, em seguida, clique no modelo Projeto de Web vazia .
   3. Nome http://localhost/ExcelTransform projeto e, em seguida, clique em OK .
2. Adicione referências para o projeto. Para fazer isso, execute as seguintes etapas:
   1. No menu Project , clique em Add Reference .
   2. Na lista de componentes na guia .NET , clique em System.Data.dll e, em seguida, clique em Selecionar .
   3. Repita a etapa anterior para System.dll , System.Web.dll e System.XML.dll .
   4. Clique em OK .
3. No menu Project , clique em Add Class , nome da classe Getdata.vb e em seguida, clique em OK .
4. Substitua o código no getdata.vb com o seguinte.
   
   Observação <username>Você deve alterar senha e identificação do usuário <nomedousuário> = < senha de alta segurança > para os valores corretos antes de executar esse código. Certifique-se que a ID de usuário tenha as permissões apropriadas para executar esta operação no banco 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

   Observação : em Getdata.vb , modificar a atribuição para sConn como uma seqüência de conexão válida para um computador que está executando o SQL Server e que contém o banco de dados de exemplo Northwind.
   
   
5. No menu Project , clique em Add New Item , clique no modelo de Web Configuration File e, em seguida, clique em OK .
6. Substitua o código no 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 HTML , a página Header.htm, nome e, em seguida, clique em OK .
8. No menu Exibir , clique em Código fonte HTML .
9. Substitua o código no 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>

   Observação : manipulador HTTP O carrega Header.htm como um documento XML. Para carregar sem erros, Header.htm deve ser bem formado XML. Se você modificar Header.htm, verifique se está bem formado; todos os elementos devem ter adequadas marcas de início e de fim e todas as atribuições de atributo devem ser colocadas entre aspas.
   
   
10. No menu Project , clique em Adicionar página HTML , clique no modelo de conjunto de quadros , nomeie o arquivo default.htm e em seguida, clique em OK . Quando solicitado, clique no tipo de conjunto de quadros de cabeçalho e, em seguida, clique em OK .
11. No menu Exibir , clique em Código fonte HTML .
12. Em Frameset.htm, definir os atributos src e de rolagem para o quadro de cabeçalho da seguinte maneira:

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

13. No Solution Explorer, clique com o botão direito do mouse em default.htm e clique em Set As Start Page .
14. Siga estas etapas para testar o exemplo:
    1. No menu Debug , clique em Start without debugging . O conjunto de quadros é aberta no navegador com uma lista drop-down no quadro de cabeçalho que contém uma lista de identificações de ordem.
    2. Selecione qualquer código do pedido na lista drop-down.
    3. Examine o XML que aparece no quadro principal. O XML é uma representação do DataSet criado do manipulador Getdata HTTP de acordo com sua solicitação.
    4. Selecione ordem adicional identificações na lista drop-down para examinar os resultados.
    5. Quando você tiver terminado de testar o aplicativo da Web, feche o Microsoft Internet Explorer.

Transformar o DataSet para o Excel XML

Nesta seção, você pode adicionar código adicional para transformar o DataSet para exibição no Excel.

1. Abra o projeto ExcelTransform Web que você acabou de criar.
2. No menu Project , clique em Add New Item , clique em modelo de Arquivo XSLT , nome projeto 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. No Getdata.vb , substitua a função SendResults com 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 estas etapas para testar o exemplo:
   1. No menu Debug , clique em Start without debugging . O conjunto de quadros é aberta no navegador com uma lista drop-down no quadro de cabeçalho que contém uma lista de identificações de ordem.
   2. Selecione qualquer código do pedido na lista drop-down. Observe que o DataSet que você solicitou foi criado e é transformado em planilha XML e exibida no Excel. Os dados no Excel contém formatação e cálculos.
   3. Quando você tiver terminado de testar o aplicativo da Web, feche o Internet Explorer.

Salvar o XML transformado em um arquivo (opcional)

Na seção anterior, o manipulador HTTP transmite o XML transformado para o cliente. Você talvez queira salvar o XML de DataSet e os resultados da transformação em um arquivo. Isso pode ser uma etapa de solução de problemas útil se você achar que a transformação não lhe os resultados que você espera. Você pode usar essa abordagem para examinar o XML de DataSet e o XML transformado para possíveis erros.

Observação : O exemplo salva os arquivos XML na pasta de aplicativo da Web. Talvez você precise alterar permissões para a pasta, como demonstrado nas etapas a seguir.

1. Defina permissões de gravação na pasta para o ASP.NET processo:
   1. Inicie o Windows Explorer.
   2. Localize a pasta de aplicativo da Web. O caminho padrão é C:\Inetpub\Wwwroot\ExcelTransform.
   3. Clique com o botão direito na pasta ExcelTransform e, em seguida, clique em Propriedades .
   4. Na guia segurança , clique em Adicionar .
   5. Em Digite os nomes de objeto para selecionar , digite o objeto nome \aspnet YourComputerName e, em seguida, clique em OK .
   6. Na guia segurança , clique para selecionar a gravação para permitir que as permissões de gravação para a conta de \aspnet YourComputerName e, em seguida, clique em OK .
2. No Getdata.vb , substitua a função SendResults com 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 estas etapas para testar o exemplo:
   1. No menu Debug , clique em Start Without Debugging .
   2. Selecione uma ordem na lista drop-down. O XML transformado aparece no quadro principal no Excel.
   3. Examinar o XML bruto do DataSet, C:\Inetpub\Wwwroot\ExcelTransform\order NNNNN .XML. Você pode abrir o XML em qualquer editor de texto ou no Excel. Se você abrir Order.xml no Excel, você será solicitado a aplicar a folha de estilos.
   4. Examine a planilha XML, C:\Inetpub\Wwwroot\ExcelTransform\order NNNNN .xls. Você pode abrir o XML em qualquer editor de texto ou no Excel.

Comentários

Quando você cria seu próprio arquivo XSLT para o Excel, primeiro crie um modelo de pasta de trabalho no Excel que contém a formatação e fórmulas que você precisa e salvar a pasta de trabalho na planilha XML. Em seguida, você pode modificar o XML para que contenha o XSL expressões e os elementos que você precisa transformar seu DataSet XML corretamente. Quando você modifica o XML que você salvou do Excel, observe o seguinte:

• As células em uma planilha são representadas por um <table> elemento no XML. O <table> tem dois atributos, ss:ExpandedColumnCount e ss:ExpandedRowCount , que designar quais células são usados na planilha (ou seja, "intervalo usado"). Se a planilha tiver um número variável de linhas ou colunas (ou ambos), definir esses atributos durante a transformação. Observe que o atributo ss:ExpandedRowCount no transform.xslt é definido com base em uma contagem de certos nós no DataSet XML.
• As fórmulas nas células são representadas em notação RC e não em notação A1.
• Cada combinação de formatação diferente que você aplicar a células em uma planilha tem seu próprio estilo. Estilos são armazenados no <style> elemento do XML Spreadsheet.

Observação Os desenvolvedores que usam o .NET Framework 1.1 devem estar cientes das alterações que foram feitas para a classe XslTransform entre o .NET Framework 1.0 e .NET Framework 1.1. Por exemplo, vários das versões sobrecarregadas dos métodos a transformação e carga (incluindo a versão que é usada neste artigo) são considerados obsoletos começando com o .NET Framework 1.1. Para obter informações adicionais sobre o método XslTransform.Transform , visite o seguinte site da Web Microsoft Developer Network (MSDN):Para obter informações adicionais, clique nos números abaixo para ler os artigos na Base de dados de Conhecimento da Microsoft: