从Excel导入数据时在MYSQL数据库中添加缺失日期

Question

我正在尝试使用PhpSpreadsheet从excel工作表导入数据。根据我的要求，使用下面的代码可以很好地工作。

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load("uploads/temp.xlsx");
$worksheet = $spreadsheet->getActiveSheet();
$spreadSheetAry = $worksheet->toArray();
$sheetCount = count($spreadSheetAry);
//echo $sheetCount;

$sql = "INSERT INTO tbl_tour (user_email,date,day,school_name,visit_status,reason) VALUES (?,?,?,?,?,?)";
$stmt=$mysqli->prepare($sql);

for ($i = 1; $i <= $sheetCount; $i ++) {
        $date ="";
        $day ="";
        $school_name ="";
        $visit_status = "";
        $reason = "";
        if (isset($spreadSheetAry[$i][1])) {
            $date = $spreadSheetAry[$i][1];
        }
        if (isset($spreadSheetAry[$i][2])) {
            $day = $spreadSheetAry[$i][2];
        }
        if (isset($spreadSheetAry[$i][3])) {
            $school_name = $spreadSheetAry[$i][3];
        }
        if (isset($spreadSheetAry[$i][4])) {
            $visit_status = $spreadSheetAry[$i][4];
        }
        if (isset($spreadSheetAry[$i][5])) {
            $reason = $spreadSheetAry[$i][5];
        }

           $stmt->bind_param('ssssss',$email,$date,$day,$school_name,$visit_status,$reason);
           $stmt->execute();
           
           
           
}
$stmt->close();

我的表格中有SR、日期、日期、学校名称、访问状态、原因等栏目。

日期包含从1到31的月份的日期。现在的问题是它失去了周日的一排。我正在寻找添加行，甚至与我的自定义数据的日期丢失日期，但我不知道我如何可以这样做。我的当前日期列包含这样的数据

01-09-2021
01-09-2021
02-09-2021
02-09-2021
03-09-2021
03-09-2021
04-09-2021
04-09-2021
06-09-2021
06-09-2021
07-09-2021
07-09-2021
08-09-2021
08-09-2021
09-09-2021
09-09-2021
11-09-2021
11-09-2021
13-09-2021
13-09-2021
14-09-2021
14-09-2021
15-09-2021
15-09-2021
16-09-2021
16-09-2021
17-09-2021
17-09-2021
18-09-2021
18-09-2021
20-09-2021
20-09-2021
21-09-2021
21-09-2021
22-09-2021
22-09-2021
23-09-2021
23-09-2021
24-09-2021
24-09-2021
25-09-2021
25-09-2021
27-09-2021
27-09-2021
28-09-2021
28-09-2021
29-09-2021
29-09-2021
30-09-2021
30-09-2021

Answer 1

我建议你在第一天和最后一天之间迭代。

$startTime = strtotime($spreadSheetAry[1][1] );
$endTime = strtotime( $spreadSheetAry[$sheetCount][1] );
$j = 1;
// Loop between timestamps, 24 hours at a time
for ( $i = $startTime; $i <= $endTime; $i = $i + 86400 ) {
  $thisDate = date( 'Y-m-d', $i ); // 2010-05-01, 2010-05-02, etc
  if($spreadSheetAry[$j][1]===$thisDate){
    // Date exists, do something.
    $j++;
  }else{
    // Date does not exist, do something else.
  }
}

Answer 2

不知道我是否正确理解了你的问题。也许这会有帮助：

if (isset($spreadSheetAry[$i][1])) {
  $date = $spreadSheetAry[$i][1];
  if (DateTime::createFromFormat('d-m-Y', $date) === false) {
    $date = ... // do your magic here :)
  }
}

祝好运!