Recently, I have been working on the project that needs to enable users to import data using the excel template file. I provided the excel file which contains the header like as the followings :

• name_title_code -> The code of name title Ex. 003 for Ms.
• fname -> First Name
• lname -> Last Name
• gender -> Gender when you were born
• citizen_id -> The Citizen ID on the ID Card
• registered_address -> The place you were born
• mailing_address -> The current address that can use to contact you
• is_same_address -> Ask whether the mailing address is the same as registered address
• is_current_outside_country -> Ask whether currently live in the country or others

The type of value will be as shown :

• name_title_code -> 3 digits number with leading zero(s)
• fname -> string/text
• lname -> string/text
• gender -> F for Female and M for Male
• citizen_id -> 13 digits number
• registered_address -> string/text
• mailing_address -> string/text
• is_same_address -> boolean either 1 for true or 0 for false
• is_current_outside_country -> boolean either 1 for true or 0 for false

I’m using Maatwebsite. I had created model for importing data from Excel with this command line : ‘php artisan make:import ImportUser’ and get ‘ImportUser’ under ‘AppImportsImportUser’ in Laravel Folder. The followings are my code in the ImportUser.

     <?php 

     namespace AppImports;
     use IlluminateValidationRule;

     use MaatwebsiteExcelConcernsToModel;
     use MaatwebsiteExcelConcernsImportable;
     use MaatwebsiteExcelConcernsWithStartRow;
     use MaatwebsiteExcelConcernsWithHeadingRow;
     use MaatwebsiteExcelConcernsWithValidation;
     use MaatwebsiteExcelConcernsSkipsEmptyRows;

     use IlluminateSupportCollection;
     use MaatwebsiteExcelConcernsToCollection;

     use Auth;
     use AppModelsUser;
     
     class ImportedStudentProfile implements ToModel, WithStartRow, WithHeadingRow, WithValidation, SkipsEmptyRows, ToCollection
     {
         use Importable;

         public function model(array $row)
         {
              // Define Each Row
              return new User([
                   'name_title_code' => $row['name_title_code'],
                   'lname' => $row['lname'],
                   'gender' => $row['gender'],
                   'citizen_id' => $row['citizen_id'],
                   'registered_address' => $row['registered_address'],
                   'mailing_address' => $row['mailing_address],
                   'is_same_address' => $row['is_same_address'],
                   'is_current_outside_country' => $row['is_current_outside_country'],
                   'created_at' => CarbonCarbon::now(),
                   'created_by' => Auth::user()->id,
                   'updated_at' => CarbonCarbon::now(),
                   'updated_by' => Auth::user()->id,
              ]);
         }

         public function startRow():int
         {
              // Skip The First Row as Header
              return 2;
         }

         public function rules(): array
         {
               return [
                    'name_title_code' => ['required', Rule::exists('name_title', 'code')],
                    'fname' => 'required',
                    'lname' => 'required',
                    'gender' => ['required', Rule::in('F', 'M')],
                    'citizen_id' => ['required', digits:13, Rule::unique('users', 'citizen_id')],
                    'registered_address' => 'required',
                    'mailing_address' => [Rule::requiredIf('is_same_address', 0)],
                    'is_same_address' => ['required', 'boolean'],
                    'is_current_outside_country' => ['required', 'boolean', Rule::declinedIf('is_same_address', 1)],

                    '*.name_title_code' => ['required', Rule::exists('name_title', 'code')],
                    '*.fname' => 'required',
                    '*.lname' => 'required',
                    '*.gender' => ['required', Rule::in('F', 'M')],
                    '*.citizen_id' => ['required', digits:13, Rule::unique('users', 'citizen_id')],
                    '*.registered_address' => 'required',
                    '*.mailing_address' => [Rule::requiredIf('is_same_address', 0)],
                    '*.is_same_address' => ['required', 'boolean'],
                    '*.is_current_outside_country' => ['required', 'boolean', Rule::declinedIf('is_same_address', 1)],
               ];
         }
     }

The followings are some part of the code in my ExcelController for import function :

public function import(Request $request)
{
    // For Basic Validation
    // Conditions for Basic Validation
    $rules = [
        'file' => 'required|mimes:xlsx,xls',
    ];
    
    // Messages for Basic Errors
    $messages = [
        'file.required' => 'Please choose the file you want.',
        'file.mimes' => 'The file must be with the extension of .xlsx or .xls only.',
    ];

    // Validate Data
    $validator = Validator::make($request->all(), $rules, $messages);

    // Error Case : Return Error
    if ($validator->fails()) {

        return redirect()
                    ->back()
                    ->withInput()
                    ->withErrors($validator)
                    ->with('error', 'Sorry!nSomething went wrong!');
    }

    // Success Case  
    else {
        
        // Advanced Validation(s)
        // NO

        // Get File
        $file = $request->file('file');

        try{

            // Process The Excel File
            Excel::import(new ImportUser, $file);

            return  redirect()
                    ->back()
                    ->with('success', 'Successfully Add Data from Excel!');
        } 
        catch(MaatwebsiteExcelValidatorsValidationException $e){

            $failures = $e->failures();

            return  redirect()
                    ->back()
                    ->with('error', 'Something went wrong in your file!')
                    ->with('failures', $failures);
        }
    }
}

I cannot use the specified rules in the ‘ImportUser’ to validate the value of each cell in each row such as Rule::unique(‘table_name’, ‘column_name), Rule::requiredIf(‘field_name’, some_value) and Rule::declinedIf(‘field_name’, some_value). For example, I migrated the database so all the records are gone and tried to insert data with Excel but it keeps telling that the ‘citizen_id’ is not unique while there is no records in the database at all.

It keep failing when I try to insert data and keep telling that there is something wrong with the rules I declared. I don’t know why. If anyone has any ideas about this, please do not hesitate to leave yours here.

Thank you in advanced.