Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

noblemfd's avatar

How to Export to Excel Based on Filtered Data

Am working on a project in Laravel. I have a view blade, when I click on Export, it should do that based on filtered data. But it exported everything.

I am using Laravel 5.8 and "maatwebsite/excel": "^3.1"

Controller

public function userresponseReport(Request $request)
{
    $data['title'] = 'User Response';

    $userresponses = DB::table('user_response as g')
    ->select(
       //DB::raw('DATE(g.created_at) as created_date'),
       DB::raw('g.created_at as created_date'),
       'g.msisdn', 
       'g.game_code', 
       'g.answer',
       'g.answer_code',
       'g.Amount_charged',
       'g.payment_ref',
       'g.status',
       'g.user_channel'                
  )               
 ->orderByRaw('g.created_at DESC'); 

    $start_date = $request->start_date;
    $end_date = $request->end_date; 

    $render=[];  
    if(isset($request->start_date) && isset($request->end_date))
    {
        $userresponses=$userresponses->whereBetween('created_at',[$start_date.' 00:00:00',$end_date.' 23:59:59']);
        $render['start_date']=$request->start_date;
        $render['end_date']=$request->end_date;
    }elseif(isset($request->start_date))
    {
        $userresponses=$userresponses->where('created_at',$request->start_date);
        $render['start_date']=$request->start_date;
    }        
    if(isset($request->msisdn))
    {
        $userresponses=$userresponses->where('msisdn','like','%'.$request->msisdn.'%');
        $render['msisdn']=$request->msisdn;
    }
    if(isset($request->game_code))
    {
        $userresponses=$userresponses->where('game_code','like','%'.$request->game_code.'%');
        $render['game_code']=$request->game_code;
    }   
    if(isset($request->user_channel))
    {
        $userresponses=$userresponses->where('user_channel','like','%'.$request->user_channel.'%');
        $render['user_channel']=$request->user_channel;
    }        
    $userresponses= $userresponses->orderBy('created_at','DESC');
    $userresponses= $userresponses->paginate(15);
    $userresponses= $userresponses->appends($render);
    $data['userresponses'] = $userresponses;

return view('report.userresponseReport',$data);        
}

public function exportuserresponse() 
{
    return Excel::download(new UserresponseExport, 'userresponse.xlsx');
}  

Export

class UserresponseExport implements FromQuery, WithHeadings, ShouldAutoSize, WithEvents
{
use Exportable;

public function query()
{
    return UserResponse::query()->select(
    DB::raw("DATE(created_at)"),
    'msisdn',
    'game_code',
    'answer',
    'answer_code',
    'Amount_charged',
    'payment_ref',
    'status'
            );
}    

public function headings(): array
{
    return [
    'Date Created',
    'MSISDN',
    'game_code',
    'Answer',
    'AnswerCode',
    'Amount Charged',
    'Payment Ref.',
    'Status'
    ];
}   

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

    ];
}  
}    

I am using Maatwebsite for the Excel export.

View blade

<div class="row" style="margin-bottom: 10px">
    {{ Form::model(request(),['method'=>'get']) }}
    <div class="col-sm-2">
         {{ Form::text('msisdn',null,['class'=>'form-control','placeholder'=>'MSISDN']) }}
    </div>
    <div class="col-sm-2">
         {{ Form::text('game_code',null,['class'=>'form-control','placeholder'=>'Game Code']) }}
    </div>   
    <div class="col-sm-2">
         {{ Form::text('user_channel',null,['class'=>'form-control','placeholder'=>'Channel']) }}
    </div>          
    <div class="col-sm-2">
        {{ Form::date('start_date',null,['class'=>'form-control','placeholder'=>'Date']) }}
    </div>
    <div class="col-sm-2">
        {{ Form::date('end_date',null,['class'=>'form-control','placeholder'=>'Date']) }}
    </div>          
    <div class="col-xs-2">
        {{ Form::submit('Search',['class'=>'btn btn-warning']) }}
        <a href="{{ route('userresponseexport') }}" class="btn btn-primary"><i class="fa fa-file-excel-o"></i> Excel</a>
    </div>
    {{ Form::close() }}
</div>



<div class="box box-primary">
    <div class="box-header with-border">
<table class="table table-bordered table-hover table-striped table-condesed" id="commenter_info_table">
<caption></caption>
<thead>
    <tr>
        <td>#</td>
        <td>Date</td>
        <td>MSISDN</td>
        <td>Game Code</td>
        <td>Game Name</td>
        <td>Answer</td>
        <td>Channel</td>
    </tr>
</thead>
<tbody>
    @foreach($userresponses as $key => $userresponse)
        <tr>
            <td>{{ ++$key }}</td>
            <td>{{ date('Y-m-d h:i:s A', strtotime($userresponse->created_date)) }}</td>
            <td>{{ $userresponse->msisdn }}</td>
            <td>{{ $userresponse->game_code }}</td>
            <td> 
                @if($userresponse->game_code=='101')
                   Trivia
                @elseif($userresponse->game_code=='102')
                   Predict and Win 
                @elseif($userresponse->game_code=='103')
                   Party With the BBN 
                @elseif($userresponse->game_code=='104')
                   Grand Prize  
                @elseif($userresponse->game_code=='105')
                   Happy Hour    
                @elseif($userresponse->game_code=='106')
                   Power Boost                         
                @endif                       
            </td>                
            <td>{{ $userresponse->answer }}</td>
            <td>{{ $userresponse->user_channel }}</td>                                    
        </tr>          
    @endforeach
        <tr>
        <td colspan="14">
            {{ $userresponses->links() }}
        </td>
        </tr>         
    </tbody>

I want to export only filtered data, but it exports everything. How do I get this issue resolved that it will export only the filtered data and not everything in the table?

Thank you

0 likes
4 replies
deepu07's avatar

@noblemfd you need to write a custom blade file to fix this issue. jus send data from the controller to blade and display the data whatever you want in an excel file.

deepu07's avatar

@noblemfd //controller

<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Events\AfterSheet;
use Illuminate\Contracts\View\View;
use Maatwebsite\Excel\Concerns\FromView;
use Maatwebsite\Excel\Sheet;

Sheet::macro('styleCells', function (Sheet $sheet, string $cellRange, array $style) {
    $sheet->getDelegate()->getStyle($cellRange)->applyFromArray($style);
});


Sheet::macro('setOrientation', function (Sheet $sheet, $orientation) {
    $sheet->getDelegate()->getPageSetup()->setOrientation($orientation);
});

class DetailsExport implements FromView, ShouldAutoSize, WithEvents, WithTitle
{

    /**
     * @return View
     */
    public function view(): View
    {
	//write a query and pass data to view
        return view()
    }

    /**
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class    => function(AfterSheet $event) {
                $cellRange = 'A1:W1'; // All headers
                $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
                $to = $event->sheet->getDelegate()->getHighestRowAndColumn();
]
    }

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

deepu07's avatar

@noblemfd

blade file

<html>
    <head>
    </head>
    <body>
        <div class="row">
                <div class="table-responsive col-md-6">
                <h2>Header</h2>
                <table>
                    <tr>
                        <th>Type</th>
                        <th>First Name</th>
                        <th>Last Name</th>
                    </tr>
                    @foreach($datas as $data)
                            <tr>
                                <td>{{ $data->type }}</td>
                                <td>{{ $data->first_name }}</td>
                                <td>{{ $data->last_name }}</td>
                            </tr>
                        @endforeach
                </table>
            </div>
        </div>
    </body>
</html>

Please or to participate in this conversation.