Communication entre PHP et Excel via l'objet COM

24/04/2004

Par Stephane Eyskens (Autres articles)
 

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.

• 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

# 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 avec 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.

<?					
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	
?>

<? 
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
?>

ActiveChart.ChartType = xlPie

//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

Debug.Print xlPie