Laravel - Excel take a lot of memories and time when I tried export a large file using chunk and queue

Question

In controller

        $exporter = new ReportExport($this->validatedData, $this->id);
        $exporter->queue($file_name,
            'gallery', \Maatwebsite\Excel\Excel::XLSX)->chain([
                new ExportEnrollmentsJobWhenDone($this->id),
            ]);

ReportExport

class ReportExport implements FromQuery, WithHeadings, WithEvents, WithCustomChunkSize, ShouldQueue
{
    use Exportable;

    protected $progress;
    protected $progressIncrement;
    public $totalRows;


    protected $validatedData;

    protected $id;

    public function __construct($validatedData, $id)
    {

        $this->validatedData = $validatedData;
        $this->id = $id;

        $this->progress = 0;
    }


    public function headings(): array
    {

        return [
            //My Headings
        ];
    }


    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            //My Events
        ];
    }

    public function query()
    {
        //My Queries
        $this->totalRows = $enrollments->count();

        $this->progressIncrement = 100 / ceil($this->totalRows / $this->chunkSize());

        return $enrollments->orderBy('id', 'desc');

    }

    public function chunkSize(): int
    {
        return 5000;
    }
    

}

Terminal logs:

 2023-07-09 13:06:49 Maatwebsite\Excel\Jobs\QueueExport ............. RUNNING
  2023-07-09 13:06:49 Maatwebsite\Excel\Jobs\QueueExport ....... 117.01ms DONE
  2023-07-09 13:06:50 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:06:56 Maatwebsite\Excel\Jobs\AppendQueryToSheet  6,822.89ms DONE
  2023-07-09 13:06:57 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:07:08 Maatwebsite\Excel\Jobs\AppendQueryToSheet  10,715.10ms DONE
  2023-07-09 13:07:09 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:07:23 Maatwebsite\Excel\Jobs\AppendQueryToSheet  14,725.47ms DONE
  2023-07-09 13:07:24 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:07:43 Maatwebsite\Excel\Jobs\AppendQueryToSheet  18,818.70ms DONE
  2023-07-09 13:07:44 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:08:07 Maatwebsite\Excel\Jobs\AppendQueryToSheet  22,557.33ms DONE
  2023-07-09 13:08:08 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:08:34 Maatwebsite\Excel\Jobs\AppendQueryToSheet  26,214.35ms DONE
  2023-07-09 13:08:35 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:09:05 Maatwebsite\Excel\Jobs\AppendQueryToSheet  29,926.11ms DONE
  2023-07-09 13:09:06 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:09:40 Maatwebsite\Excel\Jobs\AppendQueryToSheet  33,737.76ms DONE
  2023-07-09 13:09:41 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:10:18 Maatwebsite\Excel\Jobs\AppendQueryToSheet  37,834.62ms DONE
  2023-07-09 13:10:20 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:11:02 Maatwebsite\Excel\Jobs\AppendQueryToSheet  42,433.48ms DONE
  2023-07-09 13:11:03 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:11:49 Maatwebsite\Excel\Jobs\AppendQueryToSheet  45,406.48ms DONE
  2023-07-09 13:11:49 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:12:43 Maatwebsite\Excel\Jobs\AppendQueryToSheet  53,667.69ms DONE
  2023-07-09 13:12:44 Maatwebsite\Excel\Jobs\AppendQueryToSheet ...... RUNNING
  2023-07-09 13:13:43 Maatwebsite\Excel\Jobs\AppendQueryToSheet  58,882.30ms DONE
  2023-07-09 13:13:44 Maatwebsite\Excel\Jobs\CloseSheet .............. RUNNING

   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:395
    391▕         }
    392▕         $column = 0;
    393▕         $row = '';
    394▕         sscanf($cellCoordinate, '%[A-Z]%d', $column, $row);
  ➜ 395▕         $this->index[$cellCoordinate] = (--$row * self::MAX_COLUMN_ID) + Coordinate::columnIndexFromString((string) $column);
    396▕ 
    397▕         $this->currentCoordinate = $cellCoordinate;
    398▕         $this->currentCell = $cell;
    399▕         $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:395
    391▕         }
    392▕         $column = 0;
    393▕         $row = '';
    394▕         sscanf($cellCoordinate, '%[A-Z]%d', $column, $row);
  ➜ 395▕         $this->index[$cellCoordinate] = (--$row * self::MAX_COLUMN_ID) + Coordinate::columnIndexFromString((string) $column);
    396▕ 
    397▕         $this->currentCoordinate = $cellCoordinate;
    398▕         $this->currentCell = $cell;
    399▕         $this->currentCellIsDirty = true;

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

I tried to export data to an Excel file using the Maatwebsite\Excel package in Laravel. When I export a large file, I notice that each AppendQueryToSheet takes more time than before. However, I encountered a memory exhaustion error during the export process, which caused the script to terminate prematurely.

hossein emami · Accepted Answer · 2023-10-22 06:53:38Z

1

It seems that your memory_limit parameter is too low. you can increase it in your php.ini file.

for example, change the amount from:

; Maximum amount of memory a script may consume
; http://php.net/memory-limit
memory_limit = 4000M

to:

; Maximum amount of memory a script may consume
; http://php.net/memory-limit
memory_limit = 10000M

and you can set an unlimited amount for it by changing this parameter to:

; Maximum amount of memory a script may consume
; http://php.net/memory-limit
memory_limit = -1

Maybe is not a good idea, you can set the memory limit just for the method in the controller:

    ini_set('memory_limit', '-1');        
    $exporter = new ReportExport($this->validatedData, $this->id);
    $exporter->queue($file_name,
        'gallery', \Maatwebsite\Excel\Excel::XLSX)->chain([
            new ExportEnrollmentsJobWhenDone($this->id),
        ]);

edited Oct 22, 2023 at 6:53

answered Oct 21, 2023 at 13:36

hossein emami

4064 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel - Excel take a lot of memories and time when I tried export a large file using chunk and queue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related