Tuto Apprendre en ligne n’a jamais été aussi simple – Tutoriels pour tous les niveaux.
Table de Matieres
I. Présentation
En entreprise, il est fréquent de trouver des bases de données gérées par le système MySQL. De la même manière, il est fréquent de trouver la suite Microsoft Office installée sur les postes clients. Alors pourquoi ne pas exploiter la puissance de ces deux produits en les faisant travailler ensemble ?
Dans ce tutoriel, nous allons établir une connexion entre un document Excel et une base de données MySQL. Ainsi, vos données se retrouveront dans le tableur et vous pourrez manipuler les données comme bon vous semble (filtre, tri, graphique, tableaux croisés dynamiques, etc…).
Autrement dit, MySQL fournira les données et Excel les manipulera, vous bénéficierez alors de la puissance d’Excel pour jouer avec vos précieuses données.
Pour ceux qui préfèrent suivre le cours au format vidéo :
Le serveur MySQL d’un côté, l’application Excel de l’autre, il faudra établir la connexion entre les deux grâce à une connexion ODBC. Pour cela, il sera nécessaire d’installer un driver spécifique à MySQL sur la machine où l’on souhaite créer la liaison.
ODBC (Open DataBase Connectivity) : Système de connexion intégré à Windows qui permet la communication entre des applications clientes et des systèmes de gestion de base de données (SGBD).
ODBC est donc au coeur des communications entre Excel et MySQL.
III. Base de données de démonstration
Dans le cadre de ce tutoriel, j’ai créé une base de données nommée « contact » et contenant une seule table appelée « personnes« .
Dans cette table, on trouve trois champs : ID, Identification et Age. L’objectif est d’identifier rapidement des personnes avec un numéro unique, un prénom et l’âge.
Ce sont ces données que nous allons chercher à importer dans Excel grâce à la connexion ODBC.
Voici un aperçu de la base de données dans PhpMyAdmin :
Pour ma part, j’utilise WAMP sur une machine Windows, c’est également sur cette machine que j’utiliserais Excel. Autrement dit, tout est en local.
IV. Création d’un utilisateur dédié
Nous n’allons pas connecter Excel à MySQL en utilisant l’utilisateur « root » (ne me dite pas que vous y avez pensé !), c’est une précaution importante à prendre en matière de sécurité. Un utilisateur spécifique sera créé en lui donnant uniquement des droits de lecture sur la base de données « contact ».
Sinon, imaginez si l’utilisateur à trop de droits… A partir du fichier Excel, on pourrait en s’appuyant sur la connexion établie, modifier la base de données… Sachant que ce serait qu’une partie de plaisir grâce à un script, aux macros, etc.
D’où l’importance de sécuriser la connexion… Pour créer un nouvel utilisateur, accédez à PhpMyAdmin. Sur la gauche, sélectionnez votre base de données, par exemple « contact » dans mon exemple.
Cliquez sur l’onglet « Privilèges » et sur « Ajouter un utilisateur« .
Il faut ensuite remplir le formulaire. Indiquez en nom d’utilisateur « excel » et attribuez-lui un mot de passe (complexe). Quant à la ligne client, je choisis localhost.
Vous devez désactiver l’option « Donner tous les privilèges sur la base de données contact » car nous ne souhaitons pas donner tous les droits ! Contentez-vous de sélectionner « SELECT » dans la liste des autorisations pour l’utilisateur ait seulement l’autorisation de lire les données de la base.
Enfin, validez la création de l’utilisateur.
Note : Dans les paramètres de connexion ODBC, vous pouvez activer la connexion SSL à condition que votre serveur MySQL est paramétré pour cela.
V. Installer le driver MySQL Connector ODBC
Il faut se rendre sur le site de MySQL et disposer d’un compte Oracle (gratuit) pour télécharger le driver du connecteur.
Télécharger MySQL Connector ODBC
Prenez la version que vous souhaitez, 32 ou 64 bits mais soyez en adéquation avec votre OS et votre version d’Excel.
L’installation se déroule très simplement, pensez à choisir la version complète du driver.
VI. Création de la connexion ODBC MySQL
Désormais, nous allons pouvoir créer la connexion ODBC à destination du serveur MySQL. Pour cela, accédez au Panneau de configuration et cliquez sur « Outils d’administration« .
Dans la liste, ouvrez « Sources de données ODBC (32 bits) » ou « Sources de données ODBC (64 bits) » selon la version du driver installé.
Un assistant s’ouvre… Dans l’onglet « Sources de données utilisateur » cliquez sur « Ajouter » sur la droite.
Dans la liste, vous devez trouver le driver ODBC pour MySQL que nous venons d’installer. Préférez l’utilisation de la version Unicode pour les applications plus récentes. Cliquez sur « Terminer« .
Ensuite, on doit nommer la source de données (exemple : Mon App) et remplir les paramètres de connexion au serveur MySQL. Indiquez l’adresse IP ou le nom pour joindre le serveur, pour ma part c’est du local donc localhost. Le port 3306 n’a pas besoin d’être changé sauf si vous l’avez modifié dans la configuration de MySQL.
Enfin, indiquez l’utilisateur « excel » et son mot de passe, puis cliquez « Test » pour tester la connexion.
Vous devriez obtenir un message de validation :
Terminez en sélectionnant votre base de données dans la liste « Database« , pour ma part il s’agit de « contact« . On peut voir que notre source de données apparaît dans la liste enregistrée :
VII. Connecter Excel à MySQL
La connexion ODBC est prête, le serveur MySQL également, il ne reste plus qu’à connecter Excel. Ouvrez un nouveau document Excel, et dans l’onglet « Données » cliquez sur « Connexion » :
Cliquez sur le bouton « Ajouter« .
Aucune liaison existante n’est intégrée dans ce document Excel, il faut donc cliquer sur « Rechercher« .
Cliquez sur « Connexion à une nouvelle source de données » pour créer un nouveau fichier de connexion (.odc).
Sélectionnez « DSN ODBC » et cliquez sur « Suivant« .
DSN (Data Source Name) : Permet de déclarer la source de données qui sera accessible via ODBC.
On sélectionne la source de données « Mon App » que nous avions créé précédemment. Cliquez sur « Suivant« .
Décochez la case « Connexion à une table spécifique » pour englober toute la base de données et cliquez sur « Suivant » pour poursuivre.
Enfin, validez en cliquant sur « Terminer« . Il faut savoir qu’un fichier « contact.odc » sera créé sur la machine, c’est le fichier de configuration de la connexion. Il peut être exporté et copié sur une autre machine où vous souhaitez utiliser cette connexion, cela évite de refaire une partie de la configuration.
Maintenant, en cliquant sur « Connexions existantes » on va ouvrir notre connexion. Dans la liste, sélectionnez « contact » et cliquez sur « Ouvrir« .
Validez l’insertion en forme de tableau en cliquant sur « OK« .
On peut voir que les données sont bien présentent dans Excel et qu’elles sont identiques à celles de la BDD :
La connexion entre Excel et la base de données MySQL est désormais opérationnelle !
XIII. Modifier la requête SQL
Pour finir, nous allons voir comment modifier la requête SQL effectuée dans la base. Par défaut, tous les champs sont sélectionnés, on peut alors vouloir en sélectionner uniquement certains. Cliquez sur le bouton « Connexions« .
Par exemple, plutôt que d’afficher l’ID, l’identification et l’âge, on pourrait sélectionner uniquement l’identification et l’âge.
Cliquez sur « Propriétés« .
Dans l’onglet « Définition », il faudra modifier la requête SQL qui se trouve dans la zone « Texte de la commande » comme ceci (sur la copie d’écran la requête par défaut est affichée) :
Après avoir cliqué sur « OK« , vous devrez cliquer sur « Oui » pour mettre à jour les informations de connexion.
Vous revoilà dans Excel, on remarque bien qu’une colonne a disparue :
On peut très bien imaginer limiter le nombre de résultats retournés par la requête SQL, indiquer un ordre de tri, etc… A vous de voir, sachant que l’on peut aussi faire tout ça directement dans Excel. Enfin, pour actualiser les données, il suffit d’appuyer sur le bouton adéquat :
Pour pouvoir mettre à jour les données, il faut bien entendu être en ligne pour pouvoir contacter le serveur MySQL. Par ailleurs, une fois le document Excel enregistré avec des données, vous pouvez le consulter sur n’importe quel poste (en ligne ou hors ligne).
Hébergez votre site à partir de 2$ sur 👉👉👉 https://www.tnctech.ca 
