Import Export Excel & CSV File in Laravel 6

by

In this post, I will be show you how to import export csv file in laravel 6,  using maatwebsite/excel we can import export excel or csv from database in laravel 6 application.

In this post i will create full script “how to import csv file in database in laravel 6” and how to export csv file from database in laravel.

For import and export csv file from database we will use maatwebsite/excel composer package for import and export task. maatwebsite/excel package provide easy to import and export data from database. maatwebsite/excel package updated version 3 provide great way to import export data from database with validations, so first follow few step to get example.

Step 1 :  Install Laravel 6

first, we need install Laravel 6 using bellow command, So that open your terminal OR command prompt and run bellow command:

1
2
3
 
composer create-project --prefer-dist laravel/laravel laravel6
 

Step 2: Install maatwebsite/excel Package

In this step we need to install maatwebsite/excel package, So copy below command and run your terminal:

1
2
3
 
composer require maatwebsite/excel
 

Now we need some configuration for excel package, open config/app.php file and add service provider and aliase.

config/app.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
'providers' => [
 
....
 
Maatwebsite\Excel\ExcelServiceProvider::class,
 
],
 
'aliases' => [
 
....
 
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
 
],
 

 

Step 3: Add Routes

In this step, we need to create route of import export file. so open your “routes/web.php” file and add following route.

routes/web.php

1
2
3
4
5
 
Route::get('importView', 'TestController@importView');
Route::get('exportData', 'TestController@exportData')->name('exportData');
Route::post('importData', 'TestController@importData')->name('importData');
 

 

Step 5: Create Import Class

Now we need to create Import class and use that class in your controller. So you have to run following command and change following code on that file:

1
2
3
 
php artisan make:import UsersImport --model=User
 

app/Imports/UsersImport.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
<?php
   
namespace App\Imports;
   
use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
    
class UsersImport implements ToModel,WithHeadingRow,SkipsOnError,WithValidation
{
    /**
     * @param array $row
     *
     * @return User|null
     */
      /*
    * Save Excel File Data  
    */
    public function model(array $row)
    {
        return new User([
            'name'     => $row['name'],
            'email'    => $row['email'], 
            'password' => \Hash::make($row['password']),
        ]);
    }
 
    /*
    * @author Mohsin Khan
    * Skip top heading Data  
    */
    public function headingRow(): int
    {
        return 1;
    }
 
    /* @author Mohsin Khan
    ** validating each row
    */
    public function rules(): array
    {
        return [
            'name' => 'required',
            'email' => 'required',
            'password' => 'required'
        ];
    }
 
    /**
     * @param \Throwable $e
     */
    public function onError(\Throwable $e)
    {
        // Handle the exception how you'd like.
        \Log::error($e->getMessage());
         return back();
    }
 
}
 

Step 6: Create Export Class

maatwebsite 3 version package provide way to built export class and we have to use in controller. For create Export Class you have to run following command and change following code on that file:

1
2
3
 
php artisan make:export UsersExport --model=User
 

app/Exports/UsersExport.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
<?php
  
namespace App\Exports;
  
use App\User;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
  
class UsersExport implements FromCollection,WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return User::all();
    }
 
    public function headings(): array
    {
        return [
            'Name',
            'Email',
            'Password'
        ];
    }
 
}
 

Step 7: Create Controller

Now we should create a new controller as TestController in this path “app/Http/Controllers/TestController.php”. this controller will manage all importExportView, export and import request and return response, so put bellow content in controller file:

app/Http/Controllers/TestController.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
<?php
   
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\Exports\UsersExport;
use App\Imports\UsersImport;
use Maatwebsite\Excel\Facades\Excel;
  
class TestController extends Controller
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importView()
    {
       return view('import');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function exportData() 
    {
        return Excel::download(new UsersExport, 'users.xlsx');
    }
   
    /**
    * @return \Illuminate\Support\Collection
    */
    public function importData() 
    {
        Excel::import(new UsersImport,request()->file('file'));
           
        return back();
    }
}
 

 

Step 8: Create Import Blade File

In Last step, we need to create view file for import data as import.blade.php(resources/views/import.blade.php).

resources/views/import.blade.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
<!DOCTYPE html>
<html>
<head>
    <title>Laravel 6 Import Export Excel to database Example - webprepration.com</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
</head>
<body>
   
<div class="container">
    <div class="card bg-light mt-3">
        <div class="card-header">
            Laravel 6 Import Export Excel to database Example - webprepration.com
        </div>
        <div class="card-body">
            <form action="{{ route('importData') }}" method="POST" enctype="multipart/form-data">
                @csrf
                <input type="file" name="file" class="form-control">
                <br>
                <button class="btn btn-success" type="submit">Import User Data</button>
                <a class="btn btn-warning" href="{{ route('exportData') }}">Export User Data</a>
            </form>
        </div>
    </div>
</div>   
</body>
</html>
 

Now we are ready to run our example so run bellow command:

1
2
3
 
php artisan serve
 

Now you can open bellow URL on your browser:

http://localhost:8000/importView

I hope it can help you…

Leave a Comment