Cómo utilizar Visual Basic o ASP para crear una hoja de cálculo XML para Excel 2002 y Excel 2003

Seleccione idioma Seleccione idioma
Id. de artículo: 285891 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

Resumen

Microsoft Excel 2002 y Microsoft Office Excel 2003 admiten un formato XML con el que pueden cargar y guardar libros (u hojas de cálculo XML). Utilizando este formato de hoja de cálculo XML puede crear libros con formato de varias hojas en Excel sin utilizar Automatización. Este método puede ser deseable cuando necesite crear un libro de Excel, pero no es viable para automatizar Excel (como en un servidor web o desde un servicio) o si Excel no está instalado en el sistema donde se ejecuta el código.

En este artículo se muestra cómo crear una plantilla XML que, cuando se utiliza junto con transformación de Lenguaje de hoja de estilos extensible (XSL), genera un libro con formato que se puede abrir directamente en Excel. La transformación XML se muestra tanto para Páginas Active Server (ASP) como para Visual Basic. Con respecto al ejemplo de código de Visual Basic, los datos de la hoja de cálculo se generan solamente con XML/XSL; se utiliza una Automatización mínima para abrir los resultados en Excel.

Nota: el ejemplo descrito en este artículo está disponible para su descarga; para ver instrucciones de descarga, consulte la sección Descarga al final de este artículo.

Más información

Crear la plantilla XML para el libro

  1. Cree una nueva carpeta, C:\ExcelXML.
  2. En Excel, inicie un nuevo libro. Agregue los datos siguientes a las celdas A1:F2 del libro en las celdas indicadas. La celda F2 debe introducirse como una fórmula.
    A1: Order ID    B1: Product ID   C1: Unit Price   D1: Quantity   E1: Discount  F1: Total
    A2: aaa         B2: 111          C2: 222          D2: 333        E2: 0         F2: =C2*D2*(1-E2)
  3. En la celda F3, escriba la fórmula siguiente:
    =SUMA(F$2:F2)
  4. Seleccione las celdas A1:F1. En el menú Formato, haga clic en Celdas. Aplique una fuente en negrita, un borde inferior y un color sólido para el sombreado de celda. Haga clic en Aceptar.
  5. Seleccione las columnas A:F. En el menú Formato, seleccione Columna y haga clic en Ancho. Escriba 15 para el nuevo ancho de columna y haga clic en Aceptar. Con las columnas A:F todavía seleccionadas, en el menú Formato, haga clic en Celdas. En la ficha Alineación, seleccione Centro en la lista de alineación horizontal y haga clic en Aceptar.
  6. Seleccione la columna E. En el menú Formato, haga clic en Celdas. En la ficha Número, haga clic en Porcentaje y especifique 0 posiciones decimales y, a continuación, haga clic en Aceptar.
  7. Seleccione la columna F. En el menú Formato, haga clic en Celdas. En la ficha Número, haga clic en Contabilidad y, después, haga clic en Aceptar.
  8. Seleccione las celdas A3:F3. En el menú Formato, haga clic en Alto de fila, escriba 25 y haga clic en Aceptar. Con las celdas A3:F3 todavía seleccionadas, en el menú Formato, haga clic en Celdas. Aplique un borde superior a las celdas y haga clic en Aceptar.
  9. En el menú Herramientas, haga clic en Opciones. En la ficha Ver, desactive la casilla de verificación Líneas de división y haga clic en Aceptar.
  10. Seleccione la fila 2. En el menú Ventana, haga clic en Inmovilizar secciones.
  11. Seleccione la celda A1.
  12. En el menú Archivo, haga clic en Guardar como. Vaya a la carpeta C:\ExcelXML que creó y guarde el libro como "Orders.xsl" en el formato Hoja de cálculo XML.

    Nota: en el cuadro de nombre de archivo del cuadro de diálogo Guardar como, escriba el nombre de archivo entre comillas dobles para que no se agregue una extensión .xml al nombre de archivo.
  13. Salga de Excel.
  14. Abra Orders.xsl en cualquier editor de texto, como el Bloc de notas.
  15. Inserte lo siguiente entre las etiquetas <?xml version="1.0"?> y <Workbook>:
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
    <xsl:template match="/">
    <xsl:pi name="xml">version="1.0"</xsl:pi>
    					
  16. Justo al final de Orders.xsl, anexe las dos líneas siguientes:
    </xsl:template>
    </xsl:stylesheet>
    					
  17. Busque el siguiente conjunto de etiquetas en Orders.xsl (este conjunto de etiquetas representa las celdas A2:F2 de la hoja de cálculo)
       <Row ss:Height="14.25">
        <Cell><Data ss:Type="String">aaa</Data></Cell>
        <Cell><Data ss:Type="Number">111</Data></Cell>
        <Cell><Data ss:Type="Number">222</Data></Cell>
        <Cell><Data ss:Type="Number">333</Data></Cell>
        <Cell><Data ss:Type="Number">0</Data></Cell>
        <Cell ss:Formula="=RC[-3]*RC[-2]*1*(1-RC[-1])"><Data ss:Type="Number">73926</Data></Cell>
       </Row>
    					
    y reemplácelo con el código XSL siguiente:
    <xsl:for-each select="xml/rs:data/z:row">
       <Row ss:AutoFitHeight="0" ss:Height="13.5">
        <Cell><Data ss:Type="String"><xsl:value-of select="@OrderID"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@ProductID"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@UnitPrice"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@Quantity"/></Data></Cell>
        <Cell><Data ss:Type="Number"><xsl:value-of select="@Discount"/></Data></Cell>
        <Cell ss:Formula="=RC[-3]*RC[-2]*(1-RC[-1])"><Data ss:Type="Number">0</Data></Cell>
       </Row>
    </xsl:for-each>
    					
  18. Guarde los cambios realizados en Orders.xsl y cierre el archivo.

Utilizar Visual Basic para transformar un conjunto de registros ADO con persistencia XML en una hoja de cálculo XML

  1. En Visual Basic, cree un nuevo proyecto EXE estándar.
  2. En el menú Proyecto, haga clic en Referencias. Seleccione las bibliotecas de tipos para Microsoft ActiveX Data Objects 2.5 (o posterior) y Microsoft XML 3.0.
  3. Agregue un CommandButton a Form1 y agregue el código siguiente al evento Click del botón:
        Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                      "C:\program files\microsoft office\office10\samples\Northwind.mdb"
        Const sOutXML = "C:\ExcelXML\Orders.xml"
        Const sXSL = "C:\ExcelXML\Orders.xsl"
        
        'Retrieve an ADO recordset of the Orders Detail table in Northwind.
        Dim nRecords As Long, nFields As Long
        Dim rs As New ADODB.Recordset
        rs.Open "SELECT * FROM [Order Details]", sConn, adOpenStatic, adLockOptimistic
            
        'Persist the recordset to a new DOMDocument and store the record count.
        Dim oXML As New DOMDocument
        rs.Save oXML, adPersistXML
        nRecords = rs.RecordCount
        nFields = rs.Fields.Count
        rs.Close
          
        'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
        Dim oXSL As New DOMDocument
        oXSL.Load sXSL
        
        'Transform the XML using the style sheet.
        Dim oResults As New DOMDocument
        oXML.transformNodeToObject oXSL, oResults
        
        If oXSL.parseError.errorCode <> 0 Then
            MsgBox "Parse Error: " & oResults.parseError.reason
        Else
           'Modify the ss:ExpandedRowCount attribute for the <table> node to
           'indicate the correct number of rows (count of records + 1 row for
           'the header + 1 row for the total).
            Dim oTable As MSXML2.IXMLDOMElement
            Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
            oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2
    
            '***************************************
            'Save the results to a file.
            Open sOutXML For Output As #1
            Print #1, oResults.xml
            Close #1
    
            'Open the XML in Excel.
            Dim oExcel As Object
            Set oExcel = CreateObject("Excel.Application")
            oExcel.Workbooks.Open sOutXML
            oExcel.Visible = True
            oExcel.UserControl = True
            '***************************************
        End If
    					
    Nota: si es necesario, modifique la cadena de conexión (Const sConn) para que contenga la ruta de acceso de instalación correcta a la base de datos de ejemplo Northwind de Access.
  4. Presione la tecla F5 para ejecutar el programa.
  5. Haga clic en el botón en Form1. Los datos de la tabla Orders Detail de la base de datos Northwind aparecerán en la plantilla de libro de Excel que creó.
El ejemplo genera la hoja de cálculo XML y automatiza Excel utilizando el método Open de la colección Workbooks para abrir el código XML como un libro nuevo. Como un método alternativo, puede insertar directamente el código XML en una hoja de cálculo existente en una celda concreta. Para ilustrarlo, reemplace el código contenido entre los asteriscos con lo siguiente:
        'Display the data in a workbook starting at cell B2.
        Dim oExcel As Object, oBook As Object
        Set oExcel = CreateObject("Excel.Application")
        Set oBook = oExcel.Workbooks.Add
        oBook.Worksheets(1).Range("B2").Resize(nRecords + 2, nFields + 1).Value(11) = _
            oResults.xml 'Note: xlRangeValueXMLSpreadsheet=11
        oExcel.Visible = True
        oExcel.UserControl = True
				
Después de realizar este cambio, ejecute de nuevo el programa. Observe que esta vez, los datos de la hoja de cálculo XML se insertan en un nuevo libro a partir de la celda B2 de la primera hoja de cálculo. Los datos y los formatos de la celda se aplican al rango; sin embargo, con este método, las configuraciones específicas de las filas, las columnas, la hoja de cálculo y el libro no se conservan.

Utilizar ASP para transformar un conjunto de registros ADO con persistencia XML en una hoja de cálculo XML

  1. Pegue el código siguiente en el Bloc de notas. Guarde el código como ExcelXML.asp en la carpeta raíz virtual del servidor web.Nota: la carpeta raíz virtual predeterminada es C:\inetpub\wwwroot.
    <%@ Language="vbscript"%>
    
    <%
    
        Const sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\program files\microsoft office\office10\samples\Northwind.mdb"
        Const sXSL = "C:\ExcelXML\Orders.xsl"
        
        Response.Buffer = True
    
        'Retrieve an ADO recordset of the Orders Detail table in Northwind.
        Dim rs, nRecords
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open "SELECT * FROM [Order Details]", sConn, 3, 3
            
        'Persist the recordset to a new DOMDocument and store the record count.
        Dim oXML
        Set oXML = CreateObject("Microsoft.XMLDOM")
        rs.Save oXML, 1
        nRecords = rs.RecordCount
        rs.Close
          
        'Load the XSL (the workbook template with XSL directives) into a DOMDocument.
        Dim oXSL
        Set oXSL = CreateObject("Microsoft.XMLDOM")
        oXSL.Load sXSL
        
        'Transform the XML using the style sheet.
        Dim oResults
        Set oResults = CreateObject("Microsoft.XMLDOM")
        oXML.transformNodeToObject oXSL, oResults
        
        If oXSL.parseError.errorCode <> 0 Then
           Response.Write "Parse Error: " & oResults.parseError.reason
        Else
    
           'Modify the ss:ExpandedRowCount attribute for the <table> node in the XSL.
            Dim oTable
            Set oTable = oResults.selectSingleNode("Workbook/Worksheet/Table")
            oTable.setAttribute "ss:ExpandedRowCount", nRecords + 2
    
            'Return the resulting XML Spreadsheet for display in Excel.
            Response.ContentType = "application/vnd.ms-excel"
            Response.Charset = "ISO-8859-1"
            Response.Write oResults.XML
            Response.Flush
    
        End If
    
    %>
    					
    Nota: si es necesario, modifique la cadena de conexión (Const sConn) para que contenga la ruta de acceso de instalación correcta a la base de datos de ejemplo Northwind de Access.

  2. Inicie Internet Explorer y vaya a http://suServidorWeb/ExcelXML.asp, donde suServidorWeb es el nombre de su servidor web. Los datos de la tabla Orders Detail de la base de datos Northwind aparecerán en la plantilla de libro de Excel que creó.

Notas adicionales

Las características de Excel siguientes no se pueden conservar con el formato de hoja de cálculo XML:
  • Gráficos
  • Objetos OLE
  • Formas de dibujo o Autoformas
  • Proyectos de VBA
  • Grupo y contorno

Descarga

XMLSpread.exe contiene la hoja de estilos XML, el proyecto de Visual Basic y la secuencia de comandos ASP que se describen en este artículo. El siguiente archivo se puede descargar desde el Centro de descarga de Microsoft:
Contraer esta imagenAmpliar esta imagen
Descargar
Descargue ahora el paquete Xmlsprd.exe. Fecha de publicación: 27 de marzo de 2001

Para obtener más información acerca de cómo descargar los archivos de soporte técnico de Microsoft, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
119591 Cómo obtener Archivos de soporte técnico de Microsoft desde los servicios en línea
Microsoft exploró este archivo en busca de virus con el software de detección de virus más reciente disponible en la fecha de publicación. Asimismo, el archivo se almacenó en servidores seguros que ayudan a impedir la realización de cambios no autorizados.

Referencias

Para obtener más información acerca de cómo guardar conjuntos de registros de ADO en formato XML, visite el siguiente sitio web de Microsoft Developer Network (MSDN):
http://msdn2.microsoft.com/en-us/library/ms681538.aspx
Para obtener más información acerca de cómo transmitir en secuencias contenido MIME a aplicaciones de Microsoft Office, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:
266263 ERROR: Word 2000 y Excel 2000 muestran el origen de ASP cuando se usa un tipo MIME para transmitir datos en secuencias
199841 Cómo mostrar los resultados de ASP con Excel en IE con tipos MIME
271572 Cómo dar formato a un libro de Excel mientras se transmite en secuencias contenido MIME
307021 CÓMO: Transferir datos XML a Microsoft Excel 2002 mediante Visual Basic .NET

Propiedades

Id. de artículo: 285891 - Última revisión: miércoles, 13 de junio de 2007 - Versión: 7.3
La información de este artículo se refiere a:
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Active Server Pages 4.0
  • Microsoft Visual Basic 6.0 Professional Edition
Palabras clave: 
kbdownload kbhowto KB285891

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com