Customize the export to excel in laravel

I am trying to export the data to excel where data have eloquent and the below code work!

What Issue I have is, I am not able to figure out how to limit the data to current length, shown data on the page and how to filter data like if I have searched some database on a condition.

below code just export all to an excel sheet which as of now is ok, but in future, the length will give issue.


<?php

namespace App\Exports;

use App\User;
use Log;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Concerns\FromCollection;

class UsersExport implements FromQuery, WithHeadings, ShouldAutoSize, WithEvents, WithMapping {

    public function query()
    {
        $user = User::query()->with(['vehicles', 'documents.docType']);
        return $user;
    }

    public function map($user): array
    {
        return [
            $user->id,
            $user->name,
            $user->email,
            $user->phone,
            $user->gender,
            implode(', ',$user->vehicles->pluck('rc_number')->toArray()),
            implode(', ',$user->documents->pluck('docType.name')->toArray()),
            date('dS M, Y', strtotime($user->created_at)),
            date('dS M, Y', strtotime($user->updated_at)),
        ];
    }

    public function headings(): array
    {
        return [
            '#',
            'Name',
            'Email',
            'Phone',
            'Gender',
            'Vehicles',
            'Documents',
            'Created On',
            'Updated On',
        ];
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
                $cellRange = 'A1:W1'; // All headers
                $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(12);
                $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setBold(true);
            },
        ];
    }
}

please don't tell me to export from FormView as that's not possible for me at this stage or later!

FormQuery is what I can use so to this how should I work with this?

bobbybouwmann

6 years ago

Level 88

Well, you need to update your query for this right? For example

class UsersExport implements FromQuery, WithHeadings, ShouldAutoSize, WithEvents, WithMapping {
{
	private $active;

	public function __construct($active = true)
	{
		$this->active = $active;
	}

	public function query()
	{
        	$user = User::query()->with(['vehicles', 'documents.docType']);

		$user->where('active', $active ? 1 : 0);

		return $user;
	}
}

You can then call the expect method using this constructor

return (new UsersExport(true))->download('active-users.xlsx');

return (new UsersExport(false))->download('inactive-users.xlsx');

Documentation: https://docs.laravel-excel.com/3.0/exports/from-query.html#customizing-the-query

princeoo7

6 years ago

Level 3

What if I have to pass a complex condition ( multiple conditions ), should i declare all variables expected to be in construct or just las the request as query itself ?

Please or to participate in this conversation.