[Bug]:

[bilfeldt]

Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before?

• Yes, it's still reproducable

What version of Laravel Excel are you using?

3.1.35

What version of Laravel are you using?

8.77.1

What version of PHP are you using?

8.0

Describe your issue

Exporting a big database dataset from a query to a file should use DB chunking and file streaming and hence not cause a memory issue, but when trying to do so (even with the local disk) then the following memory issue occurs:

   Symfony\Component\ErrorHandler\Error\FatalError 

  Allowed memory size of 536870912 bytes exhausted (tried to allocate 83886080 bytes)

  at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php:416
    412▕     {
    413▕         if ($cellCoordinate !== $this->currentCoordinate) {
    414▕             $this->storeCurrentCell();
    415▕         }
  ➜ 416▕         $this->index[$cellCoordinate] = true;
    417▕ 
    418▕         $this->currentCoordinate = $cellCoordinate;
    419▕         $this->currentCell = $cell;
    420▕         $this->currentCellIsDirty = true;


   Whoops\Exception\ErrorException 

  Allowed memory size of 536870912 bytes exhausted (tried to allocate 83886080 bytes)

  at vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php:416
    412▕     {
    413▕         if ($cellCoordinate !== $this->currentCoordinate) {
    414▕             $this->storeCurrentCell();
    415▕         }
  ➜ 416▕         $this->index[$cellCoordinate] = true;
    417▕ 
    418▕         $this->currentCoordinate = $cellCoordinate;
    419▕         $this->currentCell = $cell;
    420▕         $this->currentCellIsDirty = true;

      +1 vendor frames 
  2   [internal]:0
      Whoops\Run::handleShutdown()

The funny part is that using fputcsv directly from the query does not cause the same problem - see "How can the issue be reproduced?"

How can the issue be reproduced?

Steps to reproduce

1. Fill a table with a lot of data (testing with ~500.000 rows and 30 columns)
2. Make an export file using query on this big table
3. Run the script below:

$export = new \App\Exports\ExampleExport();
$filename = 'test.csv';
$disk = 's3';

// Should work, but does not work :(
//\Excel::store($export, $filename, $disk);

// Pure PHP way - works
$tmpFile = uniqid().'.csv';
$this->commentNote('Exporting to temporary file', "{$filename}");
$resource = fopen(storage_path('app/'.$tmpFile), 'w'); // will use the 'local' disk
$bar = $this->output->createProgressBar($export->query()->count());
$export->query()->chunk($export->chunkSize(), function ($chunk) use ($resource, $bar) {
    foreach ($chunk as $row) {
        fputcsv($resource, (array) $row);
        $bar->advance();
    }
});
fclose($resource);
$bar->finish();
$this->newLine();

// $this->commentNote('Moving', "{$tmpFile} to {$disk}::{$filename}");
// $this->moveFile('local', $disk, $tmpFile, $filename);
// Storage::disk('local')->delete($tmpFile);

What should be the expected behaviour?

The expected behaviour would be that running the top code was also able to write the export to the local disk without a memory issue - an actually that it was capable of streaming to any filedisk without a memory issue:

$export = new \App\Exports\ExampleExport();

\Excel::store($export, 'test.csv., 'local); // or to an s3 disk

[patrickbrouwers]

There's unfortunately not a lot we can do about that here. The performance issue is within PhpSpreadsheet which builds an entire array of cells (to be interchangable with Excel types), something that you circumvent if you do a fputcsv directly. (Only current chunk is kept into memory). PhpSpreadsheet inserts the entire array of cells at once, not row per row. (So very logical that the performance is very different)

If you need a high performance export, use fputcsv (or an abstraction like league/csv). (We might open source our drop-in csv export package in the future, however for now that is closed source)

[bilfeldt]

PhpSpreadsheet inserts the entire array of cells at once, not row per row. (So very logical that the performance is very different)

Thanks so much for explaining @patrickbrouwers 👍 I was of the understanding that each query chunk was being handled individually, but your explanation makes total sense.

I can understand why that can be difficult to circumvent, and while it would be awesome if you some day open-sourced your drop-in csv export package then it's a limitation that makes sense and which works for the majority of the use-cases.

Maybe it would be worth a single line in the documentation however? I would imagine that the same is relevant for imports where the entire array is also held in memory by PhpSpreadsheet? If so then regardless of the performance improvements used during import/export using query chunking or generators then this will be a fundamental limit for the amount of data the application can handle.

Thanks for a great package and super quick response - this is sincerely appreciated.

[patrickbrouwers]

Yes for the next release I have a rewrite of the docs in the making, it will be better explain there.