Laravel-excel: [BUG] Exporting large file caused Allowed Memory Size Exhausted fatal error
I tried to generate a large excel file which has about 360 columns and 7000 rows, but got the php fatal error.
Package version, Laravel version
Package Version 2.1.9
Laravel Version 5.3
Actual behaviour
Symfony\Component\Debug\Exception\FatalErrorException: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 67108872 bytes) in /home/vagrant/projects/myproject/vendor/phpoffice/phpexcel/Classes/PHPExcel/CachedObjectStorage/CacheBase.php:173
Stack trace:
#0 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Foundation/Bootstrap/HandleExceptions.php(132): Symfony\Component\Debug\Exception\FatalErrorException->__construct()
#1 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Foundation/Bootstrap/HandleExceptions.php(118): Illuminate\Foundation\Bootstrap\HandleExceptions->fatalExceptionFromError()
#2 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Foundation/Bootstrap/HandleExceptions.php(0): Illuminate\Foundation\Bootstrap\HandleExceptions->handleShutdown()
#3 /home/vagrant/projects/myproject/vendor/phpoffice/phpexcel/Classes/PHPExcel/CachedObjectStorage/CacheBase.php(173): array_keys()
#4 /home/vagrant/projects/myproject/vendor/phpoffice/phpexcel/Classes/PHPExcel/Worksheet.php(491): PHPExcel_CachedObjectStorage_CacheBase->getCellList()
#5 /home/vagrant/projects/myproject/vendor/phpoffice/phpexcel/Classes/PHPExcel/Worksheet.php(746): PHPExcel_Worksheet->getCellCollection()
#6 /home/vagrant/projects/myproject/vendor/maatwebsite/excel/src/Maatwebsite/Excel/Classes/LaravelExcelWorksheet.php(945): PHPExcel_Worksheet->calculateColumnWidths()
#7 /home/vagrant/projects/myproject/vendor/maatwebsite/excel/src/Maatwebsite/Excel/Writers/LaravelExcelWriter.php(227): Maatwebsite\Excel\Classes\LaravelExcelWorksheet->setAutoSize()
#8 /home/vagrant/projects/myproject/app/Api/V1/Controllers/Controller.php(222): Maatwebsite\Excel\Writers\LaravelExcelWriter->sheet()
#9 /home/vagrant/projects/myproject/vendor/maatwebsite/excel/src/Maatwebsite/Excel/Excel.php(87): App\Api\V1\Controllers\Controller->App\Api\V1\Controllers\{closure}()
#10 /home/vagrant/projects/myproject/vendor/maatwebsite/excel/src/Maatwebsite/Excel/Excel.php(87): call_user_func:{/home/vagrant/projects/myproject/vendor/maatwebsite/excel/src/Maatwebsite/Excel/Excel.php:87}()
#11 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Support/Facades/Facade.php(237): Maatwebsite\Excel\Excel->create()
#12 /home/vagrant/projects/myproject/app/Api/V1/Controllers/Controller.php(223): Illuminate\Support\Facades\Facade::__callStatic()
#13 /home/vagrant/projects/myproject/app/Api/V1/Controllers/Controller.php(223): Illuminate\Support\Facades\Facade::create()
#14 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(55): App\Api\V1\Controllers\Controller->report()
#15 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(55): call_user_func_array:{/home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:55}()
#16 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(44): Illuminate\Routing\Controller->callAction()
#17 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Route.php(189): Illuminate\Routing\ControllerDispatcher->dispatch()
#18 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Route.php(144): Illuminate\Routing\Route->runController()
#19 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Router.php(653): Illuminate\Routing\Route->run()
#20 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(53): Illuminate\Routing\Router->Illuminate\Routing\{closure}()
#21 /home/vagrant/projects/myproject/vendor/dingo/api/src/Http/Middleware/Auth.php(55): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}()
#22 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(137): Dingo\Api\Http\Middleware\Auth->handle()
#23 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(33): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#24 /home/vagrant/projects/myproject/vendor/dingo/api/src/Http/Middleware/PrepareController.php(45): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}()
#25 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(137): Dingo\Api\Http\Middleware\PrepareController->handle()
#26 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(33): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#27 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}()
#28 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Router.php(655): Illuminate\Pipeline\Pipeline->then()
#29 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Router.php(629): Illuminate\Routing\Router->runRouteWithinStack()
#30 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Router.php(607): Illuminate\Routing\Router->dispatchToRoute()
#31 /home/vagrant/projects/myproject/vendor/dingo/api/src/Routing/Adapter/Laravel.php(81): Illuminate\Routing\Router->dispatch()
#32 /home/vagrant/projects/myproject/vendor/dingo/api/src/Routing/Router.php(513): Dingo\Api\Routing\Adapter\Laravel->dispatch()
#33 /home/vagrant/projects/myproject/vendor/dingo/api/src/Http/Middleware/Request.php(126): Dingo\Api\Routing\Router->dispatch()
#34 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(151): Dingo\Api\Http\Middleware\Request->Dingo\Api\Http\Middleware\{closure}()
#35 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(46): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#36 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(137): Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode->handle()
#37 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#38 /home/vagrant/projects/myproject/vendor/dingo/api/src/Http/Middleware/Request.php(127): Illuminate\Pipeline\Pipeline->then()
#39 /home/vagrant/projects/myproject/vendor/dingo/api/src/Http/Middleware/Request.php(103): Dingo\Api\Http\Middleware\Request->sendRequestThroughRouter()
#40 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(137): Dingo\Api\Http\Middleware\Request->handle()
#41 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php(33): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#42 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(104): Illuminate\Routing\Pipeline->Illuminate\Routing\{closure}()
#43 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(150): Illuminate\Pipeline\Pipeline->then()
#44 /home/vagrant/projects/myproject/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(117): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter()
#45 /home/vagrant/projects/myproject/public/index.php(53): Illuminate\Foundation\Http\Kernel->handle()
#46 /home/vagrant/projects/myproject/public/index.php(0): {main}()
#47 {main}
I haven't changed the config/excel.php file so all configs have the default values.
Can someone please help me to fix the issue? Thanks!
gitstashgithub
👍2
All 15 comments
Please share how are you trying to create the Excel Sheet ?
Also try setting cache driver to 'discISAM' ...
ebbbang
on 19 Feb 2017
Hi,
Laravel 5.1.*
I am exporting an excel format file and the data length is about 1500 records and it is taking too much time to export the xls format and even it is drop in between, kindly assist me for the same.
mpgbis-dinesh
on 7 Mar 2017
@mpgbis-dinesh now i use https://medium.com/@barryvdh/streaming-large-csv-files-with-laravel-chunked-queries-4158e484a5a2#.sdtre1kx4 for exporting CSV.
tomswinkels
on 8 Mar 2017
No, i want to export it only in the xls format and i optimise the query more n more and now i will get the data in mili seconds and this will easily render in the blade within seconds but when i am exporting the same it will take too much time and even it is drop in between.
What to do now for exporting the excel from php
mpgbis-dinesh
on 22 Mar 2017
Please reply
mpgbis-dinesh
on 24 Mar 2017
Please reply
mpgbis-dinesh
on 26 Mar 2017
Please reply
mpgbis-dinesh
on 28 Mar 2017
You can increase the memory limit ini_set('memory_limit','60m');
But this is not a good solution.
I use chunk method of query builder to export big tables from database.
see example below.
$orders = Order::withTrashed()->where('status', 'done');
Excel::create('Report', function($excel) use ($orders) {
$excel->sheet('report', function($sheet) use($orders) {
$sheet->appendRow(array(
'id', 'landing', 'departure', 'phone_id'
));
$orders->chunk(100, function($rows) use ($sheet)
{
foreach ($rows as $row)
{
$sheet->appendRow(array(
$row->id, $row->landing, $row->departure, $row->phone_id
));
}
});
});
})->download('xlsx');
narek-king
on 6 Apr 2017
An example of how to do this
Excel::create('ExcelFile', function($excel) {
$excel->sheet('Sheet1', function($sheet) {
ExampleModel::chunk(500, function($modelInstance) use($sheet) {
$modelAsArray = $modelInstance->toArray();
$sheet->appendRow($modelAsArray);
});
});
})->export('xls');
fpena
on 17 Oct 2017
👍1
@mpgbis-dinesh
It's not the bug, you are loading more data into memory so anyway it will throw errors. You will have to reduce the amount of data you take into memory. You have 360 column's so reduce chunk size to 100 or 200 then try. I did not use this package, I manually exported data to the client browser.
function export.txt
mohitpawar10
on 17 Nov 2017
👍2
@fpena Unfortunately I"m getting Undefined offset: 0 with you solution
netstudenton
on 20 Mar 2018
👍1
@netstudenton what line would that be?
fpena
on 20 Mar 2018
👍1
I'm fresh to laravel. I"m not sure which line. The browser debug doesn't tell it
I'm using the package version 2.1 and laravel 5.5
netstudenton
on 21 Mar 2018
Here is the correct version for the answer of @fpena
$reader->sheet('Basic Info', function ($sheet) {
BasicInfo::chunk(500, function ($modelInstance) use ($sheet) {
$sheet->fromArray($modelInstance);
});
farazirfan47
on 2 Nov 2018
Hi contact with your provider hosting, to request more time to max_execution_time.
because even if increase this value your provider needed that
RobertoBarajasVerdin
on 16 Jul 2020
Was this page helpful?
0 / 5 - 0 ratings
Related issues
wwendorf
路
3Comments
disto
路
3Comments
daraghoshea
路
3Comments
amine8ghandi8amine
路
3Comments
octoxan
路
3Comments
Most helpful comment
@mpgbis-dinesh
It's not the bug, you are loading more data into memory so anyway it will throw errors. You will have to reduce the amount of data you take into memory. You have 360 column's so reduce chunk size to 100 or 200 then try. I did not use this package, I manually exported data to the client browser.
function export.txt