在我的Laravel-5.8项目中，我使用Maatwebsites-3.1导入excel
进口
<?php
namespace AppImports;
use MaatwebsiteExcelConcernsWithMultipleSheets;
class LeavesImport implements WithMultipleSheets
{
public function sheets(): array
{
return [
new FirstLeaveSheetImport()
];
}
}
它称之为：
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 CarbonCarbon::instance(PhpOfficePhpSpreadsheetSharedDate::excelToDateTimeObject($value));
} catch (ErrorException $e) {
return CarbonCarbon::createFromFormat($format, $value);
}
}
public function batchSize(): int
{
return 200;
}
public function headingRow(): int
{
return 1;
}
}
控制器
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 (MaatwebsiteExcelValidatorsValidationException $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 (IlluminateDatabaseQueryException $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');
}
当我尝试上传excel文件时，它表明它是成功的。但我发现数据库中没有存储任何数据。所以当我查看错误日志时，我看到了这些：
[2020-11-16 07:38:53] production.ERROR: MaatwebsiteExcelValidatorsValidationException: The given data was invalid. in C:xampphtdocsmyappvendormaatwebsiteexcelsrcValidatorsRowValidator.php:62
Stack trace:
#0 C:xampphtdocsmyappvendormaatwebsiteexcelsrcImportsModelManager.php(166): MaatwebsiteExcelValidatorsRowValidator->validate(Array, Object(AppImportsFirstLeaveSheetImport))
#1 C:xampphtdocsmyappvendormaatwebsiteexcelsrcImportsModelManager.php(53): MaatwebsiteExcelImportsModelManager->validateRows(Object(AppImportsFirstLeaveSheetImport))
#2 C:xampphtdocsmyappvendormaatwebsiteexcelsrcImportsModelImporter.php(70): MaatwebsiteExcelImportsModelManager->flush(Object(AppImportsFirstLeaveSheetImport), true)
#3 C:xampphtdocsmyappvendormaatwebsiteexcelsrcSheet.php(248): MaatwebsiteExcelImportsModelImporter->import(Object(PhpOfficePhpSpreadsheetWorksheetWorksheet), Object(AppImportsFirstLeaveSheetImport), 2)
#4 C:xampphtdocsmyappvendormaatwebsiteexcelsrcReader.php(111): MaatwebsiteExcelSheet->import(Object(AppImportsFirstLeaveSheetImport), 2)
#5 C:xampphtdocsmyappvendorlaravelframeworksrcIlluminateDatabaseConcernsManagesTransactions.php(29): MaatwebsiteExcelReader->MaatwebsiteExcel{closure}(Object(IlluminateDatabaseMySqlConnection))
#6 C:xampphtdocsmyappvendormaatwebsiteexcelsrcTransactionsDbTransactionHandler.php(30): IlluminateDatabaseConnection->transaction(Object(Closure))
#7 C:xampphtdocsmyappvendormaatwebsiteexcelsrcReader.php(115): MaatwebsiteExcelTransactionsDbTransactionHandler->__invoke(Object(Closure))
#8 C:xampphtdocsmyappvendormaatwebsiteexcelsrcExcel.php(146): MaatwebsiteExcelReader->read(Object(AppImportsLeavesImport), 'C:\xampp\htdocs...', 'Xlsx', NULL)
#9 C:xampphtdocsmyappvendorlaravelframeworksrcIlluminateSupportFacadesFacade.php(239): MaatwebsiteExcelExcel->import(Object(AppImportsLeavesImport), 'C:\xampp\htdocs...')
#10 C:xampphtdocsmyappappHttpControllersLeaveLeaveAdjustmentsController.php(360): IlluminateSupportFacadesFacade::__callStatic('import', Array)
#11 [internal function]: AppHttpControllersLeaveLeaveAdjustmentsController->import(Object(IlluminateHttpRequest))
如何检测和纠正错误？
感谢
从文档
try {
$import->import('import-users.xlsx');
} catch (MaatwebsiteExcelValidatorsValidationException $e) {
$failures = $e->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.
}
}