tablepress / libraries / vendor / PhpSpreadsheet / Calculation / DateTimeExcel / DateValue.php

DateValue.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/DateTimeExcel/DateValue.php

187 lines 6.9 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
4
5	use DateTime;
6	use DateTimeImmutable;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
9	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper;
10
11	class DateValue
12	{
13	use ArrayEnabled;
14
15	/**
16	* DATEVALUE.
17	*
18	* Returns a value that represents a particular date.
19	* Use DATEVALUE to convert a date represented by a text string to an Excel or PHP date/time stamp
20	* value.
21	*
22	* NOTE: When used in a Cell Formula, MS Excel changes the cell format so that it matches the date
23	* format of your regional settings. PhpSpreadsheet does not change cell formatting in this way.
24	*
25	* Excel Function:
26	* DATEVALUE(dateValue)
27	*
28	* @param null\|array<mixed>\|bool\|float\|int\|string $dateValue Text that represents a date in a Microsoft Excel date format.
29	* For example, "1/30/2008" or "30-Jan-2008" are text strings within
30	* quotation marks that represent dates. Using the default date
31	* system in Excel for Windows, date_text must represent a date from
32	* January 1, 1900, to December 31, 9999. Using the default date
33	* system in Excel for the Macintosh, date_text must represent a date
34	* from January 1, 1904, to December 31, 9999. DATEVALUE returns the
35	* #VALUE! error value if date_text is out of this range.
36	* Or can be an array of date values
37	*
38	* @return array<mixed>\|DateTime\|float\|int\|string Excel date/time serial value, PHP date/time serial value or PHP date/time object,
39	* depending on the value of the ReturnDateType flag
40	* If an array of numbers is passed as the argument, then the returned result will also be an array
41	* with the same dimensions
42	*/
43	public static function fromString($dateValue)
44	{
45	if (is_array($dateValue)) {
46	return self::evaluateSingleArgumentArray([self::class, __FUNCTION__], $dateValue);
47	}
48
49	return self::fromString2($dateValue, null);
50	}
51
52	/**
53	* @return array<mixed>\|DateTime\|float\|int\|string Excel date/time serial value, PHP date/time serial value or PHP date/time object,
54	* depending on the value of the ReturnDateType flag
55	* @param null\|string\|int\|bool\|float $dateValue
56	*/
57	public static function fromString2($dateValue, ?int $calendar = null)
58	{
59	// try to parse as date iff there is at least one digit
60	if (is_string($dateValue) && preg_match('/\d/', $dateValue) !== 1) {
61	return ExcelError::VALUE();
62	}
63
64	$dti = new DateTimeImmutable();
65	$baseYear = $calendar ?? SharedDateHelper::getExcelCalendar();
66	$dateValue = trim((string) $dateValue, '"');
67	// Strip any ordinals because they're allowed in Excel (English only)
68	$dateValue = (string) preg_replace('/(\d)(st\|nd\|rd\|th)([ -\/])/Ui', '$1$3', $dateValue);
69	// Convert separators (/ . or space) to hyphens (should also handle dot used for ordinals in some countries, e.g. Denmark, Germany)
70	$dateValue = str_replace(['/', '.', '-', ' '], ' ', $dateValue);
71
72	$yearFound = false;
73	$t1 = explode(' ', $dateValue);
74	$t = '';
75	foreach ($t1 as &$t) {
76	if ((is_numeric($t)) && ($t > 31)) {
77	if ($yearFound) {
78	return ExcelError::VALUE();
79	}
80	if ($t < 100) {
81	$t += 1900;
82	}
83	$yearFound = true;
84	}
85	}
86	if (count($t1) === 1) {
87	// We've been fed a time value without any date
88	return ((!str_contains((string) $t, ':'))) ? ExcelError::Value() : 0.0;
89	}
90	unset($t);
91
92	$dateValue = self::t1ToString($t1, $dti, $yearFound);
93
94	$PHPDateArray = self::setUpArray($dateValue, $dti);
95
96	return self::finalResults($PHPDateArray, $dti, $baseYear);
97	}
98
99	/** @param list<float\|int\|string> $t1 */
100	private static function t1ToString(array $t1, DateTimeImmutable $dti, bool $yearFound): string
101	{
102	if (count($t1) == 2) {
103	// We only have two parts of the date: either day/month or month/year
104	if ($yearFound) {
105	array_unshift($t1, 1);
106	} else {
107	if (is_numeric($t1[1]) && $t1[1] > 29) {
108	$t1[1] += 1900;
109	array_unshift($t1, 1);
110	} else {
111	$t1[] = $dti->format('Y');
112	}
113	}
114	}
115	$dateValue = implode(' ', $t1);
116
117	return $dateValue;
118	}
119
120	/**
121	* Parse date.
122	*
123	* @return mixed[]
124	*/
125	private static function setUpArray(string $dateValue, DateTimeImmutable $dti): array
126	{
127	$PHPDateArray = Helpers::dateParse($dateValue);
128	if (!Helpers::dateParseSucceeded($PHPDateArray)) {
129	// If original count was 1, we've already returned.
130	// If it was 2, we added another.
131	// Therefore, neither of the first 2 strtoks below can fail.
132	$testVal1 = strtok($dateValue, '- ');
133	$testVal2 = strtok('- ');
134	$testVal3 = strtok('- ') ?: $dti->format('Y');
135	Helpers::adjustYear((string) $testVal1, (string) $testVal2, $testVal3);
136	$PHPDateArray = Helpers::dateParse($testVal1 . '-' . $testVal2 . '-' . $testVal3);
137	if (!Helpers::dateParseSucceeded($PHPDateArray)) {
138	$PHPDateArray = Helpers::dateParse($testVal2 . '-' . $testVal1 . '-' . $testVal3);
139	}
140	}
141
142	return $PHPDateArray;
143	}
144
145	/**
146	* Final results.
147	*
148	* @param mixed[] $PHPDateArray
149	*
150	* @return DateTime\|float\|int\|string Excel date/time serial value, PHP date/time serial value or PHP date/time object,
151	* depending on the value of the ReturnDateType flag
152	*/
153	private static function finalResults(array $PHPDateArray, DateTimeImmutable $dti, int $baseYear)
154	{
155	$retValue = ExcelError::Value();
156	if (Helpers::dateParseSucceeded($PHPDateArray)) {
157	/** @var array{year: int, month: int, day: int, hour: int, minute: int, second: int} $PHPDateArray */
158	// Execute function
159	Helpers::replaceIfEmpty($PHPDateArray['year'], $dti->format('Y'));
160	if ($PHPDateArray['year'] < $baseYear) {
161	return ExcelError::VALUE();
162	}
163	Helpers::replaceIfEmpty($PHPDateArray['month'], $dti->format('m'));
164	Helpers::replaceIfEmpty($PHPDateArray['day'], $dti->format('d'));
165	/** @var array{year: int, month: int, day: int, hour: int, minute: int, second: int} $PHPDateArray */
166	$PHPDateArray['hour'] = 0;
167	$PHPDateArray['minute'] = 0;
168	$PHPDateArray['second'] = 0;
169	$month = self::getInt($PHPDateArray, 'month');
170	$day = self::getInt($PHPDateArray, 'day');
171	$year = self::getInt($PHPDateArray, 'year');
172	if (!checkdate($month, $day, $year)) {
173	return ($year === 1900 && $month === 2 && $day === 29) ? Helpers::returnIn3FormatsFloat(60.0, $baseYear) : ExcelError::VALUE();
174	}
175	$retValue = Helpers::returnIn3FormatsArray($PHPDateArray, true, $baseYear);
176	}
177
178	return $retValue;
179	}
180
181	/** @param mixed[] $array */
182	private static function getInt(array $array, string $index): int
183	{
184	return (array_key_exists($index, $array) && is_numeric($array[$index])) ? (int) $array[$index] : 0;
185	}
186	}
187