How to Export data to CSV & Excel format in Laravel 8

Last updated on by

In Laravel, you don’t have to write long code to export your data, there is already a package available for that – Laravel Excel.

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

In this tutorial, I show how you can export MySQL database data in CSV & Excel format using the Laravel Excel package in Laravel 8.

How to export data to CSV & Excel format in Laravel 8

Demo

Contents

  1. Install Package
  2. Update app.php
  3. Publish package
  4. Database Configuration
  5. Create Table
  6. Model
  7. Create Export class
  8. Route
  9. Controller
  10. View
  11. Demo
  12. Conclusion

1. Install Package

Requirement –

  • PHP: ^7.2\|^8.0
  • Laravel: ^5.8
  • PhpSpreadsheet: ^1.21
  • psr/simple-cache: ^1.0
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled
  • PHP extension php_iconv enabled
  • PHP extension php_simplexml enabled
  • PHP extension php_xmlreader enabled
  • PHP extension php_zlib enabled

Install the package using composer –

composer require maatwebsite/excel

If you are getting an error while executing the above command then execute the below command –

composer require psr/simple-cache:^1.0 maatwebsite/excel

After that again execute –

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. Database Configuration

Open .env file.

Specify the host, database name, username, and password.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=tutorial
DB_USERNAME=root
DB_PASSWORD=

5. Create Table

  • Create a new table Employees using migration and add some records.
php artisan make:migration create_employees_table
  • Now, navigate to database/migrations/ folder from the project root.
  • Find a PHP file that ends with create_employees_table and open it.
  • Define the table structure in the up() method.
public function up()
{
    Schema::create('employees', function (Blueprint $table) {
        $table->id();
        $table->string('username');
        $table->string('name');
        $table->string('email');
        $table->smallInteger('age');
        $table->timestamps();
    });
}
  • Run the migration –
php artisan migrate
  • The table is been created and add some records in it.

6. Model

  • Create Employees Model.
php artisan make:model Employees
  • Open app/Models/Employees.php file.
  • Specify mass assignable Model attributes – username, name, email, and age using the $fillable property.

Completed Code

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Employees extends Model
{
    use HasFactory;

    protected $fillable = [
       'username','name','email','age'
    ];
}

7. Create Export class

I am creating 2 export classes just for example purpose –

1. EmployeesExport Class –

php artisan make:export EmployeesExport --model=Employees
  • 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.

Completed Code

<?php

namespace App\Exports;

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

class EmployeesExport implements FromCollection, WithHeadings
{

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 1. Export all data
        // return Employees::all();

        ## 2. Export specific columns
        return Employees::select('id','username','name')->get();

    }

    public function headings(): array
    {
        return [
          '#',
          'Username',
          'Name'
        ];
    }
}

2. EmployeesByAgeExport Class –

php artisan make:export EmployeesByAgeExport --model=Employees
  • Open app/Exports/EmployeesByAgeExport.php file.
  • In this class, I added __construct() to allow a parameter send while creating class instance and use it to retrieve data.
  • After initializing $result Array pass $result in collect() for return – return collect($result);
  • Mention header row names in the heading() method.

Completed Code

<?php

namespace App\Exports;

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

class EmployeesByAgeExport implements FromCollection, WithHeadings
{
    private $agegreaterthan;

    public function __construct($age=0) 
    {
        $this->agegreaterthan = $age;
    }

    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        ## 3. Conditional export and customize result
        $records = Employees::select('*')->where('age','>',$this->agegreaterthan)->get();

        $result = array();
        foreach($records as $record){
           $result[] = array(
              'id'=>$record->id,
              'username' => $record->username,
              'name' => $record->name,
              'email' => $record->email,
              'age' => $record->age,
              'status' => 1 // Custom data
           );
        }

        return collect($result);
    }

    public function headings(): array
    {
       return [
         '#',
         'Username',
         'Name',
         'Email',
         'Age',
         'Status'
       ];
    }
}

8. Route

  • Open routes/web.php file.
  • Define 4 routes –
    • / – Load index view.
    • employees/exportcsv – Export data in CSV format.
    • employees/exportexcel – Export data in Excel format.
    • employees/exportbyagecsv – POST route to export data by age.

Completed Code

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\EmployeesController;

Route::get('/', [EmployeesController::class, 'index'])->name('home'); 
Route::get('employees/exportcsv', [EmployeesController::class, 'exportCSV'])->name('employees.exportcsv');
Route::get('employees/exportexcel', [EmployeesController::class, 'exportExcel'])->name('employees.exportexcel');
Route::post('employees/exportbyagecsv', [EmployeesController::class, 'exportByAgeCSV'])->name('employees.exportbyagecsv');

9. Controller

  • Create EmployeesController Controller.
php artisan make:controller EmployeesController
  • Open app/Http/Controllers/EmployeesController.php file.
  • Import EmployeesExport,  EmployeesByAgeExport and Excel.
  • Create 4 methods –
    • index() – Load index view.
    • exportCSV() –  To export data call Excel::download().

It takes 2 parameters –

    1. Class instance.
    2. File name with extension.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesExport, $file_name);
    • exportExcel() – Using the same above code and change the file extension to .xlsx.
    • exportByAgeCSV() – Read POST age value and pass it with EmployeesByAgeExport($age) instance.
$file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv'; 
return Excel::download(new EmployeesByAgeExport($age), $file_name);

Completed Code

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Exports\EmployeesExport;
use App\Exports\EmployeesByAgeExport;
use Excel;

class EmployeesController extends Controller
{
    public function index(){
       return view('index');
    }

    // CSV Export
    public function exportCSV(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Excel Export
    public function exportExcel(){
       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.xlsx';
       return Excel::download(new EmployeesExport, $file_name);
    }

    // Conditional Export (csv)
    public function exportByAgeCSV(Request $request){

       $age = $request->age;

       $file_name = 'employees_'.date('Y_m_d_H_i_s').'.csv';
       return Excel::download(new EmployeesByAgeExport($age), $file_name);
    }
}

10. View

Create index.blade.php file in resources/views/ folder.

Create 2 anchor elements –

  1. Set href to {{ route('employees.exportcsv') }} for CSV export.
  2. Set href to {{ route('employees.exportexcel') }} for Excel export.

Create a <form method='post' action="{{ route('employees.exportbyagecsv') }}">. Add input element to enter age and a submit button.

Completed Code

<!DOCTYPE html>
<html>
<head>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1"> 
   <title>How to Export data to CSV & Excel format in Laravel 8</title>

   <!-- CSS -->
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" >
</head>
<body>

   <div class="container mt-5">

      <a class="btn btn-primary" href="{{ route('employees.exportcsv') }}">CSV Export</a> &nbsp;&nbsp;
      <a class="btn btn-primary" href="{{ route('employees.exportexcel') }}">Excel Export</a><br><br>

      <form method='post' action="{{ route('employees.exportbyagecsv') }}">
         @csrf
         <div class="mb-3">
            <label for="age" class="form-label">Age</label>
            <input type="number" class="form-control" id="age" name="age" value="0">
         </div>

         <button type="submit" class="btn btn-success">Export</button>
      </form>
   </div>

</body>
</html>

11. Demo

View Demo

12. Conclusion

Use constructor in Export class to handle passed parameters and use it to fetch data.

In this tutorial, I only mentioned 2 export formats – CSV, and Excel but there is more format available. You can learn more about it from here.

You can view this tutorial to know how to import data using the Laravel Excel package.

If you found this tutorial helpful then don't forget to share.

Leave a Comment

© 2016-2024 Makitweb, All rights reserved