How to Validate Excel Sheet Data in Laravel?

By Hardik Savani August 20, 2021 Category : Laravel

Hi Dev,

Now, let's see article of how to validate excel sheet data in laravel. I’m going to show you about laravel maatwebsite excel import validation. you will learn laravel maatwebsite validation example. we will help you to give example of laravel validate excel data. Let's see bellow example how to validate for excel data in laravel.

When we are going for work excel or csv import and export then we always use maatwebsite composer package. But when you need to validate excel sheet data using maatwebsite package and you don't know then i will give you very simple example how to done that.

you can also use this example with laravel 6, laravel 7 and laravel 8 version. you can follow bellow step and make it done like as bellow:

Preview:

Step 1 : Install Laravel 8

Here, we need install Laravel application using bellow command, So open your terminal OR command prompt and run bellow command:

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

Step 2: Install maatwebsite/excel Package

In this step we need to install maatwebsite/excel package via the Composer package manager, so one your terminal and fire bellow command:

composer require maatwebsite/excel

Now open config/app.php file and add service provider and aliase.

config/app.php

'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

<?php

use Illuminate\Support\Facades\Route;

use App\Http\Controllers\UserImportController;

/*

|--------------------------------------------------------------------------

| 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('import', [UserImportController::class, 'index']);

Route::post('import', [UserImportController::class, 'store'])->name('import');

Step 4: Create Import Class

In maatwebsite 3 version provide way to built import class and we have to use in controller. So it would be great way to create new Import class. So you have to run following command and change following code on that file:

php artisan make:import UsersImport --model=User

app/Imports/UsersImport.php

<?php

namespace App\Imports;

use App\Models\User;

use Illuminate\Support\Collection;

use Maatwebsite\Excel\Concerns\ToCollection;

use Maatwebsite\Excel\Concerns\WithHeadingRow;

use Illuminate\Support\Facades\Validator;

class UsersImport implements ToCollection, WithHeadingRow

{

/**

* @param array $row

*

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

*/

public function collection(Collection $rows)

{

Validator::make($rows->toArray(), [

'*.name' => 'required',

'*.email' => 'required',

'*.password' => 'required',

])->validate();

foreach ($rows as $row) {

User::create([

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

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

'password' => bcrypt($row['password']),

]);

}

}

}

You can download demo csv file from here: Demo CSV File.

Step 5: Create Controller

In this step, now we should create new controller as UserImportController in this path "app/Http/Controllers/UserImportController.php". this controller will manage all index and store method, so put bellow content in controller file:

app/Http/Controllers/UserImportController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Imports\UsersImport;

use Maatwebsite\Excel\Facades\Excel;

class UserImportController extends Controller

{

/**

* Write code on Method

*

* @return response()

*/

public function index()

{

return view('import');

}

/**

* Write code on Method

*

* @return response()

*/

public function store()

{

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

return back()->with('success', 'User Imported Successfully.');

}

}

Step 6: Create Blade File

In Last step, let's create import.blade.php(resources/views/import.blade.php) for layout and we will write design code here and put following code:

resources/views/import.blade.php

<!DOCTYPE html>

<html>

<head>

<title>How to validate excel sheet data in Laravel - ItSolutionStuff.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">

How to validate excel sheet data in Laravel - ItSolutionStuff.com

</div>

<div class="card-body">

<form action="{{ route('import') }}" method="POST" enctype="multipart/form-data">

@csrf

@if (count($errors) > 0)

<div class="row">

<div class="col-md-8 col-md-offset-1">

<div class="alert alert-danger alert-dismissible">

<button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>

<h4><i class="icon fa fa-ban"></i> Error!</h4>

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

{{ $error }} <br>

@endforeach

</div>

</div>

</div>

@endif

@if (Session::has('success'))

<div class="row">

<div class="col-md-8 col-md-offset-1">

<div class="alert alert-success alert-dismissible">

<button type="button" class="close" data-dismiss="alert" aria-hidden="true">×</button>

<h5>{!! Session::get('success') !!}</h5>

</div>

</div>

</div>

@endif

<input type="file" name="file" class="form-control">

<br>

<button class="btn btn-success">Submit</button>

</form>

</div>

</div>

</div>

</body>

</html>

Now you can check on your laravel 8 application.

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

php artisan serve

Now you can open bellow URL on your browser:

localhost:8000/import

you have to upload csv file as like bellow:

I hope it can help you...

Tags :