TP6 - Cas d'étude : Connecteur PHP entre Excel et MySQL

BUTS PÉDAGOGIQUES

Interopérabilité entre applications hétérogènes
Utiliser une bibliothèque PHP externe
Lire un fichier XLSX depuis un programme PHP

Nous considérons une entreprise utilisant un système de gestion pour gérer son entrepôt (WMS) qui ne gère pas les dates de péremption des matières premières qu'elle utilise. Jusque-là, un classeur Excel a été mis en place par les magasiniers pour gérer les dates de péremption par numéro de lot. Seulement, la gestion manuelle, par le responsable, entre le classeur Excel et le logiciel WMS est difficile et chronophage. Pour automatiser cela, nous allons créer une nouvelle table dans la base de données MySQL du logiciel WMS afin d'y lister les numéros de lots qui ont dépassé la date de péremption. Nous aurions bien voulu supprimer le fichier Excel, mais les opérateurs ne veulent surtout pas arrêter d'utiliser Excel. La solution retenue a été de mettre en place un script PHP qui fera le lien entre le fichier Excel et la base de données MySQL.

Figure 0.1 : Connecteur PHP entre Excel et MySQL.

Ce TP propose de voir comment le langage PHP peut être utilisé pour faire le lien entre le tableur Excel et une base de données MySQL.

Remarque : ce sujet est directement inspiré d'un cas réel rencontré par une apprentie ESTIA dans une entreprise aéronautique au Pays Basque.

Exercice 1 • Lire un fichier Excel depuis un script PHP (20 min)

Des bibliothèques comme PhpSpreadsheet ou PHPExcel permettent d'ouvrir un fichier Excel depuis un script PHP. Dans ce premier exercice, nous allons voir comment utiliser la bibliothèque PHPExcel pour accéder au contenu des cellules d'un classeur Excel 2007 (xlsx).

Téléchargez l'archive de la bibliothèque PHPExcel_1.8.0_pdf.zip sur votre disque dur.
Extraire cette archive dans le répertoire C:\TP_PHP\PHPExcel\
- (Remarque : une fois la bibliothèque extraite, le sous-répertoire "Documentation" contient des PDF qui décrivent le fonctionnement de la bibliothèque)
Téléchargez le fichier classeur_test.xlsx dans le répertoire C:\TP_PHP\ et consultez son contenu en l'ouvrant avec Excel.
Recopiez le code ci-dessous dans un fichier PHP qui s'appellera C:\TP_PHP\test.php

<?php

require_once('PHPExcel/Classes/PHPExcel.php');

// Ouvrir un fichier Excel en lecture
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("classeur_test.xlsx");
$sheet = $objPHPExcel->getActiveSheet() ;

// Premiere facon d'acceder au contenu de cellules du classeur Excel
$cell_1 = $sheet->getCell('A1') ;
$cell_2 = $sheet->getCell('B1') ;
$cell_3 = $sheet->getCell('C1') ;

echo 'Value : '.$cell_1->getValue()."\r\n" ;
echo 'Calculated Value : '.$cell_1->getCalculatedValue()."\r\n" ;
echo 'Formatted Value : '.$cell_1->getFormattedValue()."\r\n" ;

echo 'Value : '.$cell_2->getValue()."\r\n" ;
echo 'Calculated Value : '.$cell_2->getCalculatedValue()."\r\n" ;
echo 'Formatted Value : '.$cell_2->getFormattedValue()."\r\n" ;

echo 'Value : '.$cell_3->getValue()."\r\n" ;
echo 'Calculated Value : '.$cell_3->getCalculatedValue()."\r\n" ;
echo 'Formatted Value : '.$cell_3->getFormattedValue()."\r\n" ;

// Deuxieme facon d'acceder au contenu de cellules du classeur Excel
$cell_1 = $sheet->getCellByColumnAndRow(0, 1) ;
$cell_2 = $sheet->getCellByColumnAndRow(1, 1) ;
$cell_3 = $sheet->getCellByColumnAndRow(2, 1) ;

echo 'Value : '.$cell_1->getValue()."\r\n" ;
echo 'Calculated Value : '.$cell_1->getCalculatedValue()."\r\n" ;
echo 'Formatted Value : '.$cell_1->getFormattedValue()."\r\n" ;

echo 'Value : '.$cell_2->getValue()."\r\n" ;
echo 'Calculated Value : '.$cell_2->getCalculatedValue()."\r\n" ;
echo 'Formatted Value : '.$cell_2->getFormattedValue()."\r\n" ;

echo 'Value : '.$cell_3->getValue()."\r\n" ;
echo 'Calculated Value : '.$cell_3->getCalculatedValue()."\r\n" ;
echo 'Formatted Value : '.$cell_3->getFormattedValue()."\r\n" ;

// Cas d'une cellule au format date
$cell = $sheet->getCell('A2') ;

echo 'Date Value : '.$cell->getValue()."\r\n" ;
echo 'Date Calculated Value : '.$cell->getCalculatedValue()."\r\n" ;
echo 'Date Formatted Value : '.$cell->getFormattedValue()."\r\n" ;
$timestamp = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue()); // (Unix time)
$date = date('Y-m-d', $timestamp); // AAAA-MM-DD (formatted date)
echo 'Date AAAA-MM-DD : '.$date."\r\n" ;

Code 1.1 : Un script PHP qui ouvre un fichier Excel et accède aux cellules de différentes façons.

Question 1.1 : Tester ce premier programme et comprendre les fonctions de la bibliothèque PHPExcel

Testez le script test.php (en appelant l'interpréteur depuis un terminal, comme au TP3) et comprenez les façons dont il est possible d'accéder aux cellules d'un classeur Excel depuis un script PHP.
Quelles sont les deux façons utilisées pour accéder à une cellule ?
À quoi correspondent les trois différentes façons d'interpréter le contenu d'une cellule ?

Exercice 2 • Traiter le fichier Excel avec les dates de péremption (1 h)

Téléchargez le fichier classeur_materiaux.xlsx dans le répertoire C:\TP_PHP\ et consultez son contenu en l'ouvrant avec Excel. Ce fichier est saisi manuellement par les magasiniers et contient les dates de péremption pour chaque numéro de lot réceptionné.
Dans un premier temps, écrivez le script PHP qui va lire ce classeur, parcourir et afficher les cellules de la colonne B, depuis la cellule B2 jusqu'à rencontrer une cellule vide (c'est-à-dire qui contient la chaîne de caractère vide).
Dans un deuxième temps, complétez ce script pour afficher un message d'avertissement à chaque fois que la date présente dans la colonne B est antérieure à la date du jour en cours. Remarque : L'instruction suivante calcule la date du jour au format AAAA-MM-DD qui permet de faire des comparaisons :
$today = date('Y-m-d', time ()) ;

Exercice 3 • La base de donnée de WMS (30 min)

Nous allons reprendre la base de données existante de WMS. Sans modifier les tables existantes, car déjà utilisées par les fonctions en place dans le logiciel, nous allons créer une nouvelle table qui listera les numéros de lots périmés et indiquera s'ils ont été retirés du stock. Enfin, nous compléterons le script PHP précédent pour faire les insertions nécessaires dans la nouvelle table MySQL à partir de la lecture du classeur XLSX.

Le schéma relationnel de la base de données de WMS est le suivant :

Figure 3.1 : Schéma relationnel original.

Notation : les clés primaires sont soulignées, les clés étrangères sont précédées d'un croisillon « # ».

Lancez WAMP
Accédez à l'interface de contrôle phpMyAdmin : http://127.0.0.1/phpmyadmin
Téléchargez le fichier db_wms.sql (clic droit > Enregistrer la cible du lien sous). Ce fichier contient les requêtes SQL pour créer la base db_wms et les tables, puis peupler la base avec les données de l'entrepôt.
Importez ce fichier avec la fonction d'importation de phpMyAdmin.

Nous souhaitons maintenant ajouter une nouvelle table :

Figure 3.2 : Schéma relationnel modifié.

Depuis phpMyAdmin, créez cette nouvelle table dans la base db_wms (attention de choisir le moteur de stockage InnoDB pour gérer les clés étrangères).
Modifiez le script PHP précédent (exercice 2) pour faire les requêtes d'insertion dans la nouvelle table. Attention, l'insertion ne devra être faite que si le numéro de lot n'est pas déjà présent.

Pour que tout cela devienne opérationnel, il faudrait que la base de données soit réactualisée chaque jour. Pour ce faire, il est possible d'utiliser le Planificateur de tâches de Windows (voir dans le Panneau de configuration), ou le planificateur CRON sous Linux/Unix, afin de demander l'exécution du script chaque matin à 5 h 00, par exemple.

Exercice 4 • Formulaire HTML pour signaler les lots qui ont été retirés du stock physique

Enfin, pour parfaire le tout, écrire une page PHP qui récupérera, depuis la base de données, les informations de produit, quantité et fournisseur pour chacun des lots périmés qui n'ont pas encore été retirés. Ces informations de lots seront affichées dans un formulaire HTML permettant de cocher les produits, afin de pouvoir indiquer ceux qui ont été effectivement retirés du stock physique. Écrire ensuite le script PHP qui sera appelé par ce formulaire et qui s'occupera de marquer à vrai les lots qui ont été retirés.