Excel vers Mysql

Retour

Une co-production Florence CABON, POPI, Michel PIERRON et Christian HERBÉ
EXCEL communique avec MYSQL-WEB
Langages utilisés: VBA, PHP et SQL

OBJECTIF 1 Depuis Excel et d'un seul clic, insérer les données de la feuille active dans une base de données du web
OBJECTIF 2 depuis une page web, envoyer le résultat d'une requête Mysql directement dans Excel
OBJECTIF 3 Depuis Excel, faire une requête PHP-MYSQL dans une seule cellule

A voir aussi: Une approche sensiblement différente de cette problématique sur EXCELABO

OBJECTIF 1

Au départ, on a ce fichier Excel:

Partant de la feuille Excel, on veut insérer les lignes 2 et 3 dans cette table MYSQL, sur un serveur WEB:

Outils nécessaires côté micro:
    Excel97 ou supérieur version Windows (moyennant quelques modifications mineures, ça devrait tourner sur Mac)
    Internet Explorer (c'est mal, je sais)
    Client FTP (contenu dans Windows)

Outils nécessaires côté serveur:
    disposer des droits webmaster
    le celèbre tandem PHP MYSQL

Travaux à réaliser:
    écrire une macro VBA-Excel qui:
            sauvegarde la feuille Excel dans un fichier au format CSV (séparateur point-virgule)
            pilote le transfert du fichier CSV sur le serveur par FTP
            ouvre une page WEB contenant un script PHP afin d'insérer les données dans la base MYSQL

    écrire un fichier texte contenant les commandes FTP exécutées par le client FTP-DOS
    écrire le script PHP-MYSQL

Listing de la macro VBA (seules les lignes en bleu sont utiles, le vert est du commentaire):

Sub xl2php()
    'déclaration du nom du fichier csv
    nom_fichier = "tempo.csv"
    'déclaration du site à joindre
    site_web="http://www.site.fr/page_contenant_script.php"
    'déplacement vers le dossier temporaire
    ChDir Environ("TEMP")
    'copie des cellules contiguës à A1
    Range("A1").CurrentRegion.Copy
    'ouverture d'un nouveau document Excel
    Workbooks.Add
    '"collage" des données
    ActiveSheet.Paste
    'désactivation de la copie
    Application.CutCopyMode = False
    'désactivation des questions posées à l'utilisateur par Excel
    Application.DisplayAlerts = False
    'suppression des feuilles inutiles
    For i = 2 To Worksheets.Count
        Worksheets(Worksheets.Count).Delete
    Next i

    'sauvegarde du nouveau document au format CSV
    ActiveWorkbook.SaveAs Filename:=nom_fichier, FileFormat:=xlCSV, CreateBackup:=False
    'fermeture du document sauvegardé
    ActiveWindow.Close

    'exécution du programme FTP.EXE fourni par Windows
    'notez l'option -S: suivie d'un nom de fichier; celui-ci contient les commandes FTP
    Shell ("ftp -i -n -v -s:comm_ftp.txt")

    'déclaration de variable
    Dim IE As Object
    'instenciation de Internet Explorer
    Set IE = CreateObject("internetexplorer.application")
    'lancement d'Internet Explorer(IE) avec ouverture de la page web contenant le scipt PHP
    IE.Navigate (site_web)
    'mise en attente de la macro pendant le chargement d'IE et l'exécution du script PHP
    'VBA étant asynchrone, on est obligé de recourir à cet artifice pour ne pas exécuter l'instruction suivante
    ' avant la fin du démarrage de IE
    Do While IE.readystate <> 4
    Loop

'fermeture de IE
    IE.Quit
End Sub

Liste des commandes FTP à stocker dans le fichier %TEMP%\comm_ftp.txt
       cd %TEMP%
       open site_web.fr
        user     mon_login
       mon_mot_de_passe
       put tempo.csv
       bye
commentaires de ces commandes
CD = change directory ;
%TEMP% est une variable sytème de Windows contenant le chemin complet du dossier temporaire par défaut
OPEN = ouverture de la connexion au site FTP
USER = votre login (nom) sur le serveur
PUT = transfert de la machine locale vers le serveur
BYE ou BY fin du programme FTP

Listing du script PHP contenu dans la page WEB :
<?
    $i=0;
    // ouverture du fichier en mode lecture (r = ready)
    $fichier=fopen("tempo.csv","r+");
    // lecture ligne par ligne tant qu'on ne rencontre pas le code: fin de fichier(feof)
    while(!feof($fichier))
        {
            // l'instruction explode() sépare la ligne en autant de valeurs que de point-virgules rencontrés
            // fgets() prend les valeurs dans le fichier et lit 128 caractères par ligne (à adapter)
            // $tableau[] est une variable tableau ou array qui va recevoir ce qui est lu dans le fichier
            $tableau[$i]=explode(";",fgets($fichier,128));
            $i++;
        }
    // fermeture du fichier
    fclose($fichier);
    $nblignes=$i;

    //ouverture de la Base de données ; à adapter
    $bdd = mysql_connect('sql.free.fr','login','mot de passe');
    mysql_select_db('nom de la base de données',$bdd);

    // les valeurs lues dans le fichier sont insérées dans la BDD ligne par ligne
    // on va "boucler"(for = tant que ...) autant de fois que de lignes trouvées dans le fichier
    // notez que $i commence à 1 et non à zéro afin de ne pas insérer la 1ere ligne du fichier
    // pour chaque ligne, on stocke le contenu de chaque champs dans les variables $V
    // il est en effet impossible de lire une variable tableau[ligne][colonne] à l'intérieur d'une instruction
    // mysql_query
    for ($i=1;$i<$nblignes;$i++)
         {
             $v1=$tableau[$i][0];
             $v2=$tableau[$i][1];
             $v3=$tableau[$i][2];
             mysql_query("INSERT INTO nom_de_la_table VALUES ('$v1', '$v2', '$v3')");
         }

    // fermeture de la base de données
   mysql_close($bdd);

OBJECTIF 2 :
depuis une page web, envoyer le résultat d'une requête Mysql directement dans Excel (non testé sur Mac)

En fait,tout tient dans une seule ligne de code à placer OBLIGATOIREMENT en tête du script PHP. On donne ici un script complet ; il est seulement destiné à illustrer le fonctionnement de cette technique.

<?
    // à elle seule, la ligne suivante suffit à envoyer le résultat du script dans une feuille Excel
    header("Content-type: application/vnd.ms-excel");
    // la ligne suivante est facultative, elle sert à donner un nom au fichier Excel
   header("Content-Disposition: attachment; filename=toto.xls");

   // La suite est une simple requête php-mysql. On interroge la table utilisée dans l'exemple précédent.
   $bdd = mysql_connect('sql.free.fr','login','mot de passe');
   mysql_select_db('nom de la base de données',$bdd);
   // notez la présence du caractère arobase (@) , en cas d'erreur,
// il empêche PHP d'écrire un message d'erreur sur le navigateur
    $requete=@mysql_query("SELECT * FROM nom_de_la_table");
    // on vérifie le contenu de la requête ;
    if (@mysql_numrows($requete) ==0)
        {   // si elle est vide, on en informe l'utilisateur à l'aide d'un Javascript
            print "<script> alert('La requête n\'a pas abouti !')</script>";
        }

   // construction du tableau HTML
print '<table border=1>
            
             <TR><TD>Eleves</TD><TD>Math</TD><TD>Francais</TD></TR><TR>';

    // lecture du contenu de la requête avec 2 boucles imbriquées; par ligne et par colonne
    for ($ligne=0 ; $ligne<@mysql_numrows($requete);$ligne++)
     {
         for ($colonne = 0;$colonne < 3 ; $colonne++)
              {
                 print '<TD>' .mysql_result($requete , $ligne,$colonne). '</TD>';
              }
       print '</TR>';
    }
    print '</TABLE>';
    mysql_close();

// on informe l'utilisateur de la réussite
   if (@mysql_numrows($requete) >0)
        {
            print "<script> alert('La table est bien mise à jour !')</script>";
        }
?>

Un autre intérêt de cette technique réside dans le fait que les balises HTML sont respectées par Excel.
L'on aura remarqué la syntaxe de la balise <table border=1> ; si border est égal à zéro, la feuille Excel est nue, sans quadrillage. Avec border = 1, le quadrillage est visible. Si border est supérieur à 1, le quadrillage est plus épais.
Mais le plus important, c'est l'interprétation des valeurs contenues dans le tableau, notamment les nombres, y compris quand ils ont un format monétaire comprenant un espace. Ils sont vus comme tels alors que ce n'est pas le cas lorsqu'on ouvre une page directement; exemple:
fichier | ouvrir | http://adresse.fr/fichier.html
Dans ce cas, on obtient le contenu de la page web directement dans la feuille Excel mais les nombres sont lus comme des chaînes de caractères, ce qui interdit de les utiliser dans les calculs ultérieurs (on corrige cela par un copier-collage spécial mais c'est fastidieux!).

OBJECTIF 3
Faire une requête PHP-MYSQL depuis une cellule de la feuille Excel

Soit la table suivante nommée "xl2mysql2xl" sur le serveur FREE:
table Mysql

L'utilisateur Excel veut insérer dans une cellule la note de math de l'élève TOTO. Il faut que le webmestre crée le script PHP-MYSQL suivant:

<?
    $bdd = mysql_connect('sql.free.fr','login','password');
   mysql_select_db('nom de la base de données',$bdd);
// la requête sélectionne un élève en fonction de son nom contenu dans la variable $nom
    $requete=@mysql_query("SELECT * FROM xl2mysql2xl WHERE eleves = '$nom' ");
    // pour cet exemple, on affiche la note de math (2e valeur sur la ligne soit 0,1 puisque les index commencent à zéro
     print mysql_result($requete , 0,1);
   mysql_close();
?>

Maintenant que le script existe sur le serveur, on peut intéroger la base de données depuis une cellule Excel:
Depuis Excel2000:
       Menu Données
       Données externes
       Nouvelle requête sur le web

dans l'espace "parcourir le web", entrer l'url suivie par ?nom=toto ex:
http://cherbe.free.fr/divers/req_xl_cellule.php?nom=toto
En effet, cette syntaxe permet de passer une variable au script PHP. C'est cette variable qui sera utilisée dans la requête MYSQL
On pourrait ajouter d'autres variables, dans l'URL afin de choisir l'affichage d'un autre champ de la table.

ATTENTION: cette technique envoie le nom et le contenu des variables en clair dans l'adresse; elles peuvent être interceptées par des personnes mal intentionnées. A éviter en ce qui concerne les données confidentielles ou les bases de données sensibles.

Cette page a été vue 89493 fois depuis sa création en décembre 2003