Laravel Excel - Error Allowed memory size

Question

I use Maatwebsite to load excel files. I'm loading fine several documents, until it gives the next error:

PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes) in /project1/vendor/phpoffice/phpexcel/Classes/PHPExcel/Cell.php on line 582

I'm went to config/excel.php and update memoryCacheSize setting to 128MB. After do that I run php artisan cache:clear and php artisan config:cache. Try load again and returns me the same error.

How can I solve that?

Answer 1

I would recommend using box/spout for larger files. I have used both and typically use Maatwebsite for smaller files because of the flexibility of file types.

https://github.com/box/spout

Answer 2

Add this in your index.php file.

ini_set('memory_limit', '-1');

Answer 3

You have to increase the memory limit of PHP. You have many ways to do this.

First if you want to check the actual memory limit you need to create a PHP file (we can name it as php.php) or put it in an action inside any controller, and put following code in it:

<?php phpinfo(); exit;?>

First way to change memory_limit, this way modify the momeoty_limit for all proyects running in PHP:

Firstly you need find your php.ini, this data is in the phpinfo();

1. Edit php.ini .Search "memory_limit" in your php.ini, and change the value of it. If no "memory_limit" found, add the following line at the end of php.ini memory_limit = 128M ; /* Change the 128M to your needs */ Save file.

2. Reset apache.

   sudo service apache2 restart

Second option is to put it in the index.php inside your laravel proyect as @Giri Annamalai M tell you above.

ini_set('memory_limit', '-1');

This option will modify the memory limit to all proyect... If you put -1, this means that there is no limit.(dependes in compute memory)

Other option is to indicate the memory limit in the controller, inside the action indicete the memory limit:

ini_set('memory_limit', '-1');

Remember that -1 is always NO LIMIT. Maybe this is no the best secure and performance way to indicate the memory limit. You have other ways to edit the memory like the .htaccess... But i think that the 3rd is one of the best in this ocation.

Answer 4

In 2021 with version 3.1 non of this works.

I got it working with WithChunkReading.

Answer 5

I did some debugging to see what scenarios decreased the memory usage of Laravel Excel.

My best results where using a DTO, one that only contained simple variable types. Casting objects like Carbon or BigDecimal to string also saved some memory usage.

in the Export you can remove the withMapping trait because the DTO properties will do that trick for you. If you want you can change the order of the DTO's properties.

And remove the ShouldAutoSize trait, as suggested by the maintainer

e.g.

class Row {
  public int $id;
  public string $created_at;
}

class UserExport implements FromCollection
{
    public function collection()
    {
        $users = User::query()->all();

        return $users->map(
            function ($user) {
                $row = new Row();
                $row->id = $user->id;
                // cast objects like Carbon or BigDecimal to string
                $row->created_at = $entry->created_at->format('d-m-Y');

                return $row;
            }
        );
    }
}