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 | 2362 | public static function fromString(null|array|string|int|bool|float $dateValue): array|string|float|int|DateTime |
|
44 | { |
||
45 | 2362 | if (is_array($dateValue)) { |
|
0 ignored issues
–
show
introduced
by
Loading history...
|
|||
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 | 2362 | if (is_string($dateValue) && preg_match('/\\d/', $dateValue) !== 1) { |
|
51 | 107 | return ExcelError::VALUE(); |
|
52 | } |
||
53 | |||
54 | 2295 | $dti = new DateTimeImmutable(); |
|
55 | 2295 | $baseYear = SharedDateHelper::getExcelCalendar(); |
|
56 | 2295 | $dateValue = trim((string) $dateValue, '"'); |
|
57 | // Strip any ordinals because they're allowed in Excel (English only) |
||
58 | 2295 | $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 | 2295 | $dateValue = str_replace(['/', '.', '-', ' '], ' ', $dateValue); |
|
61 | |||
62 | 2295 | $yearFound = false; |
|
63 | 2295 | $t1 = explode(' ', $dateValue); |
|
64 | 2295 | $t = ''; |
|
65 | 2295 | foreach ($t1 as &$t) { |
|
66 | 2295 | if ((is_numeric($t)) && ($t > 31)) { |
|
67 | 2251 | if ($yearFound) { |
|
68 | 3 | return ExcelError::VALUE(); |
|
69 | } |
||
70 | 2251 | if ($t < 100) { |
|
71 | 9 | $t += 1900; |
|
72 | } |
||
73 | 2251 | $yearFound = true; |
|
74 | } |
||
75 | } |
||
76 | 2292 | 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 | 2260 | unset($t); |
|
81 | |||
82 | 2260 | $dateValue = self::t1ToString($t1, $dti, $yearFound); |
|
83 | |||
84 | 2260 | $PHPDateArray = self::setUpArray($dateValue, $dti); |
|
85 | |||
86 | 2260 | return self::finalResults($PHPDateArray, $dti, $baseYear); |
|
87 | } |
||
88 | |||
89 | 2260 | private static function t1ToString(array $t1, DateTimeImmutable $dti, bool $yearFound): string |
|
90 | { |
||
91 | 2260 | if (count($t1) == 2) { |
|
92 | // We only have two parts of the date: either day/month or month/year |
||
93 | 47 | if ($yearFound) { |
|
94 | 21 | array_unshift($t1, 1); |
|
95 | } else { |
||
96 | 27 | if (is_numeric($t1[1]) && $t1[1] > 29) { |
|
97 | 3 | $t1[1] += 1900; |
|
98 | 3 | array_unshift($t1, 1); |
|
99 | } else { |
||
100 | 24 | $t1[] = $dti->format('Y'); |
|
101 | } |
||
102 | } |
||
103 | } |
||
104 | 2260 | $dateValue = implode(' ', $t1); |
|
105 | |||
106 | 2260 | return $dateValue; |
|
107 | } |
||
108 | |||
109 | /** |
||
110 | * Parse date. |
||
111 | */ |
||
112 | 2260 | private static function setUpArray(string $dateValue, DateTimeImmutable $dti): array |
|
113 | { |
||
114 | 2260 | $PHPDateArray = Helpers::dateParse($dateValue); |
|
115 | 2260 | 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 | 1638 | $testVal1 = strtok($dateValue, '- '); |
|
120 | 1638 | $testVal2 = strtok('- '); |
|
121 | 1638 | $testVal3 = strtok('- ') ?: $dti->format('Y'); |
|
122 | 1638 | Helpers::adjustYear((string) $testVal1, (string) $testVal2, $testVal3); |
|
123 | 1638 | $PHPDateArray = Helpers::dateParse($testVal1 . '-' . $testVal2 . '-' . $testVal3); |
|
124 | 1638 | if (!Helpers::dateParseSucceeded($PHPDateArray)) { |
|
125 | 46 | $PHPDateArray = Helpers::dateParse($testVal2 . '-' . $testVal1 . '-' . $testVal3); |
|
126 | } |
||
127 | } |
||
128 | |||
129 | 2260 | 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 | 2260 | private static function finalResults(array $PHPDateArray, DateTimeImmutable $dti, int $baseYear): string|float|int|DateTime |
|
139 | { |
||
140 | 2260 | $retValue = ExcelError::Value(); |
|
141 | 2260 | if (Helpers::dateParseSucceeded($PHPDateArray)) { |
|
142 | // Execute function |
||
143 | 2226 | Helpers::replaceIfEmpty($PHPDateArray['year'], $dti->format('Y')); |
|
144 | 2226 | if ($PHPDateArray['year'] < $baseYear) { |
|
145 | 28 | return ExcelError::VALUE(); |
|
146 | } |
||
147 | 2199 | Helpers::replaceIfEmpty($PHPDateArray['month'], $dti->format('m')); |
|
148 | 2199 | Helpers::replaceIfEmpty($PHPDateArray['day'], $dti->format('d')); |
|
149 | 2199 | $PHPDateArray['hour'] = 0; |
|
150 | 2199 | $PHPDateArray['minute'] = 0; |
|
151 | 2199 | $PHPDateArray['second'] = 0; |
|
152 | 2199 | $month = (int) $PHPDateArray['month']; |
|
153 | 2199 | $day = (int) $PHPDateArray['day']; |
|
154 | 2199 | $year = (int) $PHPDateArray['year']; |
|
155 | 2199 | if (!checkdate($month, $day, $year)) { |
|
156 | 16 | return ($year === 1900 && $month === 2 && $day === 29) ? Helpers::returnIn3FormatsFloat(60.0) : ExcelError::VALUE(); |
|
157 | } |
||
158 | 2185 | $retValue = Helpers::returnIn3FormatsArray($PHPDateArray, true); |
|
159 | } |
||
160 | |||
161 | 2219 | return $retValue; |
|
162 | } |
||
163 | } |
||
164 |