How to Import Export Excel & CSV In Laravel 2022
Laravel Excel is an elegant wrapper around PhpSpreadsheet with the goal of simplifying exports and imports.
In this article, we will implement Laravel Excel from scratch.
1. Let's create a new laravel project.
composer create-project laravel/laravel laravel-excel-csv2. After creating the project install the laravel excel package.
composer require maatwebsite/excel3. Publish laravel excel config file.
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=configThis will create an excel.php file inside the config folder. this file contains all configurations of the laravel excel.
4. Update your database credentials in .env file.
DB_DATABASE=laravel-excel-csv // your database name
DB_USERNAME=root // your database username
DB_PASSWORD= // your database password5. Create a Movie model
php artisan make:model Movie -mcThis will create three files.
(a). Movie.php inside app/Models directory.
(b). MovieController.php inside app/Http/Controllers directory.
(c). xxxx_create_movies_table.php inside database/migrations directory.
6. Open movies migration file and make changes like this.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateMoviesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('movies', function (Blueprint $table) {
$table->id();
$table->string('movie');
$table->string('category');
$table->string('director');
$table->float('rating', 8, 1)->default(0);
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('movies');
}
}7. Migrate movies migration file.
php artisan migrate8. Open Movie.php model and update the fillable property.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Movie extends Model
{
use HasFactory;
protected $fillable = [
'movie',
'category',
'director',
'rating'
];
}Note:- Instead of defining $fillable property you can also use $guarded property like this.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Movie extends Model
{
use HasFactory;
protected $guarded= [];
}$fillable serves as a "white list" whereas $guarded serves like a "black list". One should use either $fillable or $guarded.
9. Make an import class.
php artisan make:import MoviesImport --model=MovieThis will create a MoviesImport.php file inside the app/Imports directory. Open this file make the following changes
<?php
namespace App\Imports;
use App\Models\Movie;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class MoviesImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Movie([
'movie' => $row['movie'],
'category' => $row['category'],
'director' => $row['director'],
'rating' => $row['rating']
]);
}
}Above Changes Based on the import(excel or csv) file.
Note:- All headers cells value converted into lowercase as per above example 'movie' => $row['movie']
In case your Header cell contains a value with space like Movie Name then 'movie' => $row['movie_name']
In this article, I am using import file headers if your file doesn't have a headers row then you can use indexes.
<?php
namespace App\Imports;
use App\Models\Movie;
use Maatwebsite\Excel\Concerns\ToModel;
class MoviesImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Movie([
'movie' => $row[0],
'category' => $row[1],
'director' => $row[2],
'rating' => $row[3]
]);
}
}10. Make an export class.
php artisan make:export MoviesExport --model=MovieThis will create a MoviesExport.php file inside the app/Exports directory. Open this file make the following changes
<?php
namespace App\Exports;
use App\Models\Movie;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
class MoviesExport implements FromCollection, WithHeadings, WithMapping
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Movie::all();
}
public function headings(): array
{
return [
'Id',
'Movie',
'Category',
'Director',
'Rating'
];
}
public function map($movie): array
{
return [
$movie->id,
$movie->movie,
$movie->category,
$movie->director,
$movie->rating
];
}
}11. Open routes/web.php and add the following code.
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\MovieController;
/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/
Route::get('/', [MovieController::class, 'index']);
Route::post('/import', [MovieController::class, 'import']);
Route::get('/export', [MovieController::class, 'export']); 12. Open resources/views/index.blade.php and add the following code.
@extends('layouts.main')
@section('title', 'Larvel Import Export')
@section('content')
<main>
<div class="row mb-2">
<div class="col-sm-8 offset-2">
<div class="row">
<div class="col-md-6">
<form method="POST" action="{{ url('import') }}" enctype="multipart/form-data">
@csrf
<div class="input-group">
<div class="custom-file">
<input type="file" name="file" class="custom-file-input" id="inputGroupFile" required accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel">
<label class="custom-file-label" for="inputGroupFile">Choose file</label>
</div>
<div class="input-group-append">
<button type="submit" class="btn btn-primary float-right mr-2">Import <i class="fa fa-upload"></i></button>
</div>
</div>
</form>
</div>
<div class="col-md-6">
<a href="{{ url('export') }}" class="btn btn-primary float-right @if(count($movies) == 0) disabled @endif" role="button" aria-disabled="true">
Export <i class="fa fa-download"></i>
</a>
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-sm-8 offset-2">
<table class="table">
<thead>
<tr>
<th scope="col">Id</th>
<th scope="col">Movie</th>
<th scope="col">Category</th>
<th scope="col">Director</th>
<th scope="col">Rating</th>
</tr>
</thead>
<tbody>
@forelse($movies as $movie)
<tr>
<th scope="row">{{ $movie->id }}</th>
<td>{{ $movie->movie }}</td>
<td>{{ $movie->category }}</td>
<td>{{ $movie->director }}</td>
<td><span class="badge bg-warning text-dark">{{ $movie->rating }}</span></td>
</tr>
@empty
<tr>
<td colspan="5" class="text-center">No Movies Found.</td>
</tr>
@endforelse
</tbody>
</table>
</div>
</div>
</main>
@endsection13. Link storage folder.
php artisan storage:link14. Open MoviesController.php and add the following code.
<?php
namespace App\Http\Controllers;
use App\Exports\MoviesExport;
use App\Imports\MoviesImport;
use App\Models\Movie;
use Maatwebsite\Excel\Facades\Excel;
class MovieController extends Controller
{
public function index()
{
$movies = Movie::get();
return view('index', compact('movies'));
}
public function import()
{
if(request()->hasFile('file')) {
Excel::import(new MoviesImport, request()->file('file')->store('temp'));
}
return redirect()->back();
}
public function export()
{
return Excel::download(new MoviesExport, 'movies.xlsx');
}
}Note:- In the above code you can change the extension based on the file you want to import or export. (.xlsx, .xls, .csv)
15. Finally open the project in the browser and try import and export.
Extras:-
1. Create Excel and CSV File from Custom Array
1.1 create an export class
php artisan make:export MoviesCustomExportThis will create a MoviesCustomExport.php file inside the app/Exports directory. Open this file make the following changes
<?php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class MoviesCustomExport implements FromCollection, WithHeadings
{
protected $movie;
public function __construct(array $movie)
{
$this->movie = $movie;
}
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return collect($this->movie);
}
public function headings(): array
{
return [
'Id',
'Movie',
'Category',
'Director',
'Rating'
];
}
}Note:- you can also Implement FromArray Interface
<?php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
class MoviesCustomExport implements FromArray, WithHeadings
{
protected $movie;
public function __construct(array $movie)
{
$this->movie = $movie;
}
public function array(): array
{
return $this->movie;
}
public function headings(): array
{
return [
'Id',
'Movie',
'Category',
'Director',
'Rating'
];
}
}1.2 Open MoviesController.php and add the following code.
<?php
namespace App\Http\Controllers;
use App\Exports\MoviesCustomExport;
use Maatwebsite\Excel\Facades\Excel;
class MovieController extends Controller
{
public function exportCustomData()
{
$movies = [
[
'id' => 1,
'movie' => 'The Dark Knight',
'category' => 'Action',
'director' => 'Christopher Nolan',
'rating' => 9
],
[
'id' => 2,
'movie' => 'Shawshank Redemption',
'category' => 'Drama',
'director' => 'Frank Darabont',
'rating' => 9.3
]
];
return Excel::download(new MoviesCustomExport($movies), 'movies.csv');
}
}1.3 Open routes/web.php and add this route.
Route::get('/export-custom-data', [MovieController::class, 'exportCustomData']); 1.4 Finally hit this route in the browser.
2. Create Excel and CSV File from Query Data.
2.1 Create an export class
php artisan make:export MoviesQueryExport --queryThis will create a MoviesQueryExport.php file inside the app/Exports directory. Open this file make the following changes
<?php
namespace App\Exports;
use App\Models\Movie;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
class MoviesQueryExport implements FromQuery, WithHeadings, WithMapping
{
use Exportable;
public function __construct(float $rating)
{
$this->rating = $rating;
}
/**
* @return \Illuminate\Database\Query\Builder
*/
public function query()
{
return Movie::query()->where('rating', '>=', $this->rating);
}
public function headings(): array
{
return [
'Id',
'Movie',
'Category',
'Director',
'Rating'
];
}
public function map($movie): array
{
return [
$movie->id,
$movie->movie,
$movie->category,
$movie->director,
$movie->rating
];
}
}2.2 Open MoviesController.php and add the following code.
<?php
namespace App\Http\Controllers;
use App\Exports\MoviesQueryExport;
use Maatwebsite\Excel\Facades\Excel;
class MovieController extends Controller
{
public function exportQueryData()
{
$rating = 9; // Export Popular Movies
return Excel::download(new MoviesQueryExport($rating), 'movies.csv');
}
}2.3 Open routes/web.php and add this route.
Route::get('/export-query-data', [MovieController::class, 'exportQueryData']);2.4 Finally hit this route in the browser.
3. Import and Update Data From Excel or CSV
3.1 Create and import class
php artisan make:import MoviesUpsertImport --model=MovieThis will create a MoviesUpsertImport.php file inside the app/Imports directory. Open this file make the following changes
<?php
namespace App\Imports;
use App\Models\Movie;
use Maatwebsite\Excel\Concerns\OnEachRow;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Row;
class MoviesUpsertImport implements OnEachRow, WithHeadingRow
{
public function onRow(Row $row)
{
Movie::updateOrCreate(
[
'movie' => $row['movie']
],
[
'movie' => $row['movie'],
'category' => $row['category'],
'director' => $row['director'],
'rating' => $row['rating']
]
);
}
}3.2 Open MoviesController.php and add the following code.
<?php
namespace App\Http\Controllers;
use App\Imports\MoviesUpsertImport;
use Maatwebsite\Excel\Facades\Excel;
class MovieController extends Controller
{
public function importInsertUpdate()
{
if(request()->hasFile('file')) {
Excel::import(new MoviesUpsertImport, request()->file('file')->store('temp'));
}
return redirect()->back();
}
}3.3 Open routes/web.php and add this route.
Route::post('/import-insert-update', [MovieController::class, 'importInsertUpdate']); 3.4 Finally hit this route in the browser.
Sample Excel CSV Files:- https://github.com/ultimateakash/laravel-excel-csv/tree/master/sample files
Checkout my full laravel-excel-csv example. https://github.com/ultimateakash/laravel-excel-csv
If you facing any issues. don't hesitate to comment below. I will be happy to help you.
Thanks.
Leave Your Comment