search

SpartnerNL / Laravel-Excel

🚀 Supercharged Excel exports and imports in Laravel
https://laravel-excel.com
MIT License
12.29k stars 1.92k forks source link

[Bug]: "Spreadsheet objects cannot be serialized" error with batch cell caching #4075

Closed cpxPratik closed 8 months ago

cpxPratik commented 9 months ago

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

What version of Laravel Excel are you using?

3.1.52

What version of Laravel are you using?

8.83.27

What version of PHP are you using?

PHP 8.2.13

Describe your issue

Using cell caching with batch store with default limit 60000 on config generates following error log for exporting excel with large no. of rows and multiple sheets. It some how tries to serialize and cache vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php object (not allowed by the PR), while the flushed cache items should contain cell data only.

The issue is similar to this

[2024-01-25 11:14:21] local.INFO: PhpOffice\PhpSpreadsheet\Exception: Spreadsheet objects cannot be serialized 
in /home/user/project/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php:1656
Stack trace:
#0 [internal function]: PhpOffice\PhpSpreadsheet\Spreadsheet->__serialize()
#1 /home/user/project/vendor/laravel/framework/src/Illuminate/Cache/FileStore.php(77): serialize()
#2 /home/user/project/vendor/laravel/framework/src/Illuminate/Cache/FileStore.php(200): Illuminate\Cache\FileStore->put()
#3 /home/user/project/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(363): Illuminate\Cache\FileStore->forever()
#4 /home/user/project/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(273): Illuminate\Cache\Repository->forever()
#5 /home/user/project/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(242): Illuminate\Cache\Repository->putManyForever()
#6 /home/user/project/vendor/laravel/framework/src/Illuminate/Cache/Repository.php(288): Illuminate\Cache\Repository->putMany()
#7 /home/user/project/vendor/maatwebsite/excel/src/Cache/BatchCacheDeprecated.php(76): Illuminate\Cache\Repository->setMultiple()
#8 /home/user/project/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php(361): Maatwebsite\Excel\Cache\BatchCacheDeprecated->set()
#9 /home/user/project/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Collection/Cells.php(416): PhpOffice\PhpSpreadsheet\Collection\Cells->storeCurrentCell()
#10 /home/user/project/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php(1287): PhpOffice\PhpSpreadsheet\Collection\Cells->get()
#11 /home/user/project/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php(3014): PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->getCell()
#12 /home/user/project/vendor/maatwebsite/excel/src/Sheet.php(544): PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->fromArray()
#13 /home/user/project/vendor/maatwebsite/excel/src/Sheet.php(657): Maatwebsite\Excel\Sheet->append()
#14 /home/user/project/vendor/maatwebsite/excel/src/Sheet.php(510): Maatwebsite\Excel\Sheet->appendRows()
#15 /home/user/project/vendor/maatwebsite/excel/src/Sheet.php(219): Maatwebsite\Excel\Sheet->fromArray()
#16 /home/user/project/vendor/maatwebsite/excel/src/Writer.php(72): Maatwebsite\Excel\Sheet->export()
#17 /home/user/project/vendor/maatwebsite/excel/src/Excel.php(197): Maatwebsite\Excel\Writer->export()
#18 /home/user/project/vendor/maatwebsite/excel/src/Excel.php(84): Maatwebsite\Excel\Excel->export()
#19 /home/user/project/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(261): Maatwebsite\Excel\Excel->download()
#20 /home/user/project/app/Http/Livewire/Front/Vfull/CompanyStats/CompanyStatsIndex.php(455): Illuminate\Support\Facades\Facade::__callStatic()
#21 /home/user/project/app/Http/Livewire/Front/Vfull/CompanyStats/CompanyStatsIndex.php(278): App\Http\Livewire\Front\Vfull\CompanyStats\CompanyStatsIndex->handleStatsV2()
#22 /home/user/project/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): App\Http\Livewire\Front\Vfull\CompanyStats\CompanyStatsIndex->handleDownloadStats()
#23 /home/user/project/vendor/laravel/framework/src/Illuminate/Container/Util.php(40): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
#24 /home/user/project/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\Container\Util::unwrapIfClosure()
#25 /home/user/project/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\Container\BoundMethod::callBoundMethod()
#26 /home/user/project/vendor/livewire/livewire/src/ComponentConcerns/HandlesActions.php(149): Illuminate\Container\BoundMethod::call()
#27 /home/user/project/vendor/livewire/livewire/src/HydrationMiddleware/PerformActionCalls.php(36): Livewire\Component->callMethod()
#28 /home/user/project/vendor/livewire/livewire/src/LifecycleManager.php(89): Livewire\HydrationMiddleware\PerformActionCalls::hydrate()
#29 /home/user/project/vendor/livewire/livewire/src/Connection/ConnectionHandler.php(13): Livewire\LifecycleManager->hydrate()
#30 /home/user/project/vendor/livewire/livewire/src/Controllers/HttpConnectionHandler.php(19): Livewire\Connection\ConnectionHandler->handle()
#31 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(48): Livewire\Controllers\HttpConnectionHandler->__invoke()
#32 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Route.php(261): Illuminate\Routing\ControllerDispatcher->dispatch()
#33 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Route.php(205): Illuminate\Routing\Route->runController()
#34 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Router.php(721): Illuminate\Routing\Route->run()
#35 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(128): Illuminate\Routing\Router->Illuminate\Routing\{closure}()
#36 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(50): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#37 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Routing\Middleware\SubstituteBindings->handle()
#38 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php(78): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#39 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Foundation\Http\Middleware\VerifyCsrfToken->handle()
#40 /home/user/project/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#41 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\View\Middleware\ShareErrorsFromSession->handle()
#42 /home/user/project/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(121): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#43 /home/user/project/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(64): Illuminate\Session\Middleware\StartSession->handleStatefulRequest()
#44 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Session\Middleware\StartSession->handle()
#45 /home/user/project/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#46 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle()
#47 /home/user/project/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(67): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#48 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Cookie\Middleware\EncryptCookies->handle()
#49 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#50 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Router.php(719): Illuminate\Pipeline\Pipeline->then()
#51 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Router.php(698): Illuminate\Routing\Router->runRouteWithinStack()
#52 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Router.php(662): Illuminate\Routing\Router->runRoute()
#53 /home/user/project/vendor/laravel/framework/src/Illuminate/Routing/Router.php(651): Illuminate\Routing\Router->dispatchToRoute()
#54 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(167): Illuminate\Routing\Router->dispatch()
#55 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(128): Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}()
#56 /home/user/project/vendor/livewire/livewire/src/DisableBrowserCache.php(19): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#57 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Livewire\DisableBrowserCache->handle()
#58 /home/user/project/vendor/barryvdh/laravel-debugbar/src/Middleware/InjectDebugbar.php(66): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#59 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Barryvdh\Debugbar\Middleware\InjectDebugbar->handle()
#60 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ConvertEmptyStringsToNull.php(27): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#61 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull->handle()
#62 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php(36): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#63 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Foundation\Http\Middleware\TrimStrings->handle()
#64 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#65 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle()
#66 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(86): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#67 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Illuminate\Foundation\Http\Middleware\PreventRequestsDuringMaintenance->handle()
#68 /home/user/project/vendor/fruitcake/laravel-cors/src/HandleCors.php(38): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#69 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Fruitcake\Cors\HandleCors->handle()
#70 /home/user/project/vendor/fideloper/proxy/src/TrustProxies.php(57): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#71 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(167): Fideloper\Proxy\TrustProxies->handle()
#72 /home/user/project/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#73 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(142): Illuminate\Pipeline\Pipeline->then()
#74 /home/user/project/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(111): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter()
#75 /home/user/project/public/index.php(51): Illuminate\Foundation\Http\Kernel->handle()
#76 /home/user/project/server.php(21): require_once('...')
#77 {main}

How can the issue be reproduced?

Use batch cell caching on config/excel.php and try to generate excel files with large no. of rows.

What should be the expected behaviour?

I should be able to download large excel file with multiple sheets without any errors.

patrickbrouwers commented 9 months ago

Can you try to use something like Redis as cache store. I wouldn't recommend using file caching as the i/o would probably make it slower.

rechl commented 9 months ago

@patrickbrouwers Had the same issue with 'batch' as cache.driver. Switching to illuminate backed by redis fixed it

patrickbrouwers commented 8 months ago

This should be fixed again in the latest release.