Date::dateTimeToExcel() - Code Metrics - Inspection of "Dependencies compatible with PHP 5.6" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — develop ( 4fd8e7...d3e769 )

by Adrien

created 2017-10-01 12:02 UTC

Date::dateTimeToExcel() A

↳ Parent: Date

Complexity

Conditions	1
Paths	1

Size

Total Lines	11
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	8
CRAP Score	1

Importance

Changes

Metric	Value
cc	1
eloc	8
nc	1
nop	1
dl	0
loc	11
rs	9.4285
c	0
b	0
f	0
ccs	8
cts	8
cp	1
crap	1

<?php

namespace PhpOffice\PhpSpreadsheet\Shared;

use DateTimeInterface;
use DateTimeZone;
use PhpOffice\PhpSpreadsheet\Calculation\DateTime;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Cell;
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
     *
     * @public
     * @var string[]
     */
    public static $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',
    ];

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

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

    /*
     * Default timezone to use for DateTime objects
     *
     * @private
     * @var null|\DateTimeZone
     */
    protected static $defaultTimeZone;

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

            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()
    {
        return self::$excelCalendar;
    }

    /**
     * Set the Default timezone to use for dates.
     *
     * @param DateTimeZone|string $timeZone The timezone to set for all Excel datetimestamp to PHP DateTime Object conversions
     *
     * @throws \Exception
     *
     * @return bool Success or failure
     * @return bool Success or failure
     */
    public static function setDefaultTimezone($timeZone)
    {
        if ($timeZone = self::validateTimeZone($timeZone)) {
            self::$defaultTimeZone = $timeZone;

            return true;
        }

        return false;
    }

    /**
     * Return the Default timezone being used for dates.
     *
     * @return DateTimeZone The timezone being used as default for Excel timestamp to PHP DateTime object
     */
    public static function getDefaultTimezone()
    {
        if (self::$defaultTimeZone === null) {
            self::$defaultTimeZone = new DateTimeZone('UTC');
        }

        return self::$defaultTimeZone;
    }

    /**
     * Validate a timezone.
     *
     * @param DateTimeZone|string $timeZone The timezone to validate, either as a timezone string or object
     *
     * @throws \Exception
     *
     * @return DateTimeZone The timezone as a timezone object
     * @return DateTimeZone The timezone as a timezone object
     */
    protected static function validateTimeZone($timeZone)
    {
        if (is_object($timeZone) && $timeZone instanceof DateTimeZone) {
            return $timeZone;
        } elseif (is_string($timeZone)) {
            return new DateTimeZone($timeZone);
        }

        throw new \Exception('Invalid timezone');
    }

    /**
     * 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 (UST) unless you absolutely need a conversion
     *
     * @throws \Exception
     *
     * @return \DateTime PHP date/time object
     */
    public static function excelToDateTimeObject($excelTimestamp, $timeZone = null)
    {
        $timeZone = ($timeZone === null) ? self::getDefaultTimezone() : self::validateTimeZone($timeZone);
        if ($excelTimestamp < 1.0) {
            // 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);
            }
        }
        $days = floor($excelTimestamp);
        $partDay = $excelTimestamp - $days;
        $hours = floor($partDay * 24);
        $partDay = $partDay * 24 - $hours;
        $minutes = floor($partDay * 60);
        $partDay = $partDay * 60 - $minutes;
        $seconds = round($partDay * 60);

        $interval = '+' . $days . ' days';

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

    /**
     * Convert a MS serialized datetime value from Excel to a unix timestamp.
     *
     * @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 (UST) unless you absolutely need a conversion
     *
     * @throws \Exception
     *
     * @return int Unix timetamp for this date/time
     */
    public static function excelToTimestamp($excelTimestamp, $timeZone = null)
    {
        return (int) self::excelToDateTimeObject($excelTimestamp, $timeZone)
            ->format('U');
    }

    /**
     * Convert a date from PHP to an MS Excel serialized date/time value.
     *
     * @param mixed $dateValue Unix Timestamp or PHP DateTime object or a string
     *
     * @return bool|float Excel date/time value
     *                                  or boolean FALSE on failure
     */
    public static function PHPToExcel($dateValue)
    {
        if ((is_object($dateValue)) && ($dateValue instanceof DateTimeInterface)) {
            return self::dateTimeToExcel($dateValue);
        } elseif (is_numeric($dateValue)) {
            return self::timestampToExcel($dateValue);
function acceptsInteger($int) { }

$x = '123'; // string "123"

// Instead of
acceptsInteger($x);

// we recommend to use
acceptsInteger((integer) $x);
        } 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)
    {
        return self::formattedPHPToExcel(
            $dateValue->format('Y'),
            $dateValue->format('m'),
            $dateValue->format('d'),
            $dateValue->format('H'),
            $dateValue->format('i'),
            $dateValue->format('s')
        );
    }

    /**
     * Convert a Unix timestamp to an MS Excel serialized date/time value.
     *
     * @param DateTimeInterface $dateValue Unix Timestamp
     *
     * @return float MS Excel serialized date/time value
     */
    public static function timestampToExcel($dateValue)
    {
        if (!is_numeric($dateValue)) {
            return false;
        }

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

    /**
     * formattedPHPToExcel.
     *
     * @param int $year
     * @param int $month
     * @param int $day
     * @param int $hours
     * @param int $minutes
     * @param int $seconds
     *
     * @return float Excel date/time value
     */
    public static function formattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0)
    {
        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 = substr($year, 0, 2);
        $decade = substr($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?
     *
     * @param Cell $pCell
     *
     * @return bool
     */
    public static function isDateTime(Cell $pCell)
    {
        return self::isDateTimeFormat(
            $pCell->getWorksheet()->getStyle(
                $pCell->getCoordinate()
            )->getNumberFormat()
        );
    }

    /**
     * Is a given number format a date/time?
     *
     * @param NumberFormat $pFormat
     *
     * @return bool
     */
    public static function isDateTimeFormat(NumberFormat $pFormat)
    {
        return self::isDateTimeFormatCode($pFormat->getFormatCode());
    }

    private static $possibleDateFormatCharacters = 'eymdHs';

    /**
     * Is a given number format code a date/time?
     *
     * @param string $pFormatCode
     *
     * @return bool
     */
    public static function isDateTimeFormatCode($pFormatCode)
    {
        if (strtolower($pFormatCode) === 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', $pFormatCode)) {
            //    Scientific format
            return false;
        }

        // Switch on formatcode
        switch ($pFormatCode) {
            //    Explicitly defined date formats
            case NumberFormat::FORMAT_DATE_YYYYMMDD:
            case NumberFormat::FORMAT_DATE_YYYYMMDD2:
            case NumberFormat::FORMAT_DATE_DDMMYYYY:
            case NumberFormat::FORMAT_DATE_DMYSLASH:
            case NumberFormat::FORMAT_DATE_DMYMINUS:
            case NumberFormat::FORMAT_DATE_DMMINUS:
            case NumberFormat::FORMAT_DATE_MYMINUS:
            case NumberFormat::FORMAT_DATE_DATETIME:
            case NumberFormat::FORMAT_DATE_TIME1:
            case NumberFormat::FORMAT_DATE_TIME2:
            case NumberFormat::FORMAT_DATE_TIME3:
            case NumberFormat::FORMAT_DATE_TIME4:
            case NumberFormat::FORMAT_DATE_TIME5:
            case NumberFormat::FORMAT_DATE_TIME6:
            case NumberFormat::FORMAT_DATE_TIME7:
            case NumberFormat::FORMAT_DATE_TIME8:
            case NumberFormat::FORMAT_DATE_YYYYMMDDSLASH:
            case NumberFormat::FORMAT_DATE_XLSX14:
            case NumberFormat::FORMAT_DATE_XLSX15:
            case NumberFormat::FORMAT_DATE_XLSX16:
            case NumberFormat::FORMAT_DATE_XLSX17:
            case NumberFormat::FORMAT_DATE_XLSX22:
                return true;
        }

        //    Typically number, currency or accounting (or occasionally fraction) formats
        if ((substr($pFormatCode, 0, 1) == '_') || (substr($pFormatCode, 0, 2) == '0 ')) {
            return false;
        }
        // Try checking for any of the date formatting characters that don't appear within square braces
        if (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/i', $pFormatCode)) {
            //    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 (strpos($pFormatCode, '"') !== false) {
                $segMatcher = false;
                foreach (explode('"', $pFormatCode) as $subVal) {
                    //    Only test in alternate array entries (the non-quoted blocks)
                    if (($segMatcher = !$segMatcher) &&
                        (preg_match('/(^|\])[^\[]*[' . self::$possibleDateFormatCharacters . ']/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($dateValue)
    {
        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 = DateTime::DATEVALUE($dateValue);

        if ($dateValueNew === Functions::VALUE()) {
            return false;
        }

        if (strpos($dateValue, ':') !== false) {

            $timeValue = DateTime::TIMEVALUE($dateValue);
            if ($timeValue === Functions::VALUE()) {
                return false;
            }
            $dateValueNew += $timeValue;
        }

        return $dateValueNew;
    }

    /**
     * Converts a month name (either a long or a short name) to a month number.
     *
     * @param string $month 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($month)
    {
        $monthIndex = 1;
        foreach (self::$monthNames as $shortMonthName => $longMonthName) {
            if (($month === $longMonthName) || ($month === $shortMonthName)) {

                return $monthIndex;
            }
            ++$monthIndex;
        }

        return $month;
    }

    /**
     * Strips an ordinal froma 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($day)
    {
        $strippedDayValue = (str_replace(self::$numberSuffixes, '', $day));
        if (is_numeric($strippedDayValue)) {
            return (int) $strippedDayValue;
        }

        return $day;
    }
}


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

PHPOffice / PhpSpreadsheet

Push — develop ( 4fd8e7...d3e769 )

Date::dateTimeToExcel() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like