|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel; |
|
4
|
|
|
|
|
5
|
|
|
use DateTime; |
|
6
|
|
|
use DateTimeImmutable; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\ArrayEnabled; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError; |
|
9
|
|
|
use 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|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|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
|
2368 |
|
public static function fromString(null|array|string|int|bool|float $dateValue): array|string|float|int|DateTime |
|
44
|
|
|
{ |
|
45
|
2368 |
|
if (is_array($dateValue)) { |
|
46
|
81 |
|
return self::evaluateSingleArgumentArray([self::class, __FUNCTION__], $dateValue); |
|
47
|
|
|
} |
|
48
|
|
|
|
|
49
|
|
|
// try to parse as date iff there is at least one digit |
|
50
|
2368 |
|
if (is_string($dateValue) && preg_match('/\d/', $dateValue) !== 1) { |
|
51
|
108 |
|
return ExcelError::VALUE(); |
|
52
|
|
|
} |
|
53
|
|
|
|
|
54
|
2300 |
|
$dti = new DateTimeImmutable(); |
|
55
|
2300 |
|
$baseYear = SharedDateHelper::getExcelCalendar(); |
|
56
|
2300 |
|
$dateValue = trim((string) $dateValue, '"'); |
|
57
|
|
|
// Strip any ordinals because they're allowed in Excel (English only) |
|
58
|
2300 |
|
$dateValue = (string) preg_replace('/(\d)(st|nd|rd|th)([ -\/])/Ui', '$1$3', $dateValue); |
|
59
|
|
|
// Convert separators (/ . or space) to hyphens (should also handle dot used for ordinals in some countries, e.g. Denmark, Germany) |
|
60
|
2300 |
|
$dateValue = str_replace(['/', '.', '-', ' '], ' ', $dateValue); |
|
61
|
|
|
|
|
62
|
2300 |
|
$yearFound = false; |
|
63
|
2300 |
|
$t1 = explode(' ', $dateValue); |
|
64
|
2300 |
|
$t = ''; |
|
65
|
2300 |
|
foreach ($t1 as &$t) { |
|
66
|
2300 |
|
if ((is_numeric($t)) && ($t > 31)) { |
|
67
|
2255 |
|
if ($yearFound) { |
|
68
|
3 |
|
return ExcelError::VALUE(); |
|
69
|
|
|
} |
|
70
|
2255 |
|
if ($t < 100) { |
|
71
|
9 |
|
$t += 1900; |
|
72
|
|
|
} |
|
73
|
2255 |
|
$yearFound = true; |
|
74
|
|
|
} |
|
75
|
|
|
} |
|
76
|
2297 |
|
if (count($t1) === 1) { |
|
77
|
|
|
// We've been fed a time value without any date |
|
78
|
32 |
|
return ((!str_contains((string) $t, ':'))) ? ExcelError::Value() : 0.0; |
|
79
|
|
|
} |
|
80
|
2265 |
|
unset($t); |
|
81
|
|
|
|
|
82
|
2265 |
|
$dateValue = self::t1ToString($t1, $dti, $yearFound); |
|
83
|
|
|
|
|
84
|
2265 |
|
$PHPDateArray = self::setUpArray($dateValue, $dti); |
|
85
|
|
|
|
|
86
|
2265 |
|
return self::finalResults($PHPDateArray, $dti, $baseYear); |
|
87
|
|
|
} |
|
88
|
|
|
|
|
89
|
2265 |
|
private static function t1ToString(array $t1, DateTimeImmutable $dti, bool $yearFound): string |
|
90
|
|
|
{ |
|
91
|
2265 |
|
if (count($t1) == 2) { |
|
92
|
|
|
// We only have two parts of the date: either day/month or month/year |
|
93
|
48 |
|
if ($yearFound) { |
|
94
|
21 |
|
array_unshift($t1, 1); |
|
95
|
|
|
} else { |
|
96
|
28 |
|
if (is_numeric($t1[1]) && $t1[1] > 29) { |
|
97
|
3 |
|
$t1[1] += 1900; |
|
98
|
3 |
|
array_unshift($t1, 1); |
|
99
|
|
|
} else { |
|
100
|
25 |
|
$t1[] = $dti->format('Y'); |
|
101
|
|
|
} |
|
102
|
|
|
} |
|
103
|
|
|
} |
|
104
|
2265 |
|
$dateValue = implode(' ', $t1); |
|
105
|
|
|
|
|
106
|
2265 |
|
return $dateValue; |
|
107
|
|
|
} |
|
108
|
|
|
|
|
109
|
|
|
/** |
|
110
|
|
|
* Parse date. |
|
111
|
|
|
*/ |
|
112
|
2265 |
|
private static function setUpArray(string $dateValue, DateTimeImmutable $dti): array |
|
113
|
|
|
{ |
|
114
|
2265 |
|
$PHPDateArray = Helpers::dateParse($dateValue); |
|
115
|
2265 |
|
if (!Helpers::dateParseSucceeded($PHPDateArray)) { |
|
116
|
|
|
// If original count was 1, we've already returned. |
|
117
|
|
|
// If it was 2, we added another. |
|
118
|
|
|
// Therefore, neither of the first 2 stroks below can fail. |
|
119
|
1639 |
|
$testVal1 = strtok($dateValue, '- '); |
|
120
|
1639 |
|
$testVal2 = strtok('- '); |
|
121
|
1639 |
|
$testVal3 = strtok('- ') ?: $dti->format('Y'); |
|
122
|
1639 |
|
Helpers::adjustYear((string) $testVal1, (string) $testVal2, $testVal3); |
|
123
|
1639 |
|
$PHPDateArray = Helpers::dateParse($testVal1 . '-' . $testVal2 . '-' . $testVal3); |
|
124
|
1639 |
|
if (!Helpers::dateParseSucceeded($PHPDateArray)) { |
|
125
|
47 |
|
$PHPDateArray = Helpers::dateParse($testVal2 . '-' . $testVal1 . '-' . $testVal3); |
|
126
|
|
|
} |
|
127
|
|
|
} |
|
128
|
|
|
|
|
129
|
2265 |
|
return $PHPDateArray; |
|
130
|
|
|
} |
|
131
|
|
|
|
|
132
|
|
|
/** |
|
133
|
|
|
* Final results. |
|
134
|
|
|
* |
|
135
|
|
|
* @return DateTime|float|int|string Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
|
136
|
|
|
* depending on the value of the ReturnDateType flag |
|
137
|
|
|
*/ |
|
138
|
2265 |
|
private static function finalResults(array $PHPDateArray, DateTimeImmutable $dti, int $baseYear): string|float|int|DateTime |
|
139
|
|
|
{ |
|
140
|
2265 |
|
$retValue = ExcelError::Value(); |
|
141
|
2265 |
|
if (Helpers::dateParseSucceeded($PHPDateArray)) { |
|
142
|
|
|
// Execute function |
|
143
|
2231 |
|
Helpers::replaceIfEmpty($PHPDateArray['year'], $dti->format('Y')); |
|
144
|
2231 |
|
if ($PHPDateArray['year'] < $baseYear) { |
|
145
|
28 |
|
return ExcelError::VALUE(); |
|
146
|
|
|
} |
|
147
|
2204 |
|
Helpers::replaceIfEmpty($PHPDateArray['month'], $dti->format('m')); |
|
148
|
2204 |
|
Helpers::replaceIfEmpty($PHPDateArray['day'], $dti->format('d')); |
|
149
|
2204 |
|
$PHPDateArray['hour'] = 0; |
|
150
|
2204 |
|
$PHPDateArray['minute'] = 0; |
|
151
|
2204 |
|
$PHPDateArray['second'] = 0; |
|
152
|
2204 |
|
$month = self::getInt($PHPDateArray, 'month'); |
|
153
|
2204 |
|
$day = self::getInt($PHPDateArray, 'day'); |
|
154
|
2204 |
|
$year = self::getInt($PHPDateArray, 'year'); |
|
155
|
2204 |
|
if (!checkdate($month, $day, $year)) { |
|
156
|
16 |
|
return ($year === 1900 && $month === 2 && $day === 29) ? Helpers::returnIn3FormatsFloat(60.0) : ExcelError::VALUE(); |
|
157
|
|
|
} |
|
158
|
2190 |
|
$retValue = Helpers::returnIn3FormatsArray($PHPDateArray, true); |
|
159
|
|
|
} |
|
160
|
|
|
|
|
161
|
2224 |
|
return $retValue; |
|
162
|
|
|
} |
|
163
|
|
|
|
|
164
|
|
|
private static function getInt(array $array, string $index): int |
|
165
|
|
|
{ |
|
166
|
|
|
return (array_key_exists($index, $array) && is_numeric($array[$index])) ? (int) $array[$index] : 0; |
|
167
|
|
|
} |
|
168
|
|
|
} |
|
169
|
|
|
|
PHPOffice /
PhpSpreadsheet
