[Summary view]

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

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