Comment utiliser excel pour gérer une table mysql hébergée sur un serveur distant.

Les fichiers exemples associés à cette page (zippés) sont à télécharger ici .

Préambule

Lorsque l'on est habitué à manipuler un peu excel, il parait très vite évident qu'il est bien plus simple de gérer les données d'une base mysql à partir d'excel plutôt que d'utiliser l'interface phpmyadmin, aussi conviviale soit-elle devenue. Ce choix impose cependant des manipulations multiples :

• gestion purement excellienne de la base
• export des données modifiées sous format csv ou sql
• ouverture du site distant
• upload des données.

Je souhaitais depuis un bon moment pouvoir piloter toutes ces opérations en cliquant dans mon classeur excel sur un simple bouton. Grace à l'aide essentielle de contributeurs émérites du forum MPFE, ce tutoriel et le classeur associé vous permettent maintenant de le faire ! Ces contributeurs sont :

• Christian Herbé, qui a mis sur son site un autre exemple d'utilisation de cette approche.
• Popi qui a fait une partie du travail d'intégration. Vous pouvez voir d'autres réalisations de Popi ici.
• J@C qui a peaufiné la partie concernant le transfert de fichiers via FTP. Vous en trouverez d'autres utilisations sur son site également.
• Enfin Michel Pierron nous a indiqué comment demander à internet explorer d'attendre que le boulot soit fini et j'ai mis tout ça en forme et userforms.

Avertissement :

Je ne voudrais pas faire croire à quelqu'un qui n'a jamais géré de site web (ou seulement via des interfaces toutes faites) et jamais mis l'oeil sur l'éditeur VBA d'excel qu'il va tout résoudre en un clic. Tout a été fait pour que la solution proposée ici soit la plus simple possible mais, compte tenu de la diversité des hébergeurs, des machines locales et distantes, des protocoles et des versions différentes, il se peut que vous ayiez à faire quelques modifications vous même dans le code du classeur. Aucun service après don (on ne va pas parler de vente gratuite quand même !) ne sera assuré. Je peux juste vous assurer que ce classeur ne comporte aucun virus, qu'il permet effectivement de gérer une table chez online et chez free et que son utilisation ne risque à priori vraiment pas de modifier votre excel ou de planter votre PC. Mais vous l'utilisez évidemment sous votre propre responsabilité. Si vous l'utilisez avec succès chez un autre hébergeur, vous pouvez me l'indiquer, ça rendra service à d'autres.

Pour pouvoir utiliser ce classeur il vous faut :

•  Connaître un minimum (vraiment mini) PHP et les bases mysql (vous trouverez une page d'aide ici.)
• Avoir votre propre site web !
•  
• Si vous disposez d'un site web, vous n'avez pas nécessairement de base mysql associée. Peut être que votre hébergeur ne le propose pas (changez !) ou que vous n'avez pas encore demandé la création de votre base mysql.
• Ce classeur utilise des API windows. Il ne peut donc pas fonctionner sur mac. Sur votre PC vous devez avoir une connection web ouverte au moment ou vous demandez à mettre à jour votre table sur le serveur et vous devez être capable de faire du FTP sur votre site. Il est nécessaire de disposer d'internet explorer (pas mozilla ou netscape !) sur le PC.
• Si vous avez tout ceci, munissez vous de vos login et mot de passe FTP, de vos logins et mots de passe Mysql, du nom de votre base mysql, créez un dossier local et un sur le site distant pour héberger les fichiers nécessaires et en route !

Description des opérations effectuées par le classeur excelphp.xls

Ce classeur vous permet :

• De créer une table sur une base existante
• De vider une table existante pour remplacer ses données par celle de votre classeur
• D'ajouter des fiches via un formulaire de saisie, sur vos bases de données locales et distantes
• De modifier des fiches existantes, en local et sur le serveur
• De supprimer des fiches, en local et sur le serveur.

En pratique, Excel crée à chaque requête un fichier php qui contient les instructions de connexion à votre base mysql et les instructions pour créer ou mettre à jour la table. Ce fichier est transféré ensuite par FTP sur le serveur, puis ouvert et lu par internet explorer ce qui exécute les instructions qu'il contient. IE est ensuite fermé. Cette phase de transfert sur le serveur est obligatoire pour des raisons de sécurité : on ne peut travailler sur une base que depuis un fichier hébergé sur le même serveur qu'elle.

Quelques remarques :
Les seuls tests qui sont effectués dans le classeur concernent ce qui se passe dans excel ou lors du transfert FTP. Aucun test n'est effectué pour savoir si les données envoyées sur la table sont effectivement prises en compte. Il est vivement conseillé, surtout au début, de vérifier le bon déroulement des opérations en utilisant l'interface phpmysql pour surveiller par exemple que la table a été créée ou que les données sont ajoutées correctement (dans les bonnes colonnes par exemple). Le classeur a été testé sur Online (et fonctionne !).

Attention, suivant les hébergeurs, le temps autorisé pour une opération est limité. Sur online par exemple, je ne peux pas télécharger plus de 300 ou 400 fiches d'excelabo (12 champs) à la fois. Si vous êtes dans cette situation, vous devrez fractionner les données à incure en plusieurs fois. Ceci est expliqué dans le classeur sur la page d'intro.

Utilisation du classeur excelphp

Avant de vous lancer dans la gestion de votre propre table, je vous propose de vous exercer avec une table très simple. Cette table, "contacts" associe simplement un nom un prénom et une adresse mail, sans aucune validation de données. A chaque nouvelle fiche, un numéro d'identification unique, auto-incrémenté, est associé. Celui ci n'est visible que sur le serveur.

Paramètres-première utilisation

Mettez le classeur fc-excelphp dans le dossier local de votre choix. Au lancement, une fenêtre s'ouvre avec plein de paramètres à renseigner. Rassurez vous, aux prochaines ouvertures, elle sera préremplie. En remplissant les différents champs faites attention aux majuscules/minuscules, au sens des slashs / et antislashs \, différents suivant qu'on est en local ou sur le serveur. Si vous voulez utiliser le dossier à la racine du serveur indiquez seulement un slash / pour ce répertoire. Indiquez "contacts" (sans les "") dans le champ nom de la table et le nom de votre base mysql dans le champ base.

Création de la table contacts

Pour créer la table, c'est très simple : allez sur la page bdd et cliquez sur le bouton créez la table !
Ouvrez l'interface phpmyadmin et vérifiez que vous avez dans votre base une nouvelle table nommée contacts.
Si ce n'est pas le cas, voir plus bas comment chercher l'erreur.

Ajout de fiches dans la table sur le serveur

Pour ajouter des fiches à la table "contacts" allez sur l'onglet "ajouts" et cliquez sur le bouton "nouvelle fiche". Remplissez les champs du userform. Quand vous avez terminé, fermez le userform et cliquez sur ajouter les fiches.
Des messages vous indiqueront où vous en êtes dans la procédure et comment celle ci se déroule. Normalement, en quelques secondes (sauf si vous uploadez 3000 fiches !) vos données devraient se retrouver chargées dans la table contacts sur le serveur distant. Pour vérifier que c'est bien le cas, ouvrez l'interface phpmyadmin. A côté du nom de votre table contacts, vous devez voir apparaître le nombre de fiches qu'elle contient (n'oubliez pas de rafraichir la page dans le navigateur pour voir les modifications).

Modification de fiches

Pour modifier une fiche existante, cliqez sur le bouton modifier dans la page modifs ou dans la page bdd.
Répétez l'opération autant de fois que nécessaire. Quand vous avez terminé, cliquez sur le bouton mise à jour des bases... Attention, la base dans cette configuration ultrasimple ne vérifie pas qu'une seule fiche correspond au critère sélectionné. Vous ne pouvez pas non plus changer le critère "nom" dans cette configuration d'exemple.

Suppression de fiches

Pour supprimer une fiche cliquez sur le bouton suppression dans la page bdd.

Vidange de la table et recharge

Si vous avez modifié des tas de trucs sur votre table locale, il est parfois plus simple de remplacer toutes les fiches sur le serveur. Il vous suffit pour cela de cliquer sur le bouton "remplacer tout le contenu de la table" sur la page bdd.

Attention cependant, si votre table est grande, vous pouvez avoir des problèmes liés au temps de travail autorisé sur votre serveur (voir sur la page introduction du classeur).

Résolution de problèmes éventuels

Si à l'une des étapes précédentes vous n'avez pas vu la table se créer ou le nombre de fiches se modifier, il va falloir reprendre les choses pas à pas pour voir ce qui coince.

Je vous conseille la démarche suivante :

En local : le fichier PHP

Vérifiez que dans votre dossier local, un fichier ***.php (*** varie suivant la fonction demandée) a bien été créé dans le même dossier que celui contenant le classeur excel. Si ce n'est pas le cas, vérifiez bien les paramètres que vous avez indiqué. Vous les trouvez sur la page codes. Les fichiesr php sont en fait de simples fichiers text que vous pouvez ouvrir avec notepad ou avec un éditeur html de votre choix (j'utilise pour ma part webexpert).

Vérifiez le contenu. Notamment faites attention aux instructions d'ouverture de la base. Etes vous sur du mot de passe ? de ne pas avoir mélangé le nom de la base, du user et de la table ? Vous devez lire ceci, avec les paramètres que vous avez indiqué (exemple avec une base chez free) :

<?

  $serveur="sql.free.fr";//nom du serveur
  $user="monsitefree";//votre nom utilisateur
  $password="*****";//votre mot de passe

  $base="monsitefree";//nom de la base de donnée
	
  $connexion = mysql_connect($serveur,$user,$password);
  $db = mysql_select_db($base, $connexion);

et ensuite, suivant le type d'opération demandées :

• Pour créer une table :
  

    $resultat = @mysql_query("DROP TABLE contacts"); // élimine une table qui porterait le même nom (sinon conflit !)
    $resultat = mysql_query("CREATE TABLE contacts (id INT (3) NOT NULL AUTO_INCREMENT, 
    	nom VARCHAR(25), prenom VARCHAR(25), mail VARCHAR(100), PRIMARY KEY(id))");
  

• Pour ajouter des données :
  

    $maj=mysql_query("insert into contacts values (' ', 'nom1', 'prenom1', 'mail1')");
    $maj=mysql_query("insert into contacts values (' ', 'nom2', 'prenom2', 'mail2')");
  ...
  
  

• Pour modifier des données :
  

    $maj=mysql_query("update contacts set nom='nom2', prenom='bidule', mail='machin' where nom like '%nom2%'");
  

• Pour supprimer des données
  

    $del=mysql_query("delete from contacts where nom like '%nom3%'");
  

?>

Faites TRES attention aux ponctuations. Les guillemets simples et doubles sont particulièrement casse-pieds à gérer dans le fichier excel. Vous verrez que j'ai beaucoup utilisé chr(34) pour le guillemet double pour éviter de d'emmêler les pinceaux. Quand vous modifierez l'exemple fourni, vérifiez tout particulièrement que le résultat est conforme à la syntaxe de php/mysql... N'oubliez pas non plus les ; en fin de ligne...

Transfert FTP

Vous devez voir le fichiers ***.php dans le répertoire distant indiqué dans vos paramètres. Si ce n'est pas le cas, vous avez probablement eu des messages pendant la procédure FTP vous signalant un problème.

• "connection internet impossible"... Vérifiez que vous êtes bien connecté ! Là ce n'est même pas une histoire de mot de passe ...
• "impossible de trouver le répertoire " : Soit vous n'avez pas créé sur le serveur de répertoire distant correspondant aux paramètres que vous avez indiqués, soit le chemin d'accès n'est pas bon. Parfois il faut un peu bidouiller avec les /. Faites attention à ne pas inverser / et \, essayez de télécharger à la racine du site, commencez le chemin du répertoire par un / ou pas.
• Si vous avez simplement un message vous indiquant que les fichiers n'ont pas pu être transférés, vérifiez que les fichiers existent bien en local et qu'ils sont bien dans le répertoire local spécifié. Faites le transfert avec un logiciel de FTP classique pour vérifier que tout se passe bien... Vérifiez encore une fois (si si !) vos paramètres. Chez free votre login de FTP c'est votre nom de base. Chez Online c'est de la forme xxx@mabase.net... Les logins de base mysql peuvent ou non être les mêmes suivant les hébergeurs, de même pour les mots de passe, il y a de quoi faire des erreurs !
• si rien de ceci ne marche, essayez de faire simplement fontionner le module php séparément avec un fichier test ou regardez d'autres solutions sur les sites de Christian ou de J@C.

Mise à jour de la base Mysql

Si vos fichiers sont corrects, et bien transférés mais que rien ne se passe sur la base mysql... Que faire ?
Commencez par ouvrir manuellement le fichier php par internet explorer. Si rien ne se passe alors vérifiez 3 fois que les paramètres de connection sont bons. Vous pouvez par exemple modifier ce fichier de la façon suivante :

<?php
  $bdd = mysql_connect('sql.free.fr','disciplus.simplex','monmotdepasse')or die ('Erreur : '.mysql_error() );
  mysql_select_db('disciplus.simplex',$bdd) or die ('Erreur : '.mysql_error() );
?>

En cas d'erreur vous aurez un message de Mysql qui vous guidera sur le type d'erreur.

Si tout est OK de ce côté, ouvez en local le fichier php, supprimez tout ce qui est avant le premier $truc=mysql_connect... faites commencer directement chaque ligne par l'instruction mysql du genre : INSERT into matable values... et jusqu'à la fin de la ligne. Supprimez en fin de ligne le dernier " mais gardez le ;

répétez cela pour chaque ligne (faites un test avec une ou deux lignes seulement cela suffit !). Enregistrez ce fichier sous un nom genre truc.sql et via phpmyadmin lisez ce fichier. Cette fois tout doit absolument se passer correctement. Sinon je rends mon tablier ! je n'ai aucune idée de ce qui coince et vous devrez vous y coller vous même ;-(.

Mais ne prenez pas peur si vous avez lu tout ceci avant d'avoir essayé, ce dernier long paragraphe est juste "au cas ou" ! normalement tout doit bien se passer dès la première utilisation.

Personnalisation du classeur

Une fois que vous avez réussi à faire fonctionner correctement la mise à jour de la table contacts, il est temps de faire un peu de personnalisation. Je vous conseille vivement de faire une copie du classeur qui fonctionne bien avec l'exemple, et de le garder au frigo quelque part ! De même ne vous empressez pas de détruire la table contacts sur votre base. Vous pouvez mettre de très nombreuses tables sur une même base, celle ci ne vous genera nullement pour créer la votre.

Vous pouvez supprimer (mettre en commentaire plutôt) les messages qui permettent de suivre les transferts (dans le module FTP).

Pour gérer votre propre table.

• Vous devez bien sur personnaliser les champs sur les pages bdd et fiches (ne changez pas les noms de ces feuilles ou alors changez aussi les noms correspondants dans les modules !!! risqué...).
• Il vous faut aussi ajuster la création du fichier php. Pour cela, dans les différents modules, recherchez les instructions correspondant aux noms des champs de l'exemple (nom, prénom et mail) et mettez les votres. Vous pouvez bien entendu en ajouter. Pensez à faire les modifications en conséquence dans le module de création de la table (à vous de savoir le type de chaque champ (INT, VARCHR, DATE...) et sa taille) et dans la macro PHP qui est dans le module DéclarationsFonctionsCommunes.
  Ajustez aussi les ref des plages utilisés pour exporter les données du classeur excel vers le fichier php. Attention à la déclaration des variables correspondant aux nouveaux champs.
• Vous n'avez aucune modification à apporter dans le module FTP.
• En revanche vous devrez personnaliser les différents formulaires (saisie, modification, suppression) pour qu'ils s'ajustent à votre table. Ce n'est pas franchement compliqué. Je vous conseille vivement d'incorporer dans le module de ces formulaires toutes les validations nécessaires, du genre vérifier que les champs sont bien tous remplis, qu'ils ne soient pas trop longs, qu'ils contiennent le bon type de données... Reportez vous au tutoriel sur le sujet en cas de besoin

Gestion des formats de date

J'ajoute souvent dans mes tables la date de création ou de modification de la fiche. Les formats de dates dans excel, c'est un vrai bonheur, largement partagé par Mysql, je ne vous rassure pas de ce côté ;-)

Pour vous éviter des tracas, je vous propose ceci :

Réservez une colonne pour la date.Formatez la colonne avec un format de date personnalisé de type aaaa-mm-jj puisque c'est celui utilisé par mysql. Celle ci n'est pas entrée dans le formulaire mais ajoutée au moment de la validation du formulaire. Ca donne quelque chose du genre :

Cells(num, 6).Value = Now()

Quand vous exportez ce champ dans le fichier php utilisez :

MaDate = Range("B" & i).Text (surtout pas value pour conserver le format !)

Sur la base mysql, dans la structure de la table le format du champ date est défini de la façon suivante :

dateajout varchar(10) NOT NULL default '',