Introducing Laravel Excel 3.1
Laravel Excel 3.0 was released a while ago and completely redesigned the architecture behind exports. Our next target was to do the same paradigm shift for imports. Development took some time, because we wanted to get it right from the start.
We are now ready to introduce Laravel Excel 3.1!
🚀 5 Minute quick start
Like 3.0 has export classes, 3.1 has import classes. Let’s start by creating our first import class. As an example we’ll use a users import. We’ll assume that the package is already installed, installation instructions can be found at: https://laravel-excel.maatwebsite.nl/3.1/getting-started/installation.html
Import class
The easiest way to create an import class is to use the make:import artisan command.
php artisan make:import UsersImport --model=UserIt should have created a UsersImport class in App/Imports. Because we used the --model option, the import already implements the ToModel concern. We can now implement the model method as follows:
<?phpnamespace App\Imports;use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;class UsersImport implements ToModel
{
/**
* @param array $row
*
* @return User|null
*/
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => Hash::make($row[2]),
]);
}
}
On each row that gets imported, we create a new user. We simply return a new User instance, the saving is handled by the package.
Importing your file
In your UsersController you can now add an import method that will call our UsersImport. Make sure to also add a route registration for this method.
<?phpnamespace App\Http\Controllers;use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;class UsersController extends Controller
{
public function import()
{
Excel::import(new UsersImport, 'users.xlsx');
return redirect('/')->with('success', 'All good!');
}
}
Passing the UsersImport object to the Excel::import() method will tell the package how to import the file that is passed as the second parameter. The file is expected to be located in your default filesystem disk (see config/filesystems.php).
Other disks
You can specify another disk with the third parameter like your Amazon S3 disk.
Excel::import(new UsersImport, 'users.xlsx', 's3');Uploaded files
If you let your user upload the document, you can also just pass the uploaded file directly.
Excel::import(new UsersImport, request()->file('your_file'));Supercharging your imports
The import class can be supercharged by adding the Importable trait.
<?phpnamespace App\Imports;use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;class UsersImport implements ToModel
{
use Importable;/**
* @param array $row
*
* @return User|null
*/
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => Hash::make($row[2]),
]);
}
}
We can now call the import method directly on the import class; no need for a facade.
(new UsersImport)->import('users.xlsx');Performant imports out of the box
The thing we really wanted to get right from the start is to provide the tools to make imports more performant.
Batch inserts
Usually one of the bottlenecks with imports is the database. By default, the package performs database inserts per row. However, on larger files, this can slow down your import a lot.
By implementing the WithBatchInserts concern you can limit the amount of queries done to import the entire file. The batch size will determine how many models we’ll insert at once. This will drastically reduce the import duration.
<?phpnamespace App\Imports;use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;class UsersImport implements ToModel, WithBatchInserts
{
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => Hash::make($row[2]),
]);
}
public function batchSize(): int
{
return 1000;
}
}
The package will now insert 1000 models in one query. You’ll have to play around with the batch size to find the sweet spot for your import.
Chunk reading
Importing a large file can have a huge impact on the memory usage, as the library will try to load the entire sheet into memory.
To mitigate this increase in memory usage, you can use the WithChunkReading concern. This will read the spreadsheet in chunks and keep the memory usage under control.
<?phpnamespace App\Imports;use App\User;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;class UsersImport implements ToModel, WithBatchInserts, WithChunkReading
{
public function model(array $row)
{
return new User([
'name' => $row[0],
'email' => $row[1],
'password' => Hash::make($row[2]),
]);
}
public function batchSize(): int
{
return 1000;
}public function chunkSize(): int
{
return 1000;
}
}
The file will now be read in chunks of 1000. In combination with batch inserts this should improve the speed and memory usage of your imports!
Queuing imports
By adding the ShouldQueue contract to your import class, each chunk will be queued as a separate job.
More goodies
Laravel Excel 3.1 provides a lot more goodies to make importing Excel files a breeze. You can find more about them in the official documentation https://laravel-excel.maatwebsite.nl/3.1/imports/
Support
Laravel Excel 3.1 will stay free and open source. We will provide support on a best effort basis.
Do you need help with integrating Laravel Excel or do you need help building a Laravel application? We are there to help you on a commercial basis. Contact us via info@maatwebsite.nl or via phone +31 (0)10 744 9312 to discuss the possibilities.
Postcardware
If you use the software in your production environment we would appreciate to receive a postcard of your hometown. Please send it to:
Maatwebsite
Markt 2
6231 LS Meerssen
The Netherlands
