[Calc] Les liens externes

[spYre]

Avertissement.
Ce tutoriel est la version française d’un tutoriel publié sur le forum anglais et rédigé par Villeroy. Il est proposé sur ce forum avec son accord.
J’ai choisi de rester au plus près du texte original, même lorsque celui-ci décrit certaines caractéristiques obsolètes, et de conserver la chronologie des ajouts.
Le tutoriel fonctionne ainsi comme une chronique des améliorations successives et restera utile aux utilisateurs des versions antérieures (encore nombreux semble-t-il).

==========================================================================

Voici 5 méthodes pour lier des données à une feuille de calcul. Les deux premières permettent d’importer des feuilles entières, les deux suivantes des plages de données ou des tableaux html depuis une page web, la dernière des tables et requêtes depuis une base de données ou toute autre source assimilable. Dans les chapitres 1 à 4, fichier source correspond au fichier d’où sont extraites les données, fichier cible celui qui, d’une manière ou d’une autre, est destiné à les recevoir.

1. Lien par URL de fichier
2. Feuille liée à partir d’un ficher
3. Importation de données externes nommées
4. La Fonction DDE
5. Importation depuis une source de données enregistrée

####################################################################################################################

Ajout : 2008-08-11. Exemple joint pour la question des liens relatifs.

Ajout : 2009-08-22. L’exemple joint ne fonctionne pas avec la version 3.1. Je joins une nouvelle version pour la 3.1.1 où les liens relatifs de type INDIRECT(ADRESSE(...)) fonctionnent correctement avec des valeurs ou des matrices.

Extraire les deux fichiers dans le même répertoire. Le chemin ne peut pas contenir d’apostrophe. Ouvrir target.ods et suivre les formules et commentaires en partant du haut. Se référer au chapitre 1 du présent tutoriel.

[spYre]

Importation de feuilles masquées depuis une source de type classeur, de type dBase ou de type csv.
Syntaxe : ='file:///chemin/nom.ods'#$FeuilleX.$A$1.

Insérer un lien URL sans peine.
Pour éviter de taper de longues formules, ouvrir les deux documents, commencer une formule avec '=' et, à la souris, définir une plage dans le fichier source. Le lien peut être intégré à n’importe quelle formule ou plage nommée. Le fichier source doit être enregistré sur le disque.

Principe.
'file:///chemin/nom.ods'#$FeuilleX.$A$1 fait en réalité référence à la cellule A1 d’une feuille masquée nommée 'file:///chemin/nom.ods'#$FeuilleX. Ces feuilles sont créées à la volée chaque fois que la chaîne de calcul rencontre un tel lien. La méthode par souris décrite plus haut vaut également pour les fichiers textes de type csv. L’actualisation manuelle d’un lien via le menu Édition -> Liens... [Actualiser] force la relecture des données copiées du fichier source dans la feuille masquée.

##########################################################################

Ajout : 2009-05-17: La version 3.1 n’utilise plus les feuilles masquées

http://development.openoffice.org/releases/3.1.0.html

Modification du stockage des données issues de liens externes.
http://www.openoffice.org/issues/show_bug.cgi?id=92797
Détails:
La méthode de stockage interne des données extraites par formule des documents externes a été modifiée. Jusqu’alors, toute feuille externe référencée était entièrement copiée dans une feuille masquée. Désormais, seules les données référencées sont stockées dans une structure indépendante, sans création de feuille masquée. Avantages : - ces feuilles ne sont plus déduites des 255 feuilles disponibles ; - il devrait être possible de référencer plus de 254 feuilles externes, sous réserve de mémoire allouée suffisante bien sûr ; - moins de données à enregistrer à la sauvegarde du document ; - aucun risque de perte de données involontaire puisque les données non liées ne sont plus enregistrées dans les fichiers.
À noter que les feuilles externes n’apparaîtront plus dans le Navigateur. Au niveau du fichier ODF, les données restent stockées en tant que feuilles externes, ce qui permettra aux versions antérieures de lire le document. Réenregistrer un document existant avec la nouvelle version peut avoir pour effet de diminuer de manière importante la taille du fichier, en fonction de la quantité de données présentes dans la feuille entière et celle réellement utilisée par les formules.

##########################################################################

Ajout : 2009-08-22: OOo 3.1 connaît certains problèmes avec la nouvelle implémentation, problèmes corrigés dans la 3.1.1

Commandes et paramètres.
Menu Édition -> Liens... [ Actualiser | Modifier... | Rompre le lien ].
Menu Outils -> Options... -> Calc -> Général -> Actualiser les liens au démarrage [ Toujours | Sur demande | Jamais ].
Menu Format -> Feuille -> Afficher... les feuilles masquées. Celles-ci ne contiennent que des valeurs, aucune formule. Lors de l’actualisation du lien, l’application va relire les valeurs du fichier source dans ces feuilles masquées. Les données étant lues depuis le disque, il convient de sauvegarder tout fichier source éventuellement ouvert avant actualisation.
C’est la méthode idéale si l’on souhaite embarquer toutes les feuilles utilisées par un classeur. Inutile en effet de joindre tous les fichiers liés lorsque le classeur est envoyé à un tiers. Toutefois, ce dernier ne pourra actualiser les liens, sous peine d’effacer les feuilles masquées. Si cela devait se produire par mégarde, il suffira de recharger le document sans le sauvegarder.

Limitations.
- À l’inverse d’Excel, il n’est pas possible d’utiliser les plages nommées définies dans le document source. En revanche, il est possible de définir des noms dans le document cible faisant référence à des feuilles externes. Utiliser une plage nommée avec comme référence

Code : Tout sélectionner

='file:///chemin/nom.ods'#$FeuilleX.$A$1

au lieu de la formule directe

Code : Tout sélectionner

='file:///chemin/nom.ods'#PlageNommée

- Losqu’un lien est modifié via le dialogue d’édition (menu Édition -> Liens...), il faut choisir un fichier sans pouvoir spécifier une feuille particulière. Dès lors, si le lien pointait vers une feuille "Feuille1" du fichier source original, le nouveau fichier source devra contenir une feuille du même nom.
- Il n'est pas possible de rompre un lien sans avoir préalablement supprimer toutes les références. Qu'il reste une seule formule 'file:///chemin/nom.ods'#$FeuilleX.$A$1 et le recalcul suivant rétablira un lien avec la feuille masquée 'file:///chemin/nom.ods'#$FeuilleX.
- Même si l'url apparaît comme référence absolue, il s'agit d'une référence relative. Il est impossible de déplacer les deux fichiers ensemble dans une autre structure de fichiers sans rompre le lien.

Ajout : Pour les liens relatifs, voir le fichier joint à la fin de ce tutoriel

Ajout : 2009-10-05, version 3.1. Même si l'url apparaît comme référence absolue, il s'agit d'une référence relative. Il est désormais possible de déplacer les deux fichiers ensemble dans un autre structure de fichiers sans rompre le lien.

Purge.
- Utiliser l’outil Rechercher & remplacer afin de trouver toutes les références dans les formules.
- Commenter ces formules en les faisant précéder d’une apostrophe ou en suppriment le '='.
- Vérifier également les éventuelles plages nommées (Ctrl+F3).
- Ouvrir Édition -> Liens... et rompre les liens.
- Supprimer les feuilles masquées ou les déplacer hors du fichier.

Pour les utilisateurs de l’API.
http://api.openoffice.org/docs/common/r ... SheetLinks (com.sun.star.sheet.SheetLinkMode.VALUE)

Ajout : À partir de la 3.1 : http://api.openoffice.org/docs/common/r ... Links.html

[spYre]

Importation de feuilles visibles depuis une source de type classeur, de type dBase ou de type csv.
C’est la méthode idéale pour importer des modèles de calcul (formules), pour peu que l’on garde à l’esprit les détails ci-dessous.
Menu Insertion -> Feuille à partir d’un fichier... avec option Lier. Ignorer l’option Lier a le même effet que copier une feuille du fichier source via le menu Édition -> Feuille -> Déplacer/copier...

Commandes et paramètres.
Menu Édition -> Liens... [ Actualiser | Modifier... | Rompre le lien ].
Menu Outils -> Options... -> Calc -> Général -> Actualiser les liens au chargement [ Toujours | Sur demande | Jamais ].

Principe.
Contrairement à la méthode par url, celle-ci importe une feuille qui reste visible et qui inclut les formules.
Ce qui suit est important à connaître lorsque se produisent des erreurs de type #REF! dues à des ajustements de références incorrects.
Si, dans le fichier source, la feuille à copier contient des références à d’autres feuilles de la source, les références dans le fichier cible seront ajustées en fonction de la position de ces feuilles.
Si la première feuille du fichier source se nomme "Feuille1" et la première feuille du fichier cible "F1", alors la formule $Feuille1.A1 sera transformée en $F1.A1.
Une référence relative à une feuille Feuille1.A1 pointera vers une feuille ayant la même position relative que dans le fichier source. Ce principe s’applique de la même manière aux plages nommées (nom=$Feuille1.A1 devient $F1.A1).
Par exemple, l’erreur #REF! se produira lorsque une feuille est importée en première position alors qu’elle contient des références à une première feuille. Même chose avec des références relatives à la feuille précédente si le fichier cible ne propose aucune feuille précédente.

Pour les utilisateurs de l’API.
http://api.openoffice.org/docs/common/r ... SheetLinks (com.sun.star.sheet.SheetLinkMode.NORMAL)

[spYre]

Importation de tableaux ou de matrices à partir de leur nom.
Fonctionne pour des tableaux intégrés à une source html et pour des plages de classeur nommées.
Cette méthode semble être la seule qui permette d’importer des tableaux html à partir d’un site web via l’interface utilisateur.
Menu Insertion -> Lien vers des données externes... Il est possible de définir un intervalle de mise à jour automatique.

Commandes et paramètres.
Menu Édition -> Liens... [ Actualiser | Modifier... | Rompre le lien ].
Menu Outils -> Options... -> Calc -> Général -> Actualiser les liens au chargement [ Toujours | Sur demande | Jamais ].

Principe.
Les données issues de plages nommées se comportent a priori comme les pages entières du chapitre précédent pour ce qui concerne les références à d’autres feuilles.

Limitations.
- Il peut s’avérer difficile de choisir le bon tableau html à partir de la boîte de dialogue, dans la mesure où ils sont listés par leur nom générique (tableau_1, tableau_2...). Ceci impose parfois de procéder par essais successifs. L’analyse du code source peut être une autre option.
- Pour les données html, il semble impossible de maîtriser la reconnaissance des nombres. Quel que soit le formatage de la feuille avant l’importation, un résultat sportif du type "9-5" sera interprété comme une saisie dans une cellule non formatée, et donc transformé en date.
http://www.openoffice.org/issues/show_bug.cgi?id=85540

Pour les utilisateurs de l’API.
http://api.openoffice.org/docs/common/r ... #AreaLinks

[spYre]

Importation de valeurs depuis une source de type classeur, de type dBase ou de type csv.

Insérer un lien DDE sans peine.
- Copier une plage depuis le fichier source.
- Sélectionner une cellule dans le fichier cible et ouvrir Édition -> Collage spécial...
- Cocher l’option Lien. Ceci aura pour effet d’insérer une formule du type

Code : Tout sélectionner

=DDE("soffice";"C:\chemin\nom.ods";"reference";0)

Voir "DDE, fonction" dans l’aide intégrée.
- Le nom de serveur "soffice" doit fonctionner dans Open|LibreOffice. J’ignore quels autres noms pourraient fonctionner dans MS Windows. Vu que le protocole DDE n’existe pas dans les autres systèmes d’exploitation, Open|LibreOffice embarque sa propre implémentation.
- L’argument fichier doit être fourni dans la notation système et non sous forme d’url de fichier (file:///chemin).
- L’argument plage peut être littéral, comme "FeuilleX.B1", ou se référer à une plage nommée dans le fichier source.
Attention : "B1" pointera toujours vers la première feuille du fichier source !
- Le quatrième argument numérique est optionnel et signifie :

Code : Tout sélectionner

0 (défaut) : format numérique à partir du style de cellule "Par défaut"
1 : les données sont toujours interprétées sur la base du format par défaut pour l'anglais US
2 : les données sont appliquées comme du texte, sans conversion en nombres

Commandes et paramètres.
Menu Édition -> Liens... [ Actualiser | Modifier... | Rompre le lien | Actualisation automatique ou manuelle ].
Menu Outils -> Options... -> Calc -> Général -> Actualiser les liens au chargement [ Toujours | Sur demande | Jamais ].

Principe.
Le document source est chargé en mode invisible s’il n’est pas déjà chargé par ailleurs. Comme la source est conservée en mémoire, l’actualisation automatique est rendue disponible. Toute modification de la plage source est ainsi directement répercutée dans le fichier cible. L’actualisation manuelle peut être activée dans le dialogue Édition -> Liens...

Limitations.
- Il n’est pas simple de transposer ce type de lien sur d’autres machines. Il est donc préférable de conserver une référence unique au chemin du fichier source, de manière à n’avoir qu’une cellule à modifier le cas échéant. Modifier le lien via le dialogue Édition -> Liens... n’a qu’un effet temporaire, annulé au recalcul suivant de la fonction DDE.
- Le nombre de cellules qu’il est possible de lier en une opération semble limité à quelques milliers. J’ignore les détails. La formule matricielle

Code : Tout sélectionner

{=DDE("soffice";$A$1;"Feuille1.$A$1:$A$65536")}

fonctionne jusqu’à la ligne 32747 (A1 contenant le chemin).
- Oui, la fonction DDE d’un classeur peut se connecter à des tableaux Writer... sous réserve d’ajustements : http://www.oooforum.org/forum/viewtopic.phtml?t=61950 (en anglais).

Purge.
- Utiliser l’outil Rechercher & remplacer afin de trouver toutes les formules DDE contenant les chemins à éliminer.
- Commenter ces formules en les faisant précéder d’une apostrophe ou en suppriment le '='.
- Vérifier également les éventuelles plages nommées (Ctrl+F3).
- Enfin, ouvrir Édition -> Liens... et rompre les liens.

Pour les utilisateurs de l’API.
http://api.openoffice.org/docs/common/r ... l#DDELinks

[spYre]

Cette méthode permet d’importer des tables de données à partir de diverses sources externes, y compris depuis des serveurs de base de données.
Il est même possible d’importer, via une base de données, un classeur dans un autre lorsque l’on souhaite disposer d’un ensemble de colonnes trié sur plus de 3 champs.

A. Digression relative à Base et aux sources de données enregistrées..
Une source de données enregistrée est un document Base qui rend ses tables et requêtes accessibles aux autres modules de la suite (actuellement Writer et Calc).
Les données peuvent être importées par glisser-déposer depuis le volet de sources de données (raccourci F4) ou au moyen de divers outils tels que le publipostage dans Writer ou le pilote de données dans Calc.
L’enregistrement des bases de données se gère dans Outils -> Options... -> Base -> Bases de données. Un document Base non enregistré peut être utilisé tel quel mais reste inaccessible depuis Writer ou Calc.
Un document Base peut être connecté à une source externe comme Oracle, MySQL, PostgreSQL, MSSQL, MS Access, dBase, voire à un fichier csv ou à un classeur. Le flux de données peut donc être schématisé comme ceci :

Code : Tout sélectionner

[source_externe] -> Base.odb (enregistrée) -> Calc.ods ou Writer.odt.

[source_externe] peut être n’importe quel document accessible depuis Base. Si le document Base utilise le moteur intégré hsql, toutes les tables sont enregistrées dans le fichier Base.odb sans recours à une source externe.

La base de données exemple Bibliography.
- Dans Writer ou Calc, taper F4 et déployer la base de données Bibliography fournie par défaut. "Requêtes" et "Tables" s’affichent. Le seul élément existant est une table nommée "biblio".
- Ouvrir Bases de données enregistrées... depuis le menu contextuel de "Bibliography", de manière à voir quel fichier correspond à cette source de données : "Bibliography" représente un document de base de données enregistré sous <ooo_user_dir>/user/database/biblio.odb. Fermer le dialogue.
- Ouvrir Éditer un ficher de base de données... depuis le menu contextuel de "Bibliography". Ceci ouvre le document de base de données biblio.odb.
- La base de données contient une élément "biblio" dans la catégorie "Tables".
- Dans la fenêtre principale de la base de données, la barre d’état indique, comme type de source de données, "dBase" et comme chemin "<ooo_user_dir>/user/database/biblio/".
Pour plus de détails sur les connections : menu Édition -> Base de données -> Propriétés....

Comment importer une copie liée de Bibliography.
- Glisser la table "biblio" depuis la partie gauche du volet de sources de données dans la cellule d’une feuille Calc inutilisée.
- Modifier la table dans la partie droite du volet de sources de données.
- Sélectionner une cellule dans la plage d’importation et faire menu Données -> Actualiser la plage. La plage d’importation devrait répercuter les modifications apportées à la source.

Commandes et paramètres.
Cette plage importée actualisable est une plage de base de données nommée "Importer1". Ouvrir Données -> Définir la plage..., sélectionner la plage d’importation et cliquer sur le bouton [Plus]. Les options complémentaires sont :
- Contient des étiquettes de colonnes [toujours vrai pour les plages importées] ;
- Insérer ou supprimer des cellules [les formules adjacentes à droite et en bas seront adaptées en fonction de la taille de la plage importée] ;
- Conserver le formatage [des cellules de la feuille] ;
- Ne pas enregistrer les données importées [économise l’espace disque et force l’actualisation manuelle à l’ouverture du fichier].
Une ligne au bas du dialogue mentionne la source.

Le menu Données -> Sélectionner la plage... permet la sélection des plages de données du document en cours.
Le menu Données -> Actualiser la plage actualise la plage sélectionnée (une seule cellule dans la plage suffit).
Le menu Données -> Pilote de données -> Démarrer... -> Source de données enregistrée permet la création d’un pilote de données à partir d’une table ou d’une requête de la source de données.

Limitations.
- Les sources de données nécessitent certains paramétrages pour être utilisables sur un autre système. Le détail des paramètres et des caractéristiques dépend du type de la source de données (native, hsql intégrée ou autre type externe).
- Si l’on souhaite d’autres noms que "Importer1", "Importer2"... pour les plages importées, il faut créer manuellement une plage de données, d’une seule cellule, avant d’y faire glisser les données.
- Base supporte les requêtes paramétrées, qui proposent à l’utilisateur de fournir les arguments via une boîte de dialogue, mais il n’existe aucune méthode prédéfinie permettant de remplacer ces paramètres par des valeurs de cellules. Le document ci-joint fournit une série de macros pour pallier cette limite :

dbq.ods

Macro permettant l'importation paramétrée depuis une source de données enregistrée.

(26.36 Kio) Téléchargé 1070 fois

- À la création d’un pilote de données basé sur une source de données, ni les champs DATE ni les champs TIMESTAMP ne sont importés (Rapport 81848).

Pour les utilisateurs de l’API.
http://api.openoffice.org/docs/common/r ... l#DDELinks
http://api.openoffice.org/docs/common/r ... iptor.html
http://api.openoffice.org/docs/common/r ... Range.html
Les pilotes de données créés à partir de sources de données ne sont pas accessibles via l’API.