@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.
Jun 26, 2020
4
Level 9
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
Please or to participate in this conversation.