tablepress / libraries / vendor / PhpSpreadsheet / Shared / Date.php

Date.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Shared/Date.php

651 lines 20.2 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Shared;
4
5	use DateTime;
6	use DateTimeInterface;
7	use DateTimeZone;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
9	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
10	use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell;
11	use TablePress\PhpOffice\PhpSpreadsheet\Exception;
12	use TablePress\PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
13	use TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat;
14	use Throwable;
15
16	class Date
17	{
18	/** constants */
19	const CALENDAR_WINDOWS_1900 = 1900; // Base date of 1st Jan 1900 = 1.0
20	const CALENDAR_MAC_1904 = 1904; // Base date of 2nd Jan 1904 = 1.0
21
22	/**
23	* Names of the months of the year, indexed by shortname
24	* Planned usage for locale settings.
25	*
26	* @var string[]
27	*/
28	public static array $monthNames = [
29	'Jan' => 'January',
30	'Feb' => 'February',
31	'Mar' => 'March',
32	'Apr' => 'April',
33	'May' => 'May',
34	'Jun' => 'June',
35	'Jul' => 'July',
36	'Aug' => 'August',
37	'Sep' => 'September',
38	'Oct' => 'October',
39	'Nov' => 'November',
40	'Dec' => 'December',
41	];
42
43	/**
44	* @var string[]
45	*/
46	public static array $numberSuffixes = [
47	'st',
48	'nd',
49	'rd',
50	'th',
51	];
52
53	/**
54	* Base calendar year to use for calculations
55	* Value is either CALENDAR_WINDOWS_1900 (1900) or CALENDAR_MAC_1904 (1904).
56	*/
57	protected static int $excelCalendar = self::CALENDAR_WINDOWS_1900;
58
59	/**
60	* Default timezone to use for DateTime objects.
61	*/
62	protected static ?DateTimeZone $defaultTimeZone = null;
63
64	/**
65	* Set the Excel calendar (Windows 1900 or Mac 1904).
66	*
67	* @param ?int $baseYear Excel base date (1900 or 1904)
68	*
69	* @return bool Success or failure
70	*/
71	public static function setExcelCalendar(?int $baseYear): bool
72	{
73	if (
74	($baseYear === self::CALENDAR_WINDOWS_1900)
75	\|\| ($baseYear === self::CALENDAR_MAC_1904)
76	) {
77	self::$excelCalendar = $baseYear;
78
79	return true;
80	}
81
82	return false;
83	}
84
85	/**
86	* Return the Excel calendar (Windows 1900 or Mac 1904).
87	*
88	* @return int Excel base date (1900 or 1904)
89	*/
90	public static function getExcelCalendar(): int
91	{
92	return self::$excelCalendar;
93	}
94
95	/**
96	* Set the Default timezone to use for dates.
97	*
98	* @param null\|DateTimeZone\|string $timeZone The timezone to set for all Excel datetimestamp to PHP DateTime Object conversions
99	*
100	* @return bool Success or failure
101	*/
102	public static function setDefaultTimezone($timeZone): bool
103	{
104	try {
105	$timeZone = self::validateTimeZone($timeZone);
106	self::$defaultTimeZone = $timeZone;
107	$retval = true;
108	} catch (PhpSpreadsheetException $exception) {
109	$retval = false;
110	}
111
112	return $retval;
113	}
114
115	/**
116	* Return the Default timezone, or UTC if default not set.
117	*/
118	public static function getDefaultTimezone(): DateTimeZone
119	{
120	return self::$defaultTimeZone ?? new DateTimeZone('UTC');
121	}
122
123	/**
124	* Return the Default timezone, or local timezone if default is not set.
125	*/
126	public static function getDefaultOrLocalTimezone(): DateTimeZone
127	{
128	return self::$defaultTimeZone ?? new DateTimeZone(date_default_timezone_get());
129	}
130
131	/**
132	* Return the Default timezone even if null.
133	*/
134	public static function getDefaultTimezoneOrNull(): ?DateTimeZone
135	{
136	return self::$defaultTimeZone;
137	}
138
139	/**
140	* Validate a timezone.
141	*
142	* @param null\|DateTimeZone\|string $timeZone The timezone to validate, either as a timezone string or object
143	*
144	* @return ?DateTimeZone The timezone as a timezone object
145	*/
146	private static function validateTimeZone($timeZone): ?DateTimeZone
147	{
148	if ($timeZone instanceof DateTimeZone \|\| $timeZone === null) {
149	return $timeZone;
150	}
151	if (in_array($timeZone, DateTimeZone::listIdentifiers(DateTimeZone::ALL_WITH_BC))) {
152	return new DateTimeZone($timeZone);
153	}
154
155	throw new PhpSpreadsheetException('Invalid timezone');
156	}
157
158	/**
159	* @param mixed $value Converts a date/time in ISO-8601 standard format date string to an Excel
160	* serialized timestamp.
161	* See https://en.wikipedia.org/wiki/ISO_8601 for details of the ISO-8601 standard format.
162	* @return float\|int
163	*/
164	public static function convertIsoDate($value, ?int $calendar = null)
165	{
166	if (!is_string($value)) {
167	throw new Exception('Non-string value supplied for Iso Date conversion');
168	}
169
170	$date = new DateTime($value);
171	$dateErrors = DateTime::getLastErrors();
172
173	if (is_array($dateErrors) && ($dateErrors['warning_count'] > 0 \|\| $dateErrors['error_count'] > 0)) {
174	throw new Exception("Invalid string $value supplied for datatype Date");
175	}
176
177	$newValue = self::dateTimeToExcel($date, $calendar);
178
179	if (preg_match('/^\s\d?\d:\d\d(:\d\d([.]\d+)?)?\s(am\|pm)?\s*$/i', $value) == 1) {
180	$newValue = fmod($newValue, 1.0);
181	}
182
183	return $newValue;
184	}
185
186	/**
187	* Convert a MS serialized datetime value from Excel to a PHP Date/Time object.
188	*
189	* @param float\|int $excelTimestamp MS Excel serialized date/time value
190	* @param null\|DateTimeZone\|string $timeZone The timezone to assume for the Excel timestamp,
191	* if you don't want to treat it as a UTC value
192	* Use the default (UTC) unless you absolutely need a conversion
193	*
194	* @throws \Exception
195	*
196	* @return DateTime PHP date/time object
197	*/
198	public static function excelToDateTimeObject($excelTimestamp, $timeZone = null, ?int $calendar = null): DateTime
199	{
200	$calendar ??= self::$excelCalendar;
201	$timeZone = ($timeZone === null) ? self::getDefaultTimezone() : self::validateTimeZone($timeZone);
202	if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) {
203	if ($excelTimestamp < 1 && $calendar === self::CALENDAR_WINDOWS_1900) {
204	// Unix timestamp base date
205	$baseDate = new DateTime('1970-01-01', $timeZone);
206	} else {
207	// MS Excel calendar base dates
208	if ($calendar == self::CALENDAR_WINDOWS_1900) {
209	// Allow adjustment for 1900 Leap Year in MS Excel
210	$baseDate = ($excelTimestamp < 60) ? new DateTime('1899-12-31', $timeZone) : new DateTime('1899-12-30', $timeZone);
211	} else {
212	$baseDate = new DateTime('1904-01-01', $timeZone);
213	}
214	}
215	} else {
216	$baseDate = new DateTime('1899-12-30', $timeZone);
217	}
218
219	if (is_int($excelTimestamp)) {
220	if ($excelTimestamp >= 0) {
221	return self::safeModify($baseDate, "+ $excelTimestamp days");
222	}
223
224	return self::safeModify($baseDate, "$excelTimestamp days");
225	}
226	$days = floor($excelTimestamp);
227	$partDay = $excelTimestamp - $days;
228	$hms = 86400 * $partDay;
229	$microseconds = (int) round(fmod($hms, 1) * 1000000);
230	$hms = (int) floor($hms);
231	$hours = intdiv($hms, 3600);
232	$hms -= $hours * 3600;
233	$minutes = intdiv($hms, 60);
234	$seconds = $hms % 60;
235
236	if ($days >= 0) {
237	$days = '+' . $days;
238	}
239	$interval = $days . ' days';
240
241	return self::safeModify($baseDate, $interval)
242	->setTime($hours, $minutes, $seconds, $microseconds);
243	}
244
245	/**
246	* Convert a MS serialized datetime value from Excel to a unix timestamp.
247	* The use of Unix timestamps, and therefore this function, is discouraged.
248	* They are not Y2038-safe on a 32-bit system, and have no timezone info.
249	*
250	* @param float\|int $excelTimestamp MS Excel serialized date/time value
251	* @param null\|DateTimeZone\|string $timeZone The timezone to assume for the Excel timestamp,
252	* if you don't want to treat it as a UTC value
253	* Use the default (UTC) unless you absolutely need a conversion
254	*
255	* @return int Unix timetamp for this date/time
256	*/
257	public static function excelToTimestamp($excelTimestamp, $timeZone = null, ?int $calendar = null): int
258	{
259	$dto = self::excelToDateTimeObject($excelTimestamp, $timeZone, $calendar);
260	self::roundMicroseconds($dto);
261
262	return (int) $dto->format('U');
263	}
264
265	/**
266	* Convert a date from PHP to an MS Excel serialized date/time value.
267	*
268	* @param mixed $dateValue PHP DateTime object or a string - Unix timestamp is also permitted, but discouraged;
269	* not Y2038-safe on a 32-bit system, and no timezone info
270	*
271	* @return false\|float Excel date/time value
272	* or boolean FALSE on failure
273	*/
274	public static function PHPToExcel($dateValue, ?int $calendar = null)
275	{
276	if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) {
277	return self::dateTimeToExcel($dateValue, $calendar);
278	}
279	if (is_numeric($dateValue)) {
280	return self::timestampToExcel($dateValue, $calendar);
281	}
282	if (is_string($dateValue)) {
283	return self::stringToExcel($dateValue, $calendar);
284	}
285
286	return false;
287	}
288
289	/**
290	* Convert a PHP DateTime object to an MS Excel serialized date/time value.
291	*
292	* @param DateTimeInterface $dateValue PHP DateTime object
293	*
294	* @return float MS Excel serialized date/time value
295	*/
296	public static function dateTimeToExcel(DateTimeInterface $dateValue, ?int $calendar = null): float
297	{
298	$seconds = (float) sprintf('%d.%06d', $dateValue->format('s'), $dateValue->format('u'));
299
300	return self::formattedPHPToExcel(
301	(int) $dateValue->format('Y'),
302	(int) $dateValue->format('m'),
303	(int) $dateValue->format('d'),
304	(int) $dateValue->format('H'),
305	(int) $dateValue->format('i'),
306	$seconds,
307	$calendar
308	);
309	}
310
311	/**
312	* Convert a Unix timestamp to an MS Excel serialized date/time value.
313	* The use of Unix timestamps, and therefore this function, is discouraged.
314	* They are not Y2038-safe on a 32-bit system, and have no timezone info.
315	*
316	* @param float\|int\|string $unixTimestamp Unix Timestamp
317	*
318	* @return false\|float MS Excel serialized date/time value
319	*/
320	public static function timestampToExcel($unixTimestamp, ?int $calendar = null)
321	{
322	if (!is_numeric($unixTimestamp)) {
323	return false;
324	}
325
326	return self::dateTimeToExcel(new DateTime('@' . $unixTimestamp), $calendar);
327	}
328
329	/**
330	* formattedPHPToExcel.
331	*
332	* @return float Excel date/time value
333	* @param float\|int $seconds
334	*/
335	public static function formattedPHPToExcel(int $year, int $month, int $day, int $hours = 0, int $minutes = 0, $seconds = 0, ?int $calendar = null): float
336	{
337	$calendar ??= self::$excelCalendar;
338	if ($calendar === self::CALENDAR_WINDOWS_1900) {
339	//
340	// Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
341	// This affects every date following 28th February 1900
342	//
343	$excel1900isLeapYear = true;
344	if (($year == 1900) && ($month <= 2)) {
345	$excel1900isLeapYear = false;
346	}
347	$myexcelBaseDate = 2415020;
348	} else {
349	$myexcelBaseDate = 2416481;
350	$excel1900isLeapYear = false;
351	}
352
353	// Julian base date Adjustment
354	if ($month > 2) {
355	$month -= 3;
356	} else {
357	$month += 9;
358	--$year;
359	}
360
361	// Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
362	$century = (int) substr((string) $year, 0, 2);
363	$decade = (int) substr((string) $year, 2, 2);
364	$excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myexcelBaseDate + $excel1900isLeapYear;
365
366	$excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
367
368	return (float) $excelDate + $excelTime;
369	}
370
371	/**
372	* Is a given cell a date/time?
373	* @param mixed $value
374	*/
375	public static function isDateTime(Cell $cell, $value = null, bool $dateWithoutTimeOkay = true): bool
376	{
377	$result = false;
378	$worksheet = $cell->getWorksheetOrNull();
379	$spreadsheet = ($worksheet === null) ? null : $worksheet->getParent();
380	if ($worksheet !== null && $spreadsheet !== null) {
381	$index = $spreadsheet->getActiveSheetIndex();
382	$selected = $worksheet->getSelectedCells();
383
384	try {
385	if ($value === null) {
386	$value = Functions::flattenSingleValue(
387	$cell->getCalculatedValue()
388	);
389	}
390	if (is_numeric($value)) {
391	$result = self::isDateTimeFormat(
392	$worksheet->getStyle(
393	$cell->getCoordinate()
394	)->getNumberFormat(),
395	$dateWithoutTimeOkay
396	);
397	/** @var float\|int $value */
398	self::excelToDateTimeObject($value);
399	}
400	} catch (Throwable $exception) {
401	$result = false;
402	}
403	$worksheet->setSelectedCells($selected);
404	$spreadsheet->setActiveSheetIndex($index);
405	}
406
407	return $result;
408	}
409
410	/**
411	* Is a given NumberFormat code a date/time format code?
412	*/
413	public static function isDateTimeFormat(NumberFormat $excelFormatCode, bool $dateWithoutTimeOkay = true): bool
414	{
415	return self::isDateTimeFormatCode((string) $excelFormatCode->getFormatCode(), $dateWithoutTimeOkay);
416	}
417
418	private const POSSIBLE_DATETIME_FORMAT_CHARACTERS = 'eymdHs';
419	private const POSSIBLE_TIME_FORMAT_CHARACTERS = 'Hs'; // note - no 'm' due to ambiguity
420
421	/**
422	* Is a given number format code a date/time?
423	*/
424	public static function isDateTimeFormatCode(string $excelFormatCode, bool $dateWithoutTimeOkay = true): bool
425	{
426	if (strtolower($excelFormatCode) === strtolower(NumberFormat::FORMAT_GENERAL)) {
427	// "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
428	return false;
429	}
430	if (preg_match('/[0#]E[+-]0/i', $excelFormatCode)) {
431	// Scientific format
432	return false;
433	}
434
435	// Switch on formatcode
436	$excelFormatCode = (string) NumberFormat::convertSystemFormats($excelFormatCode);
437	if (in_array($excelFormatCode, NumberFormat::DATE_TIME_OR_DATETIME_ARRAY, true)) {
438	return $dateWithoutTimeOkay \|\| in_array($excelFormatCode, NumberFormat::TIME_OR_DATETIME_ARRAY);
439	}
440
441	// Typically number, currency or accounting (or occasionally fraction) formats
442	if ((str_starts_with($excelFormatCode, '_')) \|\| (str_starts_with($excelFormatCode, '0 '))) {
443	return false;
444	}
445	// Some "special formats" provided in German Excel versions were detected as date time value,
446	// so filter them out here - "\C\H\-00000" (Switzerland) and "\D-00000" (Germany).
447	if (str_contains($excelFormatCode, '-00000')) {
448	return false;
449	}
450	$possibleFormatCharacters = $dateWithoutTimeOkay ? self::POSSIBLE_DATETIME_FORMAT_CHARACTERS : self::POSSIBLE_TIME_FORMAT_CHARACTERS;
451	// Try checking for any of the date formatting characters that don't appear within square braces
452	if (preg_match('/(^\|])[^\[]*[' . $possibleFormatCharacters . ']/i', $excelFormatCode)) {
453	// We might also have a format mask containing quoted strings...
454	// we don't want to test for any of our characters within the quoted blocks
455	if (str_contains($excelFormatCode, '"')) {
456	$segMatcher = false;
457	foreach (explode('"', $excelFormatCode) as $subVal) {
458	// Only test in alternate array entries (the non-quoted blocks)
459	$segMatcher = $segMatcher === false;
460	if (
461	$segMatcher
462	&& (preg_match('/(^\|])[^\[]*[' . $possibleFormatCharacters . ']/i', $subVal))
463	) {
464	return true;
465	}
466	}
467
468	return false;
469	}
470
471	return true;
472	}
473
474	// No date...
475	return false;
476	}
477
478	/**
479	* Convert a date/time string to Excel time.
480	*
481	* @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
482	*
483	* @return false\|float Excel date/time serial value
484	*/
485	public static function stringToExcel(string $dateValue, ?int $calendar = null)
486	{
487	if (strlen($dateValue) < 2) {
488	return false;
489	}
490	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}([.]\d+)?)?)?$/iu', $dateValue)) {
491	return false;
492	}
493
494	$dateValueNew = DateTimeExcel\DateValue::fromString2($dateValue, $calendar);
495
496	if (!is_float($dateValueNew)) {
497	return false;
498	}
499
500	if (str_contains($dateValue, ':')) {
501	$timeValue = DateTimeExcel\TimeValue::fromString($dateValue);
502	if (!is_float($timeValue)) {
503	return false;
504	}
505	$dateValueNew += $timeValue;
506	}
507
508	return $dateValueNew;
509	}
510
511	/**
512	* Converts a month name (either a long or a short name) to a month number.
513	*
514	* @param string $monthName Month name or abbreviation
515	*
516	* @return int\|string Month number (1 - 12), or the original string argument if it isn't a valid month name
517	*/
518	public static function monthStringToNumber(string $monthName)
519	{
520	$monthIndex = 1;
521	foreach (self::$monthNames as $shortMonthName => $longMonthName) {
522	if (($monthName === $longMonthName) \|\| ($monthName === $shortMonthName)) {
523	return $monthIndex;
524	}
525	++$monthIndex;
526	}
527
528	return $monthName;
529	}
530
531	/**
532	* Strips an ordinal from a numeric value.
533	*
534	* @param string $day Day number with an ordinal
535	*
536	* @return int\|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
537	*/
538	public static function dayStringToNumber(string $day)
539	{
540	$strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
541	if (is_numeric($strippedDayValue)) {
542	return (int) $strippedDayValue;
543	}
544
545	return $day;
546	}
547
548	public static function dateTimeFromTimestamp(string $date, ?DateTimeZone $timeZone = null): DateTime
549	{
550	$dtobj = DateTime::createFromFormat('U', $date) ?: new DateTime();
551	$dtobj->setTimeZone($timeZone ?? self::getDefaultOrLocalTimezone());
552
553	return $dtobj;
554	}
555
556	public static function formattedDateTimeFromTimestamp(string $date, string $format, ?DateTimeZone $timeZone = null): string
557	{
558	$dtobj = self::dateTimeFromTimestamp($date, $timeZone);
559
560	return $dtobj->format($format);
561	}
562
563	/**
564	* Round the given DateTime object to seconds.
565	*/
566	public static function roundMicroseconds(DateTime $dti): void
567	{
568	$microseconds = (int) $dti->format('u');
569	$rounded = (int) round($microseconds, -6);
570	$modify = $rounded - $microseconds;
571	if ($modify !== 0) {
572	self::safeModify($dti, ($modify > 0 ? '+' : '') . $modify . ' microseconds');
573	}
574	}
575
576	/**
577	* Safely modifies a DateTime object using a specified modification string.
578	*
579	* Prior to PHP 8.3, DateTime::modify() would return false on failure but would also
580	* emit an E_WARNING. Starting with PHP 8.3, DateTime::modify() throws a DateMalformedStringException
581	* on failure instead of returning false or emitting warnings.
582	*
583	* This method provides consistent exception-based error handling across PHP versions:
584	* - For PHP 8.3+: Uses the native exception-throwing behavior of modify()
585	* - For PHP < 8.3: Converts warnings to exceptions using a custom error handler
586	*
587	* This ensures that calling code can rely on exception handling for all date modification
588	* failures regardless of the PHP version in use.
589	*
590	* @param DateTime $dateTime The DateTime object to be modified.
591	* @param string $modifier A modification string, such as '+1 day', '-2 hours', 'last Monday', etc.
592	*
593	* @throws PhpSpreadsheetException If an error occurs during the date modification process.
594	*
595	* @return DateTime The modified DateTime object.
596	*
597	* @codeCoverageIgnore
598	*/
599	protected static function safeModify(DateTime $dateTime, string $modifier): DateTime
600	{
601	/**
602	* Starting with PHP 8.3, DateTime::modify() throws a DateMalformedStringException on failure instead of
603	* returning false or emitting warnings, so we don't need the error handler overhead and can just consume the
604	* DateTime.modify() method.
605	*/
606	if (PHP_VERSION_ID >= 80300) {
607	return $dateTime->modify($modifier);
608	}
609
610	set_error_handler(
611	/**
612	* A selective error handler meant to capture E_WARNING such the following:
613	*
614	* > Warning: DateTime::modify(): Failed to parse time string (+ 3172011706730017 days)
615	* > at position 0 (+): Unexpected character in
616	* > […]/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php on line 220
617	*
618	* @param int $severity The severity level of the error.
619	* @param string $message The error message to process.
620	*
621	* @throws PhpSpreadsheetException If the severity is E_WARNING and the message matches the specified
622	* condition.
623	*
624	* @return bool Returns false if the error is not of type E_WARNING or if the message does not match
625	* the specified condition. Throws PhpSpreadsheetException when conditions are met.
626	*/
627	static function (int $severity, string $message): bool {
628	if ($severity !== E_WARNING) {
629	return false;
630	}
631	if (!str_starts_with($message, 'DateTime::modify()')) {
632	return false;
633	}
634
635	throw new PhpSpreadsheetException($message);
636	}
637	);
638
639	try {
640	$result = $dateTime->modify($modifier);
641	if ($result === false) {
642	throw new PhpSpreadsheetException('Failed to modify date with interval: ' . $modifier);
643	}
644
645	return $result;
646	} finally {
647	restore_error_handler();
648	}
649	}
650	}
651