How to implement Laravel Excel for exporting data

Laravel Excel allows exporting data in various formats like – XLSX, CSV, XLS, HTML, etc.

1. Install Package

Install the package using composer –

composer require maatwebsite/excel

2. Update app.php

Open config/app.php file.

Add the following Maatwebsite\Excel\ExcelServiceProvider::class in 'providers' –

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

Add the following 'Excel' => Maatwebsite\Excel\Facades\Excel::class in 'aliases' –

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

3. Publish Package

Run the command –

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

This will create a new excel.php file in config/.

4. Create Export class

Rune the following command to create an export class.

php artisan make:export SalesDetailExport

Open app/Exports/EmployeesExport.php file.

Class has 2 methods –

• collection() – Load export data. Here, you can either –
  • Return all records.
  • Return specific columns or modify the return response which I did in the next Export class.
• headings() – Specify header row.

NOTE – Remove headings() methods if you don’t want to add a header row.

Complete Class Example:

<?php

namespace App\Exports;

use App\Models\Order;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class SalesDetailExport implements FromCollection, WithHeadings
{
    private $dateFrom, $dateTo;

    public function __construct($dateFrom, $dateTo)
    {
        $this->dateFrom = $dateFrom;
        $this->dateTo = $dateTo;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        $dateFrom = $this->dateFrom;
        $dateTo = $this->dateTo;

        return Order::join('order_details', 'orders.id', '=', 'order_details.order_id')
            ->join('items', 'items.id', '=', 'order_details.item_id')
            ->join('brands', 'brands.id', '=', 'items.brand_id')
            ->join('categories', 'categories.id', '=', 'items.category_id')
            ->leftJoin('sites', 'sites.id', '=', 'orders.site_id')
            ->leftJoin('distributors', 'distributors.id', '=', 'orders.distributor_id')
            ->select('orders.order_date', 'sites.name as site_name', 'distributors.name as distributor_name',
                'items.prod_line', 'categories.category', 'brands.brand', 'items.code','items.description',
                DB::raw('SUM(qty_shipped) AS qty_pc'),
                DB::raw('SUM(qty_shipped*items.um_factor) AS qty_kg'),
                DB::raw('SUM(qty_shipped*net_price) AS db_value')
            )
            ->whereBetween('orders.order_date', [$dateFrom, $dateTo])
            ->groupBy(['orders.order_date', 'orders.site_id', 'orders.distributor_id', 'items.id'])
            ->get();

    }

    public function headings(): array
    {
        return [
            'Order Date',
            'Site',
            'Distributor',
            'Prod Line',
            'Category',
            'Brand',
            'Item',
            'Description',
            'Qty PC',
            'Qty KG',
            'DB Value'
        ];
    }
}

5. Define Routes

Open routes/web.php file.

Define following routes –

Route::get(‘/export’, ‘SalesManager\ExportController@index’)->name(‘export’); // This is for the navigation

Route::get(‘/export/export-details’, ‘SalesManager\ExportController@salesDetails’)->name(‘export-details’); // This is the landing page from where the export will be triggered

Route::post(‘/export/sales-details-csv’, ‘SalesManager\ExportController@exportSalesDetailsCSV’)->name(‘export.sales-details-csv’); // This will download the file

6. Create Controller to execute the Export

Create ExportController Controller.

<?php

namespace App\Http\Controllers\SalesManager;

use App\Exports\SalesDetailExport;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class ExportController extends Controller
{
    public function index(){
        return view('sales-manager.export.index');
    }

    public function salesDetails(){
        return view('sales-manager.export.sales-details');
    }

    // CSV Export
    public function exportSalesDetailsCSV(Request $request){
        $date_from = $request->date_from;
        $date_to = $request->date_to;
        $file_name = 'sales_details_'.date('Y_m_d_H_i_s').'.csv';
        return Excel::download(new SalesDetailExport($date_from, $date_to), $file_name);
    }
}