[Summary view] [Print] [Text view]

   1  <?php
   2  
   3  /**
   4   * PHPExcel_Shared_Date
   5   *
   6   * Copyright (c) 2006 - 2015 PHPExcel
   7   *
   8   * This library is free software; you can redistribute it and/or
   9   * modify it under the terms of the GNU Lesser General Public
  10   * License as published by the Free Software Foundation; either
  11   * version 2.1 of the License, or (at your option) any later version.
  12   *
  13   * This library is distributed in the hope that it will be useful,
  14   * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  16   * Lesser General Public License for more details.
  17   *
  18   * You should have received a copy of the GNU Lesser General Public
  19   * License along with this library; if not, write to the Free Software
  20   * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  21   *
  22   * @category   PHPExcel
  23   * @package    PHPExcel_Shared
  24   * @copyright  Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
  25   * @license    http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt    LGPL
  26   * @version    ##VERSION##, ##DATE##
  27   */
  28  class PHPExcel_Shared_Date
  29  {
  30      /** constants */
  31      const CALENDAR_WINDOWS_1900 = 1900;        //    Base date of 1st Jan 1900 = 1.0
  32      const CALENDAR_MAC_1904 = 1904;            //    Base date of 2nd Jan 1904 = 1.0
  33  
  34      /*
  35       * Names of the months of the year, indexed by shortname
  36       * Planned usage for locale settings
  37       *
  38       * @public
  39       * @var    string[]
  40       */
  41      public static $monthNames = array(
  42          'Jan' => 'January',
  43          'Feb' => 'February',
  44          'Mar' => 'March',
  45          'Apr' => 'April',
  46          'May' => 'May',
  47          'Jun' => 'June',
  48          'Jul' => 'July',
  49          'Aug' => 'August',
  50          'Sep' => 'September',
  51          'Oct' => 'October',
  52          'Nov' => 'November',
  53          'Dec' => 'December',
  54      );
  55  
  56      /*
  57       * Names of the months of the year, indexed by shortname
  58       * Planned usage for locale settings
  59       *
  60       * @public
  61       * @var    string[]
  62       */
  63      public static $numberSuffixes = array(
  64          'st',
  65          'nd',
  66          'rd',
  67          'th',
  68      );
  69  
  70      /*
  71       * Base calendar year to use for calculations
  72       *
  73       * @private
  74       * @var    int
  75       */
  76      protected static $excelBaseDate = self::CALENDAR_WINDOWS_1900;
  77  
  78      /**
  79       * Set the Excel calendar (Windows 1900 or Mac 1904)
  80       *
  81       * @param     integer    $baseDate           Excel base date (1900 or 1904)
  82       * @return    boolean                        Success or failure
  83       */
  84      public static function setExcelCalendar($baseDate)
  85      {
  86          if (($baseDate == self::CALENDAR_WINDOWS_1900) ||
  87              ($baseDate == self::CALENDAR_MAC_1904)) {
  88              self::$excelBaseDate = $baseDate;
  89              return true;
  90          }
  91          return false;
  92      }
  93  
  94  
  95      /**
  96       * Return the Excel calendar (Windows 1900 or Mac 1904)
  97       *
  98       * @return     integer    Excel base date (1900 or 1904)
  99       */
 100      public static function getExcelCalendar()
 101      {
 102          return self::$excelBaseDate;
 103      }
 104  
 105  
 106      /**
 107       *    Convert a date from Excel to PHP
 108       *
 109       *    @param        integer        $dateValue            Excel date/time value
 110       *    @param        boolean        $adjustToTimezone    Flag indicating whether $dateValue should be treated as
 111       *                                                    a UST timestamp, or adjusted to UST
 112       *    @param        string         $timezone            The timezone for finding the adjustment from UST
 113       *    @return       integer        PHP serialized date/time
 114       */
 115      public static function ExcelToPHP($dateValue = 0, $adjustToTimezone = false, $timezone = null)
 116      {
 117          if (self::$excelBaseDate == self::CALENDAR_WINDOWS_1900) {
 118              $myexcelBaseDate = 25569;
 119              //    Adjust for the spurious 29-Feb-1900 (Day 60)
 120              if ($dateValue < 60) {
 121                  --$myexcelBaseDate;
 122              }
 123          } else {
 124              $myexcelBaseDate = 24107;
 125          }
 126  
 127          // Perform conversion
 128          if ($dateValue >= 1) {
 129              $utcDays = $dateValue - $myexcelBaseDate;
 130              $returnValue = round($utcDays * 86400);
 131              if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
 132                  $returnValue = (integer) $returnValue;
 133              }
 134          } else {
 135              $hours = round($dateValue * 24);
 136              $mins = round($dateValue * 1440) - round($hours * 60);
 137              $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
 138              $returnValue = (integer) gmmktime($hours, $mins, $secs);
 139          }
 140  
 141          $timezoneAdjustment = ($adjustToTimezone) ?
 142              PHPExcel_Shared_TimeZone::getTimezoneAdjustment($timezone, $returnValue) :
 143              0;
 144  
 145          return $returnValue + $timezoneAdjustment;
 146      }
 147  
 148  
 149      /**
 150       * Convert a date from Excel to a PHP Date/Time object
 151       *
 152       * @param    integer        $dateValue        Excel date/time value
 153       * @return    DateTime                    PHP date/time object
 154       */
 155      public static function ExcelToPHPObject($dateValue = 0)
 156      {
 157          $dateTime = self::ExcelToPHP($dateValue);
 158          $days = floor($dateTime / 86400);
 159          $time = round((($dateTime / 86400) - $days) * 86400);
 160          $hours = round($time / 3600);
 161          $minutes = round($time / 60) - ($hours * 60);
 162          $seconds = round($time) - ($hours * 3600) - ($minutes * 60);
 163  
 164          $dateObj = date_create('1-Jan-1970+'.$days.' days');
 165          $dateObj->setTime($hours, $minutes, $seconds);
 166  
 167          return $dateObj;
 168      }
 169  
 170  
 171      /**
 172       *    Convert a date from PHP to Excel
 173       *
 174       *    @param    mixed        $dateValue            PHP serialized date/time or date object
 175       *    @param    boolean        $adjustToTimezone    Flag indicating whether $dateValue should be treated as
 176       *                                                    a UST timestamp, or adjusted to UST
 177       *    @param    string         $timezone            The timezone for finding the adjustment from UST
 178       *    @return    mixed        Excel date/time value
 179       *                            or boolean FALSE on failure
 180       */
 181      public static function PHPToExcel($dateValue = 0, $adjustToTimezone = false, $timezone = null)
 182      {
 183          $saveTimeZone = date_default_timezone_get();
 184          date_default_timezone_set('UTC');
 185          $retValue = false;
 186          if ((is_object($dateValue)) && ($dateValue instanceof DateTime)) {
 187              $retValue = self::FormattedPHPToExcel($dateValue->format('Y'), $dateValue->format('m'), $dateValue->format('d'), $dateValue->format('H'), $dateValue->format('i'), $dateValue->format('s'));
 188          } elseif (is_numeric($dateValue)) {
 189              $retValue = self::FormattedPHPToExcel(date('Y', $dateValue), date('m', $dateValue), date('d', $dateValue), date('H', $dateValue), date('i', $dateValue), date('s', $dateValue));
 190          }
 191          date_default_timezone_set($saveTimeZone);
 192  
 193          return $retValue;
 194      }
 195  
 196  
 197      /**
 198       * FormattedPHPToExcel
 199       *
 200       * @param    integer    $year
 201       * @param    integer    $month
 202       * @param    integer    $day
 203       * @param    integer    $hours
 204       * @param    integer    $minutes
 205       * @param    integer    $seconds
 206       * @return   integer    Excel date/time value
 207       */
 208      public static function FormattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0)
 209      {
 210          if (self::$excelBaseDate == self::CALENDAR_WINDOWS_1900) {
 211              //
 212              //    Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
 213              //    This affects every date following 28th February 1900
 214              //
 215              $excel1900isLeapYear = true;
 216              if (($year == 1900) && ($month <= 2)) {
 217                  $excel1900isLeapYear = false;
 218              }
 219              $myexcelBaseDate = 2415020;
 220          } else {
 221              $myexcelBaseDate = 2416481;
 222              $excel1900isLeapYear = false;
 223          }
 224  
 225          //    Julian base date Adjustment
 226          if ($month > 2) {
 227              $month -= 3;
 228          } else {
 229              $month += 9;
 230              --$year;
 231          }
 232  
 233          //    Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
 234          $century = substr($year, 0, 2);
 235          $decade = substr($year, 2, 2);
 236          $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myexcelBaseDate + $excel1900isLeapYear;
 237  
 238          $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
 239  
 240          return (float) $excelDate + $excelTime;
 241      }
 242  
 243  
 244      /**
 245       * Is a given cell a date/time?
 246       *
 247       * @param     PHPExcel_Cell    $pCell
 248       * @return     boolean
 249       */
 250      public static function isDateTime(PHPExcel_Cell $pCell)
 251      {
 252          return self::isDateTimeFormat(
 253              $pCell->getWorksheet()->getStyle(
 254                  $pCell->getCoordinate()
 255              )->getNumberFormat()
 256          );
 257      }
 258  
 259  
 260      /**
 261       * Is a given number format a date/time?
 262       *
 263       * @param     PHPExcel_Style_NumberFormat    $pFormat
 264       * @return     boolean
 265       */
 266      public static function isDateTimeFormat(PHPExcel_Style_NumberFormat $pFormat)
 267      {
 268          return self::isDateTimeFormatCode($pFormat->getFormatCode());
 269      }
 270  
 271  
 272      private static $possibleDateFormatCharacters = 'eymdHs';
 273  
 274      /**
 275       * Is a given number format code a date/time?
 276       *
 277       * @param     string    $pFormatCode
 278       * @return     boolean
 279       */
 280      public static function isDateTimeFormatCode($pFormatCode = '')
 281      {
 282          if (strtolower($pFormatCode) === strtolower(PHPExcel_Style_NumberFormat::FORMAT_GENERAL)) {
 283              //    "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
 284              return false;
 285          }
 286          if (preg_match('/[0#]E[+-]0/i', $pFormatCode)) {
 287              //    Scientific format
 288              return false;
 289          }
 290  
 291          // Switch on formatcode
 292          switch ($pFormatCode) {
 293              //    Explicitly defined date formats
 294              case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD:
 295              case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2:
 296              case PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY:
 297              case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH:
 298              case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS:
 299              case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS:
 300              case PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS:
 301              case PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME:
 302              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1:
 303              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2:
 304              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3:
 305              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4:
 306              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5:
 307              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6:
 308              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7:
 309              case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8:
 310              case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
 311              case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14:
 312              case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15:
 313              case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16:
 314              case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17:
 315              case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22:
 316                  return true;
 317          }
 318  
 319          //    Typically number, currency or accounting (or occasionally fraction) formats
 320          if ((substr($pFormatCode, 0, 1) == '_') || (substr($pFormatCode, 0, 2) == '0 ')) {
 321              return false;
 322          }
 323          // Try checking for any of the date formatting characters that don't appear within square braces
 324          if (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i', $pFormatCode)) {
 325              //    We might also have a format mask containing quoted strings...
 326              //        we don't want to test for any of our characters within the quoted blocks
 327              if (strpos($pFormatCode, '"') !== false) {
 328                  $segMatcher = false;
 329                  foreach (explode('"', $pFormatCode) as $subVal) {
 330                      //    Only test in alternate array entries (the non-quoted blocks)
 331                      if (($segMatcher = !$segMatcher) &&
 332                          (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i', $subVal))) {
 333                          return true;
 334                      }
 335                  }
 336                  return false;
 337              }
 338              return true;
 339          }
 340  
 341          // No date...
 342          return false;
 343      }
 344  
 345  
 346      /**
 347       * Convert a date/time string to Excel time
 348       *
 349       * @param    string    $dateValue        Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
 350       * @return    float|FALSE        Excel date/time serial value
 351       */
 352      public static function stringToExcel($dateValue = '')
 353      {
 354          if (strlen($dateValue) < 2) {
 355              return false;
 356          }
 357          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)) {
 358              return false;
 359          }
 360  
 361          $dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
 362  
 363          if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
 364              return false;
 365          }
 366  
 367          if (strpos($dateValue, ':') !== false) {
 368              $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
 369              if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
 370                  return false;
 371              }
 372              $dateValueNew += $timeValue;
 373          }
 374          return $dateValueNew;
 375      }
 376  
 377      /**
 378       * Converts a month name (either a long or a short name) to a month number
 379       *
 380       * @param     string    $month    Month name or abbreviation
 381       * @return    integer|string     Month number (1 - 12), or the original string argument if it isn't a valid month name
 382       */
 383      public static function monthStringToNumber($month)
 384      {
 385          $monthIndex = 1;
 386          foreach (self::$monthNames as $shortMonthName => $longMonthName) {
 387              if (($month === $longMonthName) || ($month === $shortMonthName)) {
 388                  return $monthIndex;
 389              }
 390              ++$monthIndex;
 391          }
 392          return $month;
 393      }
 394  
 395      /**
 396       * Strips an ordinal froma numeric value
 397       *
 398       * @param     string    $day      Day number with an ordinal
 399       * @return    integer|string      The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
 400       */
 401      public static function dayStringToNumber($day)
 402      {
 403          $strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
 404          if (is_numeric($strippedDayValue)) {
 405              return (integer) $strippedDayValue;
 406          }
 407          return $day;
 408      }
 409  }