How to export large datasets to CSV in Laravel?

Exporting large datasets in Laravel can be challenging, as you need to be mindful of memory limits and processing time.

Have you seen this error message when exporting large datasets in Laravel?
Allowed memory size of 134217728 bytes exhausted (tried to allocate 20971520 bytes)

This was a problem I struggled with for a long time using the Laravel Excel package until I found spatie/simple-excel, which makes this very easy.

The example below will export a list of users into a CSV quickly and efficiently without exhausting the memory. I have used this method with over 200k records generating a CSV file of over 30MB in a few minutes.

<?php

namespace App\Http\Controllers;

use App\User;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use Spatie\SimpleExcel\SimpleExcelWriter;

class UserExportController extends Controller
{
    public function export()
    {
        $writer = SimpleExcelWriter::streamDownload('users.csv');
        $query = User::orderBy('created_at');
        
        $i = 0;
        foreach ($query->lazy(1000) as $user) 
        {
            $writer->addRow($user->toArray());

            if ($i % 1000 === 0) {
                flush(); // Flush the buffer every 1000 rows
            }
            $i++;
        }

        return $writer->toBrowser();
    }
}

Exporting  in Laravel Livewire

The method mentioned above does not work in Laravel Livewire. I attempted several methods suggested on GitHub that were supposed to work, but none of them worked for me.

To resolve this issue, I created a new endpoint using a controller that is not a Livewire component and implemented the method above. The download button in the Livewire component now links to this endpoint, passing any required parameters, and the download starts immediately.

Check sample code below!

<?php
// Route
Route::get('/campaigns/{campaignId}/recipients/download/{status?}', ['as' => 'uni.api.campaigns.recipients.download', 'uses' => 'Uni\Api\Campaigns\CampaignRecipientsDownloadController@index']);

<?php
// Controller

namespace Papillon\Http\Controllers\Uni\Api\Campaigns;

use Illuminate\Support\Str;
use Papillon\Campaigns\Campaign;
use Papillon\Campaigns\CampaignReporting;
use Spatie\SimpleExcel\SimpleExcelWriter;

class CampaignRecipientsDownloadController
{
	protected $campaign;
	protected $status = null;

	public function index($campaignId, $status = null)
	{
		$this->campaign = Campaign::findOrFail($campaignId);
		$this->status = $status;

		$fileName = Str::slug($this->campaign->name) . '-' . $this->status . '-' . now()->format('Ymd_His') . '.csv';
		$writer = SimpleExcelWriter::streamDownload($fileName);

		
		$i = 0;
		foreach ($this->getRecipientsList()->lazy(150) as $recipient) {
			$writer->addRow([
				'id' => $recipient->id,
				'email' => $recipient->email,
				'first_name' => $recipient->first_name,
				'last_name' => $recipient->last_name,
				'job_title' => $recipient->job_title,
				'company' => $recipient->company,
				'country' => $recipient->country,
				'status' => $recipient->status,
				'opened' => $recipient->opened ? 'Yes' : 'No',
				'clicked' => $recipient->click_count
			]);

			if ($i % 1000 === 0) {
				flush(); // Flush the buffer every 1000 rows
			}
			$i++;
		}


		return $writer->toBrowser();
	}

	private function getRecipientsList()
	{
		return resolve(CampaignReporting::class)->getRecipientsList($this->campaign, true, true);
	}
}

<!-- Download button -->
<x-wave-toolbar.link
    icon-set="material-symbols"
    icon="download"
    x-tooltip="`Export recipients`"
    :href="route('uni.api.campaigns.recipients.download', [$this->campaign->id, $this->recipientStatus])"
></x-wave-toolbar.link>