New file |
0,0 → 1,390 |
<?php |
|
/** |
* PHPExcel |
* |
* Copyright (c) 2006 - 2013 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 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel) |
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL |
* @version ##VERSION##, ##DATE## |
*/ |
|
|
/** |
* PHPExcel_Shared_Date |
* |
* @category PHPExcel |
* @package PHPExcel_Shared |
* @copyright Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel) |
*/ |
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 = (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 |
return $returnValue + $timezoneAdjustment; |
} // function ExcelToPHP() |
|
|
/** |
* Convert a date from Excel to a PHP Date/Time object |
* |
* @param integer $dateValue Excel date/time value |
* @return integer 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 (float) $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 = '') { |
// Switch on formatcode |
switch ($pFormatCode) { |
// General contains an epoch letter 'e', so we trap for it explicitly here |
case PHPExcel_Style_NumberFormat::FORMAT_GENERAL: |
return FALSE; |
// 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; |
} |
|
} |