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

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

ID d'article : 285891 - Dernière mise à jour : 22 mai 2006 - Révision : 1

Commentaires