Laravel : Importer, exporter les données en Excel ou CSV avec spatie/simple-excel

Publié il y a 4 semaines

Wilo Ahadi

Auteur

Wilo Ahadi

Technologies

Un guide pour importer et exporter les informations d’une base de données vers un fichier Excel (.xlsx) ou CSV (.csv) avec le package laravel spatie/simple-excel.

Introduction

La package spatie/simple-excel permet d’écrire et de lire les fichiers Excel (.xlsx) et CSV (.csv) suivant l’extension du fichier donnée :

  • « .xlsx » est l’extension de nom de fichier d’un classeur Excel. Il est utilisé par Microsoft Office à partir de la version 2007
  • « .csv » est l’extension du format CSV (Comma-Separated Values) qui présente un classeur en fichier de texte où chaque ligne est un enregistrement dont les champs sont séparés par un caractère prédéfini tels qu’une virgule, un point-virgule, …

Nous allons voir dans ce guide comment utiliser le package spatie/simple-excel dans un projet Laravel pour lire les données (contenu) d’un fichier Excel ou CSV, importer ces données dans une table de la base de données puis les exporter vers un fichier .xlsx et .csv.

Annonce :

Installer le package spatie/simple-excel

Pour importer le package spatie/simple-excel avec ses dépendances dans un projet Laravel, on exécute la commande composer suivante à la racine du projet :

composer require spatie/simple-excel

spatie/simple-excel utilise box/spout, une librairie PHP qui permet d’écrire et de lire des fichiers de tableur (CSV, XLSX et ODS).

Utiliser le package spatie/simple-excel

Prérequis

Avant de nous lancer dans l’importation – exportation en Excel ou CSV, préparons la base de données en créant la table qui va accueillir les informations du fichier Excel, les routes pour les actions import – export et un contrôleur pour décrire ces actions.

Pour les données, travaillons avec les informations des clients (personne qui se fournit chez un marchand) pour qu’on ait plusieurs champs distincts :

  • Un nom (name)
  • Une adresse email (email)
  • Un numéro de téléphone (phone)
  • Une adresse physique (address)

Le modèle et la table « clients »

Générons le modèle « Client » pour représenter un client et la table « clients » pour stocker les informations des clients en exécutant la commande artisan suivante :

php artisan make:model Client –m

Cette commande crée les fichiers app/Client.php (le modèle) et databases/migrations/..._create_clients_table.php (la migration). Décrivons la migration ..._create_clients_table.php :

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateClientsTable extends Migration
{  
    public function up()
    {
        Schema::create('clients', function (Blueprint $table) {
            $table->id(); // id
            $table->string('name'); // Le nom
            $table->string('email'); // L'adresse email
            $table->string('phone'); // Le numéro de téléphone
            $table->text('address'); // L'adresse physique
            $table->timestamps(); // created_at et updated_at
        });
    }

    // ...
}

Migrons la table « clients » dans la base de données :

php artisan migrate

Maintenant que la table « clients » est prête, nous pouvons y insérer les données provenant d’un fichier Excel.

Les routes et le contrôleur

Nous allons travailler avec 2 routes :

  • « simple-excel/import » (POST) pour traiter l’action d’importation. On la nomme « excel.import »
  • « simple-excel/export » (POST) pour traiter l’action d’exportation. On la nomme « excel.export »

Définissons-les au fichier routes/web.php :

<?php

use Illuminate\Support\Facades\Route;

// Importer un fichier Excel
Route::post("simple-excel/import", "SimpleExcelController@import")->name('excel.import');

// Exporter un fichier Excel
Route::post("simple-excel/export", "SimpleExcelController@export")->name('excel.export');

Les actions « import » et « export » de ces routes seront décrites au contrôleur app/Http/Controllers/SimpleExcelController.php que nous pouvons générer en exécutant la commande artisan suivante :

php artisan make:controller SimpleExcelController

Importer les données Excel

Pour l’importation de données d'un fichier Excel dans la base de données, mettons en place un formulaire d’upload de fichier sur une vue (template Blade) :

<h3>Importer</h3>

<p>Sélectionnez un fichier Excel (.xlsx) pour importer les données dans la table "clients".<br><strong>Les colonnes : </strong>name, email, phone, address</p>

<form method="POST" action="{{ route('excel.import') }}" enctype="multipart/form-data" >

    {{ csrf_field() }}

    <input type="file" name="fichier" >

    <button type="submit" >Importer</button>

</form>

J'ai le rendu suivant au navigateur :

Formulaire HTML d'upload de fichier

Ce formulaire est traité par la route nommée « excel.import ». On procède de la manière suivante lorsqu’on upload un fichier :

  1. On s’assure que le fichier envoyé est d’extension .xlsx (Validation)
  2. On déplace le fichier envoyé vers le dossier /public pour le lire
  3. On crée une instance Spatie\SimpleExcel\SimpleExcelReader avec la méthode SimpleExcelReader::create($fichier_excel) puis on récupère les lignes du fichier Excel avec le méthode getRows()
  4. On insère toutes les lignes dans la table « clients » de la base de données
  5. On supprime le fichier uploadé
  6. On retourne vers le formulaire avec un message

Décrivons l’action « import » au contrôleur App/Http/Controllers/SimpleExcelController.php :

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;

use App\Client;

use Spatie\SimpleExcel\SimpleExcelWriter;
use Spatie\SimpleExcel\SimpleExcelReader;

class SimpleExcelController extends Controller
{
    // Importer les données
    public function import (Request $request) {

    	// 1. Validation du fichier uploadé. Extension ".xlsx" autorisée
    	$this->validate($request, [
    		'fichier' => 'bail|required|file|mimes:xlsx'
    	]);

    	// 2. On déplace le fichier uploadé vers le dossier "public" pour le lire
    	$fichier = $request->fichier->move(public_path(), $request->fichier->hashName());

        // 3. $reader : L'instance Spatie\SimpleExcel\SimpleExcelReader
    	$reader = SimpleExcelReader::create($fichier);

        // On récupère le contenu (les lignes) du fichier
        $rows = $reader->getRows();

        // $rows est une Illuminate\Support\LazyCollection

        // 4. On insère toutes les lignes dans la base de données
        $status = Client::insert($rows->toArray());

        // Si toutes les lignes sont insérées
    	if ($status) {

            // 5. On supprimer le fichier uploadé
            $reader->close(); // On ferme le $reader
            unlink($fichier);

            // 6. Retour vers le formulaire avec un message $msg
            return back()->withMsg("Importation réussie !");

        } else { abort(500); }
    }
}

Vous pouvez utiliser ce fichier Excel client.xlsx pour tester l’importation. Il contient une liste de 1000 clients avec leurs informations.

Notez bien : La méthode getRows() retourne un objet Illuminate\Support\lazyCollection intégré au framework Laravel. Ce qui permet de travailler avec un très grand ensemble de données tout en maintenant une faible utilisation de la mémoire et d’utiliser les méthodes disponibles sur une collection (Illuminate\Support\Collection) :

$reader = SimpleExcelReader::create($fichier);

// On prend 10 lignes
$reader->take(10);

// On filtre les lignes en s'assurant que l'adresse email est correcte
$rows = $reader->getRows()->filter(function ($ligne) {
    return filter_var($ligne['email'], FILTER_VALIDATE_EMAIL) == true;
});

Exporter les données vers Excel ou CSV

Pour l’exportation de données de la table « clients » vers un fichier Excel ou CSV, mettons en place un formulaire de téléchargement de fichier avec :

  • Un champ de texte pour entrer un nom de fichier
  • Une liste d'options (<select>) pour choisir l’extension du fichier (.xlsx ou .csv)

Ce formulaire peut se présenter de la manière suivante :

<h3>Exporter</h3>
<p>Exporter la table "clients" en Excel</p>

<form method="POST" action="{{ route('excel.export') }}" >

    {{ csrf_field() }}

    <input type="text" name="name" placeholder="Nom de fichier" >

    <select name="extension" >
        <option value="xlsx" >.xlsx</option>
        <option value="csv" >.csv</option>
    </select>

    <button type="submit" >Exporter</button>

</form>

Ce qui donne le rendu suivant au navigateur :

Formulaire HTML de téléchargement de fichier

Lorsqu’on soumet ce formulaire après avoir entré un nom de fichier et sélectionné une extension, le traitement sur la route nommée « excel.export » pour créer un fichier Excel ou CSV se passe de la manière suivante :

  1. On valide les informations envoyées (name et extension)
  2. On crée un nom de fichier $file_name en concatenant name et extension
  3. On récupère les données (clients) de la base des données
  4. On crée une instance Spatie\SimpleExcel\SimpleExcelWriter avec la méthode SimpleExcelWriter::streamDownload($file_name) pour diffuser le fichier dans le navigateur
  5. On insère les informations des clients au fichier $file_name
  6. On lancer le téléchargement du fichier Excel ou CSV

Décrivons l'action « export » au contrôleur App/Http/Controllers/SimpleExcelController.php :

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;

use App\Client;

use Spatie\SimpleExcel\SimpleExcelWriter;
use Spatie\SimpleExcel\SimpleExcelReader;

class SimpleExcelController extends Controller
{
    // Importer les données ...

    // Exporter les données
    public function export (Request $request) {

    	// 1. Validation des informations du formulaire
    	$this->validate($request, [ 
    		'name' => 'bail|required|string',
    		'extension' => 'bail|required|string|in:xlsx,csv'
    	]);

    	// 2. Le nom du fichier avec l'extension : .xlsx ou .csv
    	$file_name = $request->name.".".$request->extension;

    	// 3. On récupère données de la table "clients"
    	$clients = Client::select("name", "email", "phone", "address")->get();

    	// 4. $writer : Objet Spatie\SimpleExcel\SimpleExcelWriter
    	$writer = SimpleExcelWriter::streamDownload($file_name);

 		// 5. On insère toutes les lignes au fichier Excel $file_name
    	$writer->addRows($clients->toArray());

        // 6. Lancer le téléchargement du fichier
        $writer->toBrowser();

    }
}

Appliquer du style au fichier Excel

box/spout dispose d'un editeur de style StyleBuilder qui prend en charge les couleurs, les polices, les bordures, l'allignement, ... Nous pouvons l'utiliser pour les personnaliser les lignes et les colonnes d'un fichier Excel :

use Box\Spout\Writer\Common\Creator\Style\StyleBuilder;
use Box\Spout\Common\Entity\Style\CellAlignment;
use Box\Spout\Common\Entity\Style\Color;

// Le style
$style = (new StyleBuilder())->setFontName("Arial")
                            ->setFontSize(15)
                            ->setFontColor(Color::BLACK)
                            ->setBackgroundColor(Color::rgb(246,248,250))
                            ->setShouldWrapText()
                            ->setCellAlignment(CellAlignment::LEFT)
                            ->build();

// L'instance Spatie\SimpleExcel\SimpleExcelWriter;
$writer = SimpleExcelWriter::create("fichier-stylisé.xlsx");

// On insère la collection $clients au fichier Excel en appliquant le $style à chaque ligne
$clients->each(function ($row) use ($writer, $style) {

    $writer->addRow($row->toArray(), $style);

});

Autres packages laravel – Excel

Pour traiter les fichiers Excel ou CSV dans un projet Laravel, il existe des nombreux packages. Voici ma petite sélection :

A vous  de jouer !

Annonce :
Wilo Ahadi
Wilo Ahadi, l'auteur

Passionné de l'informatique, je suis spécialiste en techniques des systèmes et réseaux, développeur web et mobile, Infographiste et designer, ... J'aime partager mon expérience en formant sur Akili School

Voir mon profil Suivre