Import Excel in Multiple Tables— Maatwebsite Laravel

Below you can find complete step by step process of how to use Laravel Maatwebsite package for import Excel sheet data into multiple tables of database in Laravel.

Step 1 — Create Table

First we have to create multiple tables in MySQL database. By using following script we can create multiple tables

php artisan make:migration create_customers_table
php artisan make:migration create_courses_table

This will create two tables named as customers and courses. We can change the constants of the table as following.

--
-- Database: `testing`
--

-- --------------------------------------------------------
class CreateUsersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email')->unique();
$table->timestamp('email_verified_at')->nullable();
$table->string('password');
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('users');
}
}
-- --------------------------------------------------------
class CreateCustomersTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('customers', function (Blueprint $table) {
$table->id();
$table->string('customer_name');
$table->string('gender');
$table->string('city');
$table->string('address');
$table->string('postal_code');
$table->string('country');
$table->rememberToken();
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('customers');
}
}
-- --------------------------------------------------------
class CreateCoursesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('courses', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('user_id');
$table->string('course_name');
$table->timestamps();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('courses');
}
}

After the above change, we have to migrate this three tables by following command:-

php artisan migrate

Step 2 — MySQL database Connection

After this you have to make database connection. For this first you have to open database.php file from config. And in this file you have to define your database configuration.

<?php

return [


    'default' => env('DB_CONNECTION', 'mysql'),

...........

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'testing'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

.............

];

After this you have to open .env file, and in this file also you have to define MySQL database configuration also.

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=testing
DB_USERNAME=root
DB_PASSWORD=

Step 3 — Download Maatwebsite Package

If you want to import excel file data in Laravel, you have to first download Maatwebsite package, this package will communicate with Excel spreadsheet data. First for download package, you have to go to command prompt and write following command.

composer require maatwebsite/excel

This command will download this package in your Laravel working folder. After this we have to register this package in our Laravel application. For this we have to go to config/app.php file. And in this file you have to define providers and aliases.

<?php

return [

........

    'providers' => [

.......

        Maatwebsite\Excel\ExcelServiceProvider::class,

    ],

    'aliases' => [

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

    ],

];

Step 4 — Controllers(ImportExcelController.php)

Now we have to make controller for handle http request for import data. In this controller we have use two use statement. First use App\Customer is used for do MySQL database operation, and second use Excel is for Maatwebsite package for import excel sheet data. In this controller, we have make two method.

index() — This is root method of this class, in this method it will fetch data from customer table and that data will be load in import_blade.php file in table format.

import() — This method has request for import excel file data. In this method first it has validate excel file format. If selected file other than excel sheet then it will return validation error. But suppose selected file is excel then it will proceed for import data. For import data here it has called Excel package class which has get data from excel file and convert into PHP array and then after insert into customer table. After successfully import of data it will return success message.

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Customer;
use App\Imports\CustomersImport;
use Excel;
class ImportExcelController extends Controller
{
public function index() {
$customerData = Customer::orderBy('id', 'desc')->get();
return view('import_excel', compact('customerData'));
}
public function import(Request $request) {
$this->validate($request, [
'select_file'  => 'required|mimes:xls,xlsx'
]);
$path = $request->file('select_file')->getRealPath();
// $data = Excel::load($path, function($reader) {})->get();
$data = Excel::import(new CustomersImport, $path);
return back()->with('success', 'Excel Data Imported successfully.');
}
}

Step 5 — Import Class

After that we have to Import class using import command as follow:

php artisan make:import CustomersImport

This will import CustomerImport class as follow. we have to modify CutomerImport as follow.

<?php
namespace App\Imports;
use App\Customer;
use App\User;
use App\Courses;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\ToModel;
use Illuminate\Support\Facades\Hash;
class CustomersImport implements ToCollection
{
   /**
   * @param array $row
   *
   * @return \Illuminate\Database\Eloquent\Model|null
   */
    public function collection(Collection $rows)
    {
        foreach ($rows as $row)
        {
           $user = User::create([
               'name' => $row[0],
               'email'    => $row[6],
               'password' => Hash::make($row[7]),
           ]);
           Customer::create([
               'customer_name' => $row[0],
               'gender' => $row[1],
               'address' => $row[2],
               'city' => $row[3],
               'postal_code' => $row[4],
               'country' => $row[5],
           ]);
           $myString = $row[8];
           $myArray = explode(',', $myString);
           foreach ($myArray as $value) {
               Courses::create([
                    'user_id' => $user->id,
                    'course_name' => $value,
               ]);
           }
      }
   }
}

Step 6 — View File(import_excel.blade.php)

This file has been load by index() method of ImportExcelController, On this file we have make form for select excel file from local computer for import data. Below form it will display customer table data. And above form we have define for display validation error message and success message.

<!DOCTYPE html>
<html>
<head>
<title>Import Excel File in Laravel</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<h3 align="center">Import Excel File in Laravel</h3>
<br />
@if(count($errors) > 0)
<div class="alert alert-danger">
Upload Validation Error<br><br>
<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
<form method="post" enctype="multipart/form-data" action="{{ url('/import_excel/import') }}">
{{ csrf_field() }}
<div class="form-group">
<table class="table">
<tr>
<td width="40%" align="right"><label>Select File for Upload</label></td>
<td width="30">
<input type="file" name="select_file" />
</td>
<td width="30%" align="left">
<input type="submit" name="upload" class="btn btn-primary" value="Upload">
</td>
</tr>
<tr>
<td width="40%" align="right"></td>
<td width="30"><span class="text-muted">.xls, .xslx</span></td>
<td width="30%" align="left"></td>
</tr>
</table>
</div>
</form>
<br />
<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>Customer Name</th>
<th>Gender</th>
<th>Address</th>
<th>City</th>
<th>Postal Code</th>
<th>Country</th>
</tr>
@foreach($customerData as $row)
<tr>
<td>{{ $row->customer_name }}</td>
<td>{{ $row->gender }}</td>
<td>{{ $row->city }}</td>
<td>{{ $row->address }}</td>
<td>{{ $row->postal_code }}</td>
<td>{{ $row->country }}</td>
</tr>
@endforeach
</table>
</div>
</div>
</div>
</div>
</body>
</html>

Step 7 — Set Routes

After this we have to set the route of controller method. For this we have to open to routes/web.php file. In this file we can define route.

<?php

.......

Route::get('/import_excel', 'ImportExcelController@index');
Route::post('/import_excel/import', 'ImportExcelController@import');

Step 8 — Run Laravel Application

Lastly, we have to run Laravel application, for this we have to go to command prompt, and write following command.

php artisan serve

This command will Laravel application, it will return base URL of Laravel application. Now for this application, we have to write following URL in browser.

http://127.0.0.1:8000/import_excel