You probably don’t need Laravel-Excel

Laravel Excel library was used on a large scale in the project, but the problems of consuming too much memory and slow import and export speed were often found in the operation process.

A new Excel processing library Spout was discovered today

Spout is a PHP library to read and write spreadsheet files (CSV, XLSX and ODS), in a fast and scalable way. Contrary to other file readers or writers, it is capable of processing very large files while keeping the memory usage really low (less than 3MB).

So today we’re going to test whether the library really lives up to its official description and compare the performance gap with Laravel Excel.

The preparatory work

First let’s create a Laravel project:

composer create-project --prefer-dist laravel/laravel test-excel-performanceCopy the code

Install Laravel Excel and our newly discovered Spout respectively:

composer require maatwebsite/excel

composer require box/spoutCopy the code

In addition, we needed a larger Excel file. I used a 10000 row by 60 column file with no style and random empty cells in the file. The file size was around 11M.

We’ll name it test.xlsx and put it in storage/public.

Test read performance

To test the read performance of each library, create a command in the framework:

php artisan make:command ExcelReaderCopy the code

Define a parameter and an option

protected $signature = 'excel:reader {path} {--drive=laravel-excel}';Copy the code

And define some parameters to be counted:

Private $rows = 0; private $rows = 0; Private $timeUsage = 0; private $timeUsage = 0; Private $memoryUsage = 0; private $memoryUsage = 0;Copy the code

The read file logic for both components is defined separately. To ensure fairness, no extra operations are done in the code, but only the number of links and cells are counted.

First, Spout. The official quick start is very simple:

private function useSpoutDrive($path)
{
    ini_set('memory_limit', -1);
    $start = now();
    $reader = ReaderFactory::create(Type::XLSX);
    $reader->setShouldFormatDates(true);
    $reader->open(storage_path('app/'.$path));
    $rows = [];
    foreach ($reader->getSheetIterator() as $sheet) {
        foreach ($sheet->getRowIterator() as $row) {
            $this->rows++;
            $rows[] = $row;
        }
    }
    $this->timeUsage = now()->diffInSeconds($start);
    $this->memoryUsage = xdebug_peak_memory_usage();
}Copy the code

Then there is Laravel Excel. Version 3.1 of Laravel Excel has made a lot of changes to the Api. Instead of the original way of nesting layers of closures, it becomes more object-oriented, which is very much to my taste.

We follow the official documentation to create a generic import class:

php artisan make:import TestImportCopy the code

In the app/imports directory you can see that a new TestImport class has been created. We removed all the extra code and left an empty class as follows:

<? php namespace App\Imports; use Illuminate\Support\Collection; use Maatwebsite\Excel\Concerns\ToArray; use Maatwebsite\Excel\Concerns\ToCollection; use Maatwebsite\Excel\Concerns\WithChunkReading; class TestImport { }Copy the code

Then we do this for import logic:

private function useLaravelExcelDrive($path) { $start = now(); / / this code is used to handle a read XML import times big mistakes, has nothing to do with this article but more redundancy Settings: : setLibXmlLoaderOptions (LIBXML_COMPACT | LIBXML_PARSEHUGE); ini_set('memory_limit', -1); $array = Excel::toArray(new TestImport(), $path); $this->rows = count($array[0]); $this->timeUsage = now()->diffInSeconds($start); $this->memoryUsage = xdebug_peak_memory_usage(); }Copy the code

Our complete code looks like this:

<? php namespace App\Console\Commands; use App\Imports\TestImport; use Box\Spout\Common\Type; use Box\Spout\Reader\ReaderFactory; use Illuminate\Console\Command; use Maatwebsite\Excel\Facades\Excel; use PhpOffice\PhpSpreadsheet\Settings; class ExcelReader extends Command { /** * The name and signature of the console command. * * @var string */ protected $signature = 'excel:reader {path} {--drive=laravel-excel}'; /** * The console command description. * * @var string */ protected $description = 'Command description'; Private $rows = 0; private $rows = 0; Private $timeUsage = 0; private $timeUsage = 0; Private $memoryUsage = 0; private $memoryUsage = 0; /** * Create a new command instance. * * @return void */ public function __construct() { parent::__construct(); } /** * @throws \Exception */ public function handle() { $path = $this->argument('path'); $option = $this->option('drive'); switch ($option) { case 'laravel-excel': $this->useLaravelExcelDrive($path); break; case 'spout': $this->useSpoutDrive($path); break; default: throw new \Exception('Invalid option ' . $option); } $this - > info (sprintf (' total read data: % s', $this - > rows)); $this->info(sprintf(' %s ', $this->timeUsage)); $this->info(sprintf($this->memoryUsage / 1024/1024)); } private function useSpoutDrive($path) { ini_set('memory_limit', -1); $start = now(); $reader = ReaderFactory::create(Type::XLSX); $reader->setShouldFormatDates(true); $reader->open(storage_path('app/'.$path)); $rows = []; foreach ($reader->getSheetIterator() as $sheet) { foreach ($sheet->getRowIterator() as $row) { $this->rows++; $rows[] = $row; } } $this->timeUsage = now()->diffInSeconds($start); $this->memoryUsage = xdebug_peak_memory_usage(); } private function useLaravelExcelDrive($path) { $start = now(); Settings::setLibXmlLoaderOptions(LIBXML_COMPACT | LIBXML_PARSEHUGE); ini_set('memory_limit', -1); $array = Excel::toArray(new TestImport(), $path); $this->rows = count($array[0]); $this->timeUsage = now()->diffInSeconds($start); $this->memoryUsage = xdebug_peak_memory_usage(); }}Copy the code

After the code is written, let’s run it through Laravel Excel:

php artisan excel:read public/test.xlsxCopy the code

The result is:

Then we run read through Spout:

php artisan excel:read public/test.xlsx --drive=spoutCopy the code

if (! function_exists('generate_test_data')) { function generate_test_data() { $faker = \Faker\Factory::create('zh_CN'); $result = []; for ($i = 0; $i < 100000; $i++) { $arr = [ 'name' => $faker->name, 'age' =>$faker->randomNumber(), 'email' => $faker->email, 'address' => $faker->address, 'company' => $faker->company, 'country' => $faker->country, 'birthday' => $faker->date(), 'city' => $faker->city, 'creditCardNumber' => $faker->creditCardNumber, 'street' => $faker->streetName, 'postCode' => $faker->postcode, ]; $result[] = $arr; } return $result; }}Copy the code

// Generated Export class namespace App\Exports; use Faker\Factory; use Maatwebsite\Excel\Concerns\FromArray; use Maatwebsite\Excel\Concerns\FromCollection; class TestExport implements FromArray { /** * @return array */ public function array(): array { return generate_test_data(); Private function useLaravelExcelDrive() {ini_set('memory_limit', -1); $start = now(); Excel::store(new TestExport(), 'testWriter.xlsx'); $this->timeUsage = now()->diffInSeconds($start); $this->memoryUsage = xdebug_peak_memory_usage(); }Copy the code

private function useSpoutDrive()
{
    ini_set('memory_limit', -1);
    $start = now();
    $writer = WriterFactory::create(Type::XLSX);
    $writer->openToFile(storage_path('app/testWriter.xlsx'));
    $writer->addRows(generate_test_data());
    $writer->close();
    $this->timeUsage = now()->diffInSeconds($start);
    $this->memoryUsage = xdebug_peak_memory_usage();
}Copy the code