|
1
|
|
|
<?php |
|
2
|
|
|
namespace App\Controller\Component; |
|
3
|
|
|
|
|
4
|
|
|
use Cake\Controller\Component; |
|
5
|
|
|
use Cake\Utility\Hash; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\IOFactory; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
|
8
|
|
|
|
|
9
|
|
|
/** |
|
10
|
|
|
* Export component. Utility to create csv, ods and xlsx spreadsheets. |
|
11
|
|
|
*/ |
|
12
|
|
|
class ExportComponent extends Component |
|
13
|
|
|
{ |
|
14
|
|
|
/** |
|
15
|
|
|
* Allowed formats for export |
|
16
|
|
|
* |
|
17
|
|
|
* @var array |
|
18
|
|
|
*/ |
|
19
|
|
|
const ALLOWED_FORMATS = ['csv', 'ods', 'xlsx']; |
|
20
|
|
|
|
|
21
|
|
|
/** |
|
22
|
|
|
* Spreadsheet columns int/letter mapping. |
|
23
|
|
|
* |
|
24
|
|
|
* @var array |
|
25
|
|
|
*/ |
|
26
|
|
|
public const LETTERS = [ |
|
27
|
|
|
1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', |
|
28
|
|
|
11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 'S', 20 => 'T', |
|
29
|
|
|
21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z', |
|
30
|
|
|
]; |
|
31
|
|
|
|
|
32
|
|
|
/** |
|
33
|
|
|
* Default properties for spreadsheet |
|
34
|
|
|
* |
|
35
|
|
|
* @var array |
|
36
|
|
|
*/ |
|
37
|
|
|
protected $defaultSpreadheetProperties = [ |
|
38
|
|
|
'creator' => 'BEdita Manager', |
|
39
|
|
|
'lastModifiedBy' => 'BEdita Manager', |
|
40
|
|
|
'title' => '', |
|
41
|
|
|
'subject' => '', |
|
42
|
|
|
'description' => '', |
|
43
|
|
|
'keywords' => '', |
|
44
|
|
|
'category' => '', |
|
45
|
|
|
]; |
|
46
|
|
|
|
|
47
|
|
|
/** |
|
48
|
|
|
* Check if format is allowed |
|
49
|
|
|
* |
|
50
|
|
|
* @param string $format The format |
|
51
|
|
|
* @return bool |
|
52
|
|
|
*/ |
|
53
|
|
|
public function checkFormat(string $format): bool |
|
54
|
|
|
{ |
|
55
|
|
|
return in_array($format, static::ALLOWED_FORMATS); |
|
56
|
|
|
} |
|
57
|
|
|
|
|
58
|
|
|
/** |
|
59
|
|
|
* Create spreadsheet with data from rows, using properties. |
|
60
|
|
|
* |
|
61
|
|
|
* @param string $format The format |
|
62
|
|
|
* @param array $rows The data |
|
63
|
|
|
* @param string $filename The file name |
|
64
|
|
|
* @param array $properties The properties |
|
65
|
|
|
* @return array |
|
66
|
|
|
*/ |
|
67
|
|
|
public function format(string $format, array $rows, string $filename, array $properties = []): array |
|
68
|
|
|
{ |
|
69
|
|
|
$spreadsheet = new Spreadsheet(); |
|
70
|
|
|
|
|
71
|
|
|
// set properties |
|
72
|
|
|
$properties = array_merge($this->defaultSpreadheetProperties, $properties); |
|
73
|
|
|
foreach ($properties as $property => $value) { |
|
74
|
|
|
$spreadsheet->getProperties()->{sprintf('set%s', ucfirst($property))}($value); |
|
75
|
|
|
} |
|
76
|
|
|
|
|
77
|
|
|
// create the worksheet |
|
78
|
|
|
$spreadsheet->setActiveSheetIndex(0); |
|
79
|
|
|
|
|
80
|
|
|
// set first row, column names |
|
81
|
|
|
if (!empty($rows)) { |
|
82
|
|
|
$first = array_values(array_shift($rows)); |
|
83
|
|
|
foreach ($first as $k => $v) { |
|
84
|
|
|
$spreadsheet->getActiveSheet()->setCellValue(sprintf('%s1', $this->column($k + 1)), $v); |
|
85
|
|
|
} |
|
86
|
|
|
} |
|
87
|
|
|
|
|
88
|
|
|
// fill data |
|
89
|
|
|
$spreadsheet->getActiveSheet()->fromArray($rows, null, 'A2'); |
|
90
|
|
|
|
|
91
|
|
|
// set autofilter |
|
92
|
|
|
$spreadsheet->getActiveSheet()->setAutoFilter($spreadsheet->getActiveSheet()->calculateWorksheetDimension()); |
|
93
|
|
|
|
|
94
|
|
|
return $this->{$format}($spreadsheet, $filename); |
|
95
|
|
|
} |
|
96
|
|
|
|
|
97
|
|
|
/** |
|
98
|
|
|
* Calculate excel column name from integer number of column. |
|
99
|
|
|
* |
|
100
|
|
|
* @param int $num The number |
|
101
|
|
|
* @return string |
|
102
|
|
|
*/ |
|
103
|
|
|
protected function column(int $num): string |
|
104
|
|
|
{ |
|
105
|
|
|
if (array_key_exists($num, self::LETTERS)) { |
|
106
|
|
|
return self::LETTERS[$num]; |
|
107
|
|
|
} |
|
108
|
|
|
$div = intdiv($num, 26); |
|
109
|
|
|
$mod = $num % 26; |
|
110
|
|
|
if ($mod === 0) { |
|
111
|
|
|
$firstLetter = self::LETTERS[$div - 1]; |
|
112
|
|
|
$secondLetter = 'Z'; |
|
113
|
|
|
} else { |
|
114
|
|
|
$firstLetter = self::LETTERS[$div]; |
|
115
|
|
|
$secondLetter = self::LETTERS[$mod]; |
|
116
|
|
|
} |
|
117
|
|
|
|
|
118
|
|
|
return $firstLetter . $secondLetter; |
|
119
|
|
|
} |
|
120
|
|
|
|
|
121
|
|
|
/** |
|
122
|
|
|
* Create spreadsheet file into memory and redirect output to download it as csv |
|
123
|
|
|
* |
|
124
|
|
|
* @param Spreadsheet $spreadsheet The spreadsheet |
|
125
|
|
|
* @param string $filename The file name |
|
126
|
|
|
* @return array |
|
127
|
|
|
*/ |
|
128
|
|
|
public function csv(Spreadsheet $spreadsheet, string $filename = 'export.csv'): array |
|
129
|
|
|
{ |
|
130
|
|
|
$options = compact('filename') + ['format' => 'text/csv']; |
|
131
|
|
|
|
|
132
|
|
|
return $this->download($spreadsheet, 'Csv', $options); |
|
133
|
|
|
} |
|
134
|
|
|
|
|
135
|
|
|
/** |
|
136
|
|
|
* Create spreadsheet file into memory and redirect output to download it as ods |
|
137
|
|
|
* |
|
138
|
|
|
* @param Spreadsheet $spreadsheet The spreadsheet |
|
139
|
|
|
* @param string $filename The file name |
|
140
|
|
|
* @return array |
|
141
|
|
|
*/ |
|
142
|
|
|
public function ods(Spreadsheet $spreadsheet, string $filename = 'export.ods'): array |
|
143
|
|
|
{ |
|
144
|
|
|
$options = compact('filename') + ['format' => 'application/vnd.oasis.opendocument.spreadsheet']; |
|
145
|
|
|
|
|
146
|
|
|
return $this->download($spreadsheet, 'Ods', $options); |
|
147
|
|
|
} |
|
148
|
|
|
|
|
149
|
|
|
/** |
|
150
|
|
|
* Create spreadsheet file into memory and redirect output to download it as xlsx |
|
151
|
|
|
* |
|
152
|
|
|
* @param Spreadsheet $spreadsheet The spreadsheet |
|
153
|
|
|
* @param string $filename The file name |
|
154
|
|
|
* @return array |
|
155
|
|
|
*/ |
|
156
|
|
|
public function xlsx(Spreadsheet $spreadsheet, string $filename = 'export.xlsx'): array |
|
157
|
|
|
{ |
|
158
|
|
|
$options = compact('filename') + ['format' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']; |
|
159
|
|
|
|
|
160
|
|
|
return $this->download($spreadsheet, 'Xlsx', $options); |
|
161
|
|
|
} |
|
162
|
|
|
|
|
163
|
|
|
/** |
|
164
|
|
|
* Create spreadsheet, write to a temporary file, set content and content type and return them. |
|
165
|
|
|
* |
|
166
|
|
|
* @param Spreadsheet $spreadsheet The spreadsheet |
|
167
|
|
|
* @param string $extension The extension, can be 'Csv', 'Ods', 'Pdf', 'Xls', 'Xlsx' |
|
168
|
|
|
* @param array $options The options |
|
169
|
|
|
* @return array |
|
170
|
|
|
*/ |
|
171
|
|
|
protected function download(Spreadsheet $spreadsheet, string $extension, array $options): array |
|
172
|
|
|
{ |
|
173
|
|
|
$tmpfilename = tempnam('/tmp', Hash::get($options, 'filename')); |
|
174
|
|
|
$writer = IOFactory::createWriter($spreadsheet, $extension); |
|
175
|
|
|
$writer->save($tmpfilename); |
|
176
|
|
|
$content = file_get_contents($tmpfilename); |
|
177
|
|
|
$contentType = Hash::get($options, 'format'); |
|
178
|
|
|
unlink($tmpfilename); |
|
179
|
|
|
|
|
180
|
|
|
return compact('content', 'contentType'); |
|
181
|
|
|
} |
|
182
|
|
|
} |
|
183
|
|
|
|
bedita /
manager
