| 1441 |
ariadna |
1 |
<?php
|
|
|
2 |
|
|
|
3 |
namespace PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
|
|
|
4 |
|
|
|
5 |
use DateTime;
|
|
|
6 |
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
|
|
|
7 |
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
|
|
|
8 |
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
|
|
|
9 |
use PhpOffice\PhpSpreadsheet\Shared\Date as SharedDateHelper;
|
|
|
10 |
|
|
|
11 |
class Helpers
|
|
|
12 |
{
|
|
|
13 |
/**
|
|
|
14 |
* Identify if a year is a leap year or not.
|
|
|
15 |
*
|
|
|
16 |
* @param int|string $year The year to test
|
|
|
17 |
*
|
|
|
18 |
* @return bool TRUE if the year is a leap year, otherwise FALSE
|
|
|
19 |
*/
|
|
|
20 |
public static function isLeapYear(int|string $year): bool
|
|
|
21 |
{
|
|
|
22 |
$year = (int) $year;
|
|
|
23 |
|
|
|
24 |
return (($year % 4) === 0) && (($year % 100) !== 0) || (($year % 400) === 0);
|
|
|
25 |
}
|
|
|
26 |
|
|
|
27 |
/**
|
|
|
28 |
* getDateValue.
|
|
|
29 |
*
|
|
|
30 |
* @return float Excel date/time serial value
|
|
|
31 |
*/
|
|
|
32 |
public static function getDateValue(mixed $dateValue, bool $allowBool = true): float
|
|
|
33 |
{
|
|
|
34 |
if (is_object($dateValue)) {
|
|
|
35 |
$retval = SharedDateHelper::PHPToExcel($dateValue);
|
|
|
36 |
if (is_bool($retval)) {
|
|
|
37 |
throw new Exception(ExcelError::VALUE());
|
|
|
38 |
}
|
|
|
39 |
|
|
|
40 |
return $retval;
|
|
|
41 |
}
|
|
|
42 |
|
|
|
43 |
self::nullFalseTrueToNumber($dateValue, $allowBool);
|
|
|
44 |
if (!is_numeric($dateValue)) {
|
|
|
45 |
$saveReturnDateType = Functions::getReturnDateType();
|
|
|
46 |
Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
|
|
|
47 |
$dateValue = DateValue::fromString($dateValue);
|
|
|
48 |
Functions::setReturnDateType($saveReturnDateType);
|
|
|
49 |
if (!is_numeric($dateValue)) {
|
|
|
50 |
throw new Exception(ExcelError::VALUE());
|
|
|
51 |
}
|
|
|
52 |
}
|
|
|
53 |
if ($dateValue < 0 && Functions::getCompatibilityMode() !== Functions::COMPATIBILITY_OPENOFFICE) {
|
|
|
54 |
throw new Exception(ExcelError::NAN());
|
|
|
55 |
}
|
|
|
56 |
|
|
|
57 |
return (float) $dateValue;
|
|
|
58 |
}
|
|
|
59 |
|
|
|
60 |
/**
|
|
|
61 |
* getTimeValue.
|
|
|
62 |
*
|
|
|
63 |
* @return float|string Excel date/time serial value, or string if error
|
|
|
64 |
*/
|
|
|
65 |
public static function getTimeValue(string $timeValue): string|float
|
|
|
66 |
{
|
|
|
67 |
$saveReturnDateType = Functions::getReturnDateType();
|
|
|
68 |
Functions::setReturnDateType(Functions::RETURNDATE_EXCEL);
|
|
|
69 |
/** @var float|string $timeValue */
|
|
|
70 |
$timeValue = TimeValue::fromString($timeValue);
|
|
|
71 |
Functions::setReturnDateType($saveReturnDateType);
|
|
|
72 |
|
|
|
73 |
return $timeValue;
|
|
|
74 |
}
|
|
|
75 |
|
|
|
76 |
/**
|
|
|
77 |
* Adjust date by given months.
|
|
|
78 |
*/
|
|
|
79 |
public static function adjustDateByMonths(mixed $dateValue = 0, float $adjustmentMonths = 0): DateTime
|
|
|
80 |
{
|
|
|
81 |
// Execute function
|
|
|
82 |
$PHPDateObject = SharedDateHelper::excelToDateTimeObject($dateValue);
|
|
|
83 |
$oMonth = (int) $PHPDateObject->format('m');
|
|
|
84 |
$oYear = (int) $PHPDateObject->format('Y');
|
|
|
85 |
|
|
|
86 |
$adjustmentMonthsString = (string) $adjustmentMonths;
|
|
|
87 |
if ($adjustmentMonths > 0) {
|
|
|
88 |
$adjustmentMonthsString = '+' . $adjustmentMonths;
|
|
|
89 |
}
|
|
|
90 |
if ($adjustmentMonths != 0) {
|
|
|
91 |
$PHPDateObject->modify($adjustmentMonthsString . ' months');
|
|
|
92 |
}
|
|
|
93 |
$nMonth = (int) $PHPDateObject->format('m');
|
|
|
94 |
$nYear = (int) $PHPDateObject->format('Y');
|
|
|
95 |
|
|
|
96 |
$monthDiff = ($nMonth - $oMonth) + (($nYear - $oYear) * 12);
|
|
|
97 |
if ($monthDiff != $adjustmentMonths) {
|
|
|
98 |
$adjustDays = (int) $PHPDateObject->format('d');
|
|
|
99 |
$adjustDaysString = '-' . $adjustDays . ' days';
|
|
|
100 |
$PHPDateObject->modify($adjustDaysString);
|
|
|
101 |
}
|
|
|
102 |
|
|
|
103 |
return $PHPDateObject;
|
|
|
104 |
}
|
|
|
105 |
|
|
|
106 |
/**
|
|
|
107 |
* Help reduce perceived complexity of some tests.
|
|
|
108 |
*/
|
|
|
109 |
public static function replaceIfEmpty(mixed &$value, mixed $altValue): void
|
|
|
110 |
{
|
|
|
111 |
$value = $value ?: $altValue;
|
|
|
112 |
}
|
|
|
113 |
|
|
|
114 |
/**
|
|
|
115 |
* Adjust year in ambiguous situations.
|
|
|
116 |
*/
|
|
|
117 |
public static function adjustYear(string $testVal1, string $testVal2, string &$testVal3): void
|
|
|
118 |
{
|
|
|
119 |
if (!is_numeric($testVal1) || $testVal1 < 31) {
|
|
|
120 |
if (!is_numeric($testVal2) || $testVal2 < 12) {
|
|
|
121 |
if (is_numeric($testVal3) && $testVal3 < 12) {
|
|
|
122 |
$testVal3 = (string) ($testVal3 + 2000);
|
|
|
123 |
}
|
|
|
124 |
}
|
|
|
125 |
}
|
|
|
126 |
}
|
|
|
127 |
|
|
|
128 |
/**
|
|
|
129 |
* Return result in one of three formats.
|
|
|
130 |
*/
|
|
|
131 |
public static function returnIn3FormatsArray(array $dateArray, bool $noFrac = false): DateTime|float|int
|
|
|
132 |
{
|
|
|
133 |
$retType = Functions::getReturnDateType();
|
|
|
134 |
if ($retType === Functions::RETURNDATE_PHP_DATETIME_OBJECT) {
|
|
|
135 |
return new DateTime(
|
|
|
136 |
$dateArray['year']
|
|
|
137 |
. '-' . $dateArray['month']
|
|
|
138 |
. '-' . $dateArray['day']
|
|
|
139 |
. ' ' . $dateArray['hour']
|
|
|
140 |
. ':' . $dateArray['minute']
|
|
|
141 |
. ':' . $dateArray['second']
|
|
|
142 |
);
|
|
|
143 |
}
|
|
|
144 |
$excelDateValue
|
|
|
145 |
= SharedDateHelper::formattedPHPToExcel(
|
|
|
146 |
$dateArray['year'],
|
|
|
147 |
$dateArray['month'],
|
|
|
148 |
$dateArray['day'],
|
|
|
149 |
$dateArray['hour'],
|
|
|
150 |
$dateArray['minute'],
|
|
|
151 |
$dateArray['second']
|
|
|
152 |
);
|
|
|
153 |
if ($retType === Functions::RETURNDATE_EXCEL) {
|
|
|
154 |
return $noFrac ? floor($excelDateValue) : $excelDateValue;
|
|
|
155 |
}
|
|
|
156 |
// RETURNDATE_UNIX_TIMESTAMP)
|
|
|
157 |
|
|
|
158 |
return SharedDateHelper::excelToTimestamp($excelDateValue);
|
|
|
159 |
}
|
|
|
160 |
|
|
|
161 |
/**
|
|
|
162 |
* Return result in one of three formats.
|
|
|
163 |
*/
|
|
|
164 |
public static function returnIn3FormatsFloat(float $excelDateValue): float|int|DateTime
|
|
|
165 |
{
|
|
|
166 |
$retType = Functions::getReturnDateType();
|
|
|
167 |
if ($retType === Functions::RETURNDATE_EXCEL) {
|
|
|
168 |
return $excelDateValue;
|
|
|
169 |
}
|
|
|
170 |
if ($retType === Functions::RETURNDATE_UNIX_TIMESTAMP) {
|
|
|
171 |
return SharedDateHelper::excelToTimestamp($excelDateValue);
|
|
|
172 |
}
|
|
|
173 |
// RETURNDATE_PHP_DATETIME_OBJECT
|
|
|
174 |
|
|
|
175 |
return SharedDateHelper::excelToDateTimeObject($excelDateValue);
|
|
|
176 |
}
|
|
|
177 |
|
|
|
178 |
/**
|
|
|
179 |
* Return result in one of three formats.
|
|
|
180 |
*/
|
|
|
181 |
public static function returnIn3FormatsObject(DateTime $PHPDateObject): DateTime|float|int
|
|
|
182 |
{
|
|
|
183 |
$retType = Functions::getReturnDateType();
|
|
|
184 |
if ($retType === Functions::RETURNDATE_PHP_DATETIME_OBJECT) {
|
|
|
185 |
return $PHPDateObject;
|
|
|
186 |
}
|
|
|
187 |
if ($retType === Functions::RETURNDATE_EXCEL) {
|
|
|
188 |
return (float) SharedDateHelper::PHPToExcel($PHPDateObject);
|
|
|
189 |
}
|
|
|
190 |
// RETURNDATE_UNIX_TIMESTAMP
|
|
|
191 |
$stamp = SharedDateHelper::PHPToExcel($PHPDateObject);
|
|
|
192 |
$stamp = is_bool($stamp) ? ((int) $stamp) : $stamp;
|
|
|
193 |
|
|
|
194 |
return SharedDateHelper::excelToTimestamp($stamp);
|
|
|
195 |
}
|
|
|
196 |
|
|
|
197 |
private static function baseDate(): int
|
|
|
198 |
{
|
|
|
199 |
if (Functions::getCompatibilityMode() === Functions::COMPATIBILITY_OPENOFFICE) {
|
|
|
200 |
return 0;
|
|
|
201 |
}
|
|
|
202 |
if (SharedDateHelper::getExcelCalendar() === SharedDateHelper::CALENDAR_MAC_1904) {
|
|
|
203 |
return 0;
|
|
|
204 |
}
|
|
|
205 |
|
|
|
206 |
return 1;
|
|
|
207 |
}
|
|
|
208 |
|
|
|
209 |
/**
|
|
|
210 |
* Many functions accept null/false/true argument treated as 0/0/1.
|
|
|
211 |
*/
|
|
|
212 |
public static function nullFalseTrueToNumber(mixed &$number, bool $allowBool = true): void
|
|
|
213 |
{
|
|
|
214 |
$number = Functions::flattenSingleValue($number);
|
|
|
215 |
$nullVal = self::baseDate();
|
|
|
216 |
if ($number === null) {
|
|
|
217 |
$number = $nullVal;
|
|
|
218 |
} elseif ($allowBool && is_bool($number)) {
|
|
|
219 |
$number = $nullVal + (int) $number;
|
|
|
220 |
}
|
|
|
221 |
}
|
|
|
222 |
|
|
|
223 |
/**
|
|
|
224 |
* Many functions accept null argument treated as 0.
|
|
|
225 |
*/
|
|
|
226 |
public static function validateNumericNull(mixed $number): int|float
|
|
|
227 |
{
|
|
|
228 |
$number = Functions::flattenSingleValue($number);
|
|
|
229 |
if ($number === null) {
|
|
|
230 |
return 0;
|
|
|
231 |
}
|
|
|
232 |
if (is_int($number)) {
|
|
|
233 |
return $number;
|
|
|
234 |
}
|
|
|
235 |
if (is_numeric($number)) {
|
|
|
236 |
return (float) $number;
|
|
|
237 |
}
|
|
|
238 |
|
|
|
239 |
throw new Exception(ExcelError::VALUE());
|
|
|
240 |
}
|
|
|
241 |
|
|
|
242 |
/**
|
|
|
243 |
* Many functions accept null/false/true argument treated as 0/0/1.
|
|
|
244 |
*
|
|
|
245 |
* @phpstan-assert float $number
|
|
|
246 |
*/
|
|
|
247 |
public static function validateNotNegative(mixed $number): float
|
|
|
248 |
{
|
|
|
249 |
if (!is_numeric($number)) {
|
|
|
250 |
throw new Exception(ExcelError::VALUE());
|
|
|
251 |
}
|
|
|
252 |
if ($number >= 0) {
|
|
|
253 |
return (float) $number;
|
|
|
254 |
}
|
|
|
255 |
|
|
|
256 |
throw new Exception(ExcelError::NAN());
|
|
|
257 |
}
|
|
|
258 |
|
|
|
259 |
public static function silly1900(DateTime $PHPDateObject, string $mod = '-1 day'): void
|
|
|
260 |
{
|
|
|
261 |
$isoDate = $PHPDateObject->format('c');
|
|
|
262 |
if ($isoDate < '1900-03-01') {
|
|
|
263 |
$PHPDateObject->modify($mod);
|
|
|
264 |
}
|
|
|
265 |
}
|
|
|
266 |
|
|
|
267 |
public static function dateParse(string $string): array
|
|
|
268 |
{
|
|
|
269 |
return self::forceArray(date_parse($string));
|
|
|
270 |
}
|
|
|
271 |
|
|
|
272 |
public static function dateParseSucceeded(array $dateArray): bool
|
|
|
273 |
{
|
|
|
274 |
return $dateArray['error_count'] === 0;
|
|
|
275 |
}
|
|
|
276 |
|
|
|
277 |
/**
|
|
|
278 |
* Despite documentation, date_parse probably never returns false.
|
|
|
279 |
* Just in case, this routine helps guarantee it.
|
|
|
280 |
*
|
|
|
281 |
* @param array|false $dateArray
|
|
|
282 |
*/
|
|
|
283 |
private static function forceArray(array|bool $dateArray): array
|
|
|
284 |
{
|
|
|
285 |
return is_array($dateArray) ? $dateArray : ['error_count' => 1];
|
|
|
286 |
}
|
|
|
287 |
}
|