Multiple Sheets Excel Export in Laravel

#excel #php #laravel #webdev

This example uses the maatwebsite/excel package.

composer require maatwebsite/excel:^3.1

1. Route

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

2. Simple View

Date input and a button that triggers the download.

<div class="mb-4 form-group col-lg-4">
    <label class="form-label">Start Date</label>
    <input type="text" name="start_date" id="start_date" class="form-control" required>
</div>
<div class="mb-4 form-group col-lg-4">
    <label class="form-label">End Date</label>
    <input type="text" name="end_date" id="end_date" class="form-control" required>
</div>

<button type="button" class="btn btn-primary exportExcel">Excel</button>

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

3. Controller

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

4. Export (Multiple Sheets)

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'),
        ];
    }
}

5. Sheet Class

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

DEV Community