Laravel 7/6 Import Export Excel, Csv to Database

In this tutorial you will learn about the Laravel 7/6 Import Export Excel, Csv to Database and its application with practical example.

In this tutorial, I’ll show you how to import and export excel/csv file into and from database in laravel 6 using maatwebsite version 3 package with example. I’ll guide you through step by step with example of importing and exporting a csv or excel file using maatwebsite/excel version 3 composer package.

Table Of Contents−

Laravel 7/6 Import Export Excel, Csv to Database
Create Laravel 7/6 Application

.env file
Create Model and Migration
Run Laravel Migration

Package
Create Import Class

Create Export Class
Create Import Excel Controller
Create Blade / View Files

Create Import Export Excel Routes

When we are developing laravel applications, there may situations where we require to import or export large amount of data into and from the application database. Importing and exporting data through excel or csv files seems a good solution here.

In laravel, maatwebsite/excel composer package made it easy to import or export excel or csv files. The maatwebsite/excel is a composer package used to import and export excel or csv files. The maatwebsite/excel provide number of features for excel or csv file import and exort in laravel.

Install Laravel Fresh Project
Set database Credentials In .env File
Install Maatwebsite Package

Add Fake Records In Database
Create Controller
Add Routes

Create Import Export Classes
Create Blade View
Start Development Server

Create Laravel 7/6 Application

First of all we need to create a fresh laravel project, download and install Laravel 7/6 using the below command

composer create-project --prefer-dist laravel/laravel laraImportExportExcel

1	composer create-project --prefer-dist laravel/laravel laraImportExportExcel

Configure Database In .env file

Now, lets create a MySQL database and connect it with laravel application. After creating database we need to set database credential in application’s .env file.

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laraImportExportExcel
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=laraImportExportExcel

DB_USERNAME=root

DB_PASSWORD=

Create Model and Migration

Now, we have to define table schema for contact table. Open terminal and let’s run the following command to generate a Contact model along with a migration file to create contact table in our database.

php artisan make:model Contact -m

1	php artisan make:model Contact -m

Once this command is executed you will find a migration file created under “database/migrations”. Lets open migration file created and put following code in it –

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateContactsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('contacts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email');
            $table->string('phone');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('contacts');
    }
}

<?php

use Illuminate\Support\Facades\Schema;

use Illuminate\Database\Schema\Blueprint;

use Illuminate\Database\Migrations\Migration;

class CreateContactsTable extends Migration

{

/**

* Run the migrations.

* @return void

public function up()

{

Schema::create('contacts', function (Blueprint $table) {

$table->bigIncrements('id');

$table->string('name');

$table->string('email');

$table->string('phone');

$table->timestamps();

});

}

/**

* Reverse the migrations.

* @return void

public function down()

{

Schema::dropIfExists('contacts');

}

Run Laravel Migration

Now, run following command to migrate database schema.

php artisan migrate

1	php artisan migrate

After, the migration executed successfully the contact table will be created in database along with a model file Contact.php in app directory.

app/Contact.php

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Contact extends Model
{
     protected $fillable = [
        'name', 'email', 'phone'
    ];
}

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model

{

protected $fillable = [

'name', 'email', 'phone'

];

}

Install Maatwebsite Package

In this step, we will install Maatwebsite Package via the composer dependency manager. Use the following command to install Maatwebsite Package.

composer require maatwebsite/excel

1	composer require maatwebsite/excel

Configure Maatwebsite Package

After Installing Maatwebsite package, we need to add service provider and alias in config/app.php file as following.

config/app.php

'providers' => [
  .......
  Maatwebsite\Excel\ExcelServiceProvider::class,
 
 ],  

'aliases' => [ 
  .......
  'Excel' => Maatwebsite\Excel\Facades\Excel::class,

],

'providers' => [

.......

Maatwebsite\Excel\ExcelServiceProvider::class,

'aliases' => [

.......

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

Now, use following command to publish Maatwesite Package configuration file:

php artisan vendor:publish

1	php artisan vendor:publish

This will create Maatwesite Package configuration file named “config/excel.php”.

Create Import Class

Now we will create a import class for Contact model to use in our ImportExportExcelController. Use the following command to create import class.

php artisan make:import ImportContacts --model=Contact

1	php artisan make:import ImportContacts --model=Contact

After, the above command executed successfully it will create ImportContacts.php file in Imports directory.

Imports/ImportContacts.php

<?php

namespace App\Imports;

use App\Contact;
use Maatwebsite\Excel\Concerns\ToModel;

class ImportContacts implements ToModel
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        return new Contact([
            //
            'name'     => @$row[0],
            'email'    => @$row[1], 
            'phone'    => @$row[2]
        ]);
    }
}

<?php

namespace App\Imports;

use App\Contact;

use Maatwebsite\Excel\Concerns\ToModel;

class ImportContacts implements ToModel

{

/**

* @param array $row

* @return \Illuminate\Database\Eloquent\Model|null

public function model(array $row)

{

return new Contact([

'name' => @$row[0],

'email' => @$row[1],

'phone' => @$row[2]

]);

}

Create Export Class

Now we will create a export class for Contact model to use in our ImportExportExcelController. Use the following command to create import class.

php artisan make:export ExportContacts --model=Contact

1	php artisan make:export ExportContacts --model=Contact

After, the above command executed successfully it will create ExportContacts.php file in Exports directory.

Exports/ExportContacts.php

<?php

namespace App\Exports;

use App\Contact;
use Maatwebsite\Excel\Concerns\FromCollection;

class ExportContacts implements FromCollection
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return Contact::all();
    }
}

<?php

namespace App\Exports;

use App\Contact;

use Maatwebsite\Excel\Concerns\FromCollection;

class ExportContacts implements FromCollection

{

/**

* @return \Illuminate\Support\Collection

public function collection()

{

return Contact::all();

}

Create Import Excel Controller

Next, we have to create a controller to display a form to upload excel file records. Lets Create a controller named ImportExportExcelController using command given below –

php artisan make:controller ImportExportExcel/ImportExportExcelController

1	php artisan make:controller ImportExportExcel/ImportExportExcelController

Once the above command executed, it will create a controller file ImportExportExcelController.php in app/Http/Controllers/ImportExportExcel directory.

Open the ImportExportExcel/ImportExportExcelController.php file and put the following code in it.

app/Http/Controllers/ImportExportExcel/ImportExportExcelController.php

<?php

namespace App\Http\Controllers\ImportExportExcel;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Imports\ImportContacts;
use App\Exports\ExportContacts;
use Maatwebsite\Excel\Facades\Excel;
use DB;
use App\Contact;

class ImportExportExcelController extends Controller

{

    public function index()
    {
        $contacts = Contact::orderBy('created_at','DESC')->get();
        return view('import_export_excel.index',compact('contacts'));
    }

    public function import(Request $request)
    {
        $request->validate([
            'import_file' => 'required'
        ]);
        Excel::import(new ImportContacts, request()->file('import_file'));
        return back()->with('success', 'Contacts imported successfully.');
    }

    public function export() 
    {
        return Excel::download(new ExportContacts, 'contacts.xlsx');
    }

}

<?php

namespace App\Http\Controllers\ImportExportExcel;

use Illuminate\Http\Request;

use App\Http\Controllers\Controller;

use App\Imports\ImportContacts;

use App\Exports\ExportContacts;

use Maatwebsite\Excel\Facades\Excel;

use DB;

use App\Contact;

class ImportExportExcelController extends Controller

{

public function index()

{

$contacts = Contact::orderBy('created_at','DESC')->get();

return view('import_export_excel.index',compact('contacts'));

}

public function import(Request $request)

{

$request->validate([

'import_file' => 'required'

]);

Excel::import(new ImportContacts, request()->file('import_file'));

return back()->with('success', 'Contacts imported successfully.');

}

public function export()

{

return Excel::download(new ExportContacts, 'contacts.xlsx');

}

Here In the controller, we have following methods –

index() :- It displays File Upload Form and Contact data.

import() :- To Upload excel file and Save records in database .

export() :- To export and download excel file for records in database .

Create Blade / View Files

In this step, we will create view/blade file to upload excel file. Lets create a blade file “index.blade.php” in “resources/views/import_export_excel/” directory and put the following code in it respectively.

resources/views/import_export_excel/index.blade.php

<!DOCTYPE html>
<html>
<head>
    <title>Laravel 6 Export Import Excel to database - W3Adda</title>

    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />

    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js" ></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" ></script>
</head>

<body>
    
<div class="container">
    <div class="card mt-4">
        <div class="card-header">
            Laravel 6 Import Export Excel to database - W3Adda
        </div>
            @if ($errors->any())
        <div class="alert alert-danger">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
            <ul>
                @foreach ($errors->all() as $error)
                    <li>{{ $error }}</li>
                @endforeach
            </ul>
        </div>
    @endif
   @if($message = Session::get('success'))
   <div class="alert alert-success alert-block">
    <button type="button" class="close" data-dismiss="alert">×</button>
           <strong>{{ $message }}</strong>
   </div>
   @endif
        <div class="card-body">
            <form action="{{ url('import-excel') }}" method="POST" name="importform" enctype="multipart/form-data">
                @csrf
                <input type="file" name="import_file" class="form-control">
                <br>
                <button class="btn btn-success">Import File</button>
                <a class="btn btn-info" href="{{ url('export-excel') }}"> 
                 Export File</a>
            </form>
        </div>
    </div>
    <div class="panel panel-default">
    <div class="panel-heading">
     <h3 class="panel-title">Customer Data</h3>
    </div>
    <div class="panel-body">
     <div class="table-responsive">
      <table class="table table-bordered table-striped">
       <tr>
        <th>Name</th>
        <th>Email</th>
        <th>Phone</th>
               </tr>
       @foreach($contacts as $c)
       <tr>
        <td>{{ $c->name }}</td>
        <td>{{ $c->email }}</td>
        <td>{{ $c->phone }}</td>
       </tr>
       @endforeach
      </table>
     </div>
    </div>
</div>

</div>
    
</body>
</html>

<!DOCTYPE html>

<html>

<head>

<title>Laravel 6 Export Import Excel to database - W3Adda</title>

</head>

<body>

Laravel 6 Import Export Excel to database - W3Adda

</div>

@if ($errors->any())

<ul>

@foreach ($errors->all() as $error)

<li>{{ $error }}</li>

@endforeach

</ul>

</div>

@endif

@if($message = Session::get('success'))

<strong>{{ $message }}</strong>

</div>

@endif

@csrf

<br>

<button class="btn btn-success">Import File</button>

Export File</a>

</form>

</div>

<h3 class="panel-title">Customer Data</h3>

</div>

<tr>

<th>Email</th>

<th>Phone</th>

</tr>

@foreach($contacts as $c)

<tr>

<td>{{ $c->email }}</td>

<td>{{ $c->phone }}</td>

</tr>

@endforeach

</table>

</div>

</body>

</html>

Create Import Export Excel Routes

After this, we need to add following routes in “routes/web.php” file along with a resource route. Lets open “routes/web.php” file and add following route.

routes/web.php

Route::get('import-export-excel', 'ImportExportExcel\ImportExportExcelController@index');
Route::post('import-export-excel', 'ImportExportExcel\ImportExportExcelController@import');
Route::get('export-excel', 'ImportExportExcel\ImportExportExcelController@export');

Route::get('import-export-excel', 'ImportExportExcel\ImportExportExcelController@index');

Route::post('import-export-excel', 'ImportExportExcel\ImportExportExcelController@import');

Route::get('export-excel', 'ImportExportExcel\ImportExportExcelController@export');

Now we are ready to run our example so lets start the development server using following artisan command –

php artisan serve

1	php artisan serve

Now, open the following URL in browser to see the output –

http://127.0.0.1:8000/import-export-excel

Output:-

laravel-6-import-export-excel-file-in-database-1

Select excel file to upload, before uploading you must have created an excel file as following:

laravel-6-import-excel-csv-file-in-database-1

laravel-6-import-export-excel-file-in-database-2

Now, import file.

After Upload Screen Output:-

laravel-6-import-export-excel-file-in-database-3

Now, click “Export File” to export data into csv file.

laravel-6-import-export-excel-file-in-database-4

I hope you like this laravel 6 Import Export Excel CSV File to Database tutorial.

AngularJS

CodeIgniter

Laravel

Node Js

Wordpress

More Blog

Learn

ABOUT PAGES

Laravel 7/6 Import Export Excel, Csv to Database