Date::isDateTime() - Code Metrics - Inspection of "fix rounding issues with microseconds calculation" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Pull Request — master (#4476)

by Blizzz

created 2025-05-19 11:59 UTC

Date::isDateTime() A

↳ Parent: Project

Complexity

Conditions	6
Paths	10

Size

Total Lines	25
Code Lines	17

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	18
CRAP Score	6

Importance

Changes

Metric	Value
eloc	17
dl	0
loc	25
ccs	18
cts	18
cp	1
rs	9.0777
c	0
b	0
f	0
cc	6
nc	10
nop	3
crap	6

<?php

namespace PhpOffice\PhpSpreadsheet\Shared;

use DateTime;
use DateTimeInterface;
use DateTimeZone;
use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class Date
{
    /** constants */
    const CALENDAR_WINDOWS_1900 = 1900; //    Base date of 1st Jan 1900 = 1.0
    const CALENDAR_MAC_1904 = 1904; //    Base date of 2nd Jan 1904 = 1.0

    /**
     * Names of the months of the year, indexed by shortname
     * Planned usage for locale settings.
     *
     * @var string[]
     */
    public static array $monthNames = [
        'Jan' => 'January',
        'Feb' => 'February',
        'Mar' => 'March',
        'Apr' => 'April',
        'May' => 'May',
        'Jun' => 'June',
        'Jul' => 'July',
        'Aug' => 'August',
        'Sep' => 'September',
        'Oct' => 'October',
        'Nov' => 'November',
        'Dec' => 'December',
    ];

    /**
     * @var string[]
     */
    public static array $numberSuffixes = [
        'st',
        'nd',
        'rd',
        'th',
    ];

    /**
     * Base calendar year to use for calculations
     * Value is either CALENDAR_WINDOWS_1900 (1900) or CALENDAR_MAC_1904 (1904).
     */
    protected static int $excelCalendar = self::CALENDAR_WINDOWS_1900;

    /**
     * Default timezone to use for DateTime objects.
     */
    protected static ?DateTimeZone $defaultTimeZone = null;

    /**
     * Set the Excel calendar (Windows 1900 or Mac 1904).
     *
     * @param ?int $baseYear Excel base date (1900 or 1904)
     *
     * @return bool Success or failure
     */
    public static function setExcelCalendar(?int $baseYear): bool
    {
        if (
            ($baseYear === self::CALENDAR_WINDOWS_1900)
            || ($baseYear === self::CALENDAR_MAC_1904)
        ) {
            self::$excelCalendar = $baseYear;

            return true;
        }

        return false;
    }

    /**
     * Return the Excel calendar (Windows 1900 or Mac 1904).
     *
     * @return int Excel base date (1900 or 1904)
     */
    public static function getExcelCalendar(): int
    {
        return self::$excelCalendar;
    }

    /**
     * Set the Default timezone to use for dates.
     *
     * @param null|DateTimeZone|string $timeZone The timezone to set for all Excel datetimestamp to PHP DateTime Object conversions
     *
     * @return bool Success or failure
     */
    public static function setDefaultTimezone($timeZone): bool
    {
        try {
            $timeZone = self::validateTimeZone($timeZone);
            self::$defaultTimeZone = $timeZone;
            $retval = true;
        } catch (PhpSpreadsheetException) {
            $retval = false;
        }

        return $retval;
    }

    /**
     * Return the Default timezone, or UTC if default not set.
     */
    public static function getDefaultTimezone(): DateTimeZone
    {
        return self::$defaultTimeZone ?? new DateTimeZone('UTC');
    }

    /**
     * Return the Default timezone, or local timezone if default is not set.
     */
    public static function getDefaultOrLocalTimezone(): DateTimeZone
    {
        return self::$defaultTimeZone ?? new DateTimeZone(date_default_timezone_get());
    }

    /**
     * Return the Default timezone even if null.
     */
    public static function getDefaultTimezoneOrNull(): ?DateTimeZone
    {
        return self::$defaultTimeZone;
    }

    /**
     * Validate a timezone.
     *
     * @param null|DateTimeZone|string $timeZone The timezone to validate, either as a timezone string or object
     *
     * @return ?DateTimeZone The timezone as a timezone object
     */
    private static function validateTimeZone($timeZone): ?DateTimeZone
    {
        if ($timeZone instanceof DateTimeZone || $timeZone === null) {
            return $timeZone;
        }
        if (in_array($timeZone, DateTimeZone::listIdentifiers(DateTimeZone::ALL_WITH_BC))) {
            return new DateTimeZone($timeZone);
        }

        throw new PhpSpreadsheetException('Invalid timezone');
    }

    /**
     * @param mixed $value Converts a date/time in ISO-8601 standard format date string to an Excel
     *                         serialized timestamp.
     *                     See https://en.wikipedia.org/wiki/ISO_8601 for details of the ISO-8601 standard format.
     */
    public static function convertIsoDate(mixed $value): float|int
    {
        if (!is_string($value)) {
            throw new Exception('Non-string value supplied for Iso Date conversion');
        }

        $date = new DateTime($value);
        $dateErrors = DateTime::getLastErrors();

        if (is_array($dateErrors) && ($dateErrors['warning_count'] > 0 || $dateErrors['error_count'] > 0)) {
            throw new Exception("Invalid string $value supplied for datatype Date");
        }

        $newValue = self::PHPToExcel($date);
        if ($newValue === false) {
            throw new Exception("Invalid string $value supplied for datatype Date");
        }

        if (preg_match('/^\s*\d?\d:\d\d(:\d\d([.]\d+)?)?\s*(am|pm)?\s*$/i', $value) == 1) {
            $newValue = fmod($newValue, 1.0);
        }

        return $newValue;
    }

    /**
     * Convert a MS serialized datetime value from Excel to a PHP Date/Time object.
     *
     * @param float|int $excelTimestamp MS Excel serialized date/time value
     * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp,
     *                                           if you don't want to treat it as a UTC value
     *                                           Use the default (UTC) unless you absolutely need a conversion
     *
     * @return DateTime PHP date/time object
     */
    public static function excelToDateTimeObject(float|int $excelTimestamp, null|DateTimeZone|string $timeZone = null): DateTime
    {
        $timeZone = ($timeZone === null) ? self::getDefaultTimezone() : self::validateTimeZone($timeZone);
        if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) {
            if ($excelTimestamp < 1 && self::$excelCalendar === self::CALENDAR_WINDOWS_1900) {
                // Unix timestamp base date
                $baseDate = new DateTime('1970-01-01', $timeZone);
            } else {
                // MS Excel calendar base dates
                if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
                    // Allow adjustment for 1900 Leap Year in MS Excel
                    $baseDate = ($excelTimestamp < 60) ? new DateTime('1899-12-31', $timeZone) : new DateTime('1899-12-30', $timeZone);
                } else {
                    $baseDate = new DateTime('1904-01-01', $timeZone);
                }
            }
        } else {
            $baseDate = new DateTime('1899-12-30', $timeZone);
        }

        if (is_int($excelTimestamp)) {
            if ($excelTimestamp >= 0) {
                return $baseDate->modify("+ $excelTimestamp days");
            }

            return $baseDate->modify("$excelTimestamp days");
        }
        $days = floor($excelTimestamp);
        $partDay = $excelTimestamp - $days;
        $hoursInMs = 86400 * $partDay;
        $wholeSeconds = (int) floor($hoursInMs);

        // flooring here might lose data due to precision issues, hence round it
        $microseconds = (int) round(($hoursInMs - $wholeSeconds) * 1_000_000);

        $microseconds = (int) round($microseconds, -2);

        // rounding may lead to edge cases
        if ($microseconds === 1_000_000) {
            $microseconds = 0;
            ++$wholeSeconds;
        }
        if ($wholeSeconds >= 86400) {
            $wholeSeconds = 0;
            ++$days;
        }

        $hours = intdiv($wholeSeconds, 3600);
        $remainingSeconds = $wholeSeconds % 3600;
        $minutes = intdiv($remainingSeconds, 60);
        $seconds = $remainingSeconds % 60;

        if ($days >= 0) {
            $days = '+' . $days;
        }
        $interval = $days . ' days';

        return $baseDate->modify($interval)
            ->setTime($hours, $minutes, $seconds, $microseconds);
    }

    /**
     * Convert a MS serialized datetime value from Excel to a unix timestamp.
     * The use of Unix timestamps, and therefore this function, is discouraged.
     * They are not Y2038-safe on a 32-bit system, and have no timezone info.
     *
     * @param float|int $excelTimestamp MS Excel serialized date/time value
     * @param null|DateTimeZone|string $timeZone The timezone to assume for the Excel timestamp,
     *                                               if you don't want to treat it as a UTC value
     *                                               Use the default (UTC) unless you absolutely need a conversion
     *
     * @return int Unix timetamp for this date/time
     */
    public static function excelToTimestamp($excelTimestamp, $timeZone = null): int
    {
        $dto = self::excelToDateTimeObject($excelTimestamp, $timeZone);
        self::roundMicroseconds($dto);

        return (int) $dto->format('U');
    }

    /**
     * Convert a date from PHP to an MS Excel serialized date/time value.
     *
     * @param mixed $dateValue PHP DateTime object or a string - Unix timestamp is also permitted, but discouraged;
     *    not Y2038-safe on a 32-bit system, and no timezone info
     *
     * @return false|float Excel date/time value
     *                                  or boolean FALSE on failure
     */
    public static function PHPToExcel(mixed $dateValue)
    {
        if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) {
            return self::dateTimeToExcel($dateValue);
        } elseif (is_numeric($dateValue)) {
            return self::timestampToExcel($dateValue);
        } elseif (is_string($dateValue)) {
            return self::stringToExcel($dateValue);
        }

        return false;
    }

    /**
     * Convert a PHP DateTime object to an MS Excel serialized date/time value.
     *
     * @param DateTimeInterface $dateValue PHP DateTime object
     *
     * @return float MS Excel serialized date/time value
     */
    public static function dateTimeToExcel(DateTimeInterface $dateValue): float
    {
        $seconds = (float) sprintf('%d.%06d', $dateValue->format('s'), $dateValue->format('u'));

        return self::formattedPHPToExcel(
            (int) $dateValue->format('Y'),
            (int) $dateValue->format('m'),
            (int) $dateValue->format('d'),
            (int) $dateValue->format('H'),
            (int) $dateValue->format('i'),
            $seconds
        );
    }

    /**
     * Convert a Unix timestamp to an MS Excel serialized date/time value.
     * The use of Unix timestamps, and therefore this function, is discouraged.
     * They are not Y2038-safe on a 32-bit system, and have no timezone info.
     *
     * @param float|int|string $unixTimestamp Unix Timestamp
     *
     * @return false|float MS Excel serialized date/time value
     */
    public static function timestampToExcel($unixTimestamp): bool|float
    {
        if (!is_numeric($unixTimestamp)) {
            return false;
        }

        return self::dateTimeToExcel(new DateTime('@' . $unixTimestamp));
    }

    /**
     * formattedPHPToExcel.
     *
     * @return float Excel date/time value
     */
    public static function formattedPHPToExcel(int $year, int $month, int $day, int $hours = 0, int $minutes = 0, float|int $seconds = 0): float
    {
        if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
            //
            //    Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
            //    This affects every date following 28th February 1900
            //
            $excel1900isLeapYear = true;
            if (($year == 1900) && ($month <= 2)) {
                $excel1900isLeapYear = false;
            }
            $myexcelBaseDate = 2415020;
        } else {
            $myexcelBaseDate = 2416481;
            $excel1900isLeapYear = false;
        }

        //    Julian base date Adjustment
        if ($month > 2) {
            $month -= 3;
        } else {
            $month += 9;
            --$year;
        }

        //    Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
        $century = (int) substr((string) $year, 0, 2);
        $decade = (int) substr((string) $year, 2, 2);
        $excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myexcelBaseDate + $excel1900isLeapYear;

        $excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;

        return (float) $excelDate + $excelTime;
    }

    /**
     * Is a given cell a date/time?
     */
    public static function isDateTime(Cell $cell, mixed $value = null, bool $dateWithoutTimeOkay = true): bool
    {
        $result = false;
        $worksheet = $cell->getWorksheetOrNull();
        $spreadsheet = ($worksheet === null) ? null : $worksheet->getParent();
        if ($worksheet !== null && $spreadsheet !== null) {
            $index = $spreadsheet->getActiveSheetIndex();
            $selected = $worksheet->getSelectedCells();

            try {
                $result = is_numeric($value ?? $cell->getCalculatedValue())
                    && self::isDateTimeFormat(
                        $worksheet->getStyle(
                            $cell->getCoordinate()
                        )->getNumberFormat(),
                        $dateWithoutTimeOkay
                    );
            } catch (Exception) {
                // Result is already false, so no need to actually do anything here
            }
            $worksheet->setSelectedCells($selected);
            $spreadsheet->setActiveSheetIndex($index);
        }

        return $result;
    }

    /**
     * Is a given NumberFormat code a date/time format code?
     */
    public static function isDateTimeFormat(NumberFormat $excelFormatCode, bool $dateWithoutTimeOkay = true): bool
    {
        return self::isDateTimeFormatCode((string) $excelFormatCode->getFormatCode(), $dateWithoutTimeOkay);
    }

    private const POSSIBLE_DATETIME_FORMAT_CHARACTERS = 'eymdHs';
    private const POSSIBLE_TIME_FORMAT_CHARACTERS = 'Hs'; // note - no 'm' due to ambiguity

    /**
     * Is a given number format code a date/time?
     */
    public static function isDateTimeFormatCode(string $excelFormatCode, bool $dateWithoutTimeOkay = true): bool
    {
        if (strtolower($excelFormatCode) === strtolower(NumberFormat::FORMAT_GENERAL)) {
            //    "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
            return false;
        }
        if (preg_match('/[0#]E[+-]0/i', $excelFormatCode)) {
            //    Scientific format
            return false;
        }

        // Switch on formatcode
        $excelFormatCode = (string) NumberFormat::convertSystemFormats($excelFormatCode);
        if (in_array($excelFormatCode, NumberFormat::DATE_TIME_OR_DATETIME_ARRAY, true)) {
            return $dateWithoutTimeOkay || in_array($excelFormatCode, NumberFormat::TIME_OR_DATETIME_ARRAY);
        }

        //    Typically number, currency or accounting (or occasionally fraction) formats
        if ((str_starts_with($excelFormatCode, '_')) || (str_starts_with($excelFormatCode, '0 '))) {
            return false;
        }
        // Some "special formats" provided in German Excel versions were detected as date time value,
        // so filter them out here - "\C\H\-00000" (Switzerland) and "\D-00000" (Germany).
        if (str_contains($excelFormatCode, '-00000')) {
            return false;
        }
        $possibleFormatCharacters = $dateWithoutTimeOkay ? self::POSSIBLE_DATETIME_FORMAT_CHARACTERS : self::POSSIBLE_TIME_FORMAT_CHARACTERS;
        // Try checking for any of the date formatting characters that don't appear within square braces
        if (preg_match('/(^|\])[^\[]*[' . $possibleFormatCharacters . ']/i', $excelFormatCode)) {
            //    We might also have a format mask containing quoted strings...
            //        we don't want to test for any of our characters within the quoted blocks
            if (str_contains($excelFormatCode, '"')) {
                $segMatcher = false;
                foreach (explode('"', $excelFormatCode) as $subVal) {
                    //    Only test in alternate array entries (the non-quoted blocks)
                    $segMatcher = $segMatcher === false;
                    if (
                        $segMatcher
                        && (preg_match('/(^|\])[^\[]*[' . $possibleFormatCharacters . ']/i', $subVal))
                    ) {
                        return true;
                    }
                }

                return false;
            }

            return true;
        }

        // No date...
        return false;
    }

    /**
     * Convert a date/time string to Excel time.
     *
     * @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
     *
     * @return false|float Excel date/time serial value
     */
    public static function stringToExcel(string $dateValue): bool|float
    {
        if (strlen($dateValue) < 2) {
            return false;
        }
        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)) {
            return false;
        }

        $dateValueNew = DateTimeExcel\DateValue::fromString($dateValue);

        if (!is_float($dateValueNew)) {
            return false;
        }

        if (str_contains($dateValue, ':')) {
            $timeValue = DateTimeExcel\TimeValue::fromString($dateValue);
            if (!is_float($timeValue)) {
                return false;
            }
            $dateValueNew += $timeValue;
        }

        return $dateValueNew;
    }

    /**
     * Converts a month name (either a long or a short name) to a month number.
     *
     * @param string $monthName Month name or abbreviation
     *
     * @return int|string Month number (1 - 12), or the original string argument if it isn't a valid month name
     */
    public static function monthStringToNumber(string $monthName)
    {
        $monthIndex = 1;
        foreach (self::$monthNames as $shortMonthName => $longMonthName) {
            if (($monthName === $longMonthName) || ($monthName === $shortMonthName)) {
                return $monthIndex;
            }
            ++$monthIndex;
        }

        return $monthName;
    }

    /**
     * Strips an ordinal from a numeric value.
     *
     * @param string $day Day number with an ordinal
     *
     * @return int|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
     */
    public static function dayStringToNumber(string $day)
    {
        $strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
        if (is_numeric($strippedDayValue)) {
            return (int) $strippedDayValue;
        }

        return $day;
    }

    public static function dateTimeFromTimestamp(string $date, ?DateTimeZone $timeZone = null): DateTime
    {
        $dtobj = DateTime::createFromFormat('U', $date) ?: new DateTime();
        $dtobj->setTimeZone($timeZone ?? self::getDefaultOrLocalTimezone());

        return $dtobj;
    }

    public static function formattedDateTimeFromTimestamp(string $date, string $format, ?DateTimeZone $timeZone = null): string
    {
        $dtobj = self::dateTimeFromTimestamp($date, $timeZone);

        return $dtobj->format($format);
    }

    /**
     * Round the given DateTime object to seconds.
     */
    public static function roundMicroseconds(DateTime $dti): void
    {
        $microseconds = (int) $dti->format('u');
        $rounded = (int) round($microseconds, -6);
        $modify = $rounded - $microseconds;
        if ($modify !== 0) {
            $dti->modify(($modify > 0 ? '+' : '') . $modify . ' microseconds');
        }
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Shared;
4
5		use DateTime;
6		use DateTimeInterface;
7		use DateTimeZone;
8		use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
9		use PhpOffice\PhpSpreadsheet\Calculation\Functions;
10		use PhpOffice\PhpSpreadsheet\Cell\Cell;
11		use PhpOffice\PhpSpreadsheet\Exception;
12		use PhpOffice\PhpSpreadsheet\Exception as PhpSpreadsheetException;
13		use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
14
15		class Date
16		{
17		/** constants */
18		const CALENDAR_WINDOWS_1900 = 1900; // Base date of 1st Jan 1900 = 1.0
19		const CALENDAR_MAC_1904 = 1904; // Base date of 2nd Jan 1904 = 1.0
20
21		/**
22		* Names of the months of the year, indexed by shortname
23		* Planned usage for locale settings.
24		*
25		* @var string[]
26		*/
27		public static array $monthNames = [
28		'Jan' => 'January',
29		'Feb' => 'February',
30		'Mar' => 'March',
31		'Apr' => 'April',
32		'May' => 'May',
33		'Jun' => 'June',
34		'Jul' => 'July',
35		'Aug' => 'August',
36		'Sep' => 'September',
37		'Oct' => 'October',
38		'Nov' => 'November',
39		'Dec' => 'December',
40		];
41
42		/**
43		* @var string[]
44		*/
45		public static array $numberSuffixes = [
46		'st',
47		'nd',
48		'rd',
49		'th',
50		];
51
52		/**
53		* Base calendar year to use for calculations
54		* Value is either CALENDAR_WINDOWS_1900 (1900) or CALENDAR_MAC_1904 (1904).
55		*/
56		protected static int $excelCalendar = self::CALENDAR_WINDOWS_1900;
57
58		/**
59		* Default timezone to use for DateTime objects.
60		*/
61		protected static ?DateTimeZone $defaultTimeZone = null;
62
63		/**
64		* Set the Excel calendar (Windows 1900 or Mac 1904).
65		*
66		* @param ?int $baseYear Excel base date (1900 or 1904)
67		*
68		* @return bool Success or failure
69		*/
70	9633	public static function setExcelCalendar(?int $baseYear): bool
71		{
72		if (
73	9633	($baseYear === self::CALENDAR_WINDOWS_1900)
74	9633	\|\| ($baseYear === self::CALENDAR_MAC_1904)
75		) {
76	9633	self::$excelCalendar = $baseYear;
77
78	9633	return true;
79		}
80
81	1	return false;
82		}
83
84		/**
85		* Return the Excel calendar (Windows 1900 or Mac 1904).
86		*
87		* @return int Excel base date (1900 or 1904)
88		*/
89	10126	public static function getExcelCalendar(): int
90		{
91	10126	return self::$excelCalendar;
92		}
93
94		/**
95		* Set the Default timezone to use for dates.
96		*
97		* @param null\|DateTimeZone\|string $timeZone The timezone to set for all Excel datetimestamp to PHP DateTime Object conversions
98		*
99		* @return bool Success or failure
100		*/
101	147	public static function setDefaultTimezone($timeZone): bool
102		{
103		try {
104	147	$timeZone = self::validateTimeZone($timeZone);
105	147	self::$defaultTimeZone = $timeZone;
106	147	$retval = true;
107	1	} catch (PhpSpreadsheetException) {
108	1	$retval = false;
109		}
110
111	147	return $retval;
112		}
113
114		/**
115		* Return the Default timezone, or UTC if default not set.
116		*/
117	2336	public static function getDefaultTimezone(): DateTimeZone
118		{
119	2336	return self::$defaultTimeZone ?? new DateTimeZone('UTC');
120		}
121
122		/**
123		* Return the Default timezone, or local timezone if default is not set.
124		*/
125	1012	public static function getDefaultOrLocalTimezone(): DateTimeZone
126		{
127	1012	return self::$defaultTimeZone ?? new DateTimeZone(date_default_timezone_get());
128		}
129
130		/**
131		* Return the Default timezone even if null.
132		*/
133	147	public static function getDefaultTimezoneOrNull(): ?DateTimeZone
134		{
135	147	return self::$defaultTimeZone;
136		}
137
138		/**
139		* Validate a timezone.
140		*
141		* @param null\|DateTimeZone\|string $timeZone The timezone to validate, either as a timezone string or object
142		*
143		* @return ?DateTimeZone The timezone as a timezone object
144		*/
145	165	private static function validateTimeZone($timeZone): ?DateTimeZone
146		{
147	165	if ($timeZone instanceof DateTimeZone \|\| $timeZone === null) {
148	165	return $timeZone;
149		}
150	26	if (in_array($timeZone, DateTimeZone::listIdentifiers(DateTimeZone::ALL_WITH_BC))) {
151	25	return new DateTimeZone($timeZone);
152		}
153
154	1	throw new PhpSpreadsheetException('Invalid timezone');
155		}
156
157		/**
158		* @param mixed $value Converts a date/time in ISO-8601 standard format date string to an Excel
159		* serialized timestamp.
160		* See https://en.wikipedia.org/wiki/ISO_8601 for details of the ISO-8601 standard format.
161		*/
162	20	public static function convertIsoDate(mixed $value): float\|int
163		{
164	20	if (!is_string($value)) {
165	1	throw new Exception('Non-string value supplied for Iso Date conversion');
166		}
167
168	19	$date = new DateTime($value);
169	19	$dateErrors = DateTime::getLastErrors();
170
171	19	if (is_array($dateErrors) && ($dateErrors['warning_count'] > 0 \|\| $dateErrors['error_count'] > 0)) {
172	1	throw new Exception("Invalid string $value supplied for datatype Date");
173		}
174
175	18	$newValue = self::PHPToExcel($date);
176	18	if ($newValue === false) {
177		throw new Exception("Invalid string $value supplied for datatype Date");
178		}
179
180	18	if (preg_match('/^\s\d?\d:\d\d(:\d\d([.]\d+)?)?\s(am\|pm)?\s*$/i', $value) == 1) {
181	4	$newValue = fmod($newValue, 1.0);
182		}
183
184	18	return $newValue;
185		}
186
187		/**
188		* Convert a MS serialized datetime value from Excel to a PHP Date/Time object.
189		*
190		* @param float\|int $excelTimestamp MS Excel serialized date/time value
191		* @param null\|DateTimeZone\|string $timeZone The timezone to assume for the Excel timestamp,
192		* if you don't want to treat it as a UTC value
193		* Use the default (UTC) unless you absolutely need a conversion
194		*
195		* @return DateTime PHP date/time object
196		*/
197	2373	public static function excelToDateTimeObject(float\|int $excelTimestamp, null\|DateTimeZone\|string $timeZone = null): DateTime
198		{
199	2373	$timeZone = ($timeZone === null) ? self::getDefaultTimezone() : self::validateTimeZone($timeZone);
200	2373	if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_EXCEL) {
201	2350	if ($excelTimestamp < 1 && self::$excelCalendar === self::CALENDAR_WINDOWS_1900) {
202		// Unix timestamp base date
203	188	$baseDate = new DateTime('1970-01-01', $timeZone);
204		} else {
205		// MS Excel calendar base dates
206	2173	if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
207		// Allow adjustment for 1900 Leap Year in MS Excel
208	2077	$baseDate = ($excelTimestamp < 60) ? new DateTime('1899-12-31', $timeZone) : new DateTime('1899-12-30', $timeZone);
209		} else {
210	98	$baseDate = new DateTime('1904-01-01', $timeZone);
211		}
212		}
213		} else {
214	23	$baseDate = new DateTime('1899-12-30', $timeZone);
215		}
216
217	2373	if (is_int($excelTimestamp)) {
218	105	if ($excelTimestamp >= 0) {
219	105	return $baseDate->modify("+ $excelTimestamp days");
220		}
221
222	1	return $baseDate->modify("$excelTimestamp days");
223		}
224	2296	$days = floor($excelTimestamp);
225	2296	$partDay = $excelTimestamp - $days;
226	2296	$hoursInMs = 86400 * $partDay;
227	2296	$wholeSeconds = (int) floor($hoursInMs);
228	2296
229	2296	// flooring here might lose data due to precision issues, hence round it
230	2296	$microseconds = (int) round(($hoursInMs - $wholeSeconds) * 1_000_000);
		0 ignored issues – show Bug introduced 2025-05-19 12:10 UTC by Report Bug Copy Issue Report A parse error occurred: Syntax error, unexpected T_STRING, expecting ',' or ')' on line 230 at column 68 Loading history...
231	2296	$microseconds = (int) round($microseconds, -2);
232	2296
233		// rounding may lead to edge cases
234	2296	if ($microseconds === 1_000_000) {
235	2291	$microseconds = 0;
236		++$wholeSeconds;
237	2296	}
238		if ($wholeSeconds >= 86400) {
239	2296	$wholeSeconds = 0;
240	2296	++$days;
241		}
242
243		$hours = intdiv($wholeSeconds, 3600);
244		$remainingSeconds = $wholeSeconds % 3600;
245		$minutes = intdiv($remainingSeconds, 60);
246		$seconds = $remainingSeconds % 60;
247
248		if ($days >= 0) {
249		$days = '+' . $days;
250		}
251		$interval = $days . ' days';
252
253		return $baseDate->modify($interval)
254		->setTime($hours, $minutes, $seconds, $microseconds);
255	55	}
256
257	55	/**
258	55	* Convert a MS serialized datetime value from Excel to a unix timestamp.
259		* The use of Unix timestamps, and therefore this function, is discouraged.
260	55	* They are not Y2038-safe on a 32-bit system, and have no timezone info.
261		*
262		* @param float\|int $excelTimestamp MS Excel serialized date/time value
263		* @param null\|DateTimeZone\|string $timeZone The timezone to assume for the Excel timestamp,
264		* if you don't want to treat it as a UTC value
265		* Use the default (UTC) unless you absolutely need a conversion
266		*
267		* @return int Unix timetamp for this date/time
268		*/
269		public static function excelToTimestamp($excelTimestamp, $timeZone = null): int
270		{
271		$dto = self::excelToDateTimeObject($excelTimestamp, $timeZone);
272	294	self::roundMicroseconds($dto);
273
274	294	return (int) $dto->format('U');
275	289	}
276	11
277	10	/**
278	7	* Convert a date from PHP to an MS Excel serialized date/time value.
279	1	*
280		* @param mixed $dateValue PHP DateTime object or a string - Unix timestamp is also permitted, but discouraged;
281		* not Y2038-safe on a 32-bit system, and no timezone info
282	6	*
283		* @return false\|float Excel date/time value
284		* or boolean FALSE on failure
285		*/
286		public static function PHPToExcel(mixed $dateValue)
287		{
288		if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) {
289		return self::dateTimeToExcel($dateValue);
290		} elseif (is_numeric($dateValue)) {
291		return self::timestampToExcel($dateValue);
292	343	} elseif (is_string($dateValue)) {
293		return self::stringToExcel($dateValue);
294	343	}
295
296	343	return false;
297	343	}
298	343
299	343	/**
300	343	* Convert a PHP DateTime object to an MS Excel serialized date/time value.
301	343	*
302	343	* @param DateTimeInterface $dateValue PHP DateTime object
303	343	*
304		* @return float MS Excel serialized date/time value
305		*/
306		public static function dateTimeToExcel(DateTimeInterface $dateValue): float
307		{
308		$seconds = (float) sprintf('%d.%06d', $dateValue->format('s'), $dateValue->format('u'));
309
310		return self::formattedPHPToExcel(
311		(int) $dateValue->format('Y'),
312		(int) $dateValue->format('m'),
313		(int) $dateValue->format('d'),
314		(int) $dateValue->format('H'),
315	28	(int) $dateValue->format('i'),
316		$seconds
317	28	);
318	1	}
319
320		/**
321	27	* Convert a Unix timestamp to an MS Excel serialized date/time value.
322		* The use of Unix timestamps, and therefore this function, is discouraged.
323		* They are not Y2038-safe on a 32-bit system, and have no timezone info.
324		*
325		* @param float\|int\|string $unixTimestamp Unix Timestamp
326		*
327		* @return false\|float MS Excel serialized date/time value
328		*/
329	2765	public static function timestampToExcel($unixTimestamp): bool\|float
330		{
331	2765	if (!is_numeric($unixTimestamp)) {
332		return false;
333		}
334
335		return self::dateTimeToExcel(new DateTime('@' . $unixTimestamp));
336	2696	}
337	2696
338	241	/**
339		* formattedPHPToExcel.
340	2696	*
341		* @return float Excel date/time value
342	71	*/
343	71	public static function formattedPHPToExcel(int $year, int $month, int $day, int $hours = 0, int $minutes = 0, float\|int $seconds = 0): float
344		{
345		if (self::$excelCalendar == self::CALENDAR_WINDOWS_1900) {
346		//
347	2765	// Fudge factor for the erroneous fact that the year 1900 is treated as a Leap Year in MS Excel
348	1828	// This affects every date following 28th February 1900
349		//
350	1750	$excel1900isLeapYear = true;
351	1750	if (($year == 1900) && ($month <= 2)) {
352		$excel1900isLeapYear = false;
353		}
354		$myexcelBaseDate = 2415020;
355	2765	} else {
356	2765	$myexcelBaseDate = 2416481;
357	2765	$excel1900isLeapYear = false;
358		}
359	2765
360		// Julian base date Adjustment
361	2765	if ($month > 2) {
362		$month -= 3;
363		} else {
364		$month += 9;
365		--$year;
366		}
367	9
368		// Calculate the Julian Date, then subtract the Excel base date (JD 2415020 = 31-Dec-1899 Giving Excel Date of 0)
369	9	$century = (int) substr((string) $year, 0, 2);
370	9	$decade = (int) substr((string) $year, 2, 2);
371	9	$excelDate = floor((146097 * $century) / 4) + floor((1461 * $decade) / 4) + floor((153 * $month + 2) / 5) + $day + 1721119 - $myexcelBaseDate + $excel1900isLeapYear;
372	9
373	9	$excelTime = (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
374	9
375		return (float) $excelDate + $excelTime;
376		}
377	9
378	9	/**
379	9	* Is a given cell a date/time?
380	9	*/
381	9	public static function isDateTime(Cell $cell, mixed $value = null, bool $dateWithoutTimeOkay = true): bool
382	9	{
383	9	$result = false;
384	1	$worksheet = $cell->getWorksheetOrNull();
385		$spreadsheet = ($worksheet === null) ? null : $worksheet->getParent();
386		if ($worksheet !== null && $spreadsheet !== null) {
387	9	$index = $spreadsheet->getActiveSheetIndex();
388	9	$selected = $worksheet->getSelectedCells();
389
390		try {
391	9	$result = is_numeric($value ?? $cell->getCalculatedValue())
392		&& self::isDateTimeFormat(
393		$worksheet->getStyle(
394		$cell->getCoordinate()
395		)->getNumberFormat(),
396		$dateWithoutTimeOkay
397	9	);
398		} catch (Exception) {
399	9	// Result is already false, so no need to actually do anything here
400		}
401		$worksheet->setSelectedCells($selected);
402		$spreadsheet->setActiveSheetIndex($index);
403		}
404
405		return $result;
406		}
407
408	99	/**
409		* Is a given NumberFormat code a date/time format code?
410	99	*/
411		public static function isDateTimeFormat(NumberFormat $excelFormatCode, bool $dateWithoutTimeOkay = true): bool
412	29	{
413		return self::isDateTimeFormatCode((string) $excelFormatCode->getFormatCode(), $dateWithoutTimeOkay);
414	75	}
415
416	1	private const POSSIBLE_DATETIME_FORMAT_CHARACTERS = 'eymdHs';
417		private const POSSIBLE_TIME_FORMAT_CHARACTERS = 'Hs'; // note - no 'm' due to ambiguity
418
419		/**
420	75	* Is a given number format code a date/time?
421	75	*/
422	31	public static function isDateTimeFormatCode(string $excelFormatCode, bool $dateWithoutTimeOkay = true): bool
423		{
424		if (strtolower($excelFormatCode) === strtolower(NumberFormat::FORMAT_GENERAL)) {
425		// "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
426	48	return false;
427	1	}
428		if (preg_match('/[0#]E[+-]0/i', $excelFormatCode)) {
429		// Scientific format
430		return false;
431	47	}
432	2
433		// Switch on formatcode
434	45	$excelFormatCode = (string) NumberFormat::convertSystemFormats($excelFormatCode);
435		if (in_array($excelFormatCode, NumberFormat::DATE_TIME_OR_DATETIME_ARRAY, true)) {
436	45	return $dateWithoutTimeOkay \|\| in_array($excelFormatCode, NumberFormat::TIME_OR_DATETIME_ARRAY);
437		}
438
439	31	// Typically number, currency or accounting (or occasionally fraction) formats
440	7	if ((str_starts_with($excelFormatCode, '_')) \|\| (str_starts_with($excelFormatCode, '0 '))) {
441	7	return false;
442		}
443	7	// Some "special formats" provided in German Excel versions were detected as date time value,
444		// so filter them out here - "\C\H\-00000" (Switzerland) and "\D-00000" (Germany).
445	7	if (str_contains($excelFormatCode, '-00000')) {
446	7	return false;
447		}
448	6	$possibleFormatCharacters = $dateWithoutTimeOkay ? self::POSSIBLE_DATETIME_FORMAT_CHARACTERS : self::POSSIBLE_TIME_FORMAT_CHARACTERS;
449		// Try checking for any of the date formatting characters that don't appear within square braces
450		if (preg_match('/(^\|\])[^\[]*[' . $possibleFormatCharacters . ']/i', $excelFormatCode)) {
451		// We might also have a format mask containing quoted strings...
452	1	// we don't want to test for any of our characters within the quoted blocks
453		if (str_contains($excelFormatCode, '"')) {
454		$segMatcher = false;
455	29	foreach (explode('"', $excelFormatCode) as $subVal) {
456		// Only test in alternate array entries (the non-quoted blocks)
457		$segMatcher = $segMatcher === false;
458		if (
459	19	$segMatcher
460		&& (preg_match('/(^\|\])[^\[]*[' . $possibleFormatCharacters . ']/i', $subVal))
461		) {
462		return true;
463		}
464		}
465
466		return false;
467		}
468
469	182	return true;
470		}
471	182
472	27	// No date...
473		return false;
474	157	}
475	154
476		/**
477		* Convert a date/time string to Excel time.
478	9	*
479		* @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
480	9	*
481	2	* @return false\|float Excel date/time serial value
482		*/
483		public static function stringToExcel(string $dateValue): bool\|float
484	8	{
485	2	if (strlen($dateValue) < 2) {
486	2	return false;
487	1	}
488		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)) {
489	2	return false;
490		}
491
492	8	$dateValueNew = DateTimeExcel\DateValue::fromString($dateValue);
493
494		if (!is_float($dateValueNew)) {
495		return false;
496		}
497
498		if (str_contains($dateValue, ':')) {
499		$timeValue = DateTimeExcel\TimeValue::fromString($dateValue);
500		if (!is_float($timeValue)) {
501		return false;
502	12	}
503		$dateValueNew += $timeValue;
504	12	}
505	12
506	12	return $dateValueNew;
507	6	}
508
509	12	/**
510		* Converts a month name (either a long or a short name) to a month number.
511		*
512	6	* @param string $monthName Month name or abbreviation
513		*
514		* @return int\|string Month number (1 - 12), or the original string argument if it isn't a valid month name
515		*/
516		public static function monthStringToNumber(string $monthName)
517		{
518		$monthIndex = 1;
519		foreach (self::$monthNames as $shortMonthName => $longMonthName) {
520		if (($monthName === $longMonthName) \|\| ($monthName === $shortMonthName)) {
521		return $monthIndex;
522	18	}
523		++$monthIndex;
524	18	}
525	18
526	15	return $monthName;
527		}
528
529	3	/**
530		* Strips an ordinal from a numeric value.
531		*
532	1018	* @param string $day Day number with an ordinal
533		*
534	1018	* @return int\|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
535	1018	*/
536		public static function dayStringToNumber(string $day)
537	1018	{
538		$strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
539		if (is_numeric($strippedDayValue)) {
540	10	return (int) $strippedDayValue;
541		}
542	10
543		return $day;
544	10	}
545
546		public static function dateTimeFromTimestamp(string $date, ?DateTimeZone $timeZone = null): DateTime
547		{
548		$dtobj = DateTime::createFromFormat('U', $date) ?: new DateTime();
549		$dtobj->setTimeZone($timeZone ?? self::getDefaultOrLocalTimezone());
550	666
551		return $dtobj;
552	666	}
553	666
554	666	public static function formattedDateTimeFromTimestamp(string $date, string $format, ?DateTimeZone $timeZone = null): string
555	666	{
556	23	$dtobj = self::dateTimeFromTimestamp($date, $timeZone);
557
558		return $dtobj->format($format);
559		}
560
561		/**
562		* Round the given DateTime object to seconds.
563		*/
564		public static function roundMicroseconds(DateTime $dti): void
565		{
566		$microseconds = (int) $dti->format('u');
567		$rounded = (int) round($microseconds, -6);
568		$modify = $rounded - $microseconds;
569		if ($modify !== 0) {
570		$dti->modify(($modify > 0 ? '+' : '') . $modify . ' microseconds');
571		}
572		}
573		}
574

PHPOffice / PhpSpreadsheet

Pull Request — master (#4476)

Date::isDateTime() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like