To export data in Excel and CSV in Laravel, use the “maatwebsite/excel 3.1″ package.
Here is the step-by-step guide:
Step 1: Installation of package
The following command will download the package and PhpSpreadsheet.
composer require maatwebsite/excel:^3.1
Step 2: Configure the package
We can add the ServiceProvider in bootstrap/providers.php :
<?php return [ App\Providers\AppServiceProvider::class, Maatwebsite\Excel\ExcelServiceProvider::class, ];
Now, we need to add an Excel 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"
It creates the new config file named config/excel.php.
Step 3: Set up a MySQL database
Configure the database in the .env file.
DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=8889 DB_DATABASE=laravelexcel DB_USERNAME=root DB_PASSWORD=root
Step 4: Create model and migration files
Type the following command.
php artisan make:model Disneyplus -m
Go to the [timestamp].create_disneypluses_table.php file and add the columns.
public function up(): void { Schema::create('disneypluses', function (Blueprint $table) { $table->bigIncrements('id'); $table->string('show_name'); $table->string('series'); $table->string('lead_actor'); $table->timestamps(); }); }
Migrate the database using the following command.
php artisan migrate
Step 5: Create a controller and routes
The next step is to create a DisneyplusController.php file.
php artisan make:controller DisneyplusController
Add the route inside the routes >> web.php file.
<?php use Illuminate\Support\Facades\Route; use App\Http\Controllers\DisneyplusController; Route::get('/', function () { return view('welcome'); }); Route::get('disneyplus/create', [DisneyplusController::class, 'create'])->name("disneyplus.create");
Step: 6 Create a form blade file for input the data
Inside the resources >> views folder, create the form.blade.php file. Add the following code.
<!-- form.blade.php --> <!DOCTYPE html> <html> <head> <style> .uper { margin-top: 40px; } </style> <meta charset="utf-8"> <title>Laravel Export in Excel and CSV Tutorial</title> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script> <script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script> </head> <body> <div class="container"> <h2>Laravel Export Excel and CSV Tutorial</h2> @if(session()->get('success')) <div class="alert alert-success"> {{ session()->get('success') }} </div><br /> @endif @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') }}"> @csrf <div class="form-group"> <label>Show Name :</label> <input type="text" name="show_name" class="form-control" placeholder="Enter Show Name" id="showname"> </div> <div class="form-group"> <label>Series :</label> <input type="text" name="series" class="form-control" placeholder="Enter Series" id="series"> </div> <div class="form-group"> <strong>Show Lead Actor :</strong> <input type="text" name="lead_actor" class="form-control" placeholder="Enter Lead Actor" id="leadactor"> </div> <div class="form-group"> <button type="submit" class="btn btn-success" id="submit">Add Show</button> </div> </form> </div> </body> </html>
To display this view file, return this view using the create() method.
Move to DisneyController.php file:
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Disneyplus; class DisneyplusController extends Controller { public function create() { return view('form'); } }
Step 7: Store data in the database
Write the store functions inside the DisneyplusController.php file.
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/create')->with('success', 'Disney Plus Show is successfully saved'); }
Inside the store function, we check for validation and store the data in the database.
<?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; class Disneyplus extends Model { use HasFactory; protected $fillable = ['show_name', 'series', 'lead_actor']; }
Add route in the web.php file:
Route::post('disneyplus/create', [DisneyplusController::class, 'store'])->name('disneyplus.store');
Go to this route: http://localhost:8000/disneyplus/create
You will see one form. Try to save the data; if everything in the code is right, you will see one entry in the database.
Step 8: Create a view file to display the data.
Before creating a view file, we must add one route inside the web.php.
Route::get('disneyplus/list', [DisneyplusController::class, 'index'])->name('disneyplus.index');
Create a view file called list.blade.php file. Add the following code.
<!-- list.blade.php --> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Laravel Export in Excel and CSV Tutorial</title> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script> <script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script> </head> <body> <div class="container"> <h2>Dashboard</h2> <table class="table table-striped"> <thead> <th>ID</th> <th>Show Name</th> <th>Series</th> <th>Lead Actor</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> </div> </body> </html>
Add the code inside the index() function of the DisneyplusController.php file.
public function index() { $shows = Disneyplus::all(); return view('list', compact('shows')); }
Go to http://localhost:8000/disneyplus/list.
You will see the listing of the shows.
Step 9: 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.
Step 10: Write the export function
Inside the DisneyplusController.php file, add the following code.
use App\Exports\DisneyplusExport; use Excel; public function export() { return Excel::download(new DisneyplusExport, 'disney.xlsx'); }
Finally, add the route to be able to access the export:
Route::get('disneyplus/export', [DisneyplusController::class, 'export'])->name('disneyplus.export');
Also, add the link to the Export inside the list.blade.php file.
<!-- list.blade.php --> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Laravel Export in Excel and CSV Tutorial</title> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script> <script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script> </head> <body> <div class="container"> <h2>Dashboard</h2> <div class="form-group"> <form action="{{ route('disneyplus.export') }}" method = "GET"> <button type="submit" class="btn btn-success">Export in Excel</button> </form> </div> <table class="table table-striped"> <thead> <th>ID</th> <th>Show Name</th> <th>Series</th> <th>Lead Actor</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> </div> </body> </html>
Eventually, go to http://127.0.0.1:8000/disneyplus/list, and you will see one link called Export.
Click on the Export button to see the disney.xlsx file inside your Download folder.
Step 11: Exporting collections in CSV
By default, the export format is determined by the file’s extension.
<!-- list.blade.php --> <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Laravel Export in Excel and CSV Tutorial</title> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script> <script src="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script> </head> <body> <div class="container"> <h2>Dashboard</h2> <div class="form-group"> <form action="{{ route('disneyplus.export') }}" method = "GET"> <button type="submit" class="btn btn-success">Export in Excel</button> </form> </div> <div class="form-group"> <form action="{{ route('disneyplus.csv') }}" method = "GET"> <button type="submit" class="btn btn-primary">Export in CSV</button> </form> </div> <table class="table table-striped"> <thead> <th>ID</th> <th>Show Name</th> <th>Series</th> <th>Lead Actor</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> </div> </body> </html>
Add route to web.php file.
Route::get('disneyplus/csv', [DisneyplusController::class, 'csv'])->name('disneyplus.csv');
Add the following code in the DisneyplusController.php file.
public function csv() { return Excel::download(new DisneyplusExport, 'disney.csv'); }
Our final file looks like the one below.
<?php namespace App\Http\Controllers; use Illuminate\Http\Request; use App\Models\Disneyplus; use App\Exports\DisneyplusExport; use 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/create')->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'); } public function csv() { 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 exporting in different formats on this link.
That’s it.
Ardi
Can I export custom data from my table in my sql? in your example, you export all of data from DisneyPlus table, is it possible if I just export data from DisneyPlus with condition?
Sodmond
Thanks for this tutorial, it worked for me in Laravel 5.8
Brentone Alistar
Thank you
Hussain
i have data of multiple models in my view blade which is with relations how can i export that