3 namespace PhpOffice\PhpSpreadsheet\Shared
;
7 use PhpOffice\PhpSpreadsheet\Calculation\DateTime
;
8 use PhpOffice\PhpSpreadsheet\Calculation\Functions
;
9 use PhpOffice\PhpSpreadsheet\Cell\Cell
;
10 use PhpOffice\PhpSpreadsheet\Style\NumberFormat
;
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
19 * Names of the months of the year, indexed by shortname
20 * Planned usage for locale settings.
24 public static $monthNames = [
42 public static $numberSuffixes = [
50 * Base calendar year to use for calculations
51 * Value is either CALENDAR_WINDOWS_1900 (1900) or CALENDAR_MAC_1904 (1904).
55 protected static $excelCalendar = self
::CALENDAR_WINDOWS_1900
;
58 * Default timezone to use for DateTime objects.
60 * @var null|\DateTimeZone
62 protected static $defaultTimeZone;
65 * Set the Excel calendar (Windows 1900 or Mac 1904).
67 * @param int $baseDate Excel base date (1900 or 1904)
69 * @return bool Success or failure
71 public static function setExcelCalendar($baseDate)
73 if (($baseDate == self
::CALENDAR_WINDOWS_1900
) ||
74 ($baseDate == self
::CALENDAR_MAC_1904
)) {
75 self
::$excelCalendar = $baseDate;
84 * Return the Excel calendar (Windows 1900 or Mac 1904).
86 * @return int Excel base date (1900 or 1904)
88 public static function getExcelCalendar()
90 return self
::$excelCalendar;
94 * Set the Default timezone to use for dates.
96 * @param DateTimeZone|string $timeZone The timezone to set for all Excel datetimestamp to PHP DateTime Object conversions
100 * @return bool Success or failure
101 * @return bool Success or failure
103 public static function setDefaultTimezone($timeZone)
105 if ($timeZone = self
::validateTimeZone($timeZone)) {
106 self
::$defaultTimeZone = $timeZone;
115 * Return the Default timezone being used for dates.
117 * @return DateTimeZone The timezone being used as default for Excel timestamp to PHP DateTime object
119 public static function getDefaultTimezone()
121 if (self
::$defaultTimeZone === null) {
122 self
::$defaultTimeZone = new DateTimeZone('UTC');
125 return self
::$defaultTimeZone;
129 * Validate a timezone.
131 * @param DateTimeZone|string $timeZone The timezone to validate, either as a timezone string or object
135 * @return DateTimeZone The timezone as a timezone object
136 * @return DateTimeZone The timezone as a timezone object
138 protected static function validateTimeZone($timeZone)
140 if (is_object($timeZone) && $timeZone instanceof DateTimeZone
) {
142 } elseif (is_string($timeZone)) {
143 return new DateTimeZone($timeZone);
146 throw new \
Exception('Invalid timezone');
150 * Convert a MS serialized datetime value from Excel to a PHP Date/Time object.
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
159 * @return \DateTime PHP date/time object
161 public static function excelToDateTimeObject($excelTimestamp, $timeZone = null)
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);
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);
174 $baseDate = new \
DateTime('1904-01-01', $timeZone);
178 $baseDate = new \
DateTime('1899-12-30', $timeZone);
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);
192 $interval = $days . ' days';
194 return $baseDate->modify($interval)
195 ->setTime($hours, $minutes, $seconds);
199 * Convert a MS serialized datetime value from Excel to a unix timestamp.
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
208 * @return int Unix timetamp for this date/time
210 public static function excelToTimestamp($excelTimestamp, $timeZone = null)
212 return (int) self
::excelToDateTimeObject($excelTimestamp, $timeZone)
217 * Convert a date from PHP to an MS Excel serialized date/time value.
219 * @param mixed $dateValue Unix Timestamp or PHP DateTime object or a string
221 * @return bool|float Excel date/time value
222 * or boolean FALSE on failure
224 public static function PHPToExcel($dateValue)
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);
238 * Convert a PHP DateTime object to an MS Excel serialized date/time value.
240 * @param DateTimeInterface $dateValue PHP DateTime object
242 * @return float MS Excel serialized date/time value
244 public static function dateTimeToExcel(DateTimeInterface
$dateValue)
246 return self
::formattedPHPToExcel(
247 $dateValue->format('Y'),
248 $dateValue->format('m'),
249 $dateValue->format('d'),
250 $dateValue->format('H'),
251 $dateValue->format('i'),
252 $dateValue->format('s')
257 * Convert a Unix timestamp to an MS Excel serialized date/time value.
259 * @param int $dateValue Unix Timestamp
261 * @return float MS Excel serialized date/time value
263 public static function timestampToExcel($dateValue)
265 if (!is_numeric($dateValue)) {
269 return self
::dateTimeToExcel(new \
DateTime('@' . $dateValue));
273 * formattedPHPToExcel.
279 * @param int $minutes
280 * @param int $seconds
282 * @return float Excel date/time value
284 public static function formattedPHPToExcel($year, $month, $day, $hours = 0, $minutes = 0, $seconds = 0)
286 if (self
::$excelCalendar == self
::CALENDAR_WINDOWS_1900
) {
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
291 $excel1900isLeapYear = true;
292 if (($year == 1900) && ($month <= 2)) {
293 $excel1900isLeapYear = false;
295 $myexcelBaseDate = 2415020;
297 $myexcelBaseDate = 2416481;
298 $excel1900isLeapYear = false;
301 // Julian base date Adjustment
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;
314 $excelTime = (($hours * 3600) +
($minutes * 60) +
$seconds) / 86400;
316 return (float) $excelDate +
$excelTime;
320 * Is a given cell a date/time?
326 public static function isDateTime(Cell
$pCell)
328 return self
::isDateTimeFormat(
329 $pCell->getWorksheet()->getStyle(
330 $pCell->getCoordinate()
336 * Is a given number format a date/time?
338 * @param NumberFormat $pFormat
342 public static function isDateTimeFormat(NumberFormat
$pFormat)
344 return self
::isDateTimeFormatCode($pFormat->getFormatCode());
347 private static $possibleDateFormatCharacters = 'eymdHs';
350 * Is a given number format code a date/time?
352 * @param string $pFormatCode
356 public static function isDateTimeFormatCode($pFormatCode)
358 if (strtolower($pFormatCode) === strtolower(NumberFormat
::FORMAT_GENERAL
)) {
359 // "General" contains an epoch letter 'e', so we trap for it explicitly here (case-insensitive check)
362 if (preg_match('/[0#]E[+-]0/i', $pFormatCode)) {
367 // Switch on formatcode
368 switch ($pFormatCode) {
369 // Explicitly defined date formats
370 case NumberFormat
::FORMAT_DATE_YYYYMMDD
:
371 case NumberFormat
::FORMAT_DATE_YYYYMMDD2
:
372 case NumberFormat
::FORMAT_DATE_DDMMYYYY
:
373 case NumberFormat
::FORMAT_DATE_DMYSLASH
:
374 case NumberFormat
::FORMAT_DATE_DMYMINUS
:
375 case NumberFormat
::FORMAT_DATE_DMMINUS
:
376 case NumberFormat
::FORMAT_DATE_MYMINUS
:
377 case NumberFormat
::FORMAT_DATE_DATETIME
:
378 case NumberFormat
::FORMAT_DATE_TIME1
:
379 case NumberFormat
::FORMAT_DATE_TIME2
:
380 case NumberFormat
::FORMAT_DATE_TIME3
:
381 case NumberFormat
::FORMAT_DATE_TIME4
:
382 case NumberFormat
::FORMAT_DATE_TIME5
:
383 case NumberFormat
::FORMAT_DATE_TIME6
:
384 case NumberFormat
::FORMAT_DATE_TIME7
:
385 case NumberFormat
::FORMAT_DATE_TIME8
:
386 case NumberFormat
::FORMAT_DATE_YYYYMMDDSLASH
:
387 case NumberFormat
::FORMAT_DATE_XLSX14
:
388 case NumberFormat
::FORMAT_DATE_XLSX15
:
389 case NumberFormat
::FORMAT_DATE_XLSX16
:
390 case NumberFormat
::FORMAT_DATE_XLSX17
:
391 case NumberFormat
::FORMAT_DATE_XLSX22
:
395 // Typically number, currency or accounting (or occasionally fraction) formats
396 if ((substr($pFormatCode, 0, 1) == '_') ||
(substr($pFormatCode, 0, 2) == '0 ')) {
399 // Try checking for any of the date formatting characters that don't appear within square braces
400 if (preg_match('/(^|\])[^\[]*[' . self
::$possibleDateFormatCharacters . ']/i', $pFormatCode)) {
401 // We might also have a format mask containing quoted strings...
402 // we don't want to test for any of our characters within the quoted blocks
403 if (strpos($pFormatCode, '"') !== false) {
405 foreach (explode('"', $pFormatCode) as $subVal) {
406 // Only test in alternate array entries (the non-quoted blocks)
407 if (($segMatcher = !$segMatcher) &&
408 (preg_match('/(^|\])[^\[]*[' . self
::$possibleDateFormatCharacters . ']/i', $subVal))) {
424 * Convert a date/time string to Excel time.
426 * @param string $dateValue Examples: '2009-12-31', '2009-12-31 15:59', '2009-12-31 15:59:10'
428 * @return false|float Excel date/time serial value
430 public static function stringToExcel($dateValue)
432 if (strlen($dateValue) < 2) {
435 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)) {
439 $dateValueNew = DateTime
::DATEVALUE($dateValue);
441 if ($dateValueNew === Functions
::VALUE()) {
445 if (strpos($dateValue, ':') !== false) {
446 $timeValue = DateTime
::TIMEVALUE($dateValue);
447 if ($timeValue === Functions
::VALUE()) {
450 $dateValueNew +
= $timeValue;
453 return $dateValueNew;
457 * Converts a month name (either a long or a short name) to a month number.
459 * @param string $month Month name or abbreviation
461 * @return int|string Month number (1 - 12), or the original string argument if it isn't a valid month name
463 public static function monthStringToNumber($month)
466 foreach (self
::$monthNames as $shortMonthName => $longMonthName) {
467 if (($month === $longMonthName) ||
($month === $shortMonthName)) {
477 * Strips an ordinal from a numeric value.
479 * @param string $day Day number with an ordinal
481 * @return int|string The integer value with any ordinal stripped, or the original string argument if it isn't a valid numeric
483 public static function dayStringToNumber($day)
485 $strippedDayValue = (str_replace(self
::$numberSuffixes, '', $day));
486 if (is_numeric($strippedDayValue)) {
487 return (int) $strippedDayValue;