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