In my Laravel-5.8 project, I am using Maatwebsites-3.1 to import excel
Imports
<?php
namespace App\Imports;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
class LeavesImport implements WithMultipleSheets
{
public function sheets(): array
{
return [
new FirstLeaveSheetImport()
];
}
}
Which calles this:
class FirstLeaveSheetImport implements ToModel, WithHeadingRow, WithBatchInserts, WithValidation
{
protected $staffid, $leavetype, $commencementdate, $resumptiondate, $reliefofficer;
private $errors = []; // array to accumulate errors
use Importable;
// public function onRow(Row $row)
public function model(array $row)
{
$this->staffid = $row['staff_id'];
$this->leavetype = $row['leave_type'];
$this->reliefofficer = $row['relief_officer'];
$this->commencementdate = $row['commencement_date'];
return new HrLeaveRequest([
'employee_id' => $this->getStaffId(),
'leave_type_id' => $this->getLeaveType(),
'commencement_date' => $this->transformDate($row['commencement_date']),
'resumption_date' => $this->transformDate($row['resumption_date']),
'no_of_days' => $row['leave_days'],
'is_adjusted' => 1,
'relief_officer_id' => $this->getReliefOfficer(),
'reason' => $row['reason'] ?? '',
'alternative_email_address' => $row['alternative_email'] ?? '',
'contact_phone_number' => $row['contact_phone'] ?? '',
'contact_address' => $row['contact_address'] ?? '',
'company_id' => Auth::user()->company_id,
'leave_status' => 4,
'is_resumption_activated' => 1,
'resumption_activation_date' => $this->transformDate($row['resumption_date']),
'employee_code' => $row['staff_id'],
'created_by' => Auth::user()->id,
'created_at' => date("Y-m-d H:i:s"),
'is_active' => 1,
]);
}
public function getStaffId(){
if(!empty($this->staffid)){
return HrEmployee::where('employee_code',$this->staffid)->where('company_id',Auth::user()->company_id)->pluck('id')->first();
} else {
return 0;
}
}
public function getLeaveType(){
if(!empty($this->leavetype) || !$this->leavetype){
return HrLeaveType::where('leave_type_name',$this->leavetype)->where('company_id',Auth::user()->company_id)->pluck('id')->first();
} else {
return 0;
}
}
public function getReliefOfficer(){
return HrEmployee::where('employee_code',$this->reliefofficer)->where('company_id',Auth::user()->company_id)->pluck('employee_code')->first();
}
public function getErrors()
{
return $this->errors;
}
public function rules(): array
{
return [
'staff_id' => 'required|max:15',
'leave_type' => 'required|max:255',
'commencement_date' => 'required',
'leave_days' => 'required|numeric',
'resumption_date' => 'required',
'relief_officer' => 'nullable|max:255',
'reason' => 'nullable|max:255',
'alternative_email' => 'nullable|email|max:255',
'contact_phone' => 'nullable|phone:NG,mobile',
'contact_address' => 'nullable|max:255',
];
}
public function customValidationAttributes()
{
return [
'staff_id' => 'Staff ID',
'leave_type' => 'Leave Type',
'commencement_date' => 'Commencement Date',
'leave_days' => 'Leave Days',
'resumption_date' => 'Resumption Date',
'relief_officer' => 'Duty Relief Officer',
'reason' => 'Reason',
'alternative_email' => 'Alternative Email Address',
'contact_phone' => 'Contact Phone No.',
'contact_address' => 'Contact Address',
];
}
public function validationMessages()
{
return [
'staffid.*required' => "Staff ID is required",
];
}
public function transformDate($value, $format = 'Y-m-d')
{
try {
return \Carbon\Carbon::instance(\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value));
} catch (\ErrorException $e) {
return \Carbon\Carbon::createFromFormat($format, $value);
}
}
public function batchSize(): int
{
return 200;
}
public function headingRow(): int
{
return 1;
}
}
Controller
public function import(Request $request){
$request->validate([
'file' => 'required|max:10000|mimes:xlsx,xls',
]);
$path1 = $request->file('file')->store('temp');
$path=storage_path('app').'/'.$path1;
try{
Excel::import(new LeavesImport, $path);
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
$failures = $e->failures();
Log::error($e);
$errormessage = "";
// dd($failures);
foreach ($failures as $failure) {
$errormess = "";
foreach($failure->errors() as $error)
{
$errormess = $errormess.$error;
}
$errormessage = $errormessage." ,\n At Row ".$failure->row().", ".$errormess."<br>";
}
// Session::flash('error', 'Excel file is not imported!');
Session::flash('error', $errormessage);
// return redirect()->route('leave.leave_adjustments.index');
return back();
}catch (\Illuminate\Database\QueryException $e)
{
$errorCode = $e->errorInfo[1];
if($errorCode == 1062){
Log::error($e);
DB::rollback();
Session::flash('error', 'You have a duplicate entry problem!');
}
return back();
}
Session::flash('success', 'Leave Records Imported Successfully');
return redirect()->route('leave.leave_reviews.index_hr');
}
When I tried t upload the excel file it shows that it was successful. But I found that no data is store in the database. So when I checked error Log, I saw these:
[2020-11-16 07:38:53] production.ERROR: Maatwebsite\Excel\Validators\ValidationException: The given data was invalid. in C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Validators\RowValidator.php:62
Stack trace:
#0 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Imports\ModelManager.php(166): Maatwebsite\Excel\Validators\RowValidator->validate(Array, Object(App\Imports\FirstLeaveSheetImport))
#1 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Imports\ModelManager.php(53): Maatwebsite\Excel\Imports\ModelManager->validateRows(Object(App\Imports\FirstLeaveSheetImport))
#2 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Imports\ModelImporter.php(70): Maatwebsite\Excel\Imports\ModelManager->flush(Object(App\Imports\FirstLeaveSheetImport), true)
#3 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Sheet.php(248): Maatwebsite\Excel\Imports\ModelImporter->import(Object(PhpOffice\PhpSpreadsheet\Worksheet\Worksheet), Object(App\Imports\FirstLeaveSheetImport), 2)
#4 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Reader.php(111): Maatwebsite\Excel\Sheet->import(Object(App\Imports\FirstLeaveSheetImport), 2)
#5 C:\xampp\htdocs\myapp\vendor\laravel\framework\src\Illuminate\Database\Concerns\ManagesTransactions.php(29): Maatwebsite\Excel\Reader->Maatwebsite\Excel\{closure}(Object(Illuminate\Database\MySqlConnection))
#6 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Transactions\DbTransactionHandler.php(30): Illuminate\Database\Connection->transaction(Object(Closure))
#7 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Reader.php(115): Maatwebsite\Excel\Transactions\DbTransactionHandler->__invoke(Object(Closure))
#8 C:\xampp\htdocs\myapp\vendor\maatwebsite\excel\src\Excel.php(146): Maatwebsite\Excel\Reader->read(Object(App\Imports\LeavesImport), 'C:\\xampp\\htdocs...', 'Xlsx', NULL)
#9 C:\xampp\htdocs\myapp\vendor\laravel\framework\src\Illuminate\Support\Facades\Facade.php(239): Maatwebsite\Excel\Excel->import(Object(App\Imports\LeavesImport), 'C:\\xampp\\htdocs...')
#10 C:\xampp\htdocs\myapp\app\Http\Controllers\Leave\LeaveAdjustmentsController.php(360): Illuminate\Support\Facades\Facade::__callStatic('import', Array)
#11 [internal function]: App\Http\Controllers\Leave\LeaveAdjustmentsController->import(Object(Illuminate\Http\Request))
How do I detect and rectify the error?
Thanks
$e->errors()in addition to$e->failiures()in case there's a hint there – apokryfos