Export Data in Excel and CSV format with Laravel Excel

Laravel Excel is a package which simplifies the import and export data in Laravel.

It allows exporting data in various format – xlsx, csv, xml, html, pdf, etc.

Require to create a separate class from where return data and set heading row.

In this tutorial, I am using it to export MySQL data in CSV and Excel format in Laravel project.

Table structure
Database Configuration
Install Laravel Excel Package
Model
Return Export Data
Controller
Route
View
Conclusion

1. Table structure

I am using users tables in the examples where stored some records –

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `name` varchar(80) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. 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=

3. Install Laravel Excel Package

Installing the package using composer.

Navigate to your project directory using the console and execute the following command.

composer require maatwebsite/excel

Create a UsersExport class.

Syntax –

php artisan make:export [class-name] --model=App\[model-name]

Execute command –

php artisan make:export UsersExport --model=App\Page

The Page Model create in next step.

4. Model

Create a Page Model.

php artisan make:model Page

To access the Database added use Illuminate\Support\Facades\DB;.

Create a single method –

getUsers() – Fetch all records from users table and return an Array.

Completed Code

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Page extends Model {

   // Fetch all users
   public static function getUsers(){

     $records = DB::table('users')->select('username','name','gender','email')->orderBy('id', 'asc')->get()->toArray();

     return $records;
   }

}

5. Return Export Data

Open app\Exports\UserExport.php file.

Add use Maatwebsite\Excel\Concerns\WithHeadings; to set heading row.

Also, need to implement WithHeadings Interface.

headings() – This is an abstract method. From here, return Array of header column names.
collection() – Fetch Array of records by calling Page::getUsers() and convert it to collect and return it.

NOTE – In the collection() method I am converting array to collection type by passing Array value to collect() because from here need to return collection type data.

Completed Code

<?php

namespace App\Exports;

use App\Page;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;

class UsersExport implements FromCollection,WithHeadings {

  public function headings(): array {
    return [
       "username","name","gender","email"
    ];
  }

  /**
  * @return \Illuminate\Support\Collection
  */
  public function collection() {

     return collect(Page::getUsers());
     // return Page::getUsers(); // Use this if you return data from Model without using toArray().
  }
}

6. Controller

Create a PagesController controller.

php artisan make:controller PagesController

Add use App\Exports\UsersExport; and use Maatwebsite\Excel\Facades\Excel;.

Create two methods –

index – Load index view.
export – This method call on <form > submit. Check which button gets clicked.

If exportexcel button gets clicked then return –

return Excel::download(new UsersExport, 'users.xlsx');

If exportcsv button gets clicked then return –

return Excel::download(new UsersExport, 'users.xlsx');

Syntax –

return Excel::download(new [Export-class-name], ['file-name']);

Completed Code

<?php

namespace App\Http\Controllers;

use App\Exports\UsersExport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Http\Request;

class PagesController extends Controller {

   public function index(){
      return view('index');
   }

   // Export data
   public function export(Request $request){

     if ($request->input('exportexcel') != null ){
        return Excel::download(new UsersExport, 'users.xlsx');
     }

     if ($request->input('exportcsv') != null ){
        return Excel::download(new UsersExport, 'users.csv');
     }

     return redirect()->action('PagesController@index');
   }
}

7. Route

Open routes/web.php file.

Here, define two routes –

/
/export – A post type route which uses in <form >.

<?php

Route::get('/', 'PagesController@index');
Route::post('/export', 'PagesController@export');

8. View

Create a new index.blade.php file in resources/views/ directory.

Create a <form > set method='post' and action='/export'.

Here, create two submit buttons –

The first button is used to export records in Excel format.
The second button is used to export records in CSV format.

Completed Code

<!DOCTYPE html>
<html>
   <head>
     <title>Export Data in Excel and CSV format with Laravel Excel</title>
   </head>
   <body>

     <form method='post' action='/export'>
       {{ csrf_field() }}
       <input type="submit" name="exportexcel" value='Excel Export'>
       <input type="submit" name="exportcsv" value='CSV Export'>
     </form>

   </body>
</html>

9. Conclusion

Customize your Array in the Model or in export class and update heading row in heading() method in the export class.

Make sure you need to return data in collection format from collection() method.

Learn about other formats from here.

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

Contents

1. Table structure

2. Database Configuration

3. Install Laravel Excel Package

4. Model

5. Return Export Data

6. Controller

7. Route

8. View

9. Conclusion