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

Introduction

Le 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 dans lequel chaque ligne représente un enregistrement. Les champs d'un enregistrement sont séparés par un caractère prédéfini tels qu’une virgule, un point-virgule, …

Nous voulons 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.

👉 Voir aussi : Importer et exporter une collection en excel avec Fast Excel

Installer le package spatie/simple-excel

Pour télécharger (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 »

Pour générer le modèle « Client » qui représente un client et la table « clients » pour stocker les informations des clients, on exécute la commande artisan suivante :

php artisan make:model Client –m

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

Décrivons le schéma des informations d'un client dans 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
        });
    }

    // ...
}

Pour importer (migrer) la table « clients » dans la base de données, on exécute la commande artisan suivante :

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 ces routes 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 Token -->
    @csrf

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

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

</form>

J'ai le rendu suivant dans le navigateur Google Chrome :

Ce formulaire est traité par l'action « import » de 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 (entrées) 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 pour afficher un message de succès

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

<?php

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

use App\Models\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 supprime 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 (name, email, phone, address).

Notez bien : La méthode getRows() retourne un objet Illuminate\Support\lazyCollection. 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 :

$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 où entrer un nom de fichier
• Une liste d'options <select> où choisir l’extension du fichier (.xlsx ou .csv) à télécharger

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

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

J'obtiens le rendu suivant dans le navigateur Google Chrome :

Lorsqu’on soumet ce formulaire après avoir entré un nom de fichier et sélectionné une extension, le traitement pour créer un fichier Excel ou CSV par l'action « export » de la route nommée « excel.export »  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 concaténant 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\Models\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 éditeur de style StyleBuilder qui prend en charge les couleurs, les polices, les bordures, l'alignement, ... 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 :

• rap2hpoutre/fast-excel
• PhpSpreadsheet
• maatwebsite/excel
• ...

Portez-vous bien ! 😉