Communication entre PHP et Excel via l'objet COM

J'ai assez souvent vu dans le forum PHP, des questions concernant la création de fichiers Excel. Cet article va donc démontrer comment créer des fichiers Excel (pas des .csv) de manière dynamique à l'aide de l'objet COM.

Je remercie Jérôme pour ses corrections apportées à ce tuto.

Article lu fois.

L'auteur

Anonyme

L'article

Publié le 31 juillet 2005 - Mis à jour le 1^er décembre 2019

I. Introduction▲

Tout d'abord, je dois vous préciser que les exemples qui vont suivre ne fonctionnent que lorsque PHP est hébergé sur une plateforme Windows que l'objet COM puisse communiquer avec Excel.

Aucune activation d'extension particulière n'est nécessaire pour travailler avec COM. Nous allons donc réaliser ce qui suit:

Création d'une petite base de données MYSQL
Création d'un script PHP qui va extraire ces données et générer un fichier Excel formaté.
Création d'un script PHP qui va extraire ces données et générer un fichier Excel contenant un graphique basé sur les données

II. Création de la base de données MYSQL▲

II-A. Phpmyadmin▲

Via PhpMyadmin, créez une base de donnée nommée "phpexcel" et copier/coller le code suivant afin de créer votre table.

Cacher/Afficher le codeSélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.

# Base de données: `phpexcel`
# 

# --------------------------------------------------------

#
# Structure de la table `appels_clients`
#

CREATE TABLE `appels_clients` (
  `ID_APPEL` smallint(6) NOT NULL auto_increment,
  `XID_CLIENT` smallint(6) NOT NULL default '0',
  `DATE_APPEL` datetime NOT NULL default '0000-00-00 00:00:00',
  `SUJET_APPEL` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`ID_APPEL`)
) TYPE=MyISAM AUTO_INCREMENT=42 ;

#
# Contenu de la table `appels_clients`
#

INSERT INTO `appels_clients` VALUES (1, 1, '2004-04-24 12:40:25', 'Problème vec application X');
INSERT INTO `appels_clients` VALUES (2, 1, '2004-04-24 08:10:00', 'Problème avec Application Y');
INSERT INTO `appels_clients` VALUES (3, 2, '2004-04-24 07:40:45', 'Problème avec l\'application Z');
INSERT INTO `appels_clients` VALUES (4, 3, '2004-04-24 12:40:25', 'Problème avec l\'application A');
INSERT INTO `appels_clients` VALUES (5, 4, '2004-04-21 12:40:25', 'Problème avec l\'application 123');
INSERT INTO `appels_clients` VALUES (6, 5, '2004-04-24 12:00:00', '');
INSERT INTO `appels_clients` VALUES (7, 6, '2004-04-24 12:00:00', '');
INSERT INTO `appels_clients` VALUES (8, 7, '2004-04-24 12:00:00', '');
INSERT INTO `appels_clients` VALUES (9, 8, '2004-04-24 12:00:00', '');
INSERT INTO `appels_clients` VALUES (10, 9, '2004-04-24 12:00:00', '');
INSERT INTO `appels_clients` VALUES (11, 10, '2004-04-24 12:00:00', '');
INSERT INTO `appels_clients` VALUES (35, 2, '2002-04-21 12:40:25', 'Problème avec l\'application 123');
INSERT INTO `appels_clients` VALUES (34, 2, '2002-04-24 12:40:25', 'Problème avec l\'application A');
INSERT INTO `appels_clients` VALUES (33, 2, '2004-04-24 07:40:45', 'Problème avec l\'application Z');
INSERT INTO `appels_clients` VALUES (32, 1, '2004-04-24 08:10:00', 'Problème avec Application Y');
INSERT INTO `appels_clients` VALUES (31, 1, '2004-04-24 12:40:25', 'Problème avec application X');
INSERT INTO `appels_clients` VALUES (36, 2, '2002-04-26 12:00:00', '');
INSERT INTO `appels_clients` VALUES (37, 3, '2004-04-27 12:00:00', '');
INSERT INTO `appels_clients` VALUES (38, 3, '2004-04-28 12:00:00', '');
INSERT INTO `appels_clients` VALUES (39, 3, '2004-03-24 12:00:00', '');
INSERT INTO `appels_clients` VALUES (40, 4, '2004-02-10 12:00:00', '');
INSERT INTO `appels_clients` VALUES (41, 5, '2003-04-24 12:00:00', '');

# --------------------------------------------------------

#
# Structure de la table `clients`
#

CREATE TABLE `clients` (
  `ID_CLIENT` smallint(6) NOT NULL auto_increment,
  `NOM_CLIENT` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`ID_CLIENT`)
) TYPE=MyISAM AUTO_INCREMENT=29 ;

#
# Contenu de la table `clients`
#

INSERT INTO `clients` VALUES (1, 'Client 1');
INSERT INTO `clients` VALUES (2, 'Client 2');
INSERT INTO `clients` VALUES (3, 'Client 3');
INSERT INTO `clients` VALUES (4, 'Client 4');
INSERT INTO `clients` VALUES (5, 'client5');
INSERT INTO `clients` VALUES (6, 'client6');
INSERT INTO `clients` VALUES (7, 'client7');
INSERT INTO `clients` VALUES (8, 'client8');
INSERT INTO `clients` VALUES (9, 'client9');
INSERT INTO `clients` VALUES (10, 'client10');


//Notez que vous pourriez bien sûr créer une table client plus complète au niveau de la signalétique 
//ici, c'est juste un exemple.

Votre base de données est maintenant prête.

III. Génération du fichier Excel sans graphique▲

III-A. Explication du résultat voulu▲

Nous allons créer un fichier Excel qui contiendra tous les appels des clients, en ayant, un client par colonne et le total du nombre d'appels par client en bas de chaque colonne.

Comme vous vous en doutez, ne sachant pas à l'avance combien de clients ont effectué des appels, il nous faudra appliquer un algorithme pouvant déterminer à la volée, dans quelle colonne, il faudra placer les données.

Les colonnes Excel sont intitulées de la manière suivante "A" "B" "C"....."Z" "AA" "AB" "AC"..."BA" "BB".. Si plus de 26 clients ont appelé, il faudra donc placer le client 27 dans la colonne "AA" et ainsi de suite.

Pour pouvoir réaliser cela, nous allons jouer sur les codes ASCII, il y a peut-être d'autres méthodes mais celle-ci me convient.

Voici à quoi le fichier Excel ressemblera

III-B. Code PHP▲

Cacher/Afficher le codeSélectionnez

<?                                      
define("FILENAME","votre_racine_web\\phpexcel.xls"); //constante: nom du fichier à générer
$conn=mysql_connect('votre_hôte','utilisateur','mdp') or die('Probleme lors de la connexion a la BD MYSQL');
mysql_select_db('phpexcel',$conn) or die('Problème lors de la sélection de la BD MYSQL');//Sélection de la DB "phpexcel"
$query="SELECT DATE_APPEL,NOM_CLIENT,XID_CLIENT  FROM APPELS_CLIENTS INNER JOIN CLIENTS ON ";
$query.="ID_CLIENT=XID_CLIENT ORDER BY XID_CLIENT,DATE_APPEL";//Requête faisant une jointure sur les 2 tables
$res=mysql_query($query) or die('Problème lors de la réception des enregistrements '.$query);//Exécution de la requête
if(mysql_num_rows($res)>0){ //Si au moins une ligne est retournée
        if(file_exists(FILENAME)){      //Suppression du fichier précédement créé
                unlink(FILENAME); //Suppression du fichier
        }
        $excel=new COM("Excel.application"); //Instanciation de l'objet COM
        $excel->sheetsinnewworkbook=1;//1 seule feuille de calcul dans le document
        $excel->Workbooks->Add();//Ajout d'un classeur
        $book=$excel->Workbooks(1);//$book contient le classeur actif
        $sheet=$book->Worksheets(1);//$sheet contient la feuille active
        $sheet->Name="Rapport appel des clients";//Attribution d'un nom à la feuille
        $j=64;//C'est ici qu'on va jouer sur les codes ascii
        $jj=64;
        $client_prec=0; 
        $nombre_ligne=0;
        while($row=mysql_fetch_object($res)){//Parcours du résultat de la requête
                if($client_prec != $row->XID_CLIENT){//Rupture sur le numéro de client
                        $j++;
                        $i=3;
                        if($nombre_ligne>0){
                                $lignes[]=$nombre_ligne;//le tableau $lignes contiendra le nbre d'appels par client
                                $nombre_ligne=0;
                        }
                        //Ce bloc if vérifie si il s'agit d'une colonne de type "A" "B" ou "AA" "AB"...
                        if($jj==64){
                                $cell=$sheet->Range(chr($j).'1');//chr, renvoie la lettre correspondant au code ASCII
                        }
                        else{
                                $cell=$sheet->Range(chr($jj).chr($j).'1');
                        }                                               
                        $cell->value=$row->NOM_CLIENT;//Attribution d'une valeur à la cellule
                        $cell->HorizontalAlignment=-4108;//On centre le texte
                        $cell->Font->ColorIndex = 41;//Coloration
                }
                $nombre_ligne++;//$nombre_ligne sert à calculer le nbre d'appels par client
                if($jj==64){
                        $cell=$sheet->Range(chr($j).$i);
                }
                else{
                        $cell=$sheet->Range(chr($jj).chr($j).$i);
                }
                //Si $j représentant la colonne simple = "Z", alors incrémenter $jj qui représente la 2ème colonne
                if(chr($j)=='Z'){
                        $jj++;
                        $j=64;
                }
                $cell->value=$row->DATE_APPEL;//Attribution de la date et de l'heure à la cellule
                $cell->NumberFormat="jj/mm/aaaa hh:mm:ss";//Définition du format de la cellule
                /* Notez que selon vos paramètres régionnaux et la configuration d'Excel, vous devez peut-être
                utiliser un format de cellule comme ceci: dd/mm/yyyy hh:mm:ss (anglais)*/
                $client_prec=$row->XID_CLIENT;//gestion de la rupture sur le numéro de client
                $i++;
        }
}
else{
        die('Les tables sont vides ou pas de jointure possible');//si la requête ne retourne rien
}

$lignes[]=$nombre_ligne;//ajout des appels du dernier client dans $lignes
$sorted=$lignes; //on copie le nombre d'enreg par client dans un autre tableau
sort($sorted); //on trie cet autre tableau
//on récupère le plus grand nombre de record, pour pouvoir aligner les résultats dans Excel 
$max_val=5+$sorted[count($sorted)-1]; 
$colonne=65;
$colonne2=64;
//Boucle allant afficher le nombre total d'appels       par client
for($i=0;$i<count($lignes);$i++){       
        if($colonne2>64){
                $cell=$sheet->Range(chr($colonne2).chr($colonne).$max_val);
        }
        else{
                $cell=$sheet->Range(chr($colonne).$max_val);
        }
        if(chr($colonne)=='Z'){
                $colonne2++;
                $colonne=64;
        }
        $cell->value="Total: ".$lignes[$i];
        $cell->HorizontalAlignment=-4108;
        $cell->Font->ColorIndex=3;
        $colonne++;
}
$book->saveas(FILENAME);//Sauvegarde du fichier
unset($sheet);//Libération de $sheet
unset($book);//Libération de $book
$excel->Workbooks->Close();//Fermeture du classeur
$excel->Quit();//On quitte Excel
unset($excel);//Libération de l'instance $excel 
?>

IV. Génération du fichier Excel avec graphique▲

IV-A. Explication du résultat voulu▲

Nous allons créer un fichier Excel qui contiendra un graphique montrant le nombre d'appels par client.

Voici à quoi le fichier Excel ressemblera

IV-B. Code PHP▲

Cacher/Afficher le codeSélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.

<? 
define("FILENAME","D:\\webroot\\phpexcel_graphique.xls");
$conn=mysql_connect('votre_hôte','utilisateur','mdp') or die('Probleme lors de la connexion a la BD MYSQL');
mysql_select_db('phpexcel',$conn) or die('Problème lors de la sélection de la BD MYSQL');//Sélection de la DB "phpexcel"
$query="SELECT NOM_CLIENT, COUNT( * ) AS NB_APPEL FROM CLIENTS INNER JOIN ";
$query.="APPELS_CLIENTS ON ID_CLIENT = XID_CLIENT GROUP BY ID_CLIENT";//La requête groupe sur le n° client
$res=mysql_query($query) or die('Problème lors de la réception des enregistrements');//Exécution de la requête

if(mysql_num_rows($res)>0){//Si au moins une ligne retournée
        //Si le fichier existe, le supprimer
        if(file_exists(FILENAME)){
                unlink(FILENAME);
        }
        $excel=new COM("Excel.application");//Instanciation de l'objet COM
        $excel->sheetsinnewworkbook=1;//Une seule feuille dans le document
        $excel->Workbooks->Add();//Ajout d'un classeur
        $book=$excel->Workbooks(1);//$book contient le classeur actif
        $sheet=$book->Worksheets(1);//$sheet contient la feuille active
        $sheet->Name="Graphique appel des clients";//Attribution d'un nom à la feuille
        $i=1;
        while($row=mysql_fetch_object($res)){//Parcours du résultat de la requête
                $cell=$sheet->Range('A'.$i);
                $cell->value=$row->NOM_CLIENT;
                $cell=$sheet->Range('B'.$i);
                $cell->value=$row->NB_APPEL;
                $i++;
        }
}
else{
        die('Les tables sont vides ou pas de jointure possible');//Rien n'est retourné par la requête
}
$i--;
$selection = $sheet->range("A1:B$i");//Sélection des cellules qui seront la source du graphique 
$graph = $sheet->chartobjects->add(150, 10, 300, 250); //Ajout du graphique
$graphique = $graph->chart;//$graphique contient le graphique actif 
$graphique->type=5;//le graphique est de type "Fromage" (piechart)
$graphique->activate;//Activation du graphique 
$graphique->setsourcedata($selection);//Définition de la source du graphique
$graphique->ApplyDataLabels->type=5;//Afficher les valeurs
$graphique->HasTitle=true;
$graphique->ChartTitle->Text="Rapport des appels clients";//Attribution d'un titre
$book->saveas(FILENAME);//Enregistrement du document
unset($sheet);//Libération de $sheet
unset($book);//Libération de $book
$excel->Workbooks->Close();//Fermeture du classeur
$excel->Quit();//On quitte Excel
unset($excel);//Libération de l'objet $excel
?>

V. Astuces▲

Etant donné que la documentation concernant les méthodes de l'objet COM combiné à l'utilisation de PHP est assez rare, il faut se débrouiller pour pouvoir aller plus loin.

Une technique qui permet d'aller plus loin est de simplement, ouvrir Excel et créer une macro automatique, celle-ci enregistrera toutes vos actions jusqu'à ce que vous la stoppiez.

Ensuite, il suffit d'aller voir le code qui a été généré par Excel et de l'implémenter dans PHP.

Le code PHP diffère bien sûr du code VBA, par exemple, cette ligne de VBA:

Cacher/Afficher le codeSélectionnez

ActiveChart.ChartType = xlPie

Ne peut bien sûr pas être retranscrite telle quelle dans PHP. Il faudra un peu la modifier, comme ceci:

Cacher/Afficher le codeSélectionnez

1.
2.
3.
4.

//Cette ligne n'est nécessaire que si le graphique n'a pas encore été créé
$graph = $sheet->chartobjects->add(les dimensions que vous voulez);
$graphique = $graph->chart;
$graphique->type=5;// 5 correspond à la constante VBA xlPie

Comme vous le voyez, cela diffère un peu. La constante VBA xlPie ne peut être utilisée directement dans PHP.

Vous devez donc en connaître la valeur. Pour cela, faites simplement un

Cacher/Afficher le codeSélectionnez

Debug.Print xlPie

dans Excel.

VI. Téléchargement et utilisation des scripts▲

Téléchargez le fichier zip et dézippez-le sur votre machine.
Créez une base de données MYSQL nommée phpexcel
Exécutez le contenu du fichier phpexcel_dump.sql
Modifiez les connexions MYSQL dans les scripts PHP et placez-y vos propres paramètres ou créez un fichier de connexion à inclure.

Ces scripts ont été testé sous Windows XP professional/MySQL 4.0.15/PHP 4.3.3

Copyright © 2005 Developpez. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.