How to Import Excel file data into database in Laravel 10
In this tutorial, I’ll guide you through the process of importing Excel files in Laravel using the popular Laravel Excel package also known as maatwebsite/excel package.
I have taken an example of Customer details to import data from excel sheet in database. Also how to update the existing data into database while excel import.
Step 1: Install the Laravel Application and setup the database.
Step 2: Install Laravel Excel Package via composer as follows:
composer require maatwebsite/excel
Step 3: Configure the Package
The Maatwebsite\Excel\ExcelServiceProvider is auto-discovered and registered by default.
If you want to register it yourself, add the ServiceProvider in "config/app.php": ( providers & aliases )
'providers' => [
// ...
Maatwebsite\Excel\ExcelServiceProvider::class,
],
'aliases' => [
// ...
'Excel' => Maatwebsite\Excel\Facades\Excel::class,
],
Step 4: To publish the config, run the vendor publish command
php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config
Step 5: Create the Migration file using the below artisan command:
php artisan make:migration create_customers_table
After successfully creating the Customer migration file, paste the below code in it - path : database/ migrations/ your_customers_table.php
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('customers', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('email');
$table->string('phone');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('customers');
}
};
now its migrate into the database using the following command:
php artisan migrate
Step 6: Create a Excel Import Class named CustomerImport with the following command:
php artisan make:import CustomerImport
After successfully creating the Import Class, paste the below code in the following path : app/ Imports/ CustomerImport.php
<?php
namespace App\Imports;
use App\Models\Customer;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class CustomerImport implements ToCollection, WithHeadingRow
{
/**
* @param Collection $collection
*/
public function collection(Collection $rows)
{
foreach ($rows as $row)
{
$customer = Customer::where('email', $row['email'])->first();
if($customer){
$customer->update([
'name' => $row['name'],
'phone' => $row['phone'],
]);
}else{
Customer::create([
'name' => $row['name'],
'email' => $row['email'],
'phone' => $row['phone'],
]);
}
}
}
}
Step 7: Create a Model named Customer using the below artisan command:
php artisan make:model Customer
After successfully creating the Customer model, paste the below code in it - path : app/ Models/ Customer.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Customer extends Model
{
use HasFactory;
protected $table = 'customers';
protected $fillable = [
'name',
'email',
'phone',
];
}
Step 8: Create a Controller named CustomerController using the below artisan command:
php artisan make:controller CustomerController
After successfully creating the CustomerController, paste the below code in it.
In this controller, write the logic of import excel as follows
<?php
namespace App\Http\Controllers;
use App\Models\Customer;
use Illuminate\Http\Request;
use App\Imports\CustomerImport;
use Maatwebsite\Excel\Facades\Excel;
class CustomerController extends Controller
{
public function index()
{
$customers = Customer::all();
return view('customer.index', compact('customers'));
}
public function importExcelData(Request $request)
{
$request->validate([
'import_file' => [
'required',
'file'
],
]);
Excel::import(new CustomerImport, $request->file('import_file'));
return redirect()->back()->with('status', 'Imported Successfully');
}
}
Step 9: Create a Blade View
Create the blade file in the following path : resources/ views/ customer/ index.blade.php
This Blade view includes a form with a file input field for uploading Excel files. It also displays a success message if the import is successful and also listed all the inserted records.
<x-app-layout>
<div class="container">
<div class="row justify-content-center">
<div class="col-md-8 mt-5">
@if (session('status'))
<div class="alert alert-success">{{ session('status') }}</div>
@endif
<div class="card">
<div class="card-header">
<h4>Import Excel Data into Database in Laravel 10</h4>
</div>
<div class="card-body">
<form action="{{ url('customer/import') }}" method="POST" enctype="multipart/form-data">
@csrf
<div class="input-group">
<input type="file" name="import_file" class="form-control" />
<button type="submit" class="btn btn-primary">Import</button>
</div>
</form>
<hr>
<table class="table table-bordered">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
@foreach ($customers as $item)
<tr>
<td>{{$item->id}}</td>
<td>{{$item->name}}</td>
<td>{{$item->email}}</td>
<td>{{$item->phone}}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</x-app-layout>
Step 10: Create a Route
Route::get('customer/import', [App\Http\Controllers\CustomerController::class, 'index']);
Route::post('customer/import', [App\Http\Controllers\CustomerController::class, 'importExcelData']);
Let's serve the application
php artisan serve
Now, we are ready to import the excel file at : http://localhost:8000/customer/import
I hope this helps you. Thanks for reading.