In this tutorial, I will give you an idea of validating and import data into the database (How to validate excel sheet data in laravel) using an excel file, I have used the maatwebsite package. It is easily working with CSV or Excel files. Also, provide an easy method to get data.
In this article I described a complete validation with custom validation messages, while we upload our Excel sheet for any kind of duplicate email or unnecessary data, then the custom messages will show an error with proper messages.
Install Maatawebsite Package : Click Here
When we use the validation, we need to call some Maatawebsite Facade, which is already installed with Excel package in our application.
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\WithValidation;Let’s start to validate and import data into a database using an excel file with the steps given below :
<?php
namespace App\Imports;
use Maatwebsite\Excel\Facades\Excel;
use App\Model\Admin\Teacher;
use Carbon\Carbon;
use Illuminate\Support\Facades\Hash;
use Maatwebsite\Excel\Concerns\ToModel;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class TeacherImport implements ToModel,WithHeadingRow,WithValidation
{
use Importable;
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function rules(): array
{
return [
'name' => 'required|max:35',
'email' => 'required|email|unique:teachers,email,NULL,id,deleted_at,NULL',
'phone' => 'required|regex:/^([0-9\s\-\+\(\)]*)$/|min:10',
'sex' => 'required|max:1',
'dob' => 'required',
'qualification' => 'required',
'address' => 'required|max:80',
'city' => 'required|max:15',
'country' => 'required|max:10',
];
}
public function customValidationMessages()
{
return [
#All Email Validation for Teacher Email
'email.required' => 'Teacher Email must not be empty!',
'email.email' => 'Incorrect Teacher email address!',
'email.unique' => 'The Teacher email has already been used',
#Max Lenght Validation
'name.required' => 'Teacher name must not be empty!',
'name.max' => 'The maximun length of The Teacher name must not exceed :max',
'dob' => 'Teacher Date of Birth must not be empty!',
'sex.required' => 'Teacher gender must not be empty!',
'sex.max' => 'The maximun length of The Teacher gender must not exceed :max',
'qualification.required' => 'Teacher Qualification Field is required',
'city.required' => 'Citys must not be empty!',
'city.max' => 'The maximun length of The city must not exceed :max',
'country.required' => 'Country must not be empty!',
'country.max' => 'The maximun length of Country must not exceed :max',
'address.required' => 'Address must not be empty!',
'address.max' => 'The maximun length of The Address must not exceed :max',
#Max Length with Contact Numeric Teacher
'phone.required' => 'Teacher contact must not be empty!',
'phone.regex' => 'Incorrect format of Teacher Contact',
];
}
public function model(array $row)
{
$row['user_name'] = 'Teach'.rand(111,99999);
$row['password'] = str_random(5);
return new Teacher([
'emp_id' => '#EMP'.rand(111,99999),
'name' => $row["name"],
'email' => $row["email"],
'sex' => $row["sex"],
'dob' => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['dob']),
'blood_group' => $row["blood_group"],
'phone' => $row["phone"],
'qualification' => $row["qualification"],
'city' => $row["city"],
'country' => $row["country"],
'join_date' => \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['join_date']),
'current_position' => $row["current_position"],
'contact' => $row["contact"],
'address' => $row["address"],
'password' => Hash::make($row['password']),
'teacher_status' => '1',
]);
}
}
While we uploaded valid data its worked fine without any error, but while we are uploading any kind of Duplicate mail or unnecessary data, our validation working properly.
So, in this blog, we have learned a complete tutorial about How to validate excel sheet data in laravel.
Also Read: Laravel – import excel sheet into database in multiple table.
Gaurav Pandey
Hi, My name is Gaurav Pandey. I'm a Laravel developer, owner of 8Bityard. I live in Uttarakhand - India and I love to write tutorials and tips that can help other developers. I am a big fan of PHP, Javascript, JQuery, Laravel, WordPress. connect@8bityard.com
