[Summary view]

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Shared;
   4  
   5  use DateTime;
   6  use DateTimeInterface;
   7  use DateTimeZone;
   8  use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
  10  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
  11  use PhpOffice\PhpSpreadsheet\Cell\Cell;
  12  use PhpOffice\PhpSpreadsheet\Exception;
  13  use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
  14  use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDate;
  15  use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
  16  
  17  class Date
  18  {
  19      /** constants */
  20      const CALENDAR_WINDOWS_1900 = 1900; //    Base date of 1st Jan 1900 = 1.0
  21      const CALENDAR_MAC_1904 = 1904; //    Base date of 2nd Jan 1904 = 1.0
  22  
  23      /**
  24       * Names of the months of the year, indexed by shortname
  25       * Planned usage for locale settings.
  26       *
  27       * @var string[]
  28       */
  29      public static $monthNames = [
  30          'Jan' => 'January',
  31          'Feb' => 'February',
  32          'Mar' => 'March',
  33          'Apr' => 'April',
  34          'May' => 'May',
  35          'Jun' => 'June',
  36          'Jul' => 'July',
  37          'Aug' => 'August',
  38          'Sep' => 'September',
  39          'Oct' => 'October',
  40          'Nov' => 'November',
  41          'Dec' => 'December',
  42      ];
  43  
  44      /**
  45       * @var string[]
  46       */
  47      public static $numberSuffixes = [
  48          'st',
  49          'nd',
  50          'rd',
  51          'th',
  52      ];
  53  
  54      /**
  55       * Base calendar year to use for calculations
  56       * Value is either CALENDAR_WINDOWS_1900 (1900) or CALENDAR_MAC_1904 (1904).
  57       *
  58       * @var int
  59       */
  60      protected static $excelCalendar = self::CALENDAR_WINDOWS_1900;
  61  
  62      /**
  63       * Default timezone to use for DateTime objects.
  64       *
  65       * @var null|DateTimeZone
  66       */
  67      protected static $defaultTimeZone;
  68  
  69      /**
  70       * Set the Excel calendar (Windows 1900 or Mac 1904).
  71       *
  72       * @param int $baseYear Excel base date (1900 or 1904)
  73       *
  74       * @return bool Success or failure
  75       */
  76      public static function setExcelCalendar($baseYear)
  77      {
  78          if (
  79              ($baseYear == self::CALENDAR_WINDOWS_1900) ||
  80              ($baseYear == self::CALENDAR_MAC_1904)
  81          ) {
  82              self::$excelCalendar = $baseYear;
  83  
  84              return true;
  85          }
  86  
  87          return false;
  88      }
  89  
  90      /**
  91       * Return the Excel calendar (Windows 1900 or Mac 1904).
  92       *
  93       * @return int Excel base date (1900 or 1904)
  94       */
  95      public static function getExcelCalendar()
  96      {
  97          return self::$excelCalendar;
  98      }
  99  
 100      /**
 101       * Set the Default timezone to use for dates.
 102       *
 103       * @param null|DateTimeZone|string $timeZone The timezone to set for all Excel datetimestamp to PHP DateTime Object conversions
 104       *
 105       * @return bool Success or failure
 106       */
 107      public static function setDefaultTimezone($timeZone)
 108      {
 109          try {
 110              $timeZone = self::validateTimeZone($timeZone);
 111              self::$defaultTimeZone = $timeZone;
 112              $retval = true;
 113          } catch (PhpSpreadsheetException $e) {
 114              $retval = false;
 115          }
 116  
 117          return $retval;
 118      }
 119  
 120      /**
 121       * Return the Default timezone, or UTC if default not set.
 122       */
 123      public static function getDefaultTimezone(): DateTimeZone
 124      {
 125          return self::$defaultTimeZone ?? new DateTimeZone('UTC');
 126      }
 127  
 128      /**
 129       * Return the Default timezone, or local timezone if default is not set.
 130       */
 131      public static function getDefaultOrLocalTimezone(): DateTimeZone
 132      {
 133          return self::$defaultTimeZone ?? new DateTimeZone(date_default_timezone_get());
 134      }
 135  
 136      /**
 137       * Return the Default timezone even if null.
 138       */
 139      public static function getDefaultTimezoneOrNull(): ?DateTimeZone
 140      {
 141          return self::$defaultTimeZone;
 142      }
 143  
 144      /**
 145       * Validate a timezone.
 146       *
 147       * @param null|DateTimeZone|string $timeZone The timezone to validate, either as a timezone string or object
 148       *
 149       * @return ?DateTimeZone The timezone as a timezone object
 150       */
 151      private static function validateTimeZone($timeZone)
 152      {
 153          if ($timeZone instanceof DateTimeZone || $timeZone === null) {
 154              return $timeZone;
 155          }
 156          if (in_array($timeZone, DateTimeZone::listIdentifiers(DateTimeZone::ALL_WITH_BC))) {
 157              return new DateTimeZone($timeZone);
 158          }
 159  
 160          throw new PhpSpreadsheetException('Invalid timezone');
 161      }
 162  
 163      /**
 164       * @param mixed $value
 165       *
 166       * @return float|int
 167       */
 168      public static function convertIsoDate($value)
 169      {
 170          if (!is_string($value)) {
 171              throw new Exception('Non-string value supplied for Iso Date conversion');
 172          }
 173  
 174          $date = new DateTime($value);
 175          $dateErrors = DateTime::getLastErrors();
 176  
 177          if (is_array($dateErrors) && ($dateErrors['warning_count'] > 0 || $dateErrors['error_count'] > 0)) {
 178              throw new Exception("Invalid string $value supplied for datatype Date");
 179          }
 180  
 181          $newValue = SharedDate::PHPToExcel($date);
 182          if ($newValue === false) {
 183              throw new Exception("Invalid string $value supplied for datatype Date");
 184          }
 185  
 186          if (preg_match('/^\\d\\d:\\d\\d:\\d\\d/', $value) == 1) {
 187              $newValue = fmod($newValue, 1.0);
 188          }
 189  
 190          return $newValue;
 191      }
 192  
 193      /**
 194       * Convert a MS serialized datetime value from Excel to a PHP Date/Time object.
 195       *
 196       * @param float|int $excelTimestamp MS Excel serialized date/time value
 197       * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp,
 198       *                                                                        if you don't want to treat it as a UTC value
 199       *                                                                    Use the default (UTC) unless you absolutely need a conversion
 200       *
 201       * @return DateTime PHP date/time object
 202       */
 203      public static function excelToDateTimeObject($excelTimestamp, $timeZone = null)
 204      {
 205          $timeZone = ($timeZone === null) ? self::getDefaultTimezone() : self::validateTimeZone($timeZone);
 206          if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) {
 207              if ($excelTimestamp < 1 && self::$excelCalendar === self::CALENDAR_WINDOWS_1900) {
 208                  // Unix timestamp base date
 209                  $baseDate = new DateTime('1970-01-01', $timeZone);
 210              } else {
 211                  // MS Excel calendar base dates
 212                  if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
 213                      // Allow adjustment for 1900 Leap Year in MS Excel
 214                      $baseDate = ($excelTimestamp < 60) ? new DateTime('1899-12-31', $timeZone) : new DateTime('1899-12-30', $timeZone);
 215                  } else {
 216                      $baseDate = new DateTime('1904-01-01', $timeZone);
 217                  }
 218              }
 219          } else {
 220              $baseDate = new DateTime('1899-12-30', $timeZone);
 221          }
 222  
 223          $days = floor($excelTimestamp);
 224          $partDay = $excelTimestamp - $days;
 225          $hours = floor($partDay * 24);
 226          $partDay = $partDay * 24 - $hours;
 227          $minutes = floor($partDay * 60);
 228          $partDay = $partDay * 60 - $minutes;
 229          $seconds = round($partDay * 60);
 230  
 231          if ($days >= 0) {
 232              $days = '+' . $days;
 233          }
 234          $interval = $days . ' days';
 235  
 236          return $baseDate->modify($interval)
 237              ->setTime((int) $hours, (int) $minutes, (int) $seconds);
 238      }
 239  
 240      /**
 241       * Convert a MS serialized datetime value from Excel to a unix timestamp.
 242       * The use of Unix timestamps, and therefore this function, is discouraged.
 243       * They are not Y2038-safe on a 32-bit system, and have no timezone info.
 244       *
 245       * @param float|int $excelTimestamp MS Excel serialized date/time value
 246       * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp,
 247       *                                                                        if you don't want to treat it as a UTC value
 248       *                                                                    Use the default (UTC) unless you absolutely need a conversion
 249       *
 250       * @return int Unix timetamp for this date/time
 251       */
 252      public static function excelToTimestamp($excelTimestamp, $timeZone = null)
 253      {
 254          return (int) self::excelToDateTimeObject($excelTimestamp, $timeZone)
 255              ->format('U');
 256      }
 257  
 258      /**
 259       * Convert a date from PHP to an MS Excel serialized date/time value.
 260       *
 261       * @param mixed $dateValue PHP DateTime object or a string - Unix timestamp is also permitted, but discouraged;
 262       *    not Y2038-safe on a 32-bit system, and no timezone info
 263       *
 264       * @return false|float Excel date/time value
 265       *                                  or boolean FALSE on failure
 266       */
 267      public static function PHPToExcel($dateValue)
 268      {
 269          if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) {
 270              return self::dateTimeToExcel($dateValue);
 271          } elseif (is_numeric($dateValue)) {
 272              return self::timestampToExcel($dateValue);
 273          } elseif (is_string($dateValue)) {
 274              return self::stringToExcel($dateValue);
 275          }
 276  
 277          return false;
 278      }
 279  
 280      /**
 281       * Convert a PHP DateTime object to an MS Excel serialized date/time value.
 282       *
 283       * @param DateTimeInterface $dateValue PHP DateTime object
 284       *
 285       * @return float MS Excel serialized date/time value
 286       */
 287      public static function dateTimeToExcel(DateTimeInterface $dateValue)
 288      {
 289          return self::formattedPHPToExcel(
 290              (int) $dateValue->format('Y'),
 291              (int) $dateValue->format('m'),
 292              (int) $dateValue->format('d'),
 293              (int) $dateValue->format('H'),
 294              (int) $dateValue->format('i'),
 295              (int) $dateValue->format('s')
 296          );
 297      }
 298  
 299      /**
 300       * Convert a Unix timestamp to an MS Excel serialized date/time value.
 301       * The use of Unix timestamps, and therefore this function, is discouraged.
 302       * They are not Y2038-safe on a 32-bit system, and have no timezone info.
 303       *
 304       * @param int $unixTimestamp Unix Timestamp
 305       *
 306       * @return false|float MS Excel serialized date/time value
 307       */
 308      public static function timestampToExcel($unixTimestamp)
 309      {
 310          if (!is_numeric($unixTimestamp)) {
 311              return false;
 312          }
 313  
 314          return self::dateTimeToExcel(new DateTime('@' . $unixTimestamp));
 315      }
 316  
 317      /**
 318       * formattedPHPToExcel.
 319       *
 320       * @param int $year
 321       * @param int $month
 322       * @param int $day
 323       * @param int $hours
 324       * @param int $minutes
 325       * @param int $seconds
 326       *
 327       * @return float Excel date/time value
 328       */
 329      public static function formattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0)
 330      {
 331          if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
 332              //
 333              //    Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
 334              //    This affects every date following 28th February 1900
 335              //
 336              $excel1900isLeapYear = true;
 337              if (($year == 1900) && ($month <= 2)) {
 338                  $excel1900isLeapYear = false;
 339              }
 340              $myexcelBaseDate = 2415020;
 341          } else {
 342              $myexcelBaseDate = 2416481;
 343              $excel1900isLeapYear = false;
 344          }
 345  
 346          //    Julian base date Adjustment
 347          if ($month > 2) {
 348              $month -= 3;
 349          } else {
 350              $month += 9;
 351              --$year;
 352          }
 353  
 354          //    Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
 355          $century = (int) substr($year, 0, 2);
 356          $decade = (int) substr($year, 2, 2);
 357          $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myexcelBaseDate + $excel1900isLeapYear;
 358  
 359          $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
 360  
 361          return (float) $excelDate + $excelTime;
 362      }
 363  
 364      /**
 365       * Is a given cell a date/time?
 366       *
 367       * @return bool
 368       */
 369      public static function isDateTime(Cell $cell)
 370      {
 371          return is_numeric($cell->getCalculatedValue()) &&
 372              self::isDateTimeFormat(
 373                  $cell->getWorksheet()->getStyle(
 374                      $cell->getCoordinate()
 375                  )->getNumberFormat()
 376              );
 377      }
 378  
 379      /**
 380       * Is a given number format a date/time?
 381       *
 382       * @return bool
 383       */
 384      public static function isDateTimeFormat(NumberFormat $excelFormatCode)
 385      {
 386          return self::isDateTimeFormatCode($excelFormatCode->getFormatCode());
 387      }
 388  
 389      private static $possibleDateFormatCharacters = 'eymdHs';
 390  
 391      /**
 392       * Is a given number format code a date/time?
 393       *
 394       * @param string $excelFormatCode
 395       *
 396       * @return bool
 397       */
 398      public static function isDateTimeFormatCode($excelFormatCode)
 399      {
 400          if (strtolower($excelFormatCode) === strtolower(NumberFormat::FORMAT_GENERAL)) {
 401              //    "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
 402              return false;
 403          }
 404          if (preg_match('/[0#]E[+-]0/i', $excelFormatCode)) {
 405              //    Scientific format
 406              return false;
 407          }
 408  
 409          // Switch on formatcode
 410          switch ($excelFormatCode) {
 411              //    Explicitly defined date formats
 412              case NumberFormat::FORMAT_DATE_YYYYMMDD:
 413              case NumberFormat::FORMAT_DATE_YYYYMMDD2:
 414              case NumberFormat::FORMAT_DATE_DDMMYYYY:
 415              case NumberFormat::FORMAT_DATE_DMYSLASH:
 416              case NumberFormat::FORMAT_DATE_DMYMINUS:
 417              case NumberFormat::FORMAT_DATE_DMMINUS:
 418              case NumberFormat::FORMAT_DATE_MYMINUS:
 419              case NumberFormat::FORMAT_DATE_DATETIME:
 420              case NumberFormat::FORMAT_DATE_TIME1:
 421              case NumberFormat::FORMAT_DATE_TIME2:
 422              case NumberFormat::FORMAT_DATE_TIME3:
 423              case NumberFormat::FORMAT_DATE_TIME4:
 424              case NumberFormat::FORMAT_DATE_TIME5:
 425              case NumberFormat::FORMAT_DATE_TIME6:
 426              case NumberFormat::FORMAT_DATE_TIME7:
 427              case NumberFormat::FORMAT_DATE_TIME8:
 428              case NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
 429              case NumberFormat::FORMAT_DATE_XLSX14:
 430              case NumberFormat::FORMAT_DATE_XLSX15:
 431              case NumberFormat::FORMAT_DATE_XLSX16:
 432              case NumberFormat::FORMAT_DATE_XLSX17:
 433              case NumberFormat::FORMAT_DATE_XLSX22:
 434                  return true;
 435          }
 436  
 437          //    Typically number, currency or accounting (or occasionally fraction) formats
 438          if ((substr($excelFormatCode, 0, 1) == '_') || (substr($excelFormatCode, 0, 2) == '0 ')) {
 439              return false;
 440          }
 441          // Some "special formats" provided in German Excel versions were detected as date time value,
 442          // so filter them out here - "\C\H\-00000" (Switzerland) and "\D-00000" (Germany).
 443          if (\strpos($excelFormatCode, '-00000') !== false) {
 444              return false;
 445          }
 446          // Try checking for any of the date formatting characters that don't appear within square braces
 447          if (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/i', $excelFormatCode)) {
 448              //    We might also have a format mask containing quoted strings...
 449              //        we don't want to test for any of our characters within the quoted blocks
 450              if (strpos($excelFormatCode, '"') !== false) {
 451                  $segMatcher = false;
 452                  foreach (explode('"', $excelFormatCode) as $subVal) {
 453                      //    Only test in alternate array entries (the non-quoted blocks)
 454                      if (
 455                          ($segMatcher = !$segMatcher) &&
 456                          (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/i', $subVal))
 457                      ) {
 458                          return true;
 459                      }
 460                  }
 461  
 462                  return false;
 463              }
 464  
 465              return true;
 466          }
 467  
 468          // No date...
 469          return false;
 470      }
 471  
 472      /**
 473       * Convert a date/time string to Excel time.
 474       *
 475       * @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
 476       *
 477       * @return false|float Excel date/time serial value
 478       */
 479      public static function stringToExcel($dateValue)
 480      {
 481          if (strlen($dateValue) < 2) {
 482              return false;
 483          }
 484          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)) {
 485              return false;
 486          }
 487  
 488          $dateValueNew = DateTimeExcel\DateValue::fromString($dateValue);
 489  
 490          if ($dateValueNew === ExcelError::VALUE()) {
 491              return false;
 492          }
 493  
 494          if (strpos($dateValue, ':') !== false) {
 495              $timeValue = DateTimeExcel\TimeValue::fromString($dateValue);
 496              if ($timeValue === ExcelError::VALUE()) {
 497                  return false;
 498              }
 499              $dateValueNew += $timeValue;
 500          }
 501  
 502          return $dateValueNew;
 503      }
 504  
 505      /**
 506       * Converts a month name (either a long or a short name) to a month number.
 507       *
 508       * @param string $monthName Month name or abbreviation
 509       *
 510       * @return int|string Month number (1 - 12), or the original string argument if it isn't a valid month name
 511       */
 512      public static function monthStringToNumber($monthName)
 513      {
 514          $monthIndex = 1;
 515          foreach (self::$monthNames as $shortMonthName => $longMonthName) {
 516              if (($monthName === $longMonthName) || ($monthName === $shortMonthName)) {
 517                  return $monthIndex;
 518              }
 519              ++$monthIndex;
 520          }
 521  
 522          return $monthName;
 523      }
 524  
 525      /**
 526       * Strips an ordinal from a numeric value.
 527       *
 528       * @param string $day Day number with an ordinal
 529       *
 530       * @return int|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
 531       */
 532      public static function dayStringToNumber($day)
 533      {
 534          $strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
 535          if (is_numeric($strippedDayValue)) {
 536              return (int) $strippedDayValue;
 537          }
 538  
 539          return $day;
 540      }
 541  
 542      public static function dateTimeFromTimestamp(string $date, ?DateTimeZone $timeZone = null): DateTime
 543      {
 544          $dtobj = DateTime::createFromFormat('U', $date) ?: new DateTime();
 545          $dtobj->setTimeZone($timeZone ?? self::getDefaultOrLocalTimezone());
 546  
 547          return $dtobj;
 548      }
 549  
 550      public static function formattedDateTimeFromTimestamp(string $date, string $format, ?DateTimeZone $timeZone = null): string
 551      {
 552          $dtobj = self::dateTimeFromTimestamp($date, $timeZone);
 553  
 554          return $dtobj->format($format);
 555      }
 556  }