显示错误的“无效日期价值”在Laravel 7中导入Excel文件时

Question

我想从Excel文件导入数据。在插入数据时，我想根据请求的Excel文件计算一些值。但是，时间差异不是返回正确的日期格式。

控制器：

public function import(Request $request){
  $request->validate([
    'import_file'=>'required'
   ]);
   Excel::import(new ImportAttendanceExcel,request()->file('import_file'));
   return back()->with('message','XL File Import Successfully!');

 }

模型：

class ImportAttendanceExcel implements ToModel, WithStartRow
{
    public function model(array $row)
    {   $id = 1;
        $settings = Setting::find($id);
        $open =  Carbon::parse($settings->opening_time);
        $end =   Carbon::parse($settings->ending_time);
        $date = Carbon::parse(Date::excelToDateTimeObject ($row[2]));
        $user = $row[0];
        $exist_attd = Attendance::where('user_id', $user)->where('attendance_date', $date)->get()->toArray();
            if($row[0] != null){
                if(empty($exist_attd)){
                    $attendances = new Attendance;
                    $attendances->user_id = $row[0];
                    $attendances->created_by = Auth::user()->id;
                    $attendances->attendance_date =Carbon::parse(Date::excelToDateTimeObject ($row[2]));
                    $attendances->attendance_status  = $row[3];
                    $attendances->check_in = Carbon::parse(Date::excelToDateTimeObject ($row[4]));
                    $attendances->check_out = Carbon::parse(Date::excelToDateTimeObject ($row[5]));
                    $csv_check_in = Carbon::parse(Date::excelToDateTimeObject($row[4]));
                    $csv_check_out = Carbon::parse(Date::excelToDateTimeObject($row[5]));
                    if( $csv_check_in > $open){
                        $late_count = $csv_check_in->diff($open)->format('%H:%I:%S');
                        $attendances->late = $late_count;
                    }
                    if( $csv_check_out < $end){
                        $early_count = $csv_check_out->diff($end)->format('%H:%I:%S');
                        $attendances->early = $early_count;
                    }

                 $attendances->save();
                }

            }
    }


    public function startRow(): int
    {
        return 2;
    }

}

带错误的返回值：

(SQL: insert into `attendances` (`user_id`, `created_by`, `attendance_date`, `attendance_status`, `check_in`, `check_out`, `early`, `updated_at`, `created_at`) values (2, 1, 44713, 1, 0.42361111111111, 0.77430555555556, 23:59:59, 2022-06-14 15:12:47, 2022-06-14 15:12:47))

Excel文件：

ID	日期	状态	检查	结帐
1	2022-06-10	1	10:15:00	18:00:00

这里的日期格式和计算无法正常工作。我该如何解决？

Answer 1

您不能直接将日期从Excel投入到DB。您需要先格式化它。由于Laravel-Excel是由phpspreadsheet构建的。您可以

use \PhpOffice\PhpSpreadsheet\Shared\Date;

// Rest of your code

$date = Date::excelToDateTimeObject($yourExcelDate);

// Rest or your code

从 phpspreadsheet文档：

\ phpoffice \ phpspreadsheet \ shared \ date :: exceltodatementobject（$ exceldate）

将日期从Excel日期/时间戳转换为返回PHP DateTime对象。

Answer 2

我的问题得到了解决：

class ImportAttendanceExcel implements ToModel, WithStartRow
    {
        public function model(array $row)
        {   $id = 1;
            $settings = Setting::find($id);
            $open =  Carbon::parse($settings->opening_time);
            $end =   Carbon::parse($settings->ending_time);
            $date = Carbon::parse(Date::excelToDateTimeObject ($row[2]));
            $user = $row[0];
            $exist_attd = Attendance::where('user_id', $user)->where('attendance_date', $date)->get()->toArray();
                if($row[0] != null){
                    if(empty($exist_attd)){
                        $attendances = new Attendance;
                        $attendances->user_id = $row[0];
                        $attendances->created_by = Auth::user()->id;
                        $attendances->attendance_date =Carbon::parse(Date::excelToDateTimeObject ($row[2]));
                        $attendances->attendance_status  = $row[3];
                        $attendances->check_in = Carbon::parse(Date::excelToDateTimeObject ($row[4]));
                        $attendances->check_out = Carbon::parse(Date::excelToDateTimeObject ($row[5]));
                        $csv_check_in = Carbon::parse(Date::excelToDateTimeObject($row[4]));
                        $csv_check_out = Carbon::parse(Date::excelToDateTimeObject($row[5]));
                        if( $csv_check_in > $open){
                            $late_count = $csv_check_in->diff($open)->format('%H:%I:%S');
                            $attendances->late = $late_count;
                        }
                        if( $csv_check_out < $end){
                            $early_count = $csv_check_out->diff($end)->format('%H:%I:%S');
                            $attendances->early = $early_count;
                        }
    
                     $attendances->save();
                    }
    
                }
        }
    
    
        public function startRow(): int
        {
            return 2;
        }
    
    }