[Bug]: Spreadsheet objects cannot be serialized

[axyr]

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.50

What version of Laravel are you using?

v10.33.0

What version of PHP are you using?

8.2

Describe your issue

First thank you for this awesome package.

I really love the way we can code exports, but I am running into problems when I need to export large amounts of rows in multiple sheets with not too much memory available.

This is also probably not a Laravel Excel problem, as the underlying PhpSpreadsheet package does not allow serialisation of Spreadsheet object:

PHPOffice/PhpSpreadsheet#3291

I checked out the existing issues that mention similar error reports:

• [Bug]: Serialization of 'Closure' is not allowed - cache FileStore #3973
• [Bug]: Exception: Serialization of 'Closure' is not allowed #3757

I tried several combinations of laravel caches (file|redis) and excel caches (memory|illuminate|batch) to make memory not the bottleneck when generating large excel exports.

I also tried different export concerns like FromGenerator, FromCollection, FromQuery and FromArray, but all generate the same error.

With the command I fiddle with settings to find the point till where I can generated a Workbook with multiple Sheets and lots of rows and columns:

How can the issue be reproduced?

I created a repository to reproduce the issue.

https://github.com/axyr/laravel-excel-serialization

This example generates a few sheets where the rows are filled with simple text generated from a foreach loop:

<?php

namespace App\Exports;

use Generator;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;

readonly class Sheet implements FromCollection, WithHeadings, WithTitle
{
    public function __construct(protected int $sheetNumber = 1, protected int $numberOfRows = 100, protected int $numberOfColumns = 26) { }

    public function title(): string
    {
        return "Sheet {$this->sheetNumber}";
    }

    public function headings(): array
    {
        $headings = [];

        for ($i = 0; $i <= $this->numberOfColumns; $i++) {
            $headings[] = "Heading {$this->sheetNumber} - {$i}";
        }

        return $headings;
    }

    public function generator(): Generator
    {
        for ($i = 0; $i <= $this->numberOfRows; $i++) {
            yield range(0, $this->numberOfColumns);
        }
    }

    public function collection(): Collection
    {
        return collect($this->array());
    }

    public function array(): array
    {
        $array = [];

        for ($i = 0; $i <= $this->numberOfRows; $i++) {
            for ($j = 0; $j <= $this->numberOfColumns; $j++) {
                $array[$i][$j] = "CS {$this->sheetNumber} {$j}";
            }
        }

        return $array;
    }
}

What should be the expected behaviour?

I would expect to create a "large" excel workbook without any errors.

[patrickbrouwers]

Can you share the stacktrace of the serialized exception

[axyr]

Absolutely!

[2023-11-27 11:32:49] local.ERROR: Spreadsheet objects cannot be serialized {"exception":"[object] (PhpOffice\\PhpSpreadsheet\\Exception(code: 0): Spreadsheet objects cannot be serialized at /Users/martijn/www/laravel/laravel-excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php:1656)
[stacktrace]
#0 [internal function]: PhpOffice\\PhpSpreadsheet\\Spreadsheet->__serialize()
#1 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Cache/FileStore.php(84): serialize(Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell))
#2 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Cache/FileStore.php(207): Illuminate\\Cache\\FileStore->put('phpspreadsheet....', Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell), 0)
#3 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(367): Illuminate\\Cache\\FileStore->forever('phpspreadsheet....', Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell))
#4 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(277): Illuminate\\Cache\\Repository->forever('phpspreadsheet....', Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell))
#5 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(246): Illuminate\\Cache\\Repository->putManyForever(Array)
#6 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(292): Illuminate\\Cache\\Repository->putMany(Array, NULL)
#7 /Users/martijn/www/laravel/laravel-excel/vendor/maatwebsite/excel/src/Cache/BatchCache.php(76): Illuminate\\Cache\\Repository->setMultiple(Array, NULL)
#8 /Users/martijn/www/laravel/laravel-excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php(361): Maatwebsite\\Excel\\Cache\\BatchCache->set('phpspreadsheet....', Object(PhpOffice\\PhpSpreadsheet\\Cell\\Cell))
#9 /Users/martijn/www/laravel/laravel-excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php(416): PhpOffice\\PhpSpreadsheet\\Collection\\Cells->storeCurrentCell()
#10 /Users/martijn/www/laravel/laravel-excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php(1280): PhpOffice\\PhpSpreadsheet\\Collection\\Cells->get('N436')
#11 /Users/martijn/www/laravel/laravel-excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php(1402): PhpOffice\\PhpSpreadsheet\\Worksheet\\Worksheet->getCell('N436')
#12 /Users/martijn/www/laravel/laravel-excel/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(306): PhpOffice\\PhpSpreadsheet\\Spreadsheet->garbageCollect()
#13 /Users/martijn/www/laravel/laravel-excel/vendor/maatwebsite/excel/src/Writer.php(168): PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx->save('/Users/martijn/...')
#14 /Users/martijn/www/laravel/laravel-excel/vendor/maatwebsite/excel/src/Jobs/AppendDataToSheet.php(83): Maatwebsite\\Excel\\Writer->write(Object(App\\Exports\\Sheet), Object(Maatwebsite\\Excel\\Files\\LocalTemporaryFile), 'Xlsx')
#15 /Users/martijn/www/laravel/laravel-excel/vendor/maatwebsite/excel/src/Jobs/Middleware/LocalizeJob.php(46): Maatwebsite\\Excel\\Jobs\\AppendDataToSheet->Maatwebsite\\Excel\\Jobs\\{closure}(Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet))
#16 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Support/Traits/Localizable.php(19): Maatwebsite\\Excel\\Jobs\\Middleware\\LocalizeJob->Maatwebsite\\Excel\\Jobs\\Middleware\\{closure}()
#17 /Users/martijn/www/laravel/laravel-excel/vendor/maatwebsite/excel/src/Jobs/Middleware/LocalizeJob.php(45): Maatwebsite\\Excel\\Jobs\\Middleware\\LocalizeJob->withLocale(NULL, Object(Closure))
#18 /Users/martijn/www/laravel/laravel-excel/vendor/maatwebsite/excel/src/Jobs/AppendDataToSheet.php(76): Maatwebsite\\Excel\\Jobs\\Middleware\\LocalizeJob->handle(Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet), Object(Closure))
#19 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Maatwebsite\\Excel\\Jobs\\AppendDataToSheet->handle(Object(Maatwebsite\\Excel\\Writer))
#20 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#21 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure(Object(Closure))
#22 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod(Object(Illuminate\\Foundation\\Application), Array, Object(Closure))
#23 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\\Container\\BoundMethod::call(Object(Illuminate\\Foundation\\Application), Array, Array, NULL)
#24 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(128): Illuminate\\Container\\Container->call(Array)
#25 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Bus\\Dispatcher->Illuminate\\Bus\\{closure}(Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet))
#26 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet))
#27 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(132): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#28 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(123): Illuminate\\Bus\\Dispatcher->dispatchNow(Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet), false)
#29 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(141): Illuminate\\Queue\\CallQueuedHandler->Illuminate\\Queue\\{closure}(Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet))
#30 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet))
#31 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(122): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))
#32 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(70): Illuminate\\Queue\\CallQueuedHandler->dispatchThroughMiddleware(Object(Illuminate\\Queue\\Jobs\\DatabaseJob), Object(Maatwebsite\\Excel\\Jobs\\AppendDataToSheet))
#33 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php(102): Illuminate\\Queue\\CallQueuedHandler->call(Object(Illuminate\\Queue\\Jobs\\DatabaseJob), Array)
#34 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(439): Illuminate\\Queue\\Jobs\\Job->fire()
#35 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(389): Illuminate\\Queue\\Worker->process('database', Object(Illuminate\\Queue\\Jobs\\DatabaseJob), Object(Illuminate\\Queue\\WorkerOptions))
#36 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(176): Illuminate\\Queue\\Worker->runJob(Object(Illuminate\\Queue\\Jobs\\DatabaseJob), 'database', Object(Illuminate\\Queue\\WorkerOptions))
#37 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(137): Illuminate\\Queue\\Worker->daemon('database', 'default', Object(Illuminate\\Queue\\WorkerOptions))
#38 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(120): Illuminate\\Queue\\Console\\WorkCommand->runWorker('database', 'default')
#39 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): Illuminate\\Queue\\Console\\WorkCommand->handle()
#40 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#41 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\\Container\\Util::unwrapIfClosure(Object(Closure))
#42 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\\Container\\BoundMethod::callBoundMethod(Object(Illuminate\\Foundation\\Application), Array, Object(Closure))
#43 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\\Container\\BoundMethod::call(Object(Illuminate\\Foundation\\Application), Array, Array, NULL)
#44 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Console/Command.php(211): Illuminate\\Container\\Container->call(Array)
#45 /Users/martijn/www/laravel/laravel-excel/vendor/symfony/console/Command/Command.php(326): Illuminate\\Console\\Command->execute(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Illuminate\\Console\\OutputStyle))
#46 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Console/Command.php(180): Symfony\\Component\\Console\\Command\\Command->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Illuminate\\Console\\OutputStyle))
#47 /Users/martijn/www/laravel/laravel-excel/vendor/symfony/console/Application.php(1081): Illuminate\\Console\\Command->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#48 /Users/martijn/www/laravel/laravel-excel/vendor/symfony/console/Application.php(320): Symfony\\Component\\Console\\Application->doRunCommand(Object(Illuminate\\Queue\\Console\\WorkCommand), Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#49 /Users/martijn/www/laravel/laravel-excel/vendor/symfony/console/Application.php(174): Symfony\\Component\\Console\\Application->doRun(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#50 /Users/martijn/www/laravel/laravel-excel/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(201): Symfony\\Component\\Console\\Application->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#51 /Users/martijn/www/laravel/laravel-excel/artisan(35): Illuminate\\Foundation\\Console\\Kernel->handle(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))
#52 {main}
"} 

[patrickbrouwers]

I see that you are using file store caching. Perhaps it would be a good idea to try a more performant caching store like redis, memcache(d).
I'm not sure why you are still getting the serialize error, this was fixed in 3.1.50 (f005070)

[axyr]

Hi Patrick,

Thanks for your reply.

Using a Redis cache, results in the same error.

I think I will reside to crank up the memory instead..