Comment faire pour utiliser Visual Basic ou ASP pour créer une feuille de calcul XML pour Excel 2002 et Excel 2003

Traductions disponibles Traductions disponibles
Numéro d'article: 285891 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Résumé

Microsoft Excel 2002 et Microsoft Office Excel 2003 prennent en charge un format XML avec lequel ils peuvent charger et enregistrer des classeurs (ou des feuilles de calcul XML). Grâce à ce format de feuille de calcul XML, vous pouvez créer des classeurs multifeuilles et mis en forme dans Excel sans utiliser l'automation. Cette approche peut être souhaitable lorsque vous devez créer un classeur Excel mais que l'automatisation d'Excel est impossible (par exemple sur un serveur Web ou à partir d'un service) ou qu'Excel n'est pas installé sur le système où votre code est exécuté.

Cet article explique comment créer un modèle XML qui, lorsqu'il est utilisé avec la transformation XSL (Extensible Stylesheet Language), génère un classeur mis en forme qui peut être ouvert directement dans Excel. La transformation XML est démontrée pour Active Server Pages (ASP) et Visual Basic. En ce qui concerne l'exemple de code Visual Basic, les données de la feuille de calcul sont générées uniquement avec XML/XSL ; une automation minimale est utilisée pour ouvrir les résultats dans Excel.

Remarque L'exemple décrit dans cet article est téléchargeable ; pour les instructions relatives au téléchargement, reportez-vous à la section Téléchargement à la fin de cet article.

Plus d'informations

Créer le modèle XML pour le classeur

  1. Créez un dossier nommé C:\ExcelXML.
  2. Dans Excel, démarrez un nouveau classeur. Ajoutez les données suivantes aux cellules A1:F 2 du classeur dans les cellules indiquées. La cellule F2 doit être entrée en tant que formule.
    A1 : ID commande B1 : ID produit   C1 : Prix unitaire   D1 : Quantité   E1 : Remise  F1 : Total
    A2 : aaa         B2 : 111          C2 : 222             D2 : 333        E2 : 0       F2 : =C2*D2*(1-E2)
  3. Dans la cellule F3, tapez la formule suivante :
    =SOMME(F$2:F2)
  4. Sélectionnez les cellules A1:F 1. Dans le menu Format, cliquez sur Cellule. Appliquez une police de caractère gras, une bordure inférieure et une couleur unie pour l'ombrage de cellule. Cliquez sur OK.
  5. Sélectionnez les colonnes A:F. Dans le menu Format, pointez sur Colonne, puis cliquez sur Largeur. Tapez 15 comme nouvelle largeur de colonne, puis cliquez sur OK. Avec les colonnes A:F toujours sélectionnées, dans le menu Format, cliquez sur Cellule. Sous l'onglet Alignement, sélectionnez Centré dans la liste de l'alignement horizontal, puis cliquez sur OK.
  6. Sélectionnez la colonne E. Dans le menu Format, cliquez sur Cellule. Sous l'onglet Nombre, cliquez sur Pourcentage et spécifiez 0 comme nombre de décimales, puis cliquez sur OK.
  7. Sélectionnez la colonne F. Dans le menu Format, cliquez sur Cellule. Sous l'onglet Nombre, cliquez sur Comptabilité, puis sur OK.
  8. Sélectionnez les cellules A3:F 3. Dans le menu Format, cliquez sur Hauteur de ligne, tapez 25, puis cliquez sur OK. Avec les cellules A3:F3 toujours sélectionnées, dans le menu Format, cliquez sur Cellule. Appliquez une bordure supérieure aux cellules, puis cliquez sur OK.
  9. Dans le menu Outils, cliquez sur Options. Sous l'onglet Affichage, désactivez la case à cocher Quadrillage puis cliquez sur OK.
  10. Sélectionnez la ligne 2. Dans le menu Fenêtre, cliquez sur Figer les volets.
  11. Sélectionnez la cellule A1.
  12. Dans le menu Fichier, cliquez sur Enregistrer sous. Recherchez le dossier C:\ExcelXML que vous avez créé et enregistrez le classeur sous « Orders.xsl » au format Feuille de calcul XML.

    Remarque : dans la zone de nom de fichier de la boîte de dialogue Enregistrer sous, placez le nom du fichier entre guillemets doubles afin qu'une extension .xml ne soit pas ajoutée à votre nom de fichier.
  13. Quittez Excel.
  14. Ouvrez Orders.xsl dans un éditeur de texte quelconque, tel que le Bloc-notes.
  15. Insérez le code suivant entre les balises <?xml version="1.0"?> et <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. Tout en bas du fichier Orders.xsl, ajoutez les deux lignes suivantes :
    </xsl:template>
    </xsl:stylesheet>
    					
  17. Recherchez le groupe de balises suivant dans Orders.xsl (ce groupe de balises représente les cellules A2:F 2 dans la feuille de calcul)
       <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>
    					
    et remplacez-le par le code XSL suivant :
    <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. Enregistrez vos modifications dans Orders.xsl et fermez le fichier.

Utiliser Visual Basic pour transformer un jeu d'enregistrements ADO au format XML en une feuille de calcul XML

  1. Dans Visual Basic, créez un projet EXE standard.
  2. Dans le menu Projet, cliquez sur Références. Sélectionnez les bibliothèques de type pour Microsoft ActiveX Data Object 2.5 (ou version ultérieure) et Microsoft XML 3.0.
  3. Ajoutez un bouton de commande à Form1 et ajoutez le code suivant à l'événement Click du bouton :
        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
    					
    Remarque Si nécessaire, modifiez la chaîne de connexion (Const sConn) de sorte qu'elle contienne le chemin d'accès correct à l'installation de l'exemple de base de données Access Les Comptoirs.
  4. Appuyez sur la touche F5 pour exécuter le programme.
  5. Cliquez sur le bouton sur Form1. Les données de la table Détail des commandes de la base de données Les Comptoirs apparaissent dans le modèle de classeur Excel que vous avez créé.
L'exemple génère la feuille de calcul XML et automatise Excel à l'aide de la méthode Open de la collection Workbooks pour ouvrir la feuille XML en tant que nouveau classeur. En guise d'alternative, vous pouvez insérer directement la feuille XML dans une feuille de calcul existante à une cellule spécifique. À des fins d'illustration, remplacez le code contenu entre les astérisques par le code suivant :
        '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
				
Après avoir apporté cette modification, réexécutez le programme. Notez que cette fois-ci, les données de la feuille de calcul XML sont insérées dans un nouveau classeur à partir de la cellule B2 de la première feuille de calcul. Les données et les formats de cellules sont appliqués à la plage ; toutefois, lorsque vous appliquez cette méthode, les paramètres spécifiques aux lignes, aux colonnes, à la feuille de calcul et au classeur ne sont pas reportés.

Utiliser ASP pour transformer un jeu d'enregistrements ADO au format XML en une feuille de calcul XML

  1. Collez le code suivant dans le Bloc-notes. Enregistrez le code sous ExcelXML.asp dans le répertoire racine virtuel de votre serveur Web.Remarque Le répertoire racine virtuel par défaut est 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
    
    %>
    					
    Remarque Si nécessaire, modifiez la chaîne de connexion (Const sConn) de sorte qu'elle contienne le chemin d'accès correct à l'installation de l'exemple de base de données Access Les Comptoirs.

  2. Démarrez Internet Explorer et naviguez jusqu'à http://votre_serveur_web/ExcelXML.asp, où votre_serveur_web correspond au nom de votre serveur Web. Les données de la table Détail des commandes de la base de données Les Comptoirs apparaissent dans le modèle de classeur Excel que vous avez créé.

Remarques supplémentaires

Les fonctionnalités Excel suivantes ne peuvent pas être conservées avec le format Feuille de calcul XML :
  • graphiques ;
  • objets OLE ;
  • formes de dessin ou Formes automatiques ;
  • projets VBA ;
  • éléments groupés et plans.

Téléchargement

XMLSpread.exe contient la feuille de style XML, le projet Visual Basic et le script ASP décrits dans cet article. Vous pouvez télécharger le fichier suivant à partir du Centre de téléchargement Microsoft :
Réduire cette imageAgrandir cette image
Téléchargement
Télécharger le package Xmlsprd.exe. Date de publication : 27 mars 2001

Pour plus d'informations sur la façon de télécharger des fichiers du Support technique Microsoft, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
119591 Comment faire pour obtenir des fichiers de support technique Microsoft auprès des services en ligne
Microsoft a analysé ce fichier en vue de détecter la présence de virus. Microsoft a utilisé les logiciels de détection de virus les plus récents disponibles à la date de publication de ce fichier. Le fichier est conservé sur des serveurs sécurisés, empêchant toute modification non autorisée du fichier.

Références

Pour plus d'informations sur l'enregistrement des jeux d'enregistrements ADO au format XML, reportez-vous au site Web MSDN (Microsoft Developer Network) à l'adresse suivante (en anglais) :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasdj00/html/wsc.asp
Pour plus d'informations sur la diffusion du contenu MIME vers les applications Office, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la Base de connaissances Microsoft.
266263 BOGUE : Word 2000 et Excel 2000 affichent la source ASP lors de l'utilisation du type MIME pour la diffusion de données en continu
199841 Comment faire pour afficher des résultats ASP à l'aide d'Excel dans Internet Explorer avec des types MIME
271572 Comment faire pour mettre en forme un classeur Excel lors de la diffusion de contenu MIME en continu
307021 Comment faire pour transférer des données vers Microsoft Excel 2002 à l'aide de Visual Basic .NET

Propriétés

Numéro d'article: 285891 - Dernière mise à jour: lundi 22 mai 2006 - Version: 7.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Active Server Pages 4.0
  • Microsoft Visual Basic 6.0 Édition professionnelle
Mots-clés : 
kbdownload kbhowto KB285891
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

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