Laravel 6 Export In Excel and CSV Data Example
In this tutorial, we will see Laravel 6 Export In Excel and CSV Data Example using the maatwebsite/excel 3.1 packages. In the general Laravel project, we always need to import or export the rows from the database. The maatwebsite/excel makes it very easy to import-export data in Laravel.
Overview of Export in Laravel
Content Overview
- 1 Overview of Export in Laravel
- 2 Export Data In Excel and CSV in Laravel 6
- 3 Laravel Excel 3.1
- 4 Laravel Excel Features
- 5 Requirements
- 6 Step 1: Installation
- 7 Step 2: Configure package
- 8 Step 3: Create model and migration files
- 9 Step 4: Create a controller and routes
- 10 Step: 5 Create a form blade file for input the data
- 11 Step: 7 Create a view file to display the data.
- 12 Step 8: Create Exports class
- 13 Step 9: Write the export function
- 14 Exporting collections in CSV in Laravel 6
- Step 1: Install the maatwebsite/excel package using the composer.
- Step 2: Configure the package.
- Step 3: Create a model and migration files.
- Step 4: Create a controller and routes.
- Step 5: Create a view file and inside the file, create an HTML form.
- Step 6: Create a view file to display the data to the user.
- Step 7: Create an export class.
- Step 8: Write the export function inside the controller file.
Export Data In Excel and CSV in Laravel 6
If you want to up and running with basic laravel functionality, then go to my other article on this web blog called Laravel 6 Crud Example From Scratch.
If you want to Generate PDF In Laravel, then check out Laravel 6 Generate PDF From View Example. For this example, we use the package called maatwebsite/excel version 3.1. So, our Laravel 6 and maatwebsite/excel 3.1.
When using the package in your application, it’s good to understand how the package functions behind the scenes. Following the behind-the-scenes will make you feel more comfortable and confident using the maximum potential of the tool.
Laravel Excel 3.1
🚀 Laravel Excel is intended at being Laravel-flavoured PhpSpreadsheet: a simple, but an elegant wrapper around PhpSpreadsheet to simplify the exports and imports.
🔥 PhpSpreadsheet is the library written in pure PHP and providing the set of classes that allow us to read from and to write to a different types of spreadsheet file formats, like Excel and LibreOffice Calc.
Laravel Excel Features
- We can easily export collections to Excel.
- We can export queries with automatic chunking for better performance.
- We can queue exports for better performance.
- We can easily export Blade views to Excel.
- We can easily import it to collections.
- We can read the Excel file in chunks.
- We can handle the import inserts in batches.
Requirements
- PHP:
^7.0
- Laravel:
^5.5
- PhpSpreadsheet:
^1.6
- PHP extension
php_zip
enabled - PHP extension
php_xml
enabled - PHP extension
php_gd2
enabled
Step 1: Installation
Require the following package in the composer.json of your Laravel 6 project. The following command will download the package and PhpSpreadsheet.
composer require maatwebsite/excel
➜ laravel6 git:(master) ✗ composer require maatwebsite/excel Using version ^3.1 for maatwebsite/excel ./composer.json has been updated Loading composer repositories with package information Updating dependencies (including require-dev) Package operations: 4 installs, 0 updates, 0 removals - Installing markbaker/matrix (1.1.4): Downloading (100%) - Installing markbaker/complex (1.4.7): Downloading (100%) - Installing phpoffice/phpspreadsheet (1.9.0): Downloading (100%) - Installing maatwebsite/excel (3.1.17): Downloading (100%) phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer) phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer) phpoffice/phpspreadsheet suggests installing jpgraph/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers) Writing lock file Generating optimized autoload files > Illuminate\Foundation\ComposerScripts::postAutoloadDump > @php artisan package:discover --ansi Discovered Package: barryvdh/laravel-dompdf Discovered Package: facade/ignition Discovered Package: fideloper/proxy Discovered Package: laravel/tinker Discovered Package: laravel/ui Discovered Package: maatwebsite/excel Discovered Package: nesbot/carbon Discovered Package: nunomaduro/collision Package manifest generated successfully. ➜ laravel6 git:(master) ✗
Step 2: Configure package
The Maatwebsite\Excel\ExcelServiceProvider
is auto-discovered and registered by default.
If you want to register by yourself, then add the ServiceProvider in config/app.php
:
'providers' => [ /* * Package Service Providers... */ Maatwebsite\Excel\ExcelServiceProvider::class, ]
Excel facade is auto-discovered.
If you want to add it manually, add a Facade in config/app.php
:
'aliases' => [ ... 'Excel' => Maatwebsite\Excel\Facades\Excel::class, ]
If you want to publish a config, run the vendor publish command:
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"
➜ laravel6 git:(master) ✗ php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" Copied File [/vendor/maatwebsite/excel/config/excel.php] To [/config/excel.php] Publishing complete. ➜ laravel6 git:(master) ✗
This will create the new config file named config/excel.php
.
Step 3: Create model and migration files
Type the following command.
php artisan make:model Disneyplus -m
Now, go to the [timestamp].create_disneypluses_table.php file and add the columns.
public function up() { Schema::create('disneypluses', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('show_name'); $table->string('series'); $table->string('lead_actor'); $table->timestamps(); }); }
Now, migrate the database using the following command.
php artisan migrate
Step 4: Create a controller and routes
Next step is to create a DisneyplusController.php file.
php artisan make:controller DisneyplusController
Now, add the two routes inside the routes >> web.php file.
// web.php Route::get('disneyplus', 'DisneyController@create')->name('disneyplus.create'); Route::post('disneyplus', 'DisneyController@store')->name('disneyplus.store');
Now, create two methods inside the DisneyplusController.php file.
<?php // DisneyplusController.php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Disneyplus; class DisneyplusController extends Controller { public function create() { } public function store() { } }
Step: 5 Create a form blade file for input the data
Now, inside the views folder, create one file called form.blade.php file. Add the following code.
@extends('layout') @section('content') <style> .uper { margin-top: 40px; } </style> <div class="card uper"> <div class="card-header"> Add Disneyplus Shows </div> <div class="card-body"> @if ($errors->any()) <div class="alert alert-danger"> <ul> @foreach ($errors->all() as $error) <li>{{ $error }}</li> @endforeach </ul> </div><br /> @endif <form method="post" action="{{ route('disneyplus.store') }}"> <div class="form-group"> @csrf <label for="name">Show Name:</label> <input type="text" class="form-control" name="show_name"/> </div> <div class="form-group"> <label for="price">Series :</label> <input type="text" class="form-control" name="series"/> </div> <div class="form-group"> <label for="quantity">Show Lead Actor :</label> <input type="text" class="form-control" name="lead_actor"/> </div> <button type="submit" class="btn btn-primary">Create Show</button> </form> </div> </div> @endsection
Step 6: Store data in the database
Now, we will write the two functions inside the DisneyplusController.php file.
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Disneyplus; class DisneyplusController extends Controller { public function create() { return view('form'); } public function store(Request $request) { $validatedData = $request->validate([ 'show_name' => 'required|max:255', 'series' => 'required|max:255', 'lead_actor' => 'required|max:255', ]); Disneyplus::create($validatedData); return redirect('/disneyplus')->with('success', 'Disney Plus Show is successfully saved'); } }
So, in the above file, first, we have shown the form file, and then inside the store function, we check for validation and then store the data into the database.
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Disneyplus extends Model { protected $fillable = ['show_name', 'series', 'lead_actor']; }
Now, go to this route: http://laravel6.test/disneyplus or http://localhost:8000/disneyplus
You will see one form. Try to save the data, and if everything in the code is right, then, you will see one entry in the database.
Step: 7 Create a view file to display the data.
Before we create a view file, we need to add one route inside the web.php.
// web.php Route::get('disneyplus/list', 'DisneyplusController@index')->name('disneyplus.index');
Now, create a view file called list.blade.php file. Add the following code.
@extends('layout') @section('content') <table class="table table-striped"> <thead> <th>ID</th> <th>Show Name</th> <th>Series</th> <th>Lead Actor</th> <th>Action</th> </thead> <tbody> @foreach($shows as $show) <tr> <td>{{$show->id}}</td> <td>{{$show->show_name}}</td> <td>{{$show->series}}</td> <td>{{$show->lead_actor}}</td> </tr> @endforeach </tbody> </table> @endsection
Now, add the code inside the index() function of DisneyplusController.php file.
public function index() { $shows = Disneyplus::all(); return view('list', compact('shows')); }
Now, go to the http://laravel6.test/disneyplus/list or http://localhost:8000/disneyplus/list
You will see the listing of the shows.
Step 8: Create Exports class
You may do this by using the make:export
command.
php artisan make:export DisneyplusExport --model=Disneyplus
The file can be found in app/Exports
directory.
The file DisneyplusExport.php is the following.
<?php namespace App\Exports; use App\Disneyplus; use Maatwebsite\Excel\Concerns\FromCollection; class DisneyplusExport implements FromCollection { /** * @return \Illuminate\Support\Collection */ public function collection() { return Disneyplus::all(); } }
If you prefer to create a export manually, you can build the following in app/Exports
.
Step 9: Write the export function
Inside the DisneyplusController.php file, add the following code.
// DisneyplusController.php use App\Disneyplus; use App\Exports\DisneyplusExport; use Maatwebsite\Excel\Facades\Excel; public function export() { return Excel::download(new DisneyplusExport, 'disney.xlsx'); }
So, our final file looks like below.
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Disneyplus; use App\Exports\DisneyplusExport; use Maatwebsite\Excel\Facades\Excel; class DisneyplusController extends Controller { public function create() { return view('form'); } public function store(Request $request) { $validatedData = $request->validate([ 'show_name' => 'required|max:255', 'series' => 'required|max:255', 'lead_actor' => 'required|max:255', ]); Disneyplus::create($validatedData); return redirect('/disneyplus')->with('success', 'Disney Plus Show is successfully saved'); } public function index() { $shows = Disneyplus::all(); return view('list', compact('shows')); } public function export() { return Excel::download(new DisneyplusExport, 'disney.xlsx'); } }
Finally, add the route to be able to access the export:
// web.php Route::get('export', 'DisneyplusController@export');
Also, add the link to the Export inside the list.blade.php file.
@foreach($shows as $show) <tr> <td>{{$show->id}}</td> <td>{{$show->show_name}}</td> <td>{{$show->series}}</td> <td>{{$show->lead_actor}}</td> <td><a href="{{action('DisneyplusController@export')}}">Export</a></td> </tr> @endforeach
Okay, now finally go to the http://laravel6.test/disneyplus/list, and now you can see one link called Export.
Click on the Export link, and you will see the disney.xlsx file inside your Download folder.
Exporting collections in CSV in Laravel 6
By default, the export format is determined by the extension of the file.
public function export() { return Excel::download(new DisneyplusExport, 'disney.csv'); }
It will download the CSV file.
If you want to configure the export format explicitly, you can pass it through as 2nd parameter.
You can find more details about export in different formats on this link.
Finally, Laravel 6 Export In Excel and CSV Data Example is over.