visualizer / vendor / phpoffice / phpexcel / Classes / PHPExcel / Shared / Date.php

Date.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.0.3, at vendor/phpoffice/phpexcel/Classes/PHPExcel/Shared/Date.php

394 lines 12.6 KB

Raw Download Zip

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