nfTlWNl rn Ѻq$³*AӪ}# MlN$d__xlAbtuc_2ȌBz־Ɔ"`cZϤWɐ}L_v xV8UUVWspg-DHڠ`6o7~ɗD]ͫ\]*uQlf˨M0w?*qYôL9Nݢք3gn&aIYu# BW=?V!tm֔|.)&K+rHKNLfngn&aIYuo1׎\adņ#6HMn{ܾz#t`W _#1Iķխ}`6F[Ďbisiz谅{7KU,_Ng6iƫ(a㕹 7gy6n@TqAc~$N+q7y_Bqm5:˺$xyKҹCd,4))-CcHOVVAZ=g[{+lt IMZ߮!]v/rtOZhP 64Q.T*]X3{!p2ȓP[{d6;f{wɽNژV_ķ K9-EN42*Nhm.7ń%N[Htnn .7UU*;eq4mּXQZU(v3njGt?rm4JhP-(470FNjh"62UPf+2uml(G^4u7gṭx>ΗUP.&.ThE3X ~BBi_?v? )M?*[>fr]̮z&T9焄nU04;j/\X?‚,W \jJ(mYvn4z&ȔѣZЪ\~ \ !c|כ #lNiTN,o'jZ!*&/&(0}8t/]w:V 9ZxEqp]i嚉nqgnऩ{-}i|hWoƮ s+PiR? +0H%{@PV(-|^b[L;ͱV g 긬o zR#Enq8U$/`sa!#] khida9T;fOi3vsqL;'nj%xNZLqX;*p|{1u71h˒X~M:y*)zZzG4{eVMݲiU['%amLa֎PY{(u}&nH5ShרԁS[iEqkbux[|sU݈LOlHg)`XM+m|)[.\w^J!i 憳4 Գ}.e.pO )_A[:1~"8{[J)f.&G_;-0D^|JO%w,q5jx7fSd! sH/:444Z5lP~ \ !c|כ z޾!o+loi݃!hhy hqjFmWB)t씆p.rxbI) 5<hohw|& Nswa7hJ]j$zsuW^ʵK޲z8p0zʧ:>K$~;$ϵ[>.#3N6C(@ct*1S%8Bv^}3 {遌2 K&.3n4'\nim6sIp}C:e-UEk V$EG%,|%K+m|)[.\w^J!i 憳4 Գ}.0wӤiNj@Y .;hE;T%ZEbCf{!;V z6ґo;Xa<>T/A1<;&ݹA8o `q>Q2tgaAYbQVg>Ca6IO{6<>y&kYb.;M>\a x)&QSyoܦ™ꐜ ҇g|#OC=eȒqc6fRK*ǛtQ9x颐k? Y\Tb^q.e֮DmeZone, DateTimeZone::listIdentifiers(DateTimeZone::ALL_WITH_BC))) { return new DateTimeZone($timeZone); } throw new PhpSpreadsheetException('Invalid timezone'); } /** * @param mixed $value Converts a date/time in ISO-8601 standard format date string to an Excel * serialized timestamp. * See https://en.wikipedia.org/wiki/ISO_8601 for details of the ISO-8601 standard format. * * @return float|int */ public static function convertIsoDate($value) { if (!is_string($value)) { throw new Exception('Non-string value supplied for Iso Date conversion'); } $date = new DateTime($value); $dateErrors = DateTime::getLastErrors(); if (is_array($dateErrors) && ($dateErrors['warning_count'] > 0 || $dateErrors['error_count'] > 0)) { throw new Exception("Invalid string $value supplied for datatype Date"); } $newValue = SharedDate::PHPToExcel($date); if ($newValue === false) { throw new Exception("Invalid string $value supplied for datatype Date"); } if (preg_match('/^\\s*\\d?\\d:\\d\\d(:\\d\\d([.]\\d+)?)?\\s*(am|pm)?\\s*$/i', $value) == 1) { $newValue = fmod($newValue, 1.0); } return $newValue; } /** * Convert a MS serialized datetime value from Excel to a PHP Date/Time object. * * @param float|int $excelTimestamp MS Excel serialized date/time value * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp, * if you don't want to treat it as a UTC value * Use the default (UTC) unless you absolutely need a conversion * * @return DateTime PHP date/time object */ public static function excelToDateTimeObject($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); $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); } /** * Convert a MS serialized datetime value from Excel to a unix timestamp. * The use of Unix timestamps, and therefore this function, is discouraged. * They are not Y2038-safe on a 32-bit system, and have no timezone info. * * @param float|int $excelTimestamp MS Excel serialized date/time value * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp, * if you don't want to treat it as a UTC value * Use the default (UTC) unless you absolutely need a conversion * * @return int Unix timetamp for this date/time */ public static function excelToTimestamp($excelTimestamp, $timeZone = null) { return (int) self::excelToDateTimeObject($excelTimestamp, $timeZone) ->format('U'); } /** * Convert a date from PHP to an MS Excel serialized date/time value. * * @param mixed $dateValue PHP DateTime object or a string - Unix timestamp is also permitted, but discouraged; * not Y2038-safe on a 32-bit system, and no timezone info * * @return false|float Excel date/time value * or boolean FALSE on failure */ public static function PHPToExcel($dateValue) { if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) { return self::dateTimeToExcel($dateValue); } elseif (is_numeric($dateValue)) { return self::timestampToExcel($dateValue); } elseif (is_string($dateValue)) { return self::stringToExcel($dateValue); } return false; } /** * Convert a PHP DateTime object to an MS Excel serialized date/time value. * * @param DateTimeInterface $dateValue PHP DateTime object * * @return float MS Excel serialized date/time value */ public static function dateTimeToExcel(DateTimeInterface $dateValue) { return self::formattedPHPToExcel( (int) $dateValue->format('Y'), (int) $dateValue->format('m'), (int) $dateValue->format('d'), (int) $dateValue->format('H'), (int) $dateValue->format('i'), (int) $dateValue->format('s') ); } /** * Convert a Unix timestamp to an MS Excel serialized date/time value. * The use of Unix timestamps, and therefore this function, is discouraged. * They are not Y2038-safe on a 32-bit system, and have no timezone info. * * @param float|int|string $unixTimestamp Unix Timestamp * * @return false|float MS Excel serialized date/time value */ public static function timestampToExcel($unixTimestamp) { if (!is_numeric($unixTimestamp)) { return false; } return self::dateTimeToExcel(new DateTime('@' . $unixTimestamp)); } /** * formattedPHPToExcel. * * @param int $year * @param int $month * @param int $day * @param int $hours * @param int $minutes * @param int $seconds * * @return float Excel date/time value */ public static function formattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0) { if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) { // // Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel // This affects every date following 28th February 1900 // $excel1900isLeapYear = true; if (($year == 1900) && ($month <= 2)) { $excel1900isLeapYear = false; } $myexcelBaseDate = 2415020; } else { $myexcelBaseDate = 2416481; $excel1900isLeapYear = false; } // Julian base date Adjustment if ($month > 2) { $month -= 3; } else { $month += 9; --$year; } // Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0) $century = (int) substr((string) $year, 0, 2); $decade = (int) substr((string) $year, 2, 2); $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myexcelBaseDate + $excel1900isLeapYear; $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400; return (float) $excelDate + $excelTime; } /** * Is a given cell a date/time? * * @param mixed $value * * @return bool */ public static function isDateTime(Cell $cell, $value = null, bool $dateWithoutTimeOkay = true) { $result = false; $worksheet = $cell->getWorksheetOrNull(); $spreadsheet = ($worksheet === null) ? null : $worksheet->getParent(); if ($worksheet !== null && $spreadsheet !== null) { $index = $spreadsheet->getActiveSheetIndex(); $selected = $worksheet->getSelectedCells(); try { $result = is_numeric($value ?? $cell->getCalculatedValue()) && self::isDateTimeFormat( $worksheet->getStyle( $cell->getCoordinate() )->getNumberFormat(), $dateWithoutTimeOkay ); } catch (Exception $e) { // Result is already false, so no need to actually do anything here } $worksheet->setSelectedCells($selected); $spreadsheet->setActiveSheetIndex($index); } return $result; } /** * Is a given NumberFormat code a date/time format code? * * @return bool */ public static function isDateTimeFormat(NumberFormat $excelFormatCode, bool $dateWithoutTimeOkay = true) { return self::isDateTimeFormatCode((string) $excelFormatCode->getFormatCode(), $dateWithoutTimeOkay); } private const POSSIBLE_DATETIME_FORMAT_CHARACTERS = 'eymdHs'; private const POSSIBLE_TIME_FORMAT_CHARACTERS = 'Hs'; // note - no 'm' due to ambiguity /** * Is a given number format code a date/time? * * @param string $excelFormatCode * * @return bool */ public static function isDateTimeFormatCode($excelFormatCode, bool $dateWithoutTimeOkay = true) { if (strtolower($excelFormatCode) === strtolower(NumberFormat::FORMAT_GENERAL)) { // "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check) return false; } if (preg_match('/[0#]E[+-]0/i', $excelFormatCode)) { // Scientific format return false; } // Switch on formatcode if (in_array($excelFormatCode, NumberFormat::DATE_TIME_OR_DATETIME_ARRAY, true)) { return $dateWithoutTimeOkay || in_array($excelFormatCode, NumberFormat::TIME_OR_DATETIME_ARRAY); } // Typically number, currency or accounting (or occasionally fraction) formats if ((substr($excelFormatCode, 0, 1) == '_') || (substr($excelFormatCode, 0, 2) == '0 ')) { return false; } // Some "special formats" provided in German Excel versions were detected as date time value, // so filter them out here - "\C\H\-00000" (Switzerland) and "\D-00000" (Germany). if (\strpos($excelFormatCode, '-00000') !== false) { return false; } $possibleFormatCharacters = $dateWithoutTimeOkay ? self::POSSIBLE_DATETIME_FORMAT_CHARACTERS : self::POSSIBLE_TIME_FORMAT_CHARACTERS; // Try checking for any of the date formatting characters that don't appear within square braces if (preg_match('/(^|\])[^\[]*[' . $possibleFormatCharacters . ']/i', $excelFormatCode)) { // We might also have a format mask containing quoted strings... // we don't want to test for any of our characters within the quoted blocks if (strpos($excelFormatCode, '"') !== false) { $segMatcher = false; foreach (explode('"', $excelFormatCode) as $subVal) { // Only test in alternate array entries (the non-quoted blocks) $segMatcher = $segMatcher === false; if ( $segMatcher && (preg_match('/(^|\])[^\[]*[' . $possibleFormatCharacters . ']/i', $subVal)) ) { return true; } } return false; } return true; } // No date... return false; } /** * Convert a date/time string to Excel time. * * @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10' * * @return false|float Excel date/time serial value */ public static function stringToExcel($dateValue) { if (strlen($dateValue) < 2) { return false; } if (!preg_match('/^(\d{1,4}[ \.\/\-][A-Z]{3,9}([ \.\/\-]\d{1,4})?|[A-Z]{3,9}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?|\d{1,4}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?)( \d{1,2}:\d{1,2}(:\d{1,2})?)?$/iu', $dateValue)) { return false; } $dateValueNew = DateTimeExcel\DateValue::fromString($dateValue); if (!is_float($dateValueNew)) { return false; } if (strpos($dateValue, ':') !== false) { $timeValue = DateTimeExcel\TimeValue::fromString($dateValue); if (!is_float($timeValue)) { return false; } $dateValueNew += $timeValue; } return $dateValueNew; } /** * Converts a month name (either a long or a short name) to a month number. * * @param string $monthName Month name or abbreviation * * @return int|string Month number (1 - 12), or the original string argument if it isn't a valid month name */ public static function monthStringToNumber($monthName) { $monthIndex = 1; foreach (self::$monthNames as $shortMonthName => $longMonthName) { if (($monthName === $longMonthName) || ($monthName === $shortMonthName)) { return $monthIndex; } ++$monthIndex; } return $monthName; } /** * Strips an ordinal from a numeric value. * * @param string $day Day number with an ordinal * * @return int|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric */ public static function dayStringToNumber($day) { $strippedDayValue = (str_replace(self::$numberSuffixes, '', $day)); if (is_numeric($strippedDayValue)) { return (int) $strippedDayValue; } return $day; } public static function dateTimeFromTimestamp(string $date, ?DateTimeZone $timeZone = null): DateTime { $dtobj = DateTime::createFromFormat('U', $date) ?: new DateTime(); $dtobj->setTimeZone($timeZone ?? self::getDefaultOrLocalTimezone()); return $dtobj; } public static function formattedDateTimeFromTimestamp(string $date, string $format, ?DateTimeZone $timeZone = null): string { $dtobj = self::dateTimeFromTimestamp($date, $timeZone); return $dtobj->format($format); } }