How to export large number of rows using laravel excel

Question

I need to export huge amount of rows to Excel. I am using Laravel-excel. I followed all the suggestions given in documentation for exporting large database.

// routes/web.php
Route::post('/dashboard/export', [DashboardController::class, 'export'])->middleware('auth');

// app/Http/Controllers/DashboardController.php
class DashboardController extends Controller
{

    public function export(Request $request)
    {
        $filename = str::random(20).".xlsx";
        (new UsersExport($request->all()))->queue($filename, 'public')->chain([
            new NotifyUserOfCompletedExcelExport(request()->user(),$filename ),
        ]);
        
        return json_encode([
            'message' => "You will receive email with download link once export is complete."
        ]);
        
    }
}


// app/Exports/UsersExport.php
class UsersExport implements FromCollection, WithHeadings
{
    use Exportable;
    public function __construct($config)
    {
        
        $this->config = $config;
    }
    
    public function collection()
    {
        $mains = DB::table('mains')->select('name', 'email', 'designation', 'country', 'university', 'discipline', 'subject', 'school_or_department');
        


        //filter one
        $one = $this->config['filterOne']['column'];
        if ($one != null) {
            // dd($one);
            $mains = $mains->where($one, 'like', $this->config['filterOne']['value'] . "%");
        }

        $mains = $mains->offset($this->config['page'] * $this->config['num_entries_per_page']);
        $mains = $mains->limit($this->config['num_entries_per_page']);
        $mains = $mains->where('delete', 0);
        return $mains->get();
    }
    
    public function headings(): array
    {
        return [
            'NAME', 'EMAIL', 'DESIGNATION', 'COUNTRY', 'UNIVERSITY', 'DISCIPLINE', 'SUBJECT', 'SCHOOL_OR_DEPARTMENT'
        ];
    }
}

Clearly you can see , I am queuing the Export. I run the command in terminal.

php artisan queue:work

It gives output as below for large number of rows(30000), then queue:work exits.

[2021-11-12 05:13:56][151] Processing: Maatwebsite\Excel\Jobs\QueueExport
[2021-11-12 05:14:17][151] Processed:  Maatwebsite\Excel\Jobs\QueueExport
[2021-11-12 05:14:25][152] Processing: Maatwebsite\Excel\Jobs\AppendDataToSheet
[2021-11-12 05:14:39][152] Processed:  Maatwebsite\Excel\Jobs\AppendDataToSheet
.
.
.
.
[2021-11-12 05:17:48][167] Processed:  Maatwebsite\Excel\Jobs\AppendDataToSheet

I thought about changing php memory limit in php.ini file

memory_limit = 512M

Still it does not work.

For small number of rows queue:work is working properly (does not exits) and the output is given below.

[2021-11-12 03:21:19][112] Processing: Maatwebsite\Excel\Jobs\QueueExport
[2021-11-12 03:21:22][112] Processed:  Maatwebsite\Excel\Jobs\QueueExport
[2021-11-12 03:21:24][113] Processing: Maatwebsite\Excel\Jobs\AppendDataToSheet
[2021-11-12 03:21:26][113] Processed:  Maatwebsite\Excel\Jobs\AppendDataToSheet
[2021-11-12 03:21:27][114] Processing: Maatwebsite\Excel\Jobs\AppendDataToSheet
[2021-11-12 03:21:28][114] Processed:  Maatwebsite\Excel\Jobs\AppendDataToSheet
[2021-11-12 03:21:29][115] Processing: Maatwebsite\Excel\Jobs\CloseSheet
[2021-11-12 03:21:30][115] Processed:  Maatwebsite\Excel\Jobs\CloseSheet
[2021-11-12 03:21:30][116] Processing: Maatwebsite\Excel\Jobs\StoreQueuedExport
[2021-11-12 03:21:31][116] Processed:  Maatwebsite\Excel\Jobs\StoreQueuedExport
[2021-11-12 03:21:31][117] Processing: App\Jobs\NotifyUserOfCompletedExcelExport
[2021-11-12 03:21:35][117] Processed:  App\Jobs\NotifyUserOfCompletedExcelExport

My question is :-

-> How can i export such a large number of rows?

-> What am i doing wrong?

-> Is there any alternative ways of achieving the same?

-> Should i create a separate service to export data in excel using some other language?

-> Is it a good idea to do such heavy processing on production front-end server?

score 4 · Accepted Answer · answered Nov 12 '21 at 08:42

Look at your queue's timeout value. From the documentation at https://laravel.com/docs/8.x/queues#job-expirations-and-timeouts :

"The queue:work Artisan command exposes a --timeout option. If a job is processing for longer than the number of seconds specified by the timeout value, the worker processing the job will exit with an error. Typically, the worker will be restarted automatically by a process manager configured on your server."

Jobs involving shorter numbers of rows look to be completing within the timeout value. Longer ones are not - much as with php.ini and max_execution times, if the job takes too long the system worries that it's broken, in some way, and terminates the job.

"sudo php artisan queue:work --memory 256" . worked for me. Thanks. — Abhinav Keshri, Nov 12 '21 at 10:30
I would suggest changing the timeout setting as well. Increasing memory has a similar effect, but it will make it more robust in the future if you know that you're going to have long-running processes. — Giles Bennett, Nov 12 '21 at 10:34

How to export large number of rows using laravel excel

1 Answers1