Posted on by admin yep

Laravel : import and export a collection in Excel with Fast Excel


A tutorial on how to use the rap2hpoutre/fast-excel package to export data from a collection or model in Excel xlsx, ods or csv format and import the data from a spreadsheet into a Laravel project.

The French version of this tutorial : Laravel : importer et exporter une collection en Excel avec Fast Excel

Introduction

Fast Excel or fast-excel is a Laravel package that allows to read and write spreadsheet files (CSV, XLSX and ODS). It offers the following features:

  • Export data from collections or models to .xlsx, .csv or .ods files
  • Import data from an Excel .xlsx or .csv file as a collection
  • Import multiple spreadsheets
  • Export large data collections in record time
  • …

A collection (Illuminate\Support\Collection) in a Laravel project, is a wrapper that provides methods to efficiently manipulate arrays of data.

This guide will show you how to install and use Fast Excel or rap2hpoutre/fast-excel to perfore the above operations.

At the time of writing this article, I am using version 9.42.2 of Laravel.

Install Fast Excel

To install the rap2hpoutre/fast-excel package in a Laravel project, run the following composer command:

composer require rap2hpoutre/fast-excel

This command will download fast-excel and its dependencies into the /vendor directory of your Laravel project.

The rap2hpoutre/fast-excel package uses the box/spout library to read and write spreadsheet files.

Using Fast Excel

Once fast-excel is downloaded in your project, you can initialize it directly in a controller and access the methods of the Rap2hpoutre\FastExcel\FastExcel class:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Rap2hpoutre\FastExcel\FastExcel; // The FastExcel class

class FastExcelController extends Controller
{
    public function index () {

        $data = collect(); // A collection or a model
        $fastexcel = new FastExcel($data); // The FastExcel instance
        dd($fastexcel);

    }
}

The global helper

FastExcel also provides the global helper fastexcel() which allows direct access to its methods anywhere in your project:

$data = collect(); // A collection or a model
$fastexcel = fastexcel($data); // The FastExcel instance

The facade

If importing the Rap2hpoutre\FastExcel\FastExcel class or using the global helper fastexcel() does not suit you, you can also register the FastExcel facade in the $aliases array of your /config/app.php file:

'aliases' => Facade::defaultAliases()->merge([

    "FastExcel" => Rap2hpoutre\FastExcel\Facades\FastExcel::class

])->toArray()

Next, initialize Fast Excel by passing data to the method data($data) where $data represents a collection or model:

// A collection or a model
$data = User::first();

// The FastExcel instance
$fastexcel = \FastExcel::data($data);

Export to xlsx, csv or ods with Fast Excel

The export($file) method of FastExcel where $file represents the name of the file followed by the extension “.xlsx”, “.ods” or “.csv” allows you to export the data of a collection or a model to the directory /public :

<?php

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

class FastExcelController extends Controller
{
    public function index () {

        // A collection of "App\Models\User"
        $users = User::all();

        // Export to file "/public/users.xlsx"
        $path = (fastexcel($users))->export("users.xlsx");

        // Export to file "/public/users.csv"
        // $path = (fastexcel($users))->export("users.csv");

        // Export to file "/public/users.ods"
        // $path = (fastexcel($users))->export("users.ods");

    }
}

In this example, $path contains the absolute path of the users.xlsx file created. Example : C:\laragon\www\laravel-fastexcel\public\users.xlsx

Rearrange columns

If you want to select the columns to be exported, rearrange the data or apply processing to them, you can use a callback after the file name in the export() method:

// Callback "function($user) { ... }" in export()  
$path = (fastexcel($users))->export("users.xlsx", function ($user) {  
    return [
        "Full Name" => ucfirst($user['name']),
        "E-mail address" => $user['email']
    ];  
});

Download the .xls, .csv or .ods file

Instead of using the export($file) method to save the .xlsx, .csv or .ods files in the /public directory, you can use the download($file) method to start the download:

// Collection of "App\Models\User";
$users = User::select('id', 'name', 'email')->get();

// Download file "users.xlsx"
return fastexcel($users)->download('users.xlsx');

Export multiple collections to an Excel file

Fast Excel allows you to export multiple collections or models to different spreadsheets in an Excel workbook using the SheetCollection class.

Let’s take a look at an example that exports data from the User, Post and Product models to the file users-posts-products.xlsx :

<?php

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

// Importing SheetCollection
use Rap2hpoutre\FastExcel\SheetCollection; 

use App\Models\Post;
use App\Models\Product;
use App\Models\User;

class FastExcelController extends Controller
{
    public function index () {

        // Collection "App\Models\User";
        $users = User::select('id', 'name', 'email')->get();

        // Collection "App\Models\Post"
        $posts = Post::orderBy("created_at")->get();

        // Collection "App\Models\Product"
        $products = Product::select('id', "name", "description")->get();

        // Collection of spreadsheets (SheetCollection)
        $sheets = new SheetCollection([
            "Users" => $users,
            "Posts" => $posts,
            "Products" => $products
        ]);

        // Exporting spreadsheets to "/public/users-posts-products.xlsx"
        $path = (fastexcel($sheets))->export("users-posts-products.xlsx");
    }

}

If you open the file users-posts-products.xlsx in Microsoft Excel, you will find the spreadsheets “Users”, “Posts” and “Products”.

Export large collections

If you have a collection that exports a large amount of data, 1M+ rows for example, you can use a generator function to avoid the memory_limit problem:

use App\Models\Client;

// A generator function
function clients () {
    foreach (Client::cursor() as $client) {
        yield $client;
    }
};

// Export to "clients.xlsx
fastexcel(iterator_to_array(clients()))->export("clients.xlsx");

Import an xlsx, csv or ods file with Fast Excel

You can use FastExcel’s import($file) method, where $file represents the path to an .xlsx or .csv file, to import the entries (rows) in $file into a collection (Illuminate\Support\Collection):

// Importing the file "/public/users.xlsx"
$data = fastexcel()->import("users.xlsx");

// $data contains a collection
dd($data);

Import into the database

FastExcel allows you to browse the lines of a file and insert them into the database using a callback after the file name in the import() method:

use App\Models\Client;

// Callback "function ($line) { ... }" in "import"
$data = fastexcel()->import("clients.xlsx", function ($line) {

    return Client::create([
        'name' => $line['name'],
        'email' => $line['email'],
        'phone' => $line['phone'],
        'address' => $line['address'],
    ]);

});

$line['name'] specifies the column named “name” in the clients.xlsx file.

Import multiple spreadsheets from an xlsx file

FastExcel’s importSheets($file) method imports spreadsheet entries (rows) from $file into a collection:

// Import of the file "/public/users-posts-products.xlsx"
$data = fastexcel()->importSheets("users-posts-products.xlsx");

// $data contains a collection of 3 arrays
dd($data);

To import a specific spreadsheet, you can specify its number or position in the workbook using the sheet($number) method:

// Import of the 2nd spreadsheet from the file "/public/users-posts-products.xlsx"
$data = fastexcel()->sheet(2)->import("users-posts-products.xlsx");

Conclusion

We have just seen how to use the Fast Excel package to export data from a collection or model to an Excel file in .xlsx, .csv or .ods format, and import data from a spreadsheet as a collection.

The Fast Excel documentation also shows how to apply styles (text color, font, background, …) to columns and rows of a spreadsheet.

Let’s summarize the fast-excel methods seen :

  • fastexcel($data) : the global FastExcel helper allows to initialize it with the $data of a collection or a model
  • import($file) : import lines from a $file .xlsx, .csv or .ods file into the collection
  • export($file) : export data from a collection or a model to a $file .xlsx, .csv or .ods file
  • importSheets($file) : import spreadsheets from $file
  • sheet($number) : import a specific spreadsheet $number
  • download($file) : start downloading the file $file

Be well! 😉

Laravel News Links