[Summary view]

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