You are here

Home » API reference » Loft Data Grids 7.2 » Date.php

class PHPExcel_Shared_Date in Loft Data Grids 7.2

Same name and namespace in other branches
  1. 6.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Shared/Date.php \PHPExcel_Shared_Date

PHPExcel_Shared_Date

@category PHPExcel @package PHPExcel_Shared @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)

Hierarchy

Expanded class hierarchy of PHPExcel_Shared_Date

9 string references to 'PHPExcel_Shared_Date'
DateTest::testDateTimeExcelToPHP1900 in vendor/phpoffice/phpexcel/unitTests/Classes/PHPExcel/Shared/DateTest.php
@dataProvider providerDateTimeExcelToPHP1900
DateTest::testDateTimeExcelToPHP1900Timezone in vendor/phpoffice/phpexcel/unitTests/Classes/PHPExcel/Shared/DateTest.php
@dataProvider providerDateTimeExcelToPHP1900Timezone
DateTest::testDateTimeExcelToPHP1904 in vendor/phpoffice/phpexcel/unitTests/Classes/PHPExcel/Shared/DateTest.php
@dataProvider providerDateTimeExcelToPHP1904
DateTest::testDateTimeFormattedPHPToExcel1900 in vendor/phpoffice/phpexcel/unitTests/Classes/PHPExcel/Shared/DateTest.php
@dataProvider providerDateTimeFormattedPHPToExcel1900
DateTest::testDateTimePHPToExcel1900 in vendor/phpoffice/phpexcel/unitTests/Classes/PHPExcel/Shared/DateTest.php
@dataProvider providerDateTimePHPToExcel1900

... See full list

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/Shared/Date.php, line 37

View source 
class PHPExcel_Shared_Date {

  /** constants */
  const CALENDAR_WINDOWS_1900 = 1900;

  //	Base date of 1st Jan 1900 = 1.0
  const CALENDAR_MAC_1904 = 1904;

  //	Base date of 2nd Jan 1904 = 1.0

  /*
   * Names of the months of the year, indexed by shortname
   * Planned usage for locale settings
   *
   * @public
   * @var	string[]
   */
  public static $_monthNames = array(
    'Jan' => 'January',
    'Feb' => 'February',
    'Mar' => 'March',
    'Apr' => 'April',
    'May' => 'May',
    'Jun' => 'June',
    'Jul' => 'July',
    'Aug' => 'August',
    'Sep' => 'September',
    'Oct' => 'October',
    'Nov' => 'November',
    'Dec' => 'December',
  );

  /*
   * Names of the months of the year, indexed by shortname
   * Planned usage for locale settings
   *
   * @public
   * @var	string[]
   */
  public static $_numberSuffixes = array(
    'st',
    'nd',
    'rd',
    'th',
  );

  /*
   * Base calendar year to use for calculations
   *
   * @private
   * @var	int
   */
  protected static $_excelBaseDate = self::CALENDAR_WINDOWS_1900;

  /**
   * Set the Excel calendar (Windows 1900 or Mac 1904)
   *
   * @param	 integer	$baseDate			Excel base date (1900 or 1904)
   * @return	 boolean						Success or failure
   */
  public static function setExcelCalendar($baseDate) {
    if ($baseDate == self::CALENDAR_WINDOWS_1900 || $baseDate == self::CALENDAR_MAC_1904) {
      self::$_excelBaseDate = $baseDate;
      return TRUE;
    }
    return FALSE;
  }

  //	function setExcelCalendar()

  /**
   * Return the Excel calendar (Windows 1900 or Mac 1904)
   *
   * @return	 integer	Excel base date (1900 or 1904)
   */
  public static function getExcelCalendar() {
    return self::$_excelBaseDate;
  }

  //	function getExcelCalendar()

  /**
   *	Convert a date from Excel to PHP
   *
   *	@param		long		$dateValue			Excel date/time value
   *	@param		boolean		$adjustToTimezone	Flag indicating whether $dateValue should be treated as
   *													a UST timestamp, or adjusted to UST
   *	@param		string	 	$timezone			The timezone for finding the adjustment from UST
   *	@return		long		PHP serialized date/time
   */
  public static function ExcelToPHP($dateValue = 0, $adjustToTimezone = FALSE, $timezone = NULL) {
    if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {
      $my_excelBaseDate = 25569;

      //	Adjust for the spurious 29-Feb-1900 (Day 60)
      if ($dateValue < 60) {
        --$my_excelBaseDate;
      }
    }
    else {
      $my_excelBaseDate = 24107;
    }

    // Perform conversion
    if ($dateValue >= 1) {
      $utcDays = $dateValue - $my_excelBaseDate;
      $returnValue = round($utcDays * 86400);
      if ($returnValue <= PHP_INT_MAX && $returnValue >= -PHP_INT_MAX) {
        $returnValue = (int) $returnValue;
      }
    }
    else {
      $hours = round($dateValue * 24);
      $mins = round($dateValue * 1440) - round($hours * 60);
      $secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
      $returnValue = (int) gmmktime($hours, $mins, $secs);
    }
    $timezoneAdjustment = $adjustToTimezone ? PHPExcel_Shared_TimeZone::getTimezoneAdjustment($timezone, $returnValue) : 0;

    // Return
    return $returnValue + $timezoneAdjustment;
  }

  //	function ExcelToPHP()

  /**
   * Convert a date from Excel to a PHP Date/Time object
   *
   * @param	integer		$dateValue		Excel date/time value
   * @return	DateTime					PHP date/time object
   */
  public static function ExcelToPHPObject($dateValue = 0) {
    $dateTime = self::ExcelToPHP($dateValue);
    $days = floor($dateTime / 86400);
    $time = round(($dateTime / 86400 - $days) * 86400);
    $hours = round($time / 3600);
    $minutes = round($time / 60) - $hours * 60;
    $seconds = round($time) - $hours * 3600 - $minutes * 60;
    $dateObj = date_create('1-Jan-1970+' . $days . ' days');
    $dateObj
      ->setTime($hours, $minutes, $seconds);
    return $dateObj;
  }

  //	function ExcelToPHPObject()

  /**
   *	Convert a date from PHP to Excel
   *
   *	@param	mixed		$dateValue			PHP serialized date/time or date object
   *	@param	boolean		$adjustToTimezone	Flag indicating whether $dateValue should be treated as
   *													a UST timestamp, or adjusted to UST
   *	@param	string	 	$timezone			The timezone for finding the adjustment from UST
   *	@return	mixed		Excel date/time value
   *							or boolean FALSE on failure
   */
  public static function PHPToExcel($dateValue = 0, $adjustToTimezone = FALSE, $timezone = NULL) {
    $saveTimeZone = date_default_timezone_get();
    date_default_timezone_set('UTC');
    $retValue = FALSE;
    if (is_object($dateValue) && $dateValue instanceof DateTime) {
      $retValue = self::FormattedPHPToExcel($dateValue
        ->format('Y'), $dateValue
        ->format('m'), $dateValue
        ->format('d'), $dateValue
        ->format('H'), $dateValue
        ->format('i'), $dateValue
        ->format('s'));
    }
    elseif (is_numeric($dateValue)) {
      $retValue = self::FormattedPHPToExcel(date('Y', $dateValue), date('m', $dateValue), date('d', $dateValue), date('H', $dateValue), date('i', $dateValue), date('s', $dateValue));
    }
    date_default_timezone_set($saveTimeZone);
    return $retValue;
  }

  //	function PHPToExcel()

  /**
   * FormattedPHPToExcel
   *
   * @param	long	$year
   * @param	long	$month
   * @param	long	$day
   * @param	long	$hours
   * @param	long	$minutes
   * @param	long	$seconds
   * @return  long				Excel date/time value
   */
  public static function FormattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0) {
    if (self::$_excelBaseDate == self::CALENDAR_WINDOWS_1900) {

      //
      //	Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
      //	This affects every date following 28th February 1900
      //
      $excel1900isLeapYear = TRUE;
      if ($year == 1900 && $month <= 2) {
        $excel1900isLeapYear = FALSE;
      }
      $my_excelBaseDate = 2415020;
    }
    else {
      $my_excelBaseDate = 2416481;
      $excel1900isLeapYear = FALSE;
    }

    //	Julian base date Adjustment
    if ($month > 2) {
      $month -= 3;
    }
    else {
      $month += 9;
      --$year;
    }

    //	Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
    $century = substr($year, 0, 2);
    $decade = substr($year, 2, 2);
    $excelDate = floor(146097 * $century / 4) + floor(1461 * $decade / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $my_excelBaseDate + $excel1900isLeapYear;
    $excelTime = ($hours * 3600 + $minutes * 60 + $seconds) / 86400;
    return (double) $excelDate + $excelTime;
  }

  //	function FormattedPHPToExcel()

  /**
   * Is a given cell a date/time?
   *
   * @param	 PHPExcel_Cell	$pCell
   * @return	 boolean
   */
  public static function isDateTime(PHPExcel_Cell $pCell) {
    return self::isDateTimeFormat($pCell
      ->getWorksheet()
      ->getStyle($pCell
      ->getCoordinate())
      ->getNumberFormat());
  }

  //	function isDateTime()

  /**
   * Is a given number format a date/time?
   *
   * @param	 PHPExcel_Style_NumberFormat	$pFormat
   * @return	 boolean
   */
  public static function isDateTimeFormat(PHPExcel_Style_NumberFormat $pFormat) {
    return self::isDateTimeFormatCode($pFormat
      ->getFormatCode());
  }

  //	function isDateTimeFormat()
  private static $possibleDateFormatCharacters = 'eymdHs';

  /**
   * Is a given number format code a date/time?
   *
   * @param	 string	$pFormatCode
   * @return	 boolean
   */
  public static function isDateTimeFormatCode($pFormatCode = '') {
    if (strtolower($pFormatCode) === strtolower(PHPExcel_Style_NumberFormat::FORMAT_GENERAL)) {

      //	"General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
      return FALSE;
    }
    if (preg_match('/[0#]E[+-]0/i', $pFormatCode)) {

      //	Scientific format
      return FALSE;
    }

    // Switch on formatcode
    switch ($pFormatCode) {

      //	Explicitly defined date formats
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYMINUS:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_DMMINUS:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_MYMINUS:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME1:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME2:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME5:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME6:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME7:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME8:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX14:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX16:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX17:
      case PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX22:
        return TRUE;
    }

    //	Typically number, currency or accounting (or occasionally fraction) formats
    if (substr($pFormatCode, 0, 1) == '_' || substr($pFormatCode, 0, 2) == '0 ') {
      return FALSE;
    }

    // Try checking for any of the date formatting characters that don't appear within square braces
    if (preg_match('/(^|\\])[^\\[]*[' . self::$possibleDateFormatCharacters . ']/i', $pFormatCode)) {

      //	We might also have a format mask containing quoted strings...
      //		we don't want to test for any of our characters within the quoted blocks
      if (strpos($pFormatCode, '"') !== FALSE) {
        $segMatcher = FALSE;
        foreach (explode('"', $pFormatCode) as $subVal) {

          //	Only test in alternate array entries (the non-quoted blocks)
          if (($segMatcher = !$segMatcher) && preg_match('/(^|\\])[^\\[]*[' . self::$possibleDateFormatCharacters . ']/i', $subVal)) {
            return TRUE;
          }
        }
        return FALSE;
      }
      return TRUE;
    }

    // No date...
    return FALSE;
  }

  //	function isDateTimeFormatCode()

  /**
   * Convert a date/time string to Excel time
   *
   * @param	string	$dateValue		Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
   * @return	float|FALSE		Excel date/time serial value
   */
  public static function stringToExcel($dateValue = '') {
    if (strlen($dateValue) < 2) {
      return FALSE;
    }
    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)) {
      return FALSE;
    }
    $dateValueNew = PHPExcel_Calculation_DateTime::DATEVALUE($dateValue);
    if ($dateValueNew === PHPExcel_Calculation_Functions::VALUE()) {
      return FALSE;
    }
    else {
      if (strpos($dateValue, ':') !== FALSE) {
        $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
        if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
          return FALSE;
        }
        $dateValueNew += $timeValue;
      }
      return $dateValueNew;
    }
  }
  public static function monthStringToNumber($month) {
    $monthIndex = 1;
    foreach (self::$_monthNames as $shortMonthName => $longMonthName) {
      if ($month === $longMonthName || $month === $shortMonthName) {
        return $monthIndex;
      }
      ++$monthIndex;
    }
    return $month;
  }
  public static function dayStringToNumber($day) {
    $strippedDayValue = str_replace(self::$_numberSuffixes, '', $day);
    if (is_numeric($strippedDayValue)) {
      return $strippedDayValue;
    }
    return $day;
  }

}

Members

Namesort descending Modifiers Type Description Overrides
PHPExcel_Shared_Date::$possibleDateFormatCharacters private static property
PHPExcel_Shared_Date::$_excelBaseDate protected static property
PHPExcel_Shared_Date::$_monthNames public static property
PHPExcel_Shared_Date::$_numberSuffixes public static property
PHPExcel_Shared_Date::CALENDAR_MAC_1904 constant
PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900 constant constants
PHPExcel_Shared_Date::dayStringToNumber public static function
PHPExcel_Shared_Date::ExcelToPHP public static function * Convert a date from Excel to PHP * *
PHPExcel_Shared_Date::ExcelToPHPObject public static function * Convert a date from Excel to a PHP Date/Time object * *
PHPExcel_Shared_Date::FormattedPHPToExcel public static function * FormattedPHPToExcel * *
PHPExcel_Shared_Date::getExcelCalendar public static function * Return the Excel calendar (Windows 1900 or Mac 1904) * *
PHPExcel_Shared_Date::isDateTime public static function * Is a given cell a date/time? * *
PHPExcel_Shared_Date::isDateTimeFormat public static function * Is a given number format a date/time? * *
PHPExcel_Shared_Date::isDateTimeFormatCode public static function * Is a given number format code a date/time? * *
PHPExcel_Shared_Date::monthStringToNumber public static function
PHPExcel_Shared_Date::PHPToExcel public static function * Convert a date from PHP to Excel * *
PHPExcel_Shared_Date::setExcelCalendar public static function * Set the Excel calendar (Windows 1900 or Mac 1904) * *
PHPExcel_Shared_Date::stringToExcel public static function * Convert a date/time string to Excel time * *