[Summary view]

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

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