Filtering data excel export in laravel example

Export an excel file after filtering data, in this post, you will understand how to export an excel file after filtering data from the database.

An Excel file will generate after filtering the data from the data, sometimes developers want to export an excel file after filtering data table data and want to export that filtering data only. When having a whole range of data in the database that time you must require specific filtering data.

In this post, I have the tech you how to integrate excel import and export in the laravel application using composer and configuration in the app, and how to export data from the database to an excel file with the heading using this post you will learn very simple easy steps.

Step 1. Install maatwebsite/excel Package

Install composer maatwebsite/excel, using this composer we will import and export Excel data from the database.

composer require maatwebsite/excel 

Step 2. Register Provider & Aliases

After successfully installing the composer we will register the plugin in the config/app.php file. Open the app.php file in your laravel application and there you will see providers and aliases attribute. just copy the below provider and aliases name and paste them into both attributes as in the below example given. The aliases will provide a global namespace to the register class in the application other wise we just need to give a full namespace to the excel class.

<?php

    'providers' => [
    Maatwebsite\Excel\ExcelServiceProvider::class
],

'aliases' => [
    'Excel' => Maatwebsite\Excel\Facades\Excel::class
],

If you wish to apply your custom configuration or want to modification in the excel setting in the application you can publish the vendor config file using the below provider command.

php artisan vendor:publish –provider=”Maatwebsite\Excel\ExcelServiceProvider” –tag=config

This command will create a config file in the config/excel.php directory, there you will see some custom modifications. you can do custom modifications from there.

Step 3. Create Export Route

Create a route file for the export excel file from the link. Just copy provided route name and paste it into your web.php file.

Route::get('sales-by-product-export',[SalesController::class,'salesByProductExport'])->name('sales.by.product.export');

Given the route link in a tag for the click to export excel file from the back, Once click on the link it will redirect to the controller from the route name and from there it will run the export define function and export file.

<a class="float-right btn btn-xs btn-primary float-right btn-flat"
      href="{{ route('sales.by.product.export',['date_range' => Request::get('date_range'),'product_name' => Request::get('product_name')]) }}">
      <i class="fa fa-download"></i> Export</a>

Create an export class using the below suggested command, it will create a given name export class in the "app/Export/" directory.

php artisan make:export ExportSalesProduct --model=SalesDetail

Use the below given command in your app command prompt and create a SalesController. You can create as per your requirement.

php artisan makes:controller SalesController

SalesController will export the excle file form the export collection class. import define an export class and add in the download method of the excel main class.

use Excel;
use App\Exports\ExportSalesProduct;
use Illuminate\Http\Request;

public function salesByProductExport(Request $request)
{
    return Excel::download(new ExportSalesProduct, 'productsales.xlsx');
}

This is my sales product export class, here I have filter order, finding data from the date range during the order collected also with the product name filter given, If $_GET method receives any request from the URL then it will filter the data else it will export whole table sales product collection from the database.

<?php

namespace App\Exports;

use App\Models\SalesDetail;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class ExportSalesProduct implements FromCollection,WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return SalesDetail::join('orders','orders.order_id','=','sales_details.order_id')
            ->whereIn('orders.order_status',[1,2])
            ->whereIn('orders.payment_status',[1,2])
            ->select(
                'product_id',
                'product_name',
                'varient',
                'color',
                'pack',
                'size',
                'flavour',
                'product_offer_price',
                'quantity'
            )
            ->when(isset($_GET['date_range']),function($query){
                $date_range = explode(' - ',$_GET['date_range']);
                $date['start'] = \Carbon\Carbon::parse($date_range[0])->format('Y-m-d H:i:s');
                $date['end'] = \Carbon\Carbon::parse($date_range[1])->addDays(1)->addSeconds(-1)->format('Y-m-d H:i:s');
                $query->whereBetween('order_date',[$date['start'],$date['end']]);
            })
            ->when(isset($_GET['product_name']),function($query){
                $query->where('product_name','like',"%{$_GET['product_name']}%");
            })
            ->get();
    }

    public function headings(): array
    {
        return [
            'PRODUCT ID',
            'PRODUCT NAME',
            'VARIENT',
            'COLOR',
            'PACK',
            'SIZE',
            'FLAVOUR',
            'PRODUCT OFFER PRICE',
            'QUANTITY'
        ];
    }
}

Dharmesh Chauhan

(Swapinfoway Founder)

Hello Sir, We are brothers origin from Gujarat India, Fullstack developers working together since 2016. We have lots of skills in web development in different technologies here I mention PHP, Laravel, Javascript, Vuejs, Ajax, API, Payment Gateway Integration, Database, HTML5, CSS3, and Server Administration. So you need our service Please Contact Us

Haresh Chauhan

(Co-Founder)