1<?php
2
3namespace PhpOffice\PhpSpreadsheet\Shared;
4
5use DateTimeInterface;
6use DateTimeZone;
7use PhpOffice\PhpSpreadsheet\Calculation\DateTime;
8use PhpOffice\PhpSpreadsheet\Calculation\Functions;
9use PhpOffice\PhpSpreadsheet\Cell\Cell;
10use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
11
12class Date
13{
14    /** constants */
15    const CALENDAR_WINDOWS_1900 = 1900; //    Base date of 1st Jan 1900 = 1.0
16    const CALENDAR_MAC_1904 = 1904; //    Base date of 2nd Jan 1904 = 1.0
17
18    /**
19     * Names of the months of the year, indexed by shortname
20     * Planned usage for locale settings.
21     *
22     * @var string[]
23     */
24    public static $monthNames = [
25        'Jan' => 'January',
26        'Feb' => 'February',
27        'Mar' => 'March',
28        'Apr' => 'April',
29        'May' => 'May',
30        'Jun' => 'June',
31        'Jul' => 'July',
32        'Aug' => 'August',
33        'Sep' => 'September',
34        'Oct' => 'October',
35        'Nov' => 'November',
36        'Dec' => 'December',
37    ];
38
39    /**
40     * @var string[]
41     */
42    public static $numberSuffixes = [
43        'st',
44        'nd',
45        'rd',
46        'th',
47    ];
48
49    /**
50     * Base calendar year to use for calculations
51     * Value is either CALENDAR_WINDOWS_1900 (1900) or CALENDAR_MAC_1904 (1904).
52     *
53     * @var int
54     */
55    protected static $excelCalendar = self::CALENDAR_WINDOWS_1900;
56
57    /**
58     * Default timezone to use for DateTime objects.
59     *
60     * @var null|\DateTimeZone
61     */
62    protected static $defaultTimeZone;
63
64    /**
65     * Set the Excel calendar (Windows 1900 or Mac 1904).
66     *
67     * @param int $baseDate Excel base date (1900 or 1904)
68     *
69     * @return bool Success or failure
70     */
71    public static function setExcelCalendar($baseDate)
72    {
73        if (($baseDate == self::CALENDAR_WINDOWS_1900) ||
74            ($baseDate == self::CALENDAR_MAC_1904)) {
75            self::$excelCalendar = $baseDate;
76
77            return true;
78        }
79
80        return false;
81    }
82
83    /**
84     * Return the Excel calendar (Windows 1900 or Mac 1904).
85     *
86     * @return int Excel base date (1900 or 1904)
87     */
88    public static function getExcelCalendar()
89    {
90        return self::$excelCalendar;
91    }
92
93    /**
94     * Set the Default timezone to use for dates.
95     *
96     * @param DateTimeZone|string $timeZone The timezone to set for all Excel datetimestamp to PHP DateTime Object conversions
97     *
98     * @throws \Exception
99     *
100     * @return bool Success or failure
101     * @return bool Success or failure
102     */
103    public static function setDefaultTimezone($timeZone)
104    {
105        if ($timeZone = self::validateTimeZone($timeZone)) {
106            self::$defaultTimeZone = $timeZone;
107
108            return true;
109        }
110
111        return false;
112    }
113
114    /**
115     * Return the Default timezone being used for dates.
116     *
117     * @return DateTimeZone The timezone being used as default for Excel timestamp to PHP DateTime object
118     */
119    public static function getDefaultTimezone()
120    {
121        if (self::$defaultTimeZone === null) {
122            self::$defaultTimeZone = new DateTimeZone('UTC');
123        }
124
125        return self::$defaultTimeZone;
126    }
127
128    /**
129     * Validate a timezone.
130     *
131     * @param DateTimeZone|string $timeZone The timezone to validate, either as a timezone string or object
132     *
133     * @throws \Exception
134     *
135     * @return DateTimeZone The timezone as a timezone object
136     * @return DateTimeZone The timezone as a timezone object
137     */
138    protected static function validateTimeZone($timeZone)
139    {
140        if (is_object($timeZone) && $timeZone instanceof DateTimeZone) {
141            return $timeZone;
142        } elseif (is_string($timeZone)) {
143            return new DateTimeZone($timeZone);
144        }
145
146        throw new \Exception('Invalid timezone');
147    }
148
149    /**
150     * Convert a MS serialized datetime value from Excel to a PHP Date/Time object.
151     *
152     * @param float|int $excelTimestamp MS Excel serialized date/time value
153     * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp,
154     *                                                                        if you don't want to treat it as a UTC value
155     *                                                                    Use the default (UST) unless you absolutely need a conversion
156     *
157     * @throws \Exception
158     *
159     * @return \DateTime PHP date/time object
160     */
161    public static function excelToDateTimeObject($excelTimestamp, $timeZone = null)
162    {
163        $timeZone = ($timeZone === null) ? self::getDefaultTimezone() : self::validateTimeZone($timeZone);
164        if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) {
165            if ($excelTimestamp < 1.0) {
166                // Unix timestamp base date
167                $baseDate = new \DateTime('1970-01-01', $timeZone);
168            } else {
169                // MS Excel calendar base dates
170                if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
171                    // Allow adjustment for 1900 Leap Year in MS Excel
172                    $baseDate = ($excelTimestamp < 60) ? new \DateTime('1899-12-31', $timeZone) : new \DateTime('1899-12-30', $timeZone);
173                } else {
174                    $baseDate = new \DateTime('1904-01-01', $timeZone);
175                }
176            }
177        } else {
178            $baseDate = new \DateTime('1899-12-30', $timeZone);
179        }
180
181        $days = floor($excelTimestamp);
182        $partDay = $excelTimestamp - $days;
183        $hours = floor($partDay * 24);
184        $partDay = $partDay * 24 - $hours;
185        $minutes = floor($partDay * 60);
186        $partDay = $partDay * 60 - $minutes;
187        $seconds = round($partDay * 60);
188
189        if ($days >= 0) {
190            $days = '+' . $days;
191        }
192        $interval = $days . ' days';
193
194        return $baseDate->modify($interval)
195            ->setTime((int) $hours, (int) $minutes, (int) $seconds);
196    }
197
198    /**
199     * Convert a MS serialized datetime value from Excel to a unix timestamp.
200     *
201     * @param float|int $excelTimestamp MS Excel serialized date/time value
202     * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp,
203     *                                                                        if you don't want to treat it as a UTC value
204     *                                                                    Use the default (UST) unless you absolutely need a conversion
205     *
206     * @throws \Exception
207     *
208     * @return int Unix timetamp for this date/time
209     */
210    public static function excelToTimestamp($excelTimestamp, $timeZone = null)
211    {
212        return (int) self::excelToDateTimeObject($excelTimestamp, $timeZone)
213            ->format('U');
214    }
215
216    /**
217     * Convert a date from PHP to an MS Excel serialized date/time value.
218     *
219     * @param mixed $dateValue Unix Timestamp or PHP DateTime object or a string
220     *
221     * @return bool|float Excel date/time value
222     *                                  or boolean FALSE on failure
223     */
224    public static function PHPToExcel($dateValue)
225    {
226        if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) {
227            return self::dateTimeToExcel($dateValue);
228        } elseif (is_numeric($dateValue)) {
229            return self::timestampToExcel($dateValue);
230        } elseif (is_string($dateValue)) {
231            return self::stringToExcel($dateValue);
232        }
233
234        return false;
235    }
236
237    /**
238     * Convert a PHP DateTime object to an MS Excel serialized date/time value.
239     *
240     * @param DateTimeInterface $dateValue PHP DateTime object
241     *
242     * @return float MS Excel serialized date/time value
243     */
244    public static function dateTimeToExcel(DateTimeInterface $dateValue)
245    {
246        return self::formattedPHPToExcel(
247            (int) $dateValue->format('Y'),
248            (int) $dateValue->format('m'),
249            (int) $dateValue->format('d'),
250            (int) $dateValue->format('H'),
251            (int) $dateValue->format('i'),
252            (int) $dateValue->format('s')
253        );
254    }
255
256    /**
257     * Convert a Unix timestamp to an MS Excel serialized date/time value.
258     *
259     * @param int $dateValue Unix Timestamp
260     *
261     * @return float MS Excel serialized date/time value
262     */
263    public static function timestampToExcel($dateValue)
264    {
265        if (!is_numeric($dateValue)) {
266            return false;
267        }
268
269        return self::dateTimeToExcel(new \DateTime('@' . $dateValue));
270    }
271
272    /**
273     * formattedPHPToExcel.
274     *
275     * @param int $year
276     * @param int $month
277     * @param int $day
278     * @param int $hours
279     * @param int $minutes
280     * @param int $seconds
281     *
282     * @return float Excel date/time value
283     */
284    public static function formattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0)
285    {
286        if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
287            //
288            //    Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
289            //    This affects every date following 28th February 1900
290            //
291            $excel1900isLeapYear = true;
292            if (($year == 1900) && ($month <= 2)) {
293                $excel1900isLeapYear = false;
294            }
295            $myexcelBaseDate = 2415020;
296        } else {
297            $myexcelBaseDate = 2416481;
298            $excel1900isLeapYear = false;
299        }
300
301        //    Julian base date Adjustment
302        if ($month > 2) {
303            $month -= 3;
304        } else {
305            $month += 9;
306            --$year;
307        }
308
309        //    Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
310        $century = substr($year, 0, 2);
311        $decade = substr($year, 2, 2);
312        $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myexcelBaseDate + $excel1900isLeapYear;
313
314        $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
315
316        return (float) $excelDate + $excelTime;
317    }
318
319    /**
320     * Is a given cell a date/time?
321     *
322     * @param Cell $pCell
323     *
324     * @return bool
325     */
326    public static function isDateTime(Cell $pCell)
327    {
328        return is_numeric($pCell->getValue()) &&
329            self::isDateTimeFormat(
330                $pCell->getWorksheet()->getStyle(
331                    $pCell->getCoordinate()
332                )->getNumberFormat()
333            );
334    }
335
336    /**
337     * Is a given number format a date/time?
338     *
339     * @param NumberFormat $pFormat
340     *
341     * @return bool
342     */
343    public static function isDateTimeFormat(NumberFormat $pFormat)
344    {
345        return self::isDateTimeFormatCode($pFormat->getFormatCode());
346    }
347
348    private static $possibleDateFormatCharacters = 'eymdHs';
349
350    /**
351     * Is a given number format code a date/time?
352     *
353     * @param string $pFormatCode
354     *
355     * @return bool
356     */
357    public static function isDateTimeFormatCode($pFormatCode)
358    {
359        if (strtolower($pFormatCode) === strtolower(NumberFormat::FORMAT_GENERAL)) {
360            //    "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
361            return false;
362        }
363        if (preg_match('/[0#]E[+-]0/i', $pFormatCode)) {
364            //    Scientific format
365            return false;
366        }
367
368        // Switch on formatcode
369        switch ($pFormatCode) {
370            //    Explicitly defined date formats
371            case NumberFormat::FORMAT_DATE_YYYYMMDD:
372            case NumberFormat::FORMAT_DATE_YYYYMMDD2:
373            case NumberFormat::FORMAT_DATE_DDMMYYYY:
374            case NumberFormat::FORMAT_DATE_DMYSLASH:
375            case NumberFormat::FORMAT_DATE_DMYMINUS:
376            case NumberFormat::FORMAT_DATE_DMMINUS:
377            case NumberFormat::FORMAT_DATE_MYMINUS:
378            case NumberFormat::FORMAT_DATE_DATETIME:
379            case NumberFormat::FORMAT_DATE_TIME1:
380            case NumberFormat::FORMAT_DATE_TIME2:
381            case NumberFormat::FORMAT_DATE_TIME3:
382            case NumberFormat::FORMAT_DATE_TIME4:
383            case NumberFormat::FORMAT_DATE_TIME5:
384            case NumberFormat::FORMAT_DATE_TIME6:
385            case NumberFormat::FORMAT_DATE_TIME7:
386            case NumberFormat::FORMAT_DATE_TIME8:
387            case NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
388            case NumberFormat::FORMAT_DATE_XLSX14:
389            case NumberFormat::FORMAT_DATE_XLSX15:
390            case NumberFormat::FORMAT_DATE_XLSX16:
391            case NumberFormat::FORMAT_DATE_XLSX17:
392            case NumberFormat::FORMAT_DATE_XLSX22:
393                return true;
394        }
395
396        //    Typically number, currency or accounting (or occasionally fraction) formats
397        if ((substr($pFormatCode, 0, 1) == '_') || (substr($pFormatCode, 0, 2) == '0 ')) {
398            return false;
399        }
400        // Try checking for any of the date formatting characters that don't appear within square braces
401        if (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/i', $pFormatCode)) {
402            //    We might also have a format mask containing quoted strings...
403            //        we don't want to test for any of our characters within the quoted blocks
404            if (strpos($pFormatCode, '"') !== false) {
405                $segMatcher = false;
406                foreach (explode('"', $pFormatCode) as $subVal) {
407                    //    Only test in alternate array entries (the non-quoted blocks)
408                    if (($segMatcher = !$segMatcher) &&
409                        (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/i', $subVal))) {
410                        return true;
411                    }
412                }
413
414                return false;
415            }
416
417            return true;
418        }
419
420        // No date...
421        return false;
422    }
423
424    /**
425     * Convert a date/time string to Excel time.
426     *
427     * @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
428     *
429     * @return false|float Excel date/time serial value
430     */
431    public static function stringToExcel($dateValue)
432    {
433        if (strlen($dateValue) < 2) {
434            return false;
435        }
436        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})?)?$/iu', $dateValue)) {
437            return false;
438        }
439
440        $dateValueNew = DateTime::DATEVALUE($dateValue);
441
442        if ($dateValueNew === Functions::VALUE()) {
443            return false;
444        }
445
446        if (strpos($dateValue, ':') !== false) {
447            $timeValue = DateTime::TIMEVALUE($dateValue);
448            if ($timeValue === Functions::VALUE()) {
449                return false;
450            }
451            $dateValueNew += $timeValue;
452        }
453
454        return $dateValueNew;
455    }
456
457    /**
458     * Converts a month name (either a long or a short name) to a month number.
459     *
460     * @param string $month Month name or abbreviation
461     *
462     * @return int|string Month number (1 - 12), or the original string argument if it isn't a valid month name
463     */
464    public static function monthStringToNumber($month)
465    {
466        $monthIndex = 1;
467        foreach (self::$monthNames as $shortMonthName => $longMonthName) {
468            if (($month === $longMonthName) || ($month === $shortMonthName)) {
469                return $monthIndex;
470            }
471            ++$monthIndex;
472        }
473
474        return $month;
475    }
476
477    /**
478     * Strips an ordinal from a numeric value.
479     *
480     * @param string $day Day number with an ordinal
481     *
482     * @return int|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
483     */
484    public static function dayStringToNumber($day)
485    {
486        $strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
487        if (is_numeric($strippedDayValue)) {
488            return (int) $strippedDayValue;
489        }
490
491        return $day;
492    }
493}
494