Click Below to subscribe

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-csv

2. After creating the project install the laravel excel package.

composer require maatwebsite/excel

3. Publish laravel excel config file.

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

This 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 password

5. Create a Movie model

php artisan make:model Movie -mc

This 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 migrate

8. 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=Movie

This 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=Movie

This 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>
@endsection

13.  Link storage folder.

php artisan storage:link

14. 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 MoviesCustomExport

This 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 --query

This 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=Movie

This 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