Archives / Articles

~ avertissement ~

De MySQL à Excel via PHP et SYLK

Parution : Septembre 2001 | Auteur : J-Pierre Dézélus | 7 commentaires

Pourquoi le format SYLK ?

Spécifications du format SYLK

Structure d'un document SYLK

Exemple de script PHP

Conclusion

Pourquoi le format SYLK ?

Lorsque l'on veut récupérer des données MySQL sous Excel, deux méthodes rapides s'offrent généralement à nous.

La première nécessite que le serveur Web tourne sur une plateforme Win32. Elle s'appuie sur les objets COM, pas toujours faciles à utiliser comme nous le montre l'article d'Arnaud Limbourg : Objets COM en PHP, une première approche.

La seconde, qui ne dépend pas de la plateforme, s'appuie sur le format CSV. Les fichiers générés sont de type texte, et reconnus par de nombreux tableurs et applications de bases de données.
Mais ce format ne permet que la récupération de donnée brutes, sans aucune mise en forme.

Il existe une troisième méthode, méconnue des développeurs, qui s'appuie elle sur le format SYLK (pour SYmbolic LinK).
J'ai rencontré plusieurs fois ce format de fichier dans des applications métier, lorsqu'il était nécessaire par exemple de pouvoir récupérer sous Excel des informations issues de la base de données de l'entreprise stockée sur gros système.
Je l'ai donc étudié, et je lui ai découvert de nombreux avantages :

les fichiers sont de type texte comme pour CSV,
les données peuvent être mises en forme (alignement, choix et tailles des polices, styles, ...),
c'est un format reconnu par la majorité des tableurs (dont Excel),
les scripts php utilisés pour générer des fichiers SYLK sont indépendants de l'OS du serveur Web.

Je me propose de vous d'écrire ici comment mettre en oeuvre ce format.

Spécifications du format SYLK

Les informations concernant ce format sont plutôt rares. Issu de Multiplan (donc Microsoft il me semble), le format SYLK a été imaginé pour permettre l'échange de données inter-applications et inter-plateformes.

Pour développer mon script je me suis appuyé sur 2 spécifications trouvées sur le net.
La 1ère sur Wotsit, un site incontournable lorsque l'on souhaite étudier un format de fichier particulier (Cf. sylk.zip).
La seconde est ici. Il s'agit document provenant de Microsoft et disponible sur le site de Russell Schulz (Cf. sylksum.doc).

J'ai aussi fait de nombreux tests sous Excel, en enregistrant des petits fichiers pour étudier comment les informations étaient mises en forme au format SYLK.
Attention Microsoft a largement étendu ce format dans les dernières versions d'Excel... Limitez-vous aux éléments décrits dans les 2 documents cités ci-dessus.

Structure d'un document SYLK

Un fichier SYLK se décompose globalement en 4 parties :

l'en-tête où l'on définit : les formats de données manipulés (entiers, réels, textes), les polices (noms, tailles et attributs), et enfin le nombre de lignes et de colonnes que contiendra le document
vient ensuite une section où l'on indique la largeur (je ne sais pas en quelle unite !?) des différentes colonnes.
puis suivent les données utiles, où pour chaque cellule nous avons 2 lignes de texte : 1 pour le format, 1 pour la donnée.
et pour terminer un délimiteur de fin de fichier.

Voici un exemple de fichier SYLK, avec 1 seule ligne de données (les lignes de pointillés sont juste là pour vous montrer les différentes parties du fichiers) :



ID;PExemple



P;PGeneral

P;P#,##0.00

P;P#,##0

P;P@



P;EArial;M200

P;EArial;M200

P;EArial;M200

P;FArial;M200;SB



B;Y1;X4

..............................................

F;W1 1 5

F;W2 2 20

F;W3 3 70

F;W4 4 20

F;W5 256 8

..............................................

F;SDM4;FG0C;Y1;X1

C;N;K"N°"

F;SDM4;FG0C;X2

C;N;K"Rubrique"

F;SDM4;FG0C;X3

C;N;K"Astuce"

F;SDM4;FG0C;X4

C;N;K"Auteur"



F;P2;FF0R;Y2;X1

C;N;K38

F;P3;FG0L;X2

C;N;K"Chaînes"

F;P3;FG0L;X3

C;N;K"Accéder à chaque caractère d'une chaîne"

F;P3;FG0C;X4

C;N;K""

..............................................

E

Vous pouvez le télécharger : exemple.slk.

Exemple de script PHP

Pour vous montrer comment utiliser le format SYLK, je vous propose de récupérer dans un fichier le contenu de la table Astuces de phpInfo.net. Enfin une petite partie des informations !!

Voici le script PHP suffisamment commenté je l'espère :


<?php

// ----------------------------------------------------------------------------

define("FORMAT_REEL",   1); // #,##0.00
define("FORMAT_ENTIER", 2); // #,##0
define("FORMAT_TEXTE",  3); // @

$cfg_formats[FORMAT_ENTIER] = "FF0";
$cfg_formats[FORMAT_REEL]   = "FF2";
$cfg_formats[FORMAT_TEXTE]  = "FG0";

// ----------------------------------------------------------------------------

$cfg_hote = 'localhost';
$cfg_user = 'root';
$cfg_pass = '';
$cfg_base = 'mabase';

// ----------------------------------------------------------------------------

if (mysql_connect($cfg_hote, $cfg_user, $cfg_pass))
{
    // construction de la requête
    // ------------------------------------------------------------------------
    $sql  = "SELECT code, rubrique, titre, auteur ";
    $sql .= "FROM astuces ";
    $sql .= "WHERE theme=1 "; // PHP
    $sql .= "ORDER BY rubrique, titre";

    // définition des différentes colonnes de données
    // ------------------------------------------------------------------------
    $champs = Array(
      //     champ       en-tête     format         alignement  largeur
      Array( 'code',     'N°',       FORMAT_ENTIER, 'R',         5 ),
      Array( 'rubrique', 'Rubrique', FORMAT_TEXTE,  'L',        20 ),
      Array( 'titre',    'Astuce',   FORMAT_TEXTE,  'L',        70 ),
      Array( 'auteur',   'Auteur',   FORMAT_TEXTE,  'C',        20 )
    );
    // ------------------------------------------------------------------------


    if ($resultat = mysql_db_query($cfg_base, $sql))
    {
        // en-tête HTTP
        // --------------------------------------------------------------------
        header('Content-disposition: filename=astuces.slk');
        header('Content-type: application/octetstream');
        header('Pragma: no-cache');
        header('Expires: 0');

        // en-tête du fichier SYLK
        // --------------------------------------------------------------------
        echo "ID;PASTUCES-phpInfo.net\n"; // ID;Pappli
        echo "\n";
        // formats
        echo "P;PGeneral\n";      
        echo "P;P#,##0.00\n";       // P;Pformat_1 (reels)
        echo "P;P#,##0\n";          // P;Pformat_2 (entiers)
        echo "P;P@\n";              // P;Pformat_3 (textes)
        echo "\n";
        // polices
        echo "P;EArial;M200\n";
        echo "P;EArial;M200\n";
        echo "P;EArial;M200\n";
        echo "P;FArial;M200;SB\n";
        echo "\n";
        // nb lignes * nb colonnes :  B;Yligmax;Xcolmax
        echo "B;Y".(mysql_num_rows($resultat)+1);
        echo ";X".($nbcol = mysql_num_fields($resultat))."\n";
        echo "\n";

        // récupération des infos de formatage des colonnes
        // --------------------------------------------------------------------
        for ($cpt = 0; $cpt < $nbcol; $cpt++)
        {
            $num_format[$cpt] = $champs[$cpt][2];
            $format[$cpt] = $cfg_formats[$num_format[$cpt]].$champs[$cpt][3];
        }

        // largeurs des colonnes
        // --------------------------------------------------------------------
        for ($cpt = 1; $cpt <= $nbcol; $cpt++)
        {
            // F;Wcoldeb colfin largeur
            echo "F;W".$cpt." ".$cpt." ".$champs[$cpt-1][4]."\n";
        }
        echo "F;W".$cpt." 256 8\n"; // F;Wcoldeb colfin largeur
        echo "\n";

        // en-tête des colonnes (en gras --> SDM4)
        // --------------------------------------------------------------------
        for ($cpt = 1; $cpt <= $nbcol; $cpt++)
        {
            echo "F;SDM4;FG0C;".($cpt == 1 ? "Y1;" : "")."X".$cpt."\n";
            echo "C;N;K\"".$champs[$cpt-1][1]."\"\n";
        }
        echo "\n";

        // données utiles
        // --------------------------------------------------------------------
        $ligne = 2;
        while ($enr = mysql_fetch_array($resultat))
        {
            // parcours des champs
            for ($cpt = 0; $cpt < $nbcol; $cpt++)
            {
                // format
                echo "F;P".$num_format[$cpt].";".$format[$cpt];
                echo ($cpt == 0 ? ";Y".$ligne : "").";X".($cpt+1)."\n";
                // valeur
                if ($num_format[$cpt] == FORMAT_TEXTE)
                    echo "C;N;K\"".str_replace(';', ';;', $enr[$cpt])."\"\n";
                else
                    echo "C;N;K".$enr[$cpt]."\n";
            }
            echo "\n";
            $ligne++;
        }

        // fin du fichier
        // --------------------------------------------------------------------
        echo "E\n";
    }
    
    mysql_close();
}

?>

» Voir le résultat : astuces.slk
» Télécharger les sources

Conclusion

Il ne vous reste plus qu'à explorer toutes les possibilités de ce format SYLK, pas forcément très abordable mais pourtant très pratique.
N'hésitez pas à me/nous faire part de vos découvertes, cela pourra peut-être faire l'objet d'une seconde partie.

Commentaires

demande infos

Posté par Florent Brusciano | Jeudi 4 Octobre 2001 à 11:20

je trouve le script très bien, mais je manque d'infos.
il semble que le format text n'accepte que 255 charactères.
Comment faire pour obtenir des champs plus long?
encore merci.

Re: demande infos

Posté par J-Pierre Dézélus

| Jeudi 4 Octobre 2001 à 16:10

Après plusieurs tests, je confirme que le format SYLK ne gère que des textes de 255c maxi.

Quand je pense que j'avais tout fait à la main...

Posté par Fabrice BOYER

| Mercredi 14 Novembre 2001 à 19:24

Votre travail est très sympa, même si j'avais déjà eu l'occasion de le faire à la main, pour générer en PHP des feuilles de calculs lisibles sous Excel sans employer ce format binaire indigeste qu'est le XLS.

Je crois que du coup je vais revoir certains points, en particulier pour l'en-tête : le mien fait une trentaine de ligne pour pas grand chose...

En espérant pouvoir contribuer à mon tour...

Petite contribution...

Posté par Fabrice BOYER

| Jeudi 15 Novembre 2001 à 01:42

Je constate dans le script proposé que vous employez le type MIME "application/octetstream" pour l'envoi de votre fichier SYLK.
Personnellement, je me sers du type "application/vnd.ms-excel", ce qui permet de l'ouvrir directement depuis son navigateur -- en tout cas sous MS IE 5 -- ou de le télécharger pour
une consultation ultérieure avec Excel. Il est vrai cependant que si on ne précise pas le nom souhaité pour l'enregistrement, le navigateur propose un "*.xls".

J'ai, moi aussi, fait quelques bidouilles pour déchiffrer le SYLK et en particulier, ce n'est
pas la peine de définir trois styles de texte identiques ! Effectivement, les lignes :
P;EArial;M200
P;FArial;M200;SB
sont des définitions de styles que l'on peut utiliser ensuite de la façon suivante :
F;SDM1;Y1;X1
C;K"Valeur"
Dans le cas précédent, je me sers donc du style "Arial, 10pt, gras" défini plus haut.
Note : on commence à compter les styles depuis le numéro 0.
Cependant, il me semble que ces RTD sont proriétaires :-( alors on n'hésitera pas à doubler
la mise en forme, ce que fait en fait l'option "D" de ";SDM1" qui elle aussi "met du gras".
Si quelqu'un sait le sens des "E" et "F" dans les définitions de style, je lui serai
reconnaissant ;-)
Note : mes notes de bidouille sont disponibles à l'adresse suivante :
http://www.fabruccio.fr.fm/doc/Journal.sylk.txt
je les ai écrites après avoir lu cet article, aussi elles s'en inspirent beaucoup.

P.S. : Pour répondre à votre interrogation concernant l'unité de mesure employée pour la
largeur des colonnes, il s'agit simplement de la largeur d'un caractères dans la police par
défaut. Ce qui garantit une dépendance forte entre la largeur d'une cellule et le texte
qu'elle contient : on ne verra jamais des "######" dans une cellule -- sous Excel -- si il n'y en avait pas à la création du fichier SYLK :-)

Si ca peux aider :

Posté par Loic Pisto

| Mercredi 27 Février 2002 à 22:52

J'ai realiser a partir de ces travaux une class sylk et une fonction pour utiliser assez simplement ce format.

Ca devrait vous faciliter son utilisation et cela se trouve la http://perso.club-internet.fr/loic.duvernay/sylk.php3

Solution plus simple d'interaction MySQL<>Excel

Posté par Alexis Soulard

| Mercredi 17 Juillet 2002 à 12:46

Peut être me trompe-je, mais il me semble que passer par une connexion ODBC pour récupérer les données d'une base MySQL dans Excel est nettement plus simple si le serveur distant accepte un host extérieur :o)
Pour les novices il suffit d'abord de télécharger MyODBC sur le site officiel de MySQL : http://www.mysql.com, puis de créer une connexion vers la base et la table souhaitée, et le plus dur est terminé.

Re: Solution plus simple d'interaction MySQL<>Exce

Posté par Alexis Soulard | Mercredi 17 Juillet 2002 à 12:52

J'ai posté mon commentaire avant de l'avoir achevé, une fois la connexion ODBC créée, il suffit dans Excel de choisir "Données"->"Données Externes"->"Créer une requête", de choisir la connexion ODBC créée précédemment et d'y préciser l'ensemble des filtres souhaités.

Cette page fait partie des Archives de phpinfo.net (période 2000-2002).

Les liens externes, mais aussi internes, n'ont pas été maintenus.

D'autre part, gardez à l'esprit que les informations proposées ont pu perdre de leur pertinence, compte tenu de l'évolution rapide de PHP depuis leur 1ère mise en ligne.