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();
    }
}