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! đŸ˜‰