PHPExcel_Shared_Date Class Reference

Collaboration diagram for PHPExcel_Shared_Date:

Static Public Member Functions
static	setExcelCalendar ($baseDate)
	Set the Excel calendar (Windows 1900 or Mac 1904)
static	getExcelCalendar ()
	Return the Excel calendar (Windows 1900 or Mac 1904)
static	ExcelToPHP ($dateValue=0)
	Convert a date from Excel to PHP.
static	ExcelToPHPObject ($dateValue=0)
	Convert a date from Excel to a PHP Date/Time object.
static	PHPToExcel ($dateValue=0)
	Convert a date from PHP to Excel.
static	FormattedPHPToExcel ($year, $month, $day, $hours=0, $minutes=0, $seconds=0)
	FormattedPHPToExcel.
static	isDateTime (PHPExcel_Cell $pCell)
	Is a given cell a date/time?
static	isDateTimeFormat (PHPExcel_Style_NumberFormat $pFormat)
	Is a given number format a date/time?
static	isDateTimeFormatCode ($pFormatCode= '')
	Is a given number format code a date/time?
static	stringToExcel ($dateValue= '')
	Convert a date/time string to Excel time.

Data Fields
const	CALENDAR_WINDOWS_1900 = 1900
	constants
const	CALENDAR_MAC_1904 = 1904

Static Public Attributes
static	$dateTimeObjectType = 'DateTime'

Static Private Attributes
static	$ExcelBaseDate = self::CALENDAR_WINDOWS_1900
static	$possibleDateFormatCharacters = 'ymdHs'

Detailed Description

Definition at line 37 of file Date.php.

Member Function Documentation

static PHPExcel_Shared_Date::ExcelToPHP (   $dateValue = 0 )

static

Convert a date from Excel to PHP.

Parameters

long

$dateValue

Excel date/time value

Returns

long PHP serialized date/time

Definition at line 80 of file Date.php.

Referenced by PHPExcel_Calculation_DateTime\DATE(), PHPExcel_Calculation_DateTime\DATENOW(), PHPExcel_Calculation_DateTime\DATEVALUE(), PHPExcel_Calculation_DateTime\EDATE(), PHPExcel_Calculation_DateTime\EOMONTH(), ExcelToPHPObject(), PHPExcel_Calculation_DateTime\HOUROFDAY(), PHPExcel_Calculation_DateTime\MINUTEOFHOUR(), PHPExcel_Calculation_DateTime\SECONDOFMINUTE(), PHPExcel_Calculation_DateTime\TIME(), PHPExcel_Calculation_DateTime\TIMEVALUE(), and PHPExcel_Calculation_DateTime\WORKDAY().

                                                          {
                if (self::$ExcelBaseDate == self::CALENDAR_WINDOWS_1900) {
                        $myExcelBaseDate = 25569;
                        //      Adjust for the spurious 29-Feb-1900 (Day 60)
                        if ($dateValue < 60) {
                                --$myExcelBaseDate;
                        }
                } else {
                        $myExcelBaseDate = 24107;
                }

                // Perform conversion
                if ($dateValue >= 1) {
                        $utcDays = $dateValue - $myExcelBaseDate;
                        $returnValue = round($utcDays * 24 * 60 * 60);
                        if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
                                $returnValue = (integer) $returnValue;
                        }
                } else {
                        $hours = round($dateValue * 24);
                        $mins = round($dateValue * 24 * 60) - round($hours * 60);
                        $secs = round($dateValue * 24 * 60 * 60) - round($hours * 60 * 60) - round($mins * 60);
                        $returnValue = (integer) gmmktime($hours, $mins, $secs);
                }

                // Return
                return $returnValue;
        }       //      function ExcelToPHP()

Here is the caller graph for this function:

static PHPExcel_Shared_Date::ExcelToPHPObject (   $dateValue = 0 )

static

Convert a date from Excel to a PHP Date/Time object.

Parameters

long

$dateValue

Excel date/time value

Returns

long PHP date/time object

Definition at line 116 of file Date.php.

References ExcelToPHP().

Referenced by PHPExcel_Calculation_DateTime\_adjustDateByMonths(), PHPExcel_Calculation_Financial\_coupFirstPeriodDate(), PHPExcel_Calculation_DateTime\DATE(), PHPExcel_Calculation_DateTime\DATEDIF(), PHPExcel_Calculation_DateTime\DATENOW(), PHPExcel_Calculation_DateTime\DAYOFMONTH(), PHPExcel_Calculation_DateTime\DAYOFWEEK(), PHPExcel_Calculation_DateTime\DAYS360(), PHPExcel_Calculation_DateTime\MONTHOFYEAR(), PHPExcel_Style_NumberFormat\toFormattedString(), PHPExcel_Calculation_DateTime\WEEKOFYEAR(), PHPExcel_Calculation_DateTime\WORKDAY(), and PHPExcel_Calculation_DateTime\YEAR().

                                                                {
                $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()

Here is the call graph for this function:

Here is the caller graph for this function:

static PHPExcel_Shared_Date::FormattedPHPToExcel (   $year,   $month,   $day,   $hours = 0,   $minutes = 0,   $seconds = 0  )

static

FormattedPHPToExcel.

Parameters

long	$year
long	$month
long	$day
long	$hours
long	$minutes
long	$seconds

Returns

long Excel date/time value

Definition at line 168 of file Date.php.

Referenced by PHPExcel_Calculation_DateTime\DATE(), PHPExcel_Calculation_DateTime\DATEVALUE(), PHPExcel_Reader_OOCalc\loadIntoExisting(), PHPToExcel(), PHPExcel_Calculation_DateTime\TIME(), and PHPExcel_Calculation_DateTime\TIMEVALUE().

                                                                                                          {
                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; }
                        $myExcelBaseDate = 2415020;
                } else {
                        $myExcelBaseDate = 2416481;
                        $excel1900isLeapYear = False;
                }

                //      Julian base date Adjustment
                if ($month > 2) {
                        $month = $month - 3;
                } else {
                        $month = $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 - $myExcelBaseDate + $excel1900isLeapYear;

                $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;

                return (float) $excelDate + $excelTime;
        }       //      function FormattedPHPToExcel()

Here is the caller graph for this function:

static PHPExcel_Shared_Date::getExcelCalendar ( )

static

Return the Excel calendar (Windows 1900 or Mac 1904)

Returns

integer $baseDate Excel base date

Definition at line 69 of file Date.php.

References $ExcelBaseDate.

Referenced by PHPExcel_Writer_Excel2007_Workbook\_writeWorkbookPr(), PHPExcel_Calculation_DateTime\DATE(), and PHPExcel_Calculation_DateTime\TIME().

                                                  {
                return self::$ExcelBaseDate;
        }       //      function getExcelCalendar()

Here is the caller graph for this function:

static PHPExcel_Shared_Date::isDateTime ( PHPExcel_Cell  $pCell )

static

Is a given cell a date/time?

Parameters

PHPExcel_Cell

$pCell

Returns

boolean

Definition at line 207 of file Date.php.

References PHPExcel_Cell\getCoordinate(), PHPExcel_Cell\getParent(), and isDateTimeFormat().

                                                                {
                return self::isDateTimeFormat($pCell->getParent()->getStyle($pCell->getCoordinate())->getNumberFormat());
        }       //      function isDateTime()

Here is the call graph for this function:

static PHPExcel_Shared_Date::isDateTimeFormat ( PHPExcel_Style_NumberFormat  $pFormat )

static

Is a given number format a date/time?

Parameters

PHPExcel_Style_NumberFormat

$pFormat

Returns

boolean

Definition at line 218 of file Date.php.

References PHPExcel_Style_NumberFormat\getFormatCode(), and isDateTimeFormatCode().

Referenced by isDateTime().

                                                                                      {
                return self::isDateTimeFormatCode($pFormat->getFormatCode());
        }       //      function isDateTimeFormat()

Here is the call graph for this function:

Here is the caller graph for this function:

static PHPExcel_Shared_Date::isDateTimeFormatCode (   $pFormatCode = '' )

static

Is a given number format code a date/time?

Parameters

string

$pFormatCode

Returns

boolean

Definition at line 231 of file Date.php.

References PHPExcel_Style_NumberFormat\FORMAT_DATE_DATETIME, PHPExcel_Style_NumberFormat\FORMAT_DATE_DDMMYYYY, PHPExcel_Style_NumberFormat\FORMAT_DATE_DMMINUS, PHPExcel_Style_NumberFormat\FORMAT_DATE_DMYMINUS, PHPExcel_Style_NumberFormat\FORMAT_DATE_DMYSLASH, PHPExcel_Style_NumberFormat\FORMAT_DATE_MYMINUS, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME1, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME2, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME3, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME4, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME5, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME6, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME7, PHPExcel_Style_NumberFormat\FORMAT_DATE_TIME8, PHPExcel_Style_NumberFormat\FORMAT_DATE_XLSX14, PHPExcel_Style_NumberFormat\FORMAT_DATE_XLSX15, PHPExcel_Style_NumberFormat\FORMAT_DATE_XLSX16, PHPExcel_Style_NumberFormat\FORMAT_DATE_XLSX17, PHPExcel_Style_NumberFormat\FORMAT_DATE_XLSX22, PHPExcel_Style_NumberFormat\FORMAT_DATE_YYYYMMDD, PHPExcel_Style_NumberFormat\FORMAT_DATE_YYYYMMDD2, and PHPExcel_Style_NumberFormat\FORMAT_DATE_YYYYMMDDSLASH.

Referenced by isDateTimeFormat(), and PHPExcel_Calculation_TextData\TEXTFORMAT().

                                                                       {
                // Switch on formatcode
                switch ($pFormatCode) {
                        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) {
                                $i = false;
                                foreach(explode('"',$pFormatCode) as $subVal) {
                                        //      Only test in alternate array entries (the non-quoted blocks)
                                        if (($i = !$i) && (preg_match('/(^|\])[^\[]*['.self::$possibleDateFormatCharacters.']/i',$subVal))) {
                                                return true;
                                        }
                                }
                                return false;
                        }
                        return true;
                }

                // No date...
                return false;
        }       //      function isDateTimeFormatCode()

Here is the caller graph for this function:

static PHPExcel_Shared_Date::PHPToExcel (   $dateValue = 0 )

static

Convert a date from PHP to Excel.

Parameters

mixed

$dateValue

PHP serialized date/time or date object

Returns

mixed Excel date/time value or boolean False on failure

Definition at line 138 of file Date.php.

References FormattedPHPToExcel().

Referenced by PHPExcel_Calculation_Financial\_coupFirstPeriodDate(), PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_DateTime\DATENOW(), PHPExcel_Calculation_DateTime\DATETIMENOW(), PHPExcel_Calculation_DateTime\EDATE(), PHPExcel_Calculation_DateTime\EOMONTH(), PHPExcel_Reader_OOCalc\loadIntoExisting(), and PHPExcel_Reader_Excel2003XML\loadIntoExisting().

                                                          {
                $saveTimeZone = date_default_timezone_get();
                date_default_timezone_set('UTC');
                $retValue = False;
                if ((is_object($dateValue)) && ($dateValue instanceof self::$dateTimeObjectType)) {
                        $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()

Here is the call graph for this function:

Here is the caller graph for this function:

static PHPExcel_Shared_Date::setExcelCalendar (   $baseDate )

static

Set the Excel calendar (Windows 1900 or Mac 1904)

Parameters

integer

$baseDate

Excel base date

Returns

boolean Success or failure

Definition at line 54 of file Date.php.

Referenced by PHPExcel_Reader_Excel5\_readDateMode(), and PHPExcel_Reader_Excel2007\load().

                                                           {
                if (($baseDate == self::CALENDAR_WINDOWS_1900) ||
                        ($baseDate == self::CALENDAR_MAC_1904)) {
                        self::$ExcelBaseDate = $baseDate;
                        return True;
                }
                return False;
        }       //      function setExcelCalendar()

Here is the caller graph for this function:

static PHPExcel_Shared_Date::stringToExcel (   $dateValue = '' )

static

Convert a date/time string to Excel time.

Parameters

string

$dateValue

Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'

Returns

float|false Excel date/time serial value

Definition at line 291 of file Date.php.

References PHPExcel_Calculation_DateTime\DATEVALUE(), PHPExcel_Calculation_DateTime\TIMEVALUE(), and PHPExcel_Calculation_Functions\VALUE().

Referenced by PHPExcel_Cell_AdvancedValueBinder\bindValue().

                                                              {
                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;
                }


        }

Here is the call graph for this function:

Here is the caller graph for this function:

Field Documentation

PHPExcel_Shared_Date::$dateTimeObjectType = 'DateTime'

static

Definition at line 45 of file Date.php.

Referenced by PHPExcel_Calculation_DateTime\_getDateValue().

PHPExcel_Shared_Date::$ExcelBaseDate = self::CALENDAR_WINDOWS_1900

staticprivate

Definition at line 43 of file Date.php.

Referenced by getExcelCalendar().

PHPExcel_Shared_Date::$possibleDateFormatCharacters = 'ymdHs'

staticprivate

Definition at line 223 of file Date.php.

const PHPExcel_Shared_Date::CALENDAR_MAC_1904 = 1904

Definition at line 41 of file Date.php.

Referenced by PHPExcel_Reader_Excel5\_readDateMode(), PHPExcel_Writer_Excel2007_Workbook\_writeWorkbookPr(), and PHPExcel_Reader_Excel2007\load().

const PHPExcel_Shared_Date::CALENDAR_WINDOWS_1900 = 1900

constants

Definition at line 40 of file Date.php.

Referenced by PHPExcel_Reader_Excel5\_readDateMode(), PHPExcel_Reader_Excel2007\load(), and PHPExcel_Calculation_DateTime\TIME().

---

The documentation for this class was generated from the following file:

• Services/Excel/PHPExcel/1.7.6/Classes/PHPExcel/Shared/Date.php