Transfert de Données dans Calc

et utilisation avancée des

sources de données

Distribué par le projet Fr.OpenOffice.org

Révision 1.00 26/01/04

Réalisé avec : OOo 1.1.0	Plate-forme / Os : Toutes

Table des Matières

1 But de ce how-to	3

2 Pré-requis	3

3 Création d'une table dBase avec Calc qui servira de base à ce how-to.	4

4 Création de la source de données	5

5 Transfert de la table dans Calc	5

6 Mise à jour des données	6

7 Utilisation d'une requête	8

8 Utilisation d'une requête Paramétrée	9

9 Utilisation d'une macro	10

9.1 But de l'utilisation d'une Macro	10

9.2 Mise en place de la Macro	10

9.3 Paramétrage de la macro	11

9.4 Création des plages nommées	12

9.5 Mise en place de la requête SQL	12

9.6 Ajout du Bouton, permettant de lancer la Macro	13

9.7 Test de la macro	13

9.8 Amélioration de la Macro	14

10 Aperçu des possibilités offertes par les sources de données	15

11 ANNEXE - La macro	16

12 Conclusion	17

13 Crédits	18

14 Licence	18

But de ce how-to

Ce how-to a pour but de montrer les possibilités de Calc dans le traitement des bases de données.

Ce how-to est donc destiné à des personnes utilisant des bases de données et souhaitant en extraire des parties dans Calc, dans le but de faire des analyses ou des traitements spécifiques.

Pré-requis

Ce how-to est destiné à des personnes ayant déjà des connaissances dans les domaines suivants :

Création et utilisation de sources de données (Outils / Sources de données ou F4)

Connaissances générales sur les bases de données

Installation de macros dans l'éditeur (Pas indispensable, mais conseillé)

Assignation d'une macro à un bouton (Pas indispensable, mais conseillé)

Création d'une table dBase avec Calc qui servira de base à ce how-to.

J'ai choisi le format dBase car c'est la seule base de données que l'on peut créer directement avec OOo et qui ne nécessite pas l'installation de programmes complémentaires.

Cependant, il est parfaitement possible (voire souhaitable) d'utiliser une autre base de données (ex : MySQL) pour réaliser ce how-to.

Procédure pour créer la table dBase :

Créer avec la méthode de votre choix un nouveau dossier destiné à recevoir la table dBase.

Dans une nouvelle feuille Calc, saisir les informations suivantes (ou faire un copier / coller) :

CODECLIENT,N,6,0	CLIENT,C,50	CA,N,8,2
1001	Client 1	100
1002	Client 2	200
1003	Client 3	300

La première ligne de ce tableau contient la définition complète des champs de la table :

CODECLIENT = Nom du 1er champ de la table sur 12 caractères maximum, en majuscule et sans espaces, caractères spéciaux ou caractères accentués

,N = Signifie que ce sera un champ de type Numérique (ex : CODECLIENT)

,C = Signifie que ce sera un champ de type Caractère (ex : CLIENT et VILLE)

,6,0 = Signifie que le champ numérique sera sur 6 chiffres avec 0 décimale (ex : CODECLIENT)

,50 = Signifie que le champ sera sur 50 caractères

Enregistrer le fichier avec le menu « Fichier / Enregistrer sous »

Choisir pour l'exemple « CLIENT » comme nom de fichier. ATTENTION : Le nom du fichier ne doit pas contenir d'espaces, de caractères spéciaux et ne pas faire plus de 10 caractères pour ne pas poser de problèmes dans les requêtes SQL.

Choisir « dBase » dans la zone « Type de fichier » et cliquer sur « Enregistrer »

Choisir dans la liste « Europe Occidentale (Windows 1252 / WinLatin 1) » et cliquer sur OK. Vous pouvez aussi utiliser le jeu de caractères Europe occidentale ISO8859-15.

ATTENTION : Si vous choisissez une autre option, vous risquez de perdre les caractères spéciaux (accents...)

Fermer le fichier en répondant « Oui » à l'avertissement de pertes de données.

Création de la source de données

Créer une source de données qui pointera sur le nouveau dossier contenant la table :

Menu « Outils / Sources de données... »

Bouton « Nouvelle source de données »

Indiquer un nom quelconque à la source de données (ex : dBase)

Dans « Type de BD », sélectionner « dBase »

Cliquer sur « ... » pour aller rechercher le nouveau dossier (ex : dBase) contenant la table

Valider la création de la source de données en cliquant sur OK

Normalement, vous devez pouvoir consulter et modifier les données de la nouvelle table depuis les sources de données :

Menu « Affichage / Sources de données » ou touche « F4 »

Cliquer sur le « + » de « dBase », sur le « + » de « Tables » et sur la table « CLIENT »

Ajouter et modifier des données dans la table

Transfert de la table dans Calc

Depuis les sources de données, faire glisser le nom de la table (ex : CLIENT) sur la cellule A1 d'une feuille Calc. Normalement, le contenu complet de la table dBase doit être recopié dans Calc.

Mise à jour des données

Les données transférées précédemment restent liées avec la table de la base de données. Si celle-ci est modifiée, il est possible de faire une mise à jour des données de la feuille Calc.

Pour montrer les différentes possibilités, nous allons faire quelques manipulations :

Effectuer une mise en forme de votre tableau (Titre en gras, Quadrillage du tableau, Adapter la largeur des colonnes)

Ajouter une nouvelle ligne en bas de tableau, pour effectuer la somme de la colonne « CA »

Dans les sources de données, ajouter 2 enregistrements.

Nous allons maintenant actualiser les données dans Calc et constater le résultat. Le menu « Données / Actualiser la plage » (rester dans la zone de transfert pour que ce menu soit actif), doit effectuer les opérations suivantes :

Les données dans Calc doivent correspondre aux données dans la table dBase :-)

Le titre en gras ne doit plus être en gras :-(

Le quadrillage a dû disparaître et la largeur des colonnes a été modifiée :-(

La ligne de total a dû être remplacée par les données :-(

Heureusement, il existe des options qui permettent de corriger les problèmes précédents. Lors du transfert des données, Calc a créé une plage de données qui contient des options :

Menu « Données / Définir une plage... »

Sélectionner le nom « Import1 » et cliquer sur « Options >> »

Cocher « Insérer / Supprimer des cellules » pour que la ligne de total suive la taille du tableau

Cocher « Conserver le formatage » pour conserver la mise en forme du tableau et la largeur des colonnes

Cocher « Ne pas enregistrer les données importées » pour diminuer la taille du fichier et effectuer l'actualisation des données lors de l'ouverture du fichier

Décocher la case « Contient des étiquettes de colonnes » devrait supprimer le nom des champs de la table mais, malheureusement, cette option semble boguée et ne fonctionne pas.

Une fois ce paramétrage terminé, vous pouvez recommencer la mise en forme de votre tableau, ajouter des enregistrements dans la table et actualiser les données pour voir la différence.

Utilisation d'une requête

Avec l'exemple précédent, il n'est pas possible de transférer une partie des données de la table ou de choisir les champs à transférer.

Pour remédier à cela, il est possible de créer une requête dans la source de données et de glisser le nom de cette requête sur la feuille Calc pour obtenir ce résultat :

Ouvrir les sources de données (F4)

Clic droit sur « Requête » de la source de données « dBase »

Nouvelle requête (vue SQL)

Saisir la requête « SELECT CLIENT, CA FROM CLIENT WHERE CA>100 ORDER BY CA »

Enregistrer la requête en laissant le nom par défaut « Requête1 »

Quitter le programme d'ébauche de requêtes

Depuis les sources de données, faire glisser le nom de la requête sur la feuille Calc

Pour la mise en forme et l'ajout d'une ligne de total, il est possible d'utiliser les options du chapitre précédent.

Avec les requêtes, il est donc possible :

De choisir les enregistrements de la table

De choisir les champs

De choisir l'ordre de tri

De relier plusieurs tables (mais pas avec dBase)

etc...

Utilisation d'une requête Paramétrée

Avec l'exemple précédent, pour choisir les enregistrements à transférer, il faut obligatoirement modifier la requête, ce qui est peut être pénible si les modifications sont nombreuses.

Pour remédier à cela, il est possible de créer une requête paramétrée qui demandera de donner le contenu d'une variable avant de s'exécuter :

Ouvrir les sources de données (F4)

Clic droit sur « Requête » de la source de données « dBase »

Nouvelle requête (vue SQL)

Saisir la requête « SELECT CLIENT, CA FROM CLIENT WHERE CA>:LeCA ORDER BY CA »

Le signe « : », indique que la requête est paramétrée et que le paramètre est nommé « LeCA ». Lors de l'exécution de la requête, une boîte de dialogue apparaîtra pour demander la valeur du paramètre. Il est possible également de mettre plusieurs paramètres dans la requête.

Enregistrer la requête en laissant le nom par défaut « Requête2 »

Quitter le programme d'ébauche de requêtes

Depuis les sources de données, faire glisser le nom de la requête sur la feuille Calc

Avec les requêtes paramétrées, il est donc possible :

De choisir les enregistrements de la table à chaque actualisation des données en utilisant le menu « Données / Actualisation des données »

De choisir les champs

De choisir l'ordre de tri

De relier plusieurs tables (mais pas avec dBase)

etc...

Utilisation d'une macro

But de l'utilisation d'une Macro

Avec l'exemple précédent, l'utilisateur doit renseigner la valeur des paramètres en utilisant le menu « Données / Actualisation des données ». S'il y a plusieurs paramètres à renseigner, cela ne sera pas très pratique.

De plus, avec les requêtes paramétrées, il est impossible de ne pas renseigner un paramètre dans le but de transférer toutes les données.

Pour remédier à cela, il faut passer par une macro qui va se charger :

De la création de la plage de données

Du paramétrage de la requête en fonction d'un ou plusieurs paramètres

De l'exécution de la requête

Mise en place de la Macro

Procédure pour copier la macro donnée en ANNEXE dans l'éditeur Basic :

Copier la macro donnée en Annexe

Menu « Outil / Macros / Macro... »

Sélectionner le nom de votre fichier dans la liste et cliquer sur « Nouveau »

Valider le nom du module « Module1 » proposé par défaut

Effacer toutes les lignes écrites par défaut dans l'éditeur Basic (sub main, macro1..)

Coller le code dans l'éditeur Basic.

Paramétrage de la macro

Ligne 8 : Le seul paramètre obligatoire est le nom de la source de données « NomSource »

Ligne 11 : Vous pouvez changer le nom de la plage de données avec le paramètre « NomDataBaseRange ». Si ce paramètre n'est pas renseigné (par défaut), le programme prendra le nom de la feuille active. Ce qui permet par défaut d'avoir plusieurs plages de données dans un même classeur mais pas sur une même feuille.

Ligne 12 : Le paramètre « AdresseDestination » permet d'indiquer l'adresse de destination du transfert des données dans Calc. Si ce paramètre n'est pas renseigné (par défaut), le programme cherchera un nom de plage de données portant le même nom que la feuille active. Si le nom n'est pas trouvé, le programme indiquera une erreur.

Ligne 13 : Le paramètre « Requete » donne la requête SQL complète. Si ce paramètre n'est pas renseigné, le programme cherchera la requête dans une cellule nommée « Nom Feuille Active » + « SQL » (ex : « Feuille1SQL »)

Création des plages nommées

Comme nous l'avons vu dans le chapitre précédent, la macro a besoin de deux plages nommées pour fonctionner. Le nom de ces plages nommées doit correspondre au nom de la feuille. Pour faciliter la compréhension, nous allons commencer par renommer la feuille et ensuite créer les 2 plages nommées :

Renommer la feuille :

Clic droit sur l'onglet de la feuille + « Renommer la feuille »

Pour l'exemple, utiliser le nom « Base »

Créer la plage nommée "Base" qui contiendra le résultat du transfert des données

Sélectionner, par exemple la cellule « A5 »

Menu « Insertion / Noms / Définir »

Saisir « Base » et cliquer sur « Ajouter » et sur « OK » -> ATTENTION : Ce nom doit être le même que celui de la feuille

Créer la plage nommée « BaseSQL » qui contiendra la requête SQL

Sélectionner par exemple la cellule « A1 »

Menu « Insertion / Noms / Définir »

Saisir « BaseSQL » et cliquer sur « Ajouter » et sur « OK » -> ATTENTION : Ce nom doit être le même que celui de la feuille en ajoutant « SQL »

Mise en place de la requête SQL

Il suffit de saisir dans la cellule nommée « BaseSQL », c'est à dire la cellule « A1 » dans notre exemple, la requête SQL désirée :

SELECT CLIENT, CA FROM CLIENT WHERE CA>100 ORDER BY CA

Ajout du Bouton, permettant de lancer la Macro

Pour terminer et tester notre exemple, il ne reste plus qu'à mettre en place le bouton qui permettra d'exécuter la macro et donc de transférer le résultat de la requête dans la plage « Base ».

Afficher les fonctions de formulaire à l'aide de (5ième icône) de la barre verticale.

Cliquer sur le deuxième bouton pour créer un « Bouton »

Définir les dimensions et l'emplacement du bouton en faisant glisser la souris sur la feuille Calc

Menu « Format / Contrôle » ou « Clic droit » + « Contrôle », pour afficher les propriétés

Dans « Titre », saisir « Transfert des Données »

Cliquer sur l'onglet « Événements »

Cliquer sur « .... » de la deuxième ligne « Lors du déclenchement »

Dans la zone en bas à gauche, cliquer sur les « + » pour faire apparaître, dans la fenêtre de droite, le nom de la Macro « TransfertDesDonnees »

Cliquer sur le bouton « Assigner », puis sur « OK » et fermer la fenêtre des propriétés

Sortir du mode conception, en cliquant sur de la barre de fonctions de formulaires

Test de la macro

Normalement, en cliquant sur le bouton les données correspondantes à la requête doivent être transférées sur la feuille Calc.

Il est possible de modifier la requête SQL et de re-cliquer sur le bouton pour faire de nouveaux tests.

Il est possible également que la requête SQL soit le résultat d'une formule faisant intervenir une ou plusieurs cellules, ce qui permet de la paramétrer facilement, ex :

En B1, saisir 200

Dans « BaseSQL », c'est à dire la cellule « A1 » saisir la formule « ="SELECT CLIENT, CA FROM CLIENT WHERE CA>" & B1 & " ORDER BY CA" »

Amélioration de la Macro

En général, les utilisateurs ne connaissent pas le langage SQL et, dans ce cas, il est préférable que la requête SQL soit inscrite dans la macro plutôt que sur la feuille de calcul. Nous allons donc modifier la macro, pour créer une requête SQL paramétrée :

Effacer la requête SQL écrite en A1

Saisir le CA minimum, à transférer, dans la cellule C1 ou ne rien saisir pour transférer toute les données

Coller le code ci-dessous dans la macro « TransfertDesDonnees » en dessous de la ligne : « LaFeuille=Thiscomponent.getCurrentController.activeSheet » (ligne 15)

LaFeuille=Thiscomponent.getCurrentController.activeSheet

Requete="SELECT CLIENT, CA FROM CLIENT "

LeCA=LaFeuille.getcellRangebyName("C1").string

if LeCA<>"" then Requete=Requete & " WHERE CA>" & LeCA & " "

Requete=Requete & " Order By CA "

Il est possible d'avoir plusieurs paramètres mais je pense, qu'avec cet exemple, vous ne devriez pas avoir de mal à adapter la macro à vos besoins.

Grâce à cette modification, il est possible de transférer toutes les données de la table si la cellule B2 n'est pas renseignée.

Aperçu des possibilités offertes par les sources de données

Avec les sources de données, il est possible d'effectuer de nombreuses opérations sur les bases de données. En voici quelques exemples :

Création d'une table :

Clic droit sur « Tables »

Menu « Nouvelle ébauche de table »

Modification de la définition d'une table :

Clic droit sur la table

Menu « Éditer la table »

Création d'un ou plusieurs index pour une table :

Clic droit sur la table

Menu « Éditer la table »

Menu « Outils / Ébauche d'index »

Copier une table à l'intérieur d'une base de données, ou dans une autre base de données :

Faire glisser le nom de la table sur « Tables » de la source de données en cours ou d'une autre source de données

Suivre l'assistant qui s'affiche

Copier des enregistrements dans Calc :

Sélectionner un ou plusieurs enregistrements (avec CTRL ou MAJ) d'une table d'une source de données

Faire glisser la sélection sur la feuille Calc

Ajouter un champ de Mailing dans Writer :

Faire glisser le nom d'un champ d'une table sur le document Writer

Ajouter un contrôle de formulaire dans Calc ou Writer, en mode édition de formulaire

Pour Calc, faire glisser le nom d'un champ d'une table sur le document Calc

Pour Writer, faire glisser le nom d'un champ d'une table en appuyant sur CTRL+MAJ

Créer une nouvelle table à partir d'un tableau Calc :

Sélectionner un tableau dans Calc où la première ligne indique le nom des champs de la table à créer

Faire glisser la sélection sur « Table »

Indiquer le nom de la table à créer dans l'assistant et suivre les indications

Ajouter des données d'un tableau Calc dans une table d'une source de données :

Sélectionner les données du Tableau Calc en mettant de préférence sur la première ligne du tableau le nom des champs de la table de destination

Faire glisser la sélection sur « Tables » de la sources de données

Dans l'assistant, saisir le nom de la table où vous voulez ajouter les enregistrements

Sélectionner l'option « Joindre les données » et continuer à suivre l'assistant

ANNEXE - La macro

Ci-dessous, la macro à copier / coller dans l'éditeur Basic

sub TransfertDesDonnees()

'Programme créé par Tony GALMICHE le 03/01/04

'Avec l'aide de Laurent GODARD et de son programme CalcSQL

dim NomDataBaseRange as string

dim AdresseDestination as string

dim Requete as string

'** Paramètre obligatoire **************************************************

NomSource="dBase"

'***************************************************************************

'** Paramètres facultatifs *************************************************

'NomDataBaseRange="DonneesExternes1"

'AdresseDestination="A6:D7"

'Requete="SELECT ARTIST, CATEGORY, TITLE, TRACKS FROM cds where category='rock'"

'***************************************************************************

LaFeuille=Thiscomponent.getCurrentController.activeSheet

'**Si NomDataBaseRange="" -> NomDataBaseRange = Nom de la feuille active ***

if NomDataBaseRange="" then	NomDataBaseRange=LaFeuille.name & "Import"

'***************************************************************************

'**Si AdresseDestination="" -> AdresseDestination = Nom feuille active *****

if AdresseDestination="" then	AdresseDestination=LaFeuille.name

'***************************************************************************

'** Si Requete="" -> Requète dans plage nommée NomFeuille & SQL ************

if Requete="" then

Requete=LaFeuille.getcellRangebyName(LaFeuille.name & "SQL").string

end if

'***************************************************************************

'** Création DataBaseRange si n'existe pas *********************************

LesDataRanges=thisComponent.DataBaseRanges

if not LesDataRanges.hasByName(NomDataBaseRange) then

LeRange=LaFeuille.getCellRangeByName(AdresseDestination)

LeRangeAddress=LeRange.RangeAddress

MonDataRange=LesDataRanges.AddNewByName(NomDataBaseRange,LeRangeAddress)

endif

oDataRange=LesDataRanges.getbyname(NomDataBaseRange)

oDataRange.KeepFormats=true 'Conserver la mise en forme

oDataRange.MoveCells=true 'Ajouter / Supprimer des lignes

'oDataRange.StripData=true 'Enregistrer les données

'***************************************************************************

'** Mise en place requète SQL dans la DataBaseRange ************************

oDBContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")

RetourBase=oDBContext.hasByName(NomSource)

LeCellRange=LaFeuille.getCellRangeByName(NomDataBaseRange)

dim ParamSource(3) as new com.sun.star.beans.PropertyValue

paramSource(0).name="DatabaseName"

paramSource(0).value=NomSource

paramSource(1).name="SourceType"

paramSource(1).value=com.sun.star.sheet.DataImportMode.SQL

paramSource(2).name="SourceObject"

paramSource(2).value=Requete

paramSource(3).name="Name"

paramSource(3).value="toto"

'***************************************************************************

'** Transfert des données **************************************************

LeCellRange.doImport(paramSource())

'***************************************************************************

End sub

Conclusion

Voilà, c'est la fin de ce how-to et j'espère avoir réussi à vous faire découvrir une partie des merveilles de OOo, sans vous donner mal à la tête...

Si vous avez d'autres astuces sur les sources de données, n'hésitez pas à me contacter pour que je les ajoutent à ce how-to dans une prochaine version.

Crédits

Auteur : Tony GALMICHE ( tony.galmiche@tiscali.fr ou tonygalmiche@openoffice.org )

Remerciements :

Merci à Laurent GODARD, pour son aide dans la mise au point de la Macro.

Merci à Olivier BIETZER pour sa relecture.

Merci à Sophie GAUTIER pour sa relecture et ses tests.

Merci à Christophe FICHOT pour sa relecture, ses tests et ses corrections.

Intégré par : Sophie GAUTIER

Dernière modification : 26/01/04

Contacts : Projet Documentation OpenOffice.org - Fr.OpenOffice.org

Traduction :

Licence

Appendix

Public Documentation License Notice

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the "License"); you may only use this Documentation if you comply with the terms of this License. A copy of the License is available at http://www.openoffice.org/licenses/PDL.html.

The Original Documentation is ''Transfert_de_donnees.sxw''. The Initial Writer of the Original Documentation is Tony GALMICHE Copyright (C) 2003. All Rights Reserved (Initial Writer contact: tony.galmiche@tiscali.fr or tonygalmiche@openoffice.org ) .

Contributor(s): ______________________________________.

Portions created by ______ are Copyright (C)_________[Insert year(s)]. All Rights Reserved. (Contributor contact(s):________________[Insert hyperlink/alias]).

NOTE: The text of this Appendix may differ slightly from the text of the notices in the files of the Original Documentation. You should use the text of this Appendix rather than the text found in the Original Documentation for Your Modifications.

Transfert_de_donnees 0.93

Fr.OpenOffice.org	OpenOffice.org Documentation Project How-To	6 / 18

2003-12-27T18:24:14

Christophe FICHOT