Question 16544 in Laravel Question posted in
The official Laravel Documentation can be found here.

excel sheet validation in laravel

I using laravel excel Maatwebsite.

public function collection(Collection $rows)
{

  Validator::make($rows->toArray(), [
         '*.price' => 'numeric',
     ])->validate();

}

I need output

Excel Row No not array number

Row No 1.price must be a number.

Tags:

2

Answers


  1. 0

    as I understand you need to validate uploaded excel rows.
    the package excel Maatwebsite provided validation rules feature too.

    in your import class you need to add WithValidation, WithHeadingRow interface and rules mothod. in this way uploaded excel will be validated before insert into database:

    namespace AppImports;

use AppUser;
use MaatwebsiteExcelConcernsToModel;
use MaatwebsiteExcelValidatorsFailure;
use MaatwebsiteExcelConcernsImportable;
use MaatwebsiteExcelConcernsSkipsOnFailure;
use MaatwebsiteExcelConcernsWithValidation;
use MaatwebsiteExcelConcernsSkipsFailures;

class UsersImport implements ToModel, WithValidation, WithHeadingRow,SkipsOnFailure
{
    use Importable,SkipsFailures;

    public function model(array $row)
    {
        return new User([
            'name'     => $row['name'],
            'email'    => $row['email'],
            'password' => 'secret',
        ]);
    }

    public function rules(): array
    {
        return [
            'email' => Rule::in(['[email protected]']),

             // Above is alias for as it always validates in batches
             '*.email' => Rule::in(['[email protected]']),
        ];
    }
}

    and to gather errors:

     $import = new UsersImport();
$import->import('users.xlsx');
$failures= $import->failures() ;
foreach ($failures as $failure) {
     $failure->row(); // row that went wrong
     $failure->attribute(); // either heading key (if using heading row concern) or column index
     $failure->errors(); // Actual error messages from Laravel validator
     $failure->values(); // The values of the row that has failed.
}

    now $failures contains all validation error in all rows

    Login or Signup to reply.
  2. 0

    This example use interface Validator that take different arguments to initialize; (array $data, array $rules, array $messages, array $customAttributes) to validate rows and referenced from web sources, do not implement ToModel concern and is similar to the example showed in the question.

    namespace AppImports;

use AppUser;
use IlluminateSupportCollection;
use IlluminateSupportFacadesValidator;
use MaatwebsiteExcelConcernsToCollection;

class UsersImport implements ToCollection
{
    public function collection(Collection $rows)
    {
         Validator::make($rows->toArray(), [
             '*.0' => 'required',
         ])->validate();

        foreach ($rows as $row) {
            User::create([
                'name' => $row[0],
            ]);
        }
    }
}

    Another scenario like defined a custom data or Excel file like the following, suppose you want to access specific cells, you can implement the WithMappedCells concern.

    index user date subscription
    0 user 2022-12-08 true
    1 user 2022-12-08 true
    2 user 2022-12-08 false
    3 user 2022-12-08 true 
    use AppUser;
use MaatwebsiteExcelConcernsToModel;
use MaatwebsiteExcelConcernsWithMappedCells;

class UsersImport implements WithMappedCells, ToModel 
{
    public function mapping(): array
    {
        return [
            'index'  => 'B1',
            'user' => 'B2',
            'date' => 'B3',
            'subscription' => 'B4',
        ];
    }
    
    public function model(array $row)
    {
        return new User([
            'index' => $row['index'],
            'user' => $row['user'],
            'date' => $row['date'],
            'subscription' => $row['subscription']
        ]);
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search