Spreadsheet_Excel_Writer en PHP

Cette librairie est donc un portage de la classe Perl, vous pouvez la télécharger ici. La dernière mise à jour date d'avril 2002. Cette classe est déjà relativement complète, elle s'inspire directement de la classe Perl, jusqu'à la convention de nommage. Pour la documentation Johann Hanne renvoie à la doc officielle de Perl (en anglais uniquement). Malheureusement toutes les méthodes ne sont pas implémentées et il faut se contenter des exemples mis sur son site pour s'inspirer et générer un fichier Excel.

Cette librairie est également un portage de la classe Perl, vous pouvez la télécharger ici. La dernière version bêta date de septembre 2006, celle-ci semble plus complète. Une documentation multilingue se trouve sur le site de PEAR. La différence se joue surtout au niveau de la convention de nommage qui ne reprend pas de Perl, en fait elle supprime l'underscore (_), dans le nom de la méthode. La documentation ne fait pas référence à toutes les méthodes, en effet dans le package Perl on trouve de nombreux exemples, que l'on peut s'amuser à faire en PHP (vous en trouverez d'ailleurs pas mal dans les sources), et en réalisant quelques portages on se rend compte que certaines méthodes fonctionnent, alors qu'elles ne sont pas dans la documentation (la méthode sheet() notamment).

Pour réaliser nos fichiers Excel, nous aurons également besoin de la librairie OLE de PEAR. Tandis que Johann Hanne l'inclut directement dans sa librairie, pour utiliser la librairie de Xavier Noguer il faudra la télécharger sur le site de PEAR

III-A-1. La structure▲

class Spreadsheet_Excel_Writer extends Spreadsheet_Excel_Writer_Workbook
{
    /**
    * The constructor. It just creates a Workbook
    *
    * @param string $filename The optional filename for the Workbook, $filename create tempory file.
    * @return Spreadsheet_Excel_Writer_Workbook The Workbook created
    */
    function Spreadsheet_Excel_Writer($filename='tmp.xls')
    {
        
        $this->Spreadsheet_Excel_Writer_Workbook($filename);
    }

    /**
    * Send HTTP headers for the Excel file.
    *
    * @param string $filename The filename to use for HTTP headers
    * @access public
    */
    function send($filename)
    {
        header("Content-type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=\"$filename\"");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
        header("Pragma: public");
    }

    /**
    * Utility function for writing formulas
    * Converts a cell's coordinates to the A1 format.
    *
    * @access public
    * @static
    * @param integer $row Row for the cell to convert (0-indexed).
    * @param integer $col Column for the cell to convert (0-indexed).
    * @return string The cell identifier in A1 format
    */
    function rowcolToCell($row, $col)
    {
        if ($col > 255) { //maximum column value exceeded
            return new PEAR_Error("Maximum column value exceeded: $col");
        }

        $int = (int)($col / 26);
        $frac = $col % 26;
        $chr1 = '';

        if ($int > 0) {
            $chr1 = chr(ord('A') + $int - 1);
        }

        $chr2 = chr(ord('A') + $frac);
        $row++;

        return $chr1 . $chr2 . $row;
    }

    
}

class Spreadsheet_Excel_Writer extends Spreadsheet_Excel_Writer_Workbook
{
    private $_filename;
    /**
    * The constructor. It just creates a Workbook
    *
    * @param string $filename The optional filename for the Workbook, $filename create tempory file.
    * @return Spreadsheet_Excel_Writer_Workbook The Workbook created
    */
    function Spreadsheet_Excel_Writer($filename = 'test.xls')
    {
        $this->Spreadsheet_Excel_Writer_Workbook($filename);
    }

    /**
    * Send HTTP headers for the Excel file.
    *
    * @param string $filename The filename to use for HTTP headers
    * @access public
    */
    function send($filename='fichier.xls')
    {
        header("Content-type: application/vnd.ms-excel");
        header("Content-Disposition: attachment; filename=\"$filename\"");
        header("Expires: 0");
        header("Cache-Control: must-revalidate, post-check=0,pre-check=0");
        header("Pragma: public");
    }

    /**
    * Utility function for writing formulas
    * Converts a cell's coordinates to the A1 format.
    *
    * @access public
    * @static
    * @param integer $row Row for the cell to convert (0-indexed).
    * @param integer $col Column for the cell to convert (0-indexed).
    * @return string The cell identifier in A1 format
    */
    function rowcolToCell($row, $col)
    {
        if ($col > 255) { //maximum column value exceeded
            return new PEAR_Error("Maximum column value exceeded: $col");
        }

        $int = (int)($col / 26);
        $frac = $col % 26;
        $chr1 = '';

        if ($int > 0) {
            $chr1 = chr(ord('A') + $int - 1);
        }

        $chr2 = chr(ord('A') + $frac);
        $row++;

        return $chr1 . $chr2 . $row;
    }

    /**
     * Read file create tempory file
     * @access  public
     *
     */
     function sendFile()
     {
         readfile($this->_filename);
         unlink($this->_filename);
     }
}

    function sendFile()
    {
        readfile($this->_filename);
        unlink($this->_filename);
    }

Exemple ici

set_time_limit(300);

require_once 'Spreadsheet/Excel/Writer.php';

$workbook = new Spreadsheet_Excel_Writer();
$workbook->setTempDir('./tempdoc');
$workbook->send('base.xls');
$worksheet = $workbook->addWorksheet();

$worksheet->write(1,2,'toto');


$workbook->close();
$workbook->sendFile();

Examinons ce fichier ligne à ligne.

set_time_limit(300);

Set_time_limit(), permet de dépasser le temps d'attente qui généralement est limité à 30 secondes, ce qui peut être nécessaire pour le fichier suivant par exemple, et qu'on augmente la boucle dans de plus grandes proportions. Malheureusement, pour des raisons de sécurité la fonction est désactivée chez Developpez.com, je vais donc la supprimer de mes exemples. Heureusement, très peu de scripts ont besoin de Set_time_limit().

require_once 'Spreadsheet/Excel/Writer.php';

On inclut le fichier de la classe Writer, pour pouvoir instancier un objet Spreadsheet_Excel_Writer().

Cette ligne est inutile dans un projet Zend Framework si vous utilisez Zend_Loader::registerAutoload().

$workbook = new Spreadsheet_Excel_Writer();

Nous créons une instance de notre classe, nous pouvons si nous le voulons lui passer en paramètre le nom d'un fichier temporaire.

$workbook->setTempDir('./tempdoc');

Définissons le dossier temporaire à utiliser pour le fichier OLE. Utilisez cette méthode si vous n'avez pas le droit d'écrire dans le dossier temporaire par défaut.(1)Pour des raisons de sécurité Developpez.com, n'autorise pas d'écrire dans le répertoire temporaire. Renseignez-vous pour savoir si vous devez utiliser cette méthode

$workbook->send('base.xls');

Nous envoyons les entêtes qui permettent de dire aux navigateurs que nous voulons un fichier Excel, ici également vous pouvez passer en paramètre le nom que votre fichier portera.

Si nous arrêtions notre script à ce stade nous n'aurions qu'une application Excel, qui s'ouvrirait complètement vide.

$worksheet = $workbook->addWorksheet();

Ici, nous créons notre première Feuille(sheet), si vous désirez donner un nom à l'onglet de la feuille, vous pouvez lui passer ce nom en paramètre addWorksheet('Feuille1').

Pour plus de détails sur les Feuilles, reportez-vous au chapitre 3.

$worksheet->write(1,2,'toto');

Ici nous écrivons dans une cellule.

La méthode write prend trois paramètres minimums :

• La ligne du document Excel (cela commence à zéro) ;
• La colonne du document Excel (cela commence à zéro) ;
• Ce que l'on veut mettre dans la cellule (texte, formule, etc.).

Dans le cas ici, nous afficherons donc dans la cellule 2C, le mot : 'toto' Voici de quoi vous faciliter la navigation dans les cellules.

Pour plus de détails sur les cellules, reportez-vous au chapitre 4.

$workbook->close();

Nous fermons l'objet.

$workbook->sendFile();

Nous envoyons la lecture de notre fichier.

Nous venons de réaliser notre premier fichier Excel.

<?php
require_once "Spreadsheet/Excel/Writer.php";
$workbook = new Spreadsheet_Excel_Writer();
$workbook->send('format.xls');
$format = $workbook->addFormat(
    array(
    'Size' => 10,//taille du texte
       'Align' => 'center',//alignement du texte
       'Color' => 'white',//couleur du texte
       'FgColor' => 'magenta'));//couleur du fond de cellule
$worksheet = $workbook->addWorksheet();
$worksheet->write(1, 0, "Bonjour en Magenta !", $format);
$workbook->close();
$workbook->sendFile();
?>

Cet exemple provient directement de la doc PEAR, cela va nous créer un fichier Excel avec une seule cellule écrite avec une taille 10 en blanc sous fond magenta. Concrètement, nous créons une nouvelle variable $format dans laquelle nous appelons la méthode « addformat » et lui passons un tableau de paramètres.

Cette variable sera passée en 4e arguments de la méthode write.

Définition d'une cellule formatée :

$format = $workbook->addFormat(
    array(
     'Size' => 10,//taille du texte
     'Align' => 'center',//alignement du texte
     'Color' => 'white',//couleur du texte
     'FgColor' => 'magenta'));//couleur du fond de cellule

Nous passons la variable en argument :

$worksheet->write(1, 0, "Bonjour en Magenta !", $format);

Voici les paramètres possibles :

• Align : définit l'alignement de la cellule ;
• VAlign : définit l'alignement de la cellule ;
• HAlign : définit l'alignement de la cellule ;
• Merge : cette méthode est un alias de la méthode non intuitive setAlign('merge') ;
• Bold : définit l'intensité de la mise en gras d'un texte ;
• Bottom : définit l'épaisseur de la bordure inférieure de la cellule ;
• Top : définit l'épaisseur de la bordure supérieure de la cellule ;
• Left : définit l'épaisseur de la bordure gauche de la cellule ;
• Right : définit l'épaisseur de la bordure droite de la cellule ;
• Border : définit les bordures d'une cellule dans un même style ;
• BorderColor : définit toutes les bordures de la cellule à une même couleur ;
• BottomColor : définit la couleur de la bordure inférieure de la cellule ;
• TopColor : définit la couleur de la bordure supérieure de la cellule ;
• LeftColor : définit la couleur de la bordure gauche de la cellule ;
• RightColor : définit la couleur de la bordure droite de la cellule ;
• FgColor : définit la couleur de premier plan de la cellule ;
• BgColor : définit la couleur d'arrière-plan de la cellule ;
• Color : définit la couleur pour le contenu de la cellule ;
• Pattern : définit les attributs d'une cellule ;
• Underline : définit le soulignement du texte ;
• Italic : définit le style de la police de caractères en italique ;
• Size : définit la taille de la police de caractères ;
• TextWrap : définit le retour automatique à la ligne du texte ;
• TextRotation : définit l'orientation du texte ;
• NumFormat : définit le format numérique ;
• StrikeOut : définit la police de caractères comme barrée ;
• OutLine : définit le dessin d'une police de caractères ;
• Shadow : définit la police de caractères comme ombrée ;
• Script : définit le type de script pour le texte ;
• FontFamily : définit la famille de police de caractères.

Nous pouvons également combiner les formats en créant des arrays et en utilisant « array_merge(); ».

Exemple ici.

<?php
require_once "Spreadsheet/Excel/Writer.php";
$workbook = new Spreadsheet_Excel_Writer();
$workbook->send('format.xls');
$couleur = array(    
       Color => 'white',//couleur du texte
       FgColor => 'magenta');//couleur du fond de cellule
$taille = array(
    Size => 10,//taille du texte
       Align => 'center');//alignement du texte

$Merge = array_merge($couleur,$taille);

$format1 = $workbook->addformat($taille);
$format2 = $workbook->addformat($couleur);
$formatMerge = $workbook->addformat($Merge);
$worksheet = $workbook->addWorksheet();
$worksheet->write(1, 0, "format 1", $format1);
$worksheet->write(2, 0, "format 2", $format2);
$worksheet->write(3, 0, "format merge ", $formatMerge);
$workbook->close();
$workbook->sendFile();
?>

Mais le plus simple dans le format de cellule est d'utiliser son setter, en effet chaque propriété peut être appelée séparément et ajoutée à « addformat() ».

<?php
require_once "Spreadsheet/Excel/Writer.php";
$workbook = new Spreadsheet_Excel_Writer();
$workbook->send('format.xls');

$worksheet = $workbook->addWorksheet();

$format= $workbook->addformat();
$format->setTextRotation(90);
$worksheet->write(1, 0, "Maitre", $format);

$format1= $workbook->addformat();
$format1->setTextRotation(270);
$worksheet->write(2, 0, "Pylos", $format1);


$format2 = $workbook->addformat();
$format2->setTextRotation(-1);
$format2->setFgColor(11);

$worksheet->write(2, 2, "MaitrePylos", $format2);


$workbook->close();
$workbook->sendFile();
?>

Exemple

Un fichier Excel sans formule mathématique ne serait pas un fichier Excel.

Il suffit de noter « =SUM(A1,B2) », par exemple pour avoir l'addition.

require_once 'Spreadsheet/Excel/Writer.php';


$workbook = new Spreadsheet_Excel_Writer();
$workbook->send('stats.xls');
$worksheet = $workbook->addWorksheet();
# Set the column width for columns 1
$worksheet->setColumn(0, 0, 20);


# Create a format for the headings
$format = $workbook->addFormat();
$format->setBold();


# Write the sample data
$worksheet->write(0, 0, 'Sample', $format);
$worksheet->write(1, 0, 1);
$worksheet->write(2, 0, 2);
$worksheet->write(3, 0, 3);
$worksheet->write(4, 0, 4);
$worksheet->write(5, 0, 5);
$worksheet->write(6, 0, 6);
$worksheet->write(7, 0, 7);
$worksheet->write(8, 0, 8);

$worksheet->write(0, 1, 'Length', $format);
$worksheet->write(1, 1, 25.4);
$worksheet->write(2, 1, 25.4);
$worksheet->write(3, 1, 24.8);
$worksheet->write(4, 1, 25.0);
$worksheet->write(5, 1, 25.3);
$worksheet->write(6, 1, 24.9);
$worksheet->write(7, 1, 25.2);
$worksheet->write(8, 1, 24.8);

# Write some statistical functions
$worksheet->write(0,  4, 'Count', $format);
$worksheet->write(1,  4, '=COUNT(B2:B9)');

$worksheet->write(0,  5, 'Sum', $format);
$worksheet->write(1,  5, '=SUM(B2:B9)');

$worksheet->write(0,  6, 'Average', $format);
$worksheet->write(1,  6, '=AVERAGE(B2:B9)');

$worksheet->write(0,  7, 'Min', $format);
$worksheet->write(1,  7, '=MIN(B2:B9)');

$worksheet->write(0,  8, 'Max', $format);
$worksheet->write(1,  8, '=MAX(B2:B9)');

$worksheet->write(0,  9, 'Standard Deviation', $format);
$worksheet->write(1,  9, '=STDEV(B2:B9)');

$worksheet->write(0, 10, 'Kurtosis', $format);
$worksheet->write(1, 10, '=KURT(B2:B9)');


$workbook->close();
$workbook->sendFile();

Vous remarquerez deux choses :
1) Il faut un '=' pour démarrer la formule.
2) Le nom des cellules dans la formule est de type nominatif A1-B6-K9, etc. et non plus de type array 0,1,2,3...