fluentform / app / Services / Spout / Reader / XLSX / Helper / DateFormatHelper.php

DateFormatHelper.php in Fluent Forms – Customizable Contact Forms, Survey, Quiz, & Conversational Form Builder 3.6.51, at app/Services/Spout/Reader/XLSX/Helper/DateFormatHelper.php

125 lines 5.8 KB

Raw Download Zip

1	<?php
2
3	namespace Box\Spout\Reader\XLSX\Helper;
4
5	/**
6	* Class DateFormatHelper
7	* This class provides helper functions to format Excel dates
8	*
9	* @package Box\Spout\Reader\XLSX\Helper
10	*/
11	class DateFormatHelper
12	{
13	const KEY_GENERAL = 'general';
14	const KEY_HOUR_12 = '12h';
15	const KEY_HOUR_24 = '24h';
16
17	/**
18	* This map is used to replace Excel format characters by their PHP equivalent.
19	* Keys should be ordered from longest to smallest.
20	*
21	* @var array Mapping between Excel format characters and PHP format characters
22	*/
23	private static $excelDateFormatToPHPDateFormatMapping = [
24	self::KEY_GENERAL => [
25	// Time
26	'am/pm' => 'A', // Uppercase Ante meridiem and Post meridiem
27	':mm' => ':i', // Minutes with leading zeros - if preceded by a ":" (otherwise month)
28	'mm:' => 'i:', // Minutes with leading zeros - if followed by a ":" (otherwise month)
29	'ss' => 's', // Seconds, with leading zeros
30	'.s' => '', // Ignore (fractional seconds format does not exist in PHP)
31
32	// Date
33	'e' => 'Y', // Full numeric representation of a year, 4 digits
34	'yyyy' => 'Y', // Full numeric representation of a year, 4 digits
35	'yy' => 'y', // Two digit representation of a year
36	'mmmmm' => 'M', // Short textual representation of a month, three letters ("mmmmm" should only contain the 1st letter...)
37	'mmmm' => 'F', // Full textual representation of a month
38	'mmm' => 'M', // Short textual representation of a month, three letters
39	'mm' => 'm', // Numeric representation of a month, with leading zeros
40	'm' => 'n', // Numeric representation of a month, without leading zeros
41	'dddd' => 'l', // Full textual representation of the day of the week
42	'ddd' => 'D', // Textual representation of a day, three letters
43	'dd' => 'd', // Day of the month, 2 digits with leading zeros
44	'd' => 'j', // Day of the month without leading zeros
45	],
46	self::KEY_HOUR_12 => [
47	'hh' => 'h', // 12-hour format of an hour without leading zeros
48	'h' => 'g', // 12-hour format of an hour without leading zeros
49	],
50	self::KEY_HOUR_24 => [
51	'hh' => 'H', // 24-hour hours with leading zero
52	'h' => 'G', // 24-hour format of an hour without leading zeros
53	],
54	];
55
56	/**
57	* Converts the given Excel date format to a format understandable by the PHP date function.
58	*
59	* @param string $excelDateFormat Excel date format
60	* @return string PHP date format (as defined here: http://php.net/manual/en/function.date.php)
61	*/
62	public static function toPHPDateFormat($excelDateFormat)
63	{
64	// Remove brackets potentially present at the beginning of the format string
65	// and text portion of the format at the end of it (starting with ";")
66	// See §18.8.31 of ECMA-376 for more detail.
67	$dateFormat = preg_replace('/^(?:\[\$[^\]]+?\])?([^;])./', '$1', $excelDateFormat);
68
69	// Double quotes are used to escape characters that must not be interpreted.
70	// For instance, ["Day " dd] should result in "Day 13" and we should not try to interpret "D", "a", "y"
71	// By exploding the format string using double quote as a delimiter, we can get all parts
72	// that must be transformed (even indexes) and all parts that must not be (odd indexes).
73	$dateFormatParts = explode('"', $dateFormat);
74
75	foreach ($dateFormatParts as $partIndex => $dateFormatPart) {
76	// do not look at odd indexes
77	if ($partIndex % 2 === 1) {
78	continue;
79	}
80
81	// Make sure all characters are lowercase, as the mapping table is using lowercase characters
82	$transformedPart = strtolower($dateFormatPart);
83
84	// Remove escapes related to non-format characters
85	$transformedPart = str_replace('\\', '', $transformedPart);
86
87	// Apply general transformation first...
88	$transformedPart = strtr($transformedPart, self::$excelDateFormatToPHPDateFormatMapping[self::KEY_GENERAL]);
89
90	// ... then apply hour transformation, for 12-hour or 24-hour format
91	if (self::has12HourFormatMarker($dateFormatPart)) {
92	$transformedPart = strtr($transformedPart, self::$excelDateFormatToPHPDateFormatMapping[self::KEY_HOUR_12]);
93	} else {
94	$transformedPart = strtr($transformedPart, self::$excelDateFormatToPHPDateFormatMapping[self::KEY_HOUR_24]);
95	}
96
97	// overwrite the parts array with the new transformed part
98	$dateFormatParts[$partIndex] = $transformedPart;
99	}
100
101	// Merge all transformed parts back together
102	$phpDateFormat = implode('"', $dateFormatParts);
103
104	// Finally, to have the date format compatible with the DateTime::format() function, we need to escape
105	// all characters that are inside double quotes (and double quotes must be removed).
106	// For instance, ["Day " dd] should become [\D\a\y\ dd]
107	$phpDateFormat = preg_replace_callback('/"(.+?)"/', function($matches) {
108	$stringToEscape = $matches[1];
109	$letters = preg_split('//u', $stringToEscape, -1, PREG_SPLIT_NO_EMPTY);
110	return '\\' . implode('\\', $letters);
111	}, $phpDateFormat);
112
113	return $phpDateFormat;
114	}
115
116	/**
117	* @param string $excelDateFormat Date format as defined by Excel
118	* @return bool Whether the given date format has the 12-hour format marker
119	*/
120	private static function has12HourFormatMarker($excelDateFormat)
121	{
122	return (stripos($excelDateFormat, 'am/pm') !== false);
123	}
124	}
125