Файл: protected/extensions/PHPExcel/Classes/PHPExcel/Shared/Date.php
Строк: 385
<?php
/**
* PHPExcel_Shared_Date
*
* Copyright (c) 2006 - 2015 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel_Shared
* @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version ##VERSION##, ##DATE##
*/
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;
}
/**
* Return the Excel calendar (Windows 1900 or Mac 1904)
*
* @return integer Excel base date (1900 or 1904)
*/
public static function getExcelCalendar()
{
return self::$excelBaseDate;
}
/**
* Convert a date from Excel to PHP
*
* @param integer $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 integer PHP serialized date/time
*/
public static function ExcelToPHP($dateValue = 0, $adjustToTimezone = false, $timezone = null)
{
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 * 86400);
if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
$returnValue = (integer) $returnValue;
}
} else {
$hours = round($dateValue * 24);
$mins = round($dateValue * 1440) - round($hours * 60);
$secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
$returnValue = (integer) gmmktime($hours, $mins, $secs);
}
$timezoneAdjustment = ($adjustToTimezone) ?
PHPExcel_Shared_TimeZone::getTimezoneAdjustment($timezone, $returnValue) :
0;
return $returnValue + $timezoneAdjustment;
}
/**
* 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;
}
/**
* 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');
$timezoneAdjustment = ($adjustToTimezone) ?
PHPExcel_Shared_TimeZone::getTimezoneAdjustment($timezone ? $timezone : $saveTimeZone, $dateValue) :
0;
$retValue = false;
if ((is_object($dateValue)) && ($dateValue instanceof DateTime)) {
$dateValue->add(new DateInterval('PT' . $timezoneAdjustment . 'S'));
$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)) {
$dateValue += $timezoneAdjustment;
$retValue = self::FormattedPHPToExcel(date('Y', $dateValue), date('m', $dateValue), date('d', $dateValue), date('H', $dateValue), date('i', $dateValue), date('s', $dateValue));
} elseif (is_string($dateValue)) {
$retValue = self::stringToExcel($dateValue);
}
date_default_timezone_set($saveTimeZone);
return $retValue;
}
/**
* FormattedPHPToExcel
*
* @param integer $year
* @param integer $month
* @param integer $day
* @param integer $hours
* @param integer $minutes
* @param integer $seconds
* @return integer 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;
}
$myexcelBaseDate = 2415020;
} else {
$myexcelBaseDate = 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 - $myexcelBaseDate + $excel1900isLeapYear;
$excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
return (float) $excelDate + $excelTime;
}
/**
* 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()
);
}
/**
* 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());
}
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;
}
/**
* 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;
}
if (strpos($dateValue, ':') !== false) {
$timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($dateValue);
if ($timeValue === PHPExcel_Calculation_Functions::VALUE()) {
return false;
}
$dateValueNew += $timeValue;
}
return $dateValueNew;
}
/**
* Converts a month name (either a long or a short name) to a month number
*
* @param string $month Month name or abbreviation
* @return integer|string Month number (1 - 12), or the original string argument if it isn't a valid month name
*/
public static function monthStringToNumber($month)
{
$monthIndex = 1;
foreach (self::$monthNames as $shortMonthName => $longMonthName) {
if (($month === $longMonthName) || ($month === $shortMonthName)) {
return $monthIndex;
}
++$monthIndex;
}
return $month;
}
/**
* Strips an ordinal froma numeric value
*
* @param string $day Day number with an ordinal
* @return integer|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
*/
public static function dayStringToNumber($day)
{
$strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
if (is_numeric($strippedDayValue)) {
return (integer) $strippedDayValue;
}
return $day;
}
}