Multiple Sheets Excel Export in Laravel

Source: Dev.to

---

⚠️ Collection Error: Content refinement error: Error: 429 “you (bkperio) have reached your weekly usage limit, upgrade for higher limits: https://ollama.com/upgrade (ref: 04a94fa3-416d-41f1-92a3-1764ae858e4d)”

---

This example uses the maatwebsite/excel package. composer require maatwebsite/excel:^3.1

Route::get(‘/export-excel’, [App\Http\Controllers\MyController::class, ‘excel’])->name(‘export-excel’);

Date input and a button that triggers the download.

Start Date


End Date

Excel

$(document).on(‘click’, ‘.exportExcel’, function() { var start = $(‘#start_date’).val(); var end = $(‘#end_date’).val();

window.location.href = {{ route(‘export-excel’) }}?start=${start}&end=${end}; });

Note: if the query is taking long, then its better to separate the sheets. Just create separate files and try to use FromQuery and WithChunkReading. use Illuminate\Http\Request; use Maatwebsite\Excel\Facades\Excel; use App\Excels\Exports\MyExport; use Illuminate\Support\Facades\DB;

class MyController extends Controller { public function excel(Request $request) { $start = $request->start_date; $end = $request->end_date;

    // Sheet 1
    $sheet1 = DB::table('users')
        ->select('name','email')
        ->whereBetween('created_at', [$start, $end])
        ->get()
        ->map(fn($r) => [$r->name, $r->email]);

    // Sheet 2
    $sheet2 = DB::table('orders')
        ->select('invoice','total')
        ->whereBetween('created_at', [$start, $end])
        ->get()
        ->map(fn($r) => [$r->invoice, $r->total]);

    return Excel::download(new MyExport($sheet1, $sheet2), 'report.xlsx');
}

}

namespace App\Excels\Exports;

use Maatwebsite\Excel\Concerns\WithMultipleSheets; use App\Excels\Exports\Sheets\MySheet;

class MyExport implements WithMultipleSheets { public function __construct( protected $sheet1, protected $sheet2 ) {} public function sheets(): array { return [ new MySheet($this->sheet1, ‘Users’), new MySheet($this->sheet2, ‘Orders’), ]; } }

Each instance represents one Excel tab. namespace App\Excels\Exports\Sheets;

use Maatwebsite\Excel\Concerns\FromArray; use Maatwebsite\Excel\Concerns\WithTitle; use Illuminate\Support\Collection; class MySheet implements FromArray, WithTitle { public function __construct( protected $data, protected $title ) {}

public function array(): array
{
    if ($this->title === 'Users') {
        return [
            ['NAME', 'EMAIL'],
            ...$this->data->toArray(),
        ];
    }

    return [
        ['INVOICE', 'TOTAL'],
        ...$this->data->toArray(),
    ];
}

public function title(): string
{
    return $this->title;
}

}

Need help building your app? I’m available for freelance web & Android development — raflizocky.netlify.app ☕ Support my writing: paypal.me/raflizocky · saweria.co/raflizocky