读取到的时间 float(0.20833333333333)
原格式 15:00:00
代码
if (Request::isPost()) { $file_url = input('upfile'); // 本地上传文件地址 // 读取文件内容 $local_file_url = __dir__.'/../../../public'.$file_url; // $spreadsheet = new Spreadsheet(); // $sheet = $spreadsheet->getActiveSheet(); $inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($local_file_url); $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType); $spreadsheet = $reader->load($local_file_url); //载入excel表格 // $spreadsheet = $reader->load('students.xlsx'); //载入excel表格 $worksheet = $spreadsheet->getActiveSheet(); $highestRow = $worksheet->getHighestRow(); // 总行数 $highestColumn = $worksheet->getHighestColumn(); // 总列数 $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5 $lines = $highestRow - 1; // 行数 if ($lines <= 0) { $this->error('Excel表格中没有数据'); } // $sql = "INSERT INTO `tp_enroll` (`team_num`,`firing_time`,`member_num`) VALUES "; $new = []; for ($row = 1; $row <= $highestRow; ++$row) { $new[$row]['id'] = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //id $e_time = $worksheet->getCellByColumnAndRow(12, $row)->getValue(); // float(0.20833333333333) if(!empty($e_time) ){ // $toTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($e_time); // 可以调用 $toTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestampMy($e_time); // 自定义的方法 调用的方法 可以获取到时间格式 }else{ $toTimestamp = 0; } $new[$row]['team_ok_time']= $toTimestamp; }
调用的方法 是我改写的
// 改的自定义方法public static function excelToTimestampMy($excelTimestamp, $timeZone = null){ // return (int) self::excelToDateTimeObjectMy($excelTimestamp, $timeZone) // ->format('U'); return self::excelToDateTimeObjectMy($excelTimestamp, $timeZone);}public static function excelToDateTimeObjectMy($excelTimestamp, $timeZone = null){ $timeZone = ($timeZone === null) ? self::getDefaultTimezone() : self::validateTimeZone($timeZone); if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) { if ($excelTimestamp < 1 && self::$excelCalendar === self::CALENDAR_WINDOWS_1900) { // Unix timestamp base date $baseDate = new \DateTime('1970-01-01', $timeZone); } else { // MS Excel calendar base dates if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) { // Allow adjustment for 1900 Leap Year in MS Excel $baseDate = ($excelTimestamp < 60) ? new \DateTime('1899-12-31', $timeZone) : new \DateTime('1899-12-30', $timeZone); } else { $baseDate = new \DateTime('1904-01-01', $timeZone); } } } else { $baseDate = new \DateTime('1899-12-30', $timeZone); } $days = floor($excelTimestamp); return $excelTimestamp; // $partDay = $excelTimestamp ; // // $partDay = $excelTimestamp - $days; // $hours = floor($partDay * 24); // $partDay = $partDay * 24 - $hours; // $minutes = floor($partDay * 60); // $partDay = $partDay * 60 - $minutes; // $seconds = round($partDay * 60); // if ($days >= 0) { // $days = '+' . $days; // } // $interval = $days . ' days'; // return $baseDate->modify($interval) // ->setTime((int) $hours, (int) $minutes, (int) $seconds);}
来源地址:https://blog.csdn.net/qq_39835505/article/details/132469173