Microsoft Excel permet d’importer facilement des données XML créées à partir d’autres bases de données et applications dans le but de mapper les éléments XML d’un schéma XML vers les cellules d’une feuille de calcul et d’exporter les données XML modifiées pour permettre une interaction avec ces bases de données et applications tierces. Grâce à ces fonctionnalités XML, Office Excel peut être considéré comme un générateur de fichiers de données XML avec une interface utilisateur familière.
Contenu de cet article
Pourquoi utiliser la technologie XML dans Excel ?
La technologie XML est conçue pour gérer et partager des données structurées dans un fichier texte lisible par l’utilisateur. Cette technologie est conforme aux normes de l’industrie et peut être traitée par diverses bases de données et applications. Avec la technologie XML, les concepteurs d’applications ont les moyens de créer des balises, des structures de données et des schémas personnalisés qui leur sont propres. Pour résumer, la technologie XML facilite grandement la définition, la transmission, la validation et l’interprétation des données entre les bases de données, les applications et les organisations.
Données et fichiers de schéma XML
Excel utilise principalement deux types de fichiers XML :
-
Fichiers de données XML (.xml) contenant les balises et les données structurées personnalisées.
-
Fichiers de schéma (.xsd), qui contiennent des balises de schéma qui appliquent des règles, telles que le type de données et la validation.
La norme XML définit également les fichiers XSLT (.xslt), qui servent à appliquer des styles et à transformer les données XML en différents formats de présentation. Vous pouvez appliquer ces transformations avant d’importer des fichiers XML dans Excel et après avoir exporté des fichiers XML à partir d’Excel. Si des fichiers XSLT sont liés aux fichiers de données XML que vous importez dans Excel, vous avez la possibilité d’appliquer ou non la mise en forme avant que les données soient ajoutées à la feuille de calcul, à condition toutefois d’ouvrir les fichiers XML à l’aide de la commande Ouvrir dans Excel. Choisissez le type de fichier Fichiers XML (*.xml) avant de cliquer sur le bouton Ouvrir pour afficher les fichiers XML dans le dossier.
Scénarios XML et Excel clés
La technologie XML et Excel vous permettent de gérer les classeurs et les données selon des méthodes qui étaient auparavant impossibles ou difficiles à mettre en œuvre. En utilisant des mappages XML, vous pouvez facilement ajouter, identifier et extraire des données métiers spécifiques à partir de documents Excel. Par exemple, une facture comportant le nom et l’adresse d’un client ou un rapport contenant les résultats financiers du dernier trimestre ne sont plus simplement des rapports statiques. Vous pouvez importer en toute simplicité ces informations à partir de bases de données et d’applications, les modifier, puis les exporter dans ces mêmes bases de données et applications ou dans d’autres.
Vous trouverez ci-dessous des scénarios clés que les fonctionnalités XML visent à résoudre :
-
Étendre les fonctionnalités des modèles Excel existants en mappant des éléments XML vers des cellules existantes. Il devient ainsi plus facile d’obtenir des données XML depuis ou vers vos modèles sans qu’il soit nécessaire de modifier leur conception.
-
Utiliser des données XML comme entrées dans vos modèles de calcul existants en mappant des éléments XML vers des feuilles de calcul existantes.
-
Importer des fichiers de données XML dans un nouveau classeur.
-
Importer des données XML dans votre feuille de calcul Excel à partir d’un service Web.
-
Exporter des données de cellules mappées dans des fichiers de données XML indépendants des autres données du classeur.
Processus de base en matière d’utilisation de données XML dans Excel
Le diagramme suivant illustre la façon dont les différents fichiers et les différentes opérations s’emboîtent lorsque vous utilisez des données XML dans Excel. Ce processus s’articule essentiellement en cinq phases :
Utilisation de mappages XML
Vous pouvez créer ou ouvrir un classeur dans Excel, joindre un fichier de schéma XML (.xsd) au classeur, puis utiliser le volet Office Source XML pour mapper des éléments XML du schéma à des cellules ou des tables individuelles. Après avoir mappé les éléments XML vers votre feuille de calcul, vous pouvez importer et exporter des données XML dans ou depuis les cellules mappées.
Lorsque vous ajoutez un fichier de schéma XML (.xsd) dans votre classeur, vous créez un mappage XML. En règle générale, les mappages XML sont utilisés pour créer des cellules mappées et pour gérer la relation entre les cellules mappées et les éléments individuels du schéma XML. De plus, ces mappages XML servent à lier le contenu des cellules mappées aux éléments du schéma lors de l’importation ou de l’exportation de fichiers de données XML (.xml).
Vous pouvez créer deux types de cellules mappées, à savoir, des cellules mappées uniques ou des cellules répétées (qui apparaissent sous forme de tableaux XML). Au moment de concevoir votre feuille de calcul, pour plus de souplesse, vous pouvez faire glisser les cellules mappées n’importe où sur une feuille de calcul et dans n’importe quel ordre — quitte à ce que cet ordre soit différent de celui du schéma XML. Vous pouvez également désigner les éléments à mapper et ceux qu’il ne faut pas mapper.
Il est important de connaître les règles suivantes quant à l’utilisation de mappages XML :
-
Un classeur peut contenir un ou plusieurs mappages XML.
-
Vous ne pouvez mapper qu’un seul élément à la fois à un emplacement d’un classeur.
-
Chaque mappage XML est une entité indépendante, même si dans un même classeur, plusieurs mappages XML font référence au même schéma.
-
Un mappage XML ne peut contenir qu’un seul élément racine. Si vous ajoutez un schéma qui définit plus d’un élément racine, vous êtes invité à choisir l’élément racine à utiliser pour le nouveau mappage XML.
Utilisation du volet Office Source XML
Le volet Office Source XML permet de gérer les mappages XML. Pour l’ouvrir, sous l’onglet Développeur, dans le groupe XML, cliquez sur Source. Les caractéristiques de ce volet Office sont illustrées dans le diagramme suivant.
1. Recense les mappages XML qui ont été ajoutés au classeur
2. Affiche une liste hiérarchique des éléments XML du mappage XML actuellement affiché
3. Permet de définir les options relatives à l’utilisation du volet Office Source XML et aux données XML, notamment le mode d’aperçu des données et des en-têtes de contrôles
4. Ouvre la boîte de dialogue Mappages XML, dans laquelle vous pouvez ajouter, supprimer ou renommer des mappages XML
5. Vérifie si vous pouvez exporter des données XML par le biais du mappage XML actif
Types d’éléments et leurs icônes
Le tableau suivant fournit un récapitulatif de chaque type d’élément XML pouvant être utilisé dans Excel et l’icône utilisée pour les représenter.
|
Type d’élément |
Icône |
|---|---|
|
Élément parent |
|
|
Élément parent requis |
|
|
Élément parent répété |
|
|
Élément parent répété requis |
|
|
Élément enfant |
|
|
Élément enfant requis |
|
|
Élément enfant répété |
|
|
Élément enfant répété requis |
|
|
Attribut |
|
|
Attribut requis |
|
|
Contenu simple dans une structure complexe |
|
|
Contenu simple requis dans une structure complexe |
|
Utilisation de cellules mappées uniques
Une cellule mappée unique est une cellule qui a été mappée vers un élément XML non répété. Pour créer une cellule de ce type, il vous suffit de faire glisser un élément XML non répété du volet Office Source XML vers une cellule unique dans une feuille de calcul.
Lorsque vous faites glisser un élément XML non répété sur la feuille de calcul, vous pouvez utiliser une balise active si vous décidez d’inclure le nom de l’élément XML comme en-tête, au-dessus ou juste à gauche de la cellule mappée unique. Vous pouvez également utiliser une valeur de cellule existante comme en-tête.
Vous pouvez également utiliser la formule d’une cellule mappée unique si celle-ci est mappée vers un élément XML avec un type de données XSD (XML Schema Definition) qu’Excel interprète comme un nombre, une date ou une heure.
Utilisation de cellules répétées dans des tableaux XML
Les tableaux XML sont comparables aux tableaux Excel, tant sur le plan visuel que celui des fonctionnalités. Un tableau XML est un tableau Excel qui a été mappé vers un ou plusieurs éléments XML répétés. Chaque colonne du tableau XML représente un élément XML.
Un tableau XML est créé dans les cas suivants :
-
Vous utilisez la commande Importer (dans le groupe XML de l’onglet Développeur) pour importer un fichier de données XML.
-
Utilisez la commande Ouvrir à partir d’Excel pour ouvrir un fichier de données XML, puis sélectionnez En tant que table XML dans la boîte de dialogue Open XML .
-
Vous utilisez la commande Provenance : Importation de données XML (à partir du bouton de commande À partir d’autres sources, dans le groupe Données externes de l’onglet Données) pour importer un fichier de données XML — puis sélectionnez Tableau XML d’une feuille de calcul existante ou Nouvelle feuille dans la boîte de dialogue Importer des données.
-
Vous faites glisser un ou plusieurs éléments répétés du volet Office Source XML vers une feuille de calcul.
Lorsque vous créez un tableau XML, les noms d’éléments XML sont automatiquement utilisés comme en-têtes de colonnes. Vous pouvez les remplacer par l’en-tête de colonne de votre choix. Toutefois, les noms d’éléments XML d’origine sont toujours utilisés lorsque vous exportez des données à partir des cellules mappées.
Le bouton Options du volet Office Source XML donne accès à deux options qui s’avèrent utiles lorsque vous utilisez des tableaux XML :
-
Fusionner automatiquement les éléments lors du mappage Lorsque cette option est sélectionnée, Excel crée un tableau XML à partir de champs multiples à mesure qu’ils sont déposés sur la feuille de calcul. Cette option fonctionne à condition que les divers champs soient déposés sur la même ligne, l’un à côté de l’autre. Lorsque cette option est désactivée, chaque élément apparaît sous la forme d’un tableau XML distinct.
-
Mes données ont des en-têtes Lorsque cette option est sélectionnée, les données d’en-tête existantes sont utilisées comme en-têtes de colonnes pour les éléments répétés que vous mappez vers votre feuille de calcul. Lorsque cette option est désactivée, les noms d’éléments XML sont utilisés comme en-têtes de colonnes.
En utilisant des tableaux XML, vous pouvez facilement importer, exporter, trier, filtrer et imprimer des données basées sur une source de données XML. Toutefois, les tableaux XML présentent quelques limites dans la façon dont ils peuvent être organisés dans la feuille de calcul.
-
Les tableaux XML sont basés sur des lignes, ce qui signifie qu’ils croissent vers le bas à partir de la ligne d’en-tête. Vous ne pouvez pas ajouter de nouvelles entrées au-dessus des lignes existantes.
-
Vous ne pouvez pas modifier un tableau XML de façon à ce que les nouvelles entrées soient ajoutées à droite.
Vous pouvez utiliser des formules dans les colonnes mappées sur des éléments XML avec un type de données XSD (XML Schema Definition) qu’Excel interprète comme un nombre, une date ou une heure. Comme dans un tableau Excel, les formules sont recopiées vers le bas lorsque de nouvelles colonnes sont ajoutées à un tableau XML.
Éléments de sécurité à prendre en compte à propos des mappages XML
Un mappage XML, de même que les informations sur sa source de données, sont enregistrées avec le classeur Excel, et non avec une feuille de calcul spécifique. Un utilisateur malveillant peut accéder à ces informations de mappage par le biais d’une macro Microsoft VBA (Visual Basic pour Applications). Par ailleurs, si vous enregistrez votre classeur en tant que fichier au format XML ouvert Excel Office prenant en charge les macros, ces informations de mappage peuvent être affichées dans le Bloc-notes Microsoft ou tout autre programme d’édition de texte.
Si vous souhaitez continuer à utiliser les informations de mappage mais que vous voulez supprimer les informations potentiellement sensibles sur la source de données, vous pouvez supprimer du classeur la définition de la source de données du schéma XML sans pour autant empêcher l’exportation des données XML en désactivant la case à cocher Enregistrer la définition de la source de données dans le classeur de la boîte de dialogue Propriétés du mappage XML, à laquelle vous accédez via la commande Propriétés du mappage du groupe XML de l’onglet Développeur.
Si vous supprimez une feuille de calcul avant de supprimer un mappage, les informations de mappage sur les sources de données et les autres informations potentiellement sensibles sont toujours enregistrées dans le classeur. Si vous mettez à jour le classeur de façon à supprimer les informations sensibles, veillez à supprimer le mappage XML avant de supprimer le classeur pour que les informations de mappage soient supprimées définitivement du classeur.
Importation de données XML
Vous pouvez importer des données XML dans un mappage XML existant de votre classeur. En important des données, vous liez les données du fichier à un mappage XML stocké dans votre classeur. Autrement dit, à chaque élément de données du fichier de données XML correspond à un élément, dans le schéma XML, que vous avez mappé à partir d’un fichier de schéma XML ou d’un schéma inféré. Chaque mappage XML ne peut avoir qu’une seule liaison de données XML, et une liaison de données XML est liée à tous les mappages créés à partir d’un même mappage XML.
La boîte de dialogue Propriétés du mappage XML (pour l’ouvrir, cliquez sur Propriétés du mappage dans le groupe XML de l’onglet Développeur) comporte trois options, toutes sélectionnées par défaut, que vous pouvez définir ou désactiver pour contrôler le comportement d’une liaison de données XML :
-
Valider les données par rapport au schéma pour l’importation/exportation Spécifie si Excel valide les données par rapport au mappage XML lors de l’importation de données. Cliquez sur cette option si vous voulez vous assurer que les données XML que vous importez sont conformes au schéma XML.
-
Remplacer les données existantes par les nouvelles données Spécifie si les données sont remplacées lors de l’importation de données. Cliquez sur cette option si vous voulez remplacer les données actuelles par de nouvelles données dans le cas où, par exemple, le nouveau fichier de données XML contient des données à jour.
-
Ajouter les nouvelles données aux tableaux XML existants Spécifie si le contenu de la source de données est ajouté aux données existantes du classeur. Cliquez sur cette option lorsque, par exemple, vous consolidez les données de plusieurs fichiers de données similaires dans un tableau XML ou si vous ne voulez pas que le contenu d’une cellule contenant une fonction soit remplacé.
Lorsque vous importez des données XML, vous pouvez juger utile de remplacer certaines cellules mappées mais pas les autres. Par exemple, certaines cellules mappées peuvent contenir des formules, dont vous souhaitez éviter le remplacement lors de l’importation d’un fichier XML. Vous pouvez adopter dans ce cas deux approches différentes, à savoir :
-
Annuler le mappage des éléments dont vous ne souhaitez pas remplacer le contenu avant l’importation des données XML. Après avoir importé les données XML, vous pouvez remapper l’élément XML vers les cellules contenant les formules, de sorte que vous puissiez exporter les résultats des formules dans le fichier de données XML.
-
Créer deux mappages XML à partir du même schéma XML. Utilisez un mappage XML pour l’importation des données XML. Dans ce mappage XML d’importation, ne mappez pas les éléments vers les cellules qui contiennent des formules ou d’autres données que vous ne voulez pas remplacer. Utilisez un autre mappage XML pour exporter les données. Dans ce mappage XML d’exportation, mappez les éléments que vous souhaitez exporter dans un fichier XML.
Remarque : La fonctionnalité qui permettait d’importer des données XML à partir d’un service web en utilisant un fichier de connexion au service de récupération des données (.uxdc) pour se connecter à une source de données n’est plus prise en charge dans les versions ultérieures à Excel 2003 via l’interface utilisateur. Si vous ouvrez un classeur qui a été créé dans Excel 2003, vous pourrez toujours consulter les données, mais vous ne pourrez pas modifier ou actualiser la source de données.
Utilisation d’un schéma inféré
Si vous importez des données XML sans ajouter au préalable un schéma XML correspondant afin de créer un mappage XML, Excel essaie d’inférer automatiquement un schéma sur la base des balises définies dans le fichier de données XML. Le schéma inféré, qui est stocké avec le classeur, vous permet d’utiliser des données XML si le classeur n’est associé à aucun fichier de schéma XML.
Lorsque les données XML importées que vous utilisez sont associées à un schéma inféré, vous pouvez également personnaliser le volet Office Source XML. Sélectionnez l’option Aperçu des données dans le volet Office dans le bouton Options pour afficher la première ligne de données sous forme d’exemples de données dans la liste d’éléments, si vous avez importé des données XML associées au mappage XML dans la session actuelle d’Excel.
Vous ne pouvez pas exporter le schéma inféré Excel en tant que fichier de données de schéma XML (.xsd) distinct. Bien qu’il existe des éditeurs de schéma XML et d’autres méthodes de création de fichiers de schéma XML, il se peut que vous n’y ayez pas facilement accès ou que vous ne sachiez pas comment les utiliser. Il existe une autre solution qui consiste à utiliser le complément Outils XML pour Excel 2003 version 1.1, qui permet de créer un schéma à partir d’un mappage XML. Pour plus d’informations, voir Using the Excel 2003 XML Tools Add-in Version 1.1.
Exportation de données XML
L’exportation de données XML consiste à exporter le contenu de cellules mappées sur la feuille de calcul. Lorsque vous exportez des données, Excel applique les règles suivantes pour identifier les données à sauvegarder et déterminer le mode de sauvegarde approprié :
-
Si aucun élément vide n’est créé lorsqu’il existe des cellules vides pour un élément facultatif, des éléments vides sont créé lorsqu’il existe des cellules vides pour un élément requis.
-
Le codage UTF-8 (Unicode Transformation Format-8) est utilisé pour écrire les données.
-
Tous les espaces de noms sont définis dans l’élément XML racine.
-
Excel remplace les préfixes d’espaces de noms existants. L’espace de noms par défaut est associé au préfixe ns0. Les espaces de noms successifs sont désignés ns1, ns2 à ns<count> où <count> correspond au nombre d’espaces de noms écrits dans le fichier XML.
-
Les nœuds de commentaire ne sont pas conservés.
Vous pouvez afficher la boîte de dialogue Propriétés du mappage XML (cliquez sur Propriétés de la carte dans le groupe XML sous l’onglet Développeur ).) puis utilisez l’option Valider les données par rapport au schéma pour l’importation et l’exportation (active par défaut) pour spécifier si Excel valide les données par rapport au mappage XML lors de l’exportation de données. Cliquez sur cette option pour vous assurer que les données XML que vous exportez sont conformes au schéma XML.
Utilisation du format de fichier XML ouvert Office prenant en charge les macros Excel
Vous pouvez enregistrer un classeur Excel dans divers formats de fichiers, notamment sous forme de fichier au format XML ouvert Office prenant en charge les macros Excel (.xlsm). Excel intègre un schéma XML qui définit le contenu d’un classeur Excel, notamment les balises XML stockant toutes les informations de classeur, telles que les données et les propriétés, et qui définit la structure globale du classeur. Les applications personnalisées peuvent utiliser ce fichier au format XML Office prenant en charge les macros Excel. Par exemple, des développeurs peuvent éventuellement créer une application personnalisée visant à rechercher dans plusieurs classeurs les données enregistrées dans ce format et à créer un système de génération de rapports basé sur les données trouvées.
Vous avez besoin d’une aide supplémentaire ?
Vous pouvez toujours poser des questions à un expert de la Communauté technique Excel ou obtenir une assistance dans la Communauté de support.
