|
1
|
|
|
<?php |
|
2
|
|
|
namespace Ellumilel\Helpers; |
|
3
|
|
|
|
|
4
|
|
|
/** |
|
5
|
|
|
* Class PHPExcelHelper |
|
6
|
|
|
* @package Ellumilel\Helpers |
|
7
|
|
|
*/ |
|
8
|
|
|
class ExcelHelper |
|
9
|
|
|
{ |
|
10
|
|
|
/** |
|
11
|
|
|
* @link http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx |
|
12
|
|
|
* XFE1048577 |
|
13
|
|
|
*/ |
|
14
|
|
|
const EXCEL_MAX_ROW = 1048576; |
|
15
|
|
|
const EXCEL_MAX_RANGE = 2147483647; |
|
16
|
|
|
const EXCEL_MAX_COL = 16384; |
|
17
|
|
|
|
|
18
|
|
|
/** |
|
19
|
|
|
* @param string $dateInput |
|
20
|
|
|
* |
|
21
|
|
|
* @return mixed |
|
22
|
|
|
*/ |
|
23
|
|
|
public static function convertDateTime($dateInput) |
|
24
|
|
|
{ |
|
25
|
|
|
$epoch = 1900; |
|
26
|
|
|
$norm = 300; |
|
27
|
|
|
$year = $month = $day = $offset = $seconds = 0; |
|
28
|
|
|
$dateTime = $dateInput; |
|
29
|
|
|
if (preg_match("/(\d{4})\-(\d{2})\-(\d{2})/", $dateTime, $matches)) { |
|
30
|
|
|
$year = $matches[1]; |
|
31
|
|
|
$month = $matches[2]; |
|
32
|
|
|
$day = $matches[3]; |
|
33
|
|
|
} |
|
34
|
|
|
if (preg_match("/(\d{2}):(\d{2}):(\d{2})/", $dateTime, $matches)) { |
|
35
|
|
|
$seconds = ($matches[1] * 60 * 60 + $matches[2] * 60 + $matches[3]) / 86400; |
|
36
|
|
|
} |
|
37
|
|
|
if ("$year-$month-$day" == '1899-12-31' || "$year-$month-$day" == '1900-01-00') { |
|
38
|
|
|
return $seconds; |
|
39
|
|
|
} |
|
40
|
|
|
if ("$year-$month-$day" == '1900-02-29') { |
|
41
|
|
|
return 60 + $seconds; |
|
42
|
|
|
} |
|
43
|
|
|
$range = $year - $epoch; |
|
44
|
|
|
// check leapDay |
|
45
|
|
|
$leap = (new \DateTime($dateInput))->format('L'); |
|
46
|
|
|
$mDays = [31, ($leap ? 29 : 28), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]; |
|
47
|
|
|
if (($year < $epoch || $year > 9999) || ($month < 1 || $month > 12) || $day < 1 || $day > $mDays[$month - 1]) { |
|
48
|
|
|
return 0; |
|
49
|
|
|
} |
|
50
|
|
|
$days = $day + ($range * 365) + array_sum(array_slice($mDays, 0, $month - 1)); |
|
51
|
|
|
$days += intval(($range) / 4) - intval(($range + $offset) / 100); |
|
52
|
|
|
$days += intval(($range + $offset + $norm) / 400) - intval($leap); |
|
53
|
|
|
if ($days > 59) { |
|
54
|
|
|
$days++; |
|
55
|
|
|
} |
|
56
|
|
|
|
|
57
|
|
|
return $days + $seconds; |
|
58
|
|
|
} |
|
59
|
|
|
|
|
60
|
|
|
/** |
|
61
|
|
|
* @param $val |
|
62
|
|
|
* |
|
63
|
|
|
* @return mixed |
|
64
|
|
|
*/ |
|
65
|
|
|
public static function xmlspecialchars($val) |
|
66
|
|
|
{ |
|
67
|
|
|
return str_replace("'", "'", htmlspecialchars($val)); |
|
68
|
|
|
} |
|
69
|
|
|
|
|
70
|
|
|
/** |
|
71
|
|
|
* @param int $rowNumber |
|
72
|
|
|
* @param int $columnNumber |
|
73
|
|
|
* |
|
74
|
|
|
* @return string Cell label/coordinates (A1, C3, AA42) |
|
75
|
|
|
*/ |
|
76
|
|
|
public static function xlsCell($rowNumber, $columnNumber) |
|
77
|
|
|
{ |
|
78
|
|
|
$n = $columnNumber; |
|
79
|
|
|
for ($r = ""; $n >= 0; $n = intval($n / 26) - 1) { |
|
80
|
|
|
$r = chr($n % 26 + 0x41).$r; |
|
81
|
|
|
} |
|
82
|
|
|
|
|
83
|
|
|
return $r.($rowNumber + 1); |
|
84
|
|
|
} |
|
85
|
|
|
|
|
86
|
|
|
/** |
|
87
|
|
|
* @link https://msdn.microsoft.com/ru-RU/library/aa365247%28VS.85%29.aspx |
|
88
|
|
|
* |
|
89
|
|
|
* @param string $filename |
|
90
|
|
|
* |
|
91
|
|
|
* @return mixed |
|
92
|
|
|
*/ |
|
93
|
|
View Code Duplication |
public static function checkFilename($filename) |
|
|
|
|
|
|
94
|
|
|
{ |
|
95
|
|
|
$invalidCharacter = array_merge( |
|
96
|
|
|
array_map('chr', range(0, 31)), |
|
97
|
|
|
['<', '>', '?', '"', ':', '|', '\\', '/', '*', '&'] |
|
98
|
|
|
); |
|
99
|
|
|
|
|
100
|
|
|
return str_replace($invalidCharacter, '', $filename); |
|
101
|
|
|
} |
|
102
|
|
|
} |
|
103
|
|
|
|
ellumilel /
php-excel-writer
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.