1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace LeKoala\ExcelImportExport; |
4
|
|
|
|
5
|
|
|
use Exception; |
6
|
|
|
use Generator; |
7
|
|
|
use LeKoala\SpreadCompat\Common\Options; |
8
|
|
|
use LeKoala\SpreadCompat\SpreadCompat; |
|
|
|
|
9
|
|
|
use SilverStripe\Forms\Form; |
10
|
|
|
use SilverStripe\Core\ClassInfo; |
11
|
|
|
use SilverStripe\ORM\DataObject; |
12
|
|
|
use SilverStripe\Control\Controller; |
13
|
|
|
use SilverStripe\Core\Config\Config; |
14
|
|
|
use SilverStripe\Control\HTTPResponse; |
15
|
|
|
use PhpOffice\PhpSpreadsheet\IOFactory; |
16
|
|
|
use SilverStripe\Dev\BulkLoader_Result; |
17
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Csv; |
18
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date; |
19
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet; |
20
|
|
|
use SilverStripe\Core\Config\Configurable; |
21
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\IReader; |
22
|
|
|
use PhpOffice\PhpSpreadsheet\Writer\IWriter; |
23
|
|
|
use SilverStripe\Assets\FileNameFilter; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* Support class for the module |
27
|
|
|
* |
28
|
|
|
* @author Koala |
29
|
|
|
*/ |
30
|
|
|
class ExcelImportExport |
31
|
|
|
{ |
32
|
|
|
use Configurable; |
33
|
|
|
|
34
|
|
|
/** |
35
|
|
|
* Setting this to false improve performance but may lead to skipped cells |
36
|
|
|
* @var bool |
37
|
|
|
*/ |
38
|
|
|
public static $iterate_only_existing_cells = false; |
39
|
|
|
|
40
|
|
|
/** |
41
|
|
|
* Useful if importing only one sheet or if computation fails |
42
|
|
|
* @var bool |
43
|
|
|
*/ |
44
|
|
|
public static $use_old_calculated_value = false; |
45
|
|
|
|
46
|
|
|
/** |
47
|
|
|
* Some excel sheets need extra processing |
48
|
|
|
* @var boolean |
49
|
|
|
*/ |
50
|
|
|
public static $process_headers = false; |
51
|
|
|
|
52
|
|
|
/** |
53
|
|
|
* @var string |
54
|
|
|
*/ |
55
|
|
|
public static $default_tmp_reader = 'Xlsx'; |
56
|
|
|
|
57
|
|
|
/** |
58
|
|
|
* @var integer |
59
|
|
|
*/ |
60
|
|
|
public static $limit_exports = 1000; |
61
|
|
|
|
62
|
|
|
/** |
63
|
|
|
* Get all db fields for a given dataobject class |
64
|
|
|
* |
65
|
|
|
* @param string $class |
66
|
|
|
* @return array |
67
|
|
|
*/ |
68
|
|
|
public static function allFieldsForClass($class) |
69
|
|
|
{ |
70
|
|
|
$dataClasses = ClassInfo::dataClassesFor($class); |
71
|
|
|
$fields = []; |
72
|
|
|
$dataObjectSchema = DataObject::getSchema(); |
73
|
|
|
foreach ($dataClasses as $dataClass) { |
74
|
|
|
$dataFields = $dataObjectSchema->databaseFields($dataClass); |
75
|
|
|
$fields = array_merge($fields, array_keys($dataFields)); |
76
|
|
|
} |
77
|
|
|
return array_combine($fields, $fields); |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/** |
81
|
|
|
* Get fields that should be exported by default for a class |
82
|
|
|
* |
83
|
|
|
* @param string $class |
84
|
|
|
* @return array |
85
|
|
|
*/ |
86
|
|
|
public static function exportFieldsForClass($class) |
87
|
|
|
{ |
88
|
|
|
$singl = singleton($class); |
89
|
|
|
if ($singl->hasMethod('exportedFields')) { |
90
|
|
|
return $singl->exportedFields(); |
91
|
|
|
} |
92
|
|
|
$exportedFields = Config::inst()->get($class, 'exported_fields'); |
93
|
|
|
|
94
|
|
|
if (!$exportedFields) { |
95
|
|
|
$exportedFields = array_keys(self::allFieldsForClass($class)); |
96
|
|
|
} |
97
|
|
|
|
98
|
|
|
$unexportedFields = Config::inst()->get($class, 'unexported_fields'); |
99
|
|
|
|
100
|
|
|
if ($unexportedFields) { |
101
|
|
|
$exportedFields = array_diff($exportedFields, $unexportedFields); |
102
|
|
|
} |
103
|
|
|
|
104
|
|
|
$fields = []; |
105
|
|
|
foreach ($exportedFields as $key => $value) { |
106
|
|
|
if (is_int($key)) { |
107
|
|
|
$key = $value; |
108
|
|
|
} |
109
|
|
|
$fields[$key] = $value; |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
return $fields; |
113
|
|
|
} |
114
|
|
|
|
115
|
|
|
/** |
116
|
|
|
* Get fields that can be imported by default for a class |
117
|
|
|
* |
118
|
|
|
* @param string $class |
119
|
|
|
* @return array |
120
|
|
|
*/ |
121
|
|
|
public static function importFieldsForClass($class) |
122
|
|
|
{ |
123
|
|
|
$singl = singleton($class); |
124
|
|
|
if ($singl->hasMethod('importedFields')) { |
125
|
|
|
return $singl->importedFields(); |
126
|
|
|
} |
127
|
|
|
$importedFields = Config::inst()->get($class, 'imported_fields'); |
128
|
|
|
|
129
|
|
|
if (!$importedFields) { |
130
|
|
|
$importedFields = array_keys(self::allFieldsForClass($class)); |
131
|
|
|
} |
132
|
|
|
|
133
|
|
|
$unimportedFields = Config::inst()->get($class, 'unimported_Fields'); |
134
|
|
|
|
135
|
|
|
if ($unimportedFields) { |
136
|
|
|
$importedFields = array_diff($importedFields, $unimportedFields); |
137
|
|
|
} |
138
|
|
|
return array_combine($importedFields, $importedFields); |
139
|
|
|
} |
140
|
|
|
|
141
|
|
|
/** |
142
|
|
|
* Output a sample file for a class |
143
|
|
|
* |
144
|
|
|
* A custom file can be provided with a custom sampleExcelFile method |
145
|
|
|
* either as a file path or as a Excel instance |
146
|
|
|
* |
147
|
|
|
* @param string $class |
148
|
|
|
* @return void |
149
|
|
|
*/ |
150
|
|
|
public static function sampleFileForClass($class) |
151
|
|
|
{ |
152
|
|
|
$ext = self::getDefaultExtension(); |
153
|
|
|
$filter = new FileNameFilter(); |
154
|
|
|
$fileName = $filter->filter("sample-file-for-$class.$ext"); |
155
|
|
|
$spreadsheet = null; |
|
|
|
|
156
|
|
|
|
157
|
|
|
$sng = singleton($class); |
158
|
|
|
|
159
|
|
|
// Deprecated |
160
|
|
|
if ($sng->hasMethod('sampleExcelFile')) { |
161
|
|
|
$spreadsheet = $sng->sampleExcelFile(); |
162
|
|
|
// PHPSpreadsheet is required for this |
163
|
|
|
$writer = self::getDefaultWriter($spreadsheet); |
164
|
|
|
self::outputHeaders($fileName); |
165
|
|
|
$writer->save('php://output'); |
166
|
|
|
exit(); |
|
|
|
|
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
if ($sng->hasMethod('sampleImportData')) { |
170
|
|
|
$data = $sng->sampleImportData(); |
171
|
|
|
} else { |
172
|
|
|
// Simply output the default headers |
173
|
|
|
$data = [ExcelImportExport::importFieldsForClass($class)]; |
174
|
|
|
} |
175
|
|
|
|
176
|
|
|
if (!is_iterable($data)) { |
177
|
|
|
throw new Exception("`sampleImportData` must return an iterable"); |
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
$options = new Options(); |
181
|
|
|
$options->creator = "SilverStripe"; |
182
|
|
|
SpreadCompat::output($data, $fileName, $options); |
183
|
|
|
exit(); |
|
|
|
|
184
|
|
|
} |
185
|
|
|
|
186
|
|
|
/** |
187
|
|
|
* @param Controller $controller |
188
|
|
|
* @return bool |
189
|
|
|
*/ |
190
|
|
|
public static function checkImportForm($controller) |
191
|
|
|
{ |
192
|
|
|
if (!$controller->showImportForm) { |
193
|
|
|
return false; |
194
|
|
|
} |
195
|
|
|
$modelClass = $controller->getModelClass(); |
196
|
|
|
if (is_array($controller->showImportForm)) { |
197
|
|
|
/** @var array $valid */ |
198
|
|
|
$valid = $controller->showImportForm; |
199
|
|
|
if (!in_array($modelClass, $valid)) { |
200
|
|
|
return false; |
201
|
|
|
} |
202
|
|
|
} |
203
|
|
|
return true; |
204
|
|
|
} |
205
|
|
|
|
206
|
|
|
/** |
207
|
|
|
* @param string $handler |
208
|
|
|
* @param Form $form |
209
|
|
|
* @param Controller $controller |
210
|
|
|
* @return HTTPResponse |
211
|
|
|
*/ |
212
|
|
|
public static function useCustomHandler($handler, Form $form, Controller $controller) |
213
|
|
|
{ |
214
|
|
|
if (!$handler || !method_exists($handler, "load")) { |
215
|
|
|
$form->sessionMessage("Invalid handler: $handler", 'bad'); |
216
|
|
|
return $controller->redirectBack(); |
217
|
|
|
} |
218
|
|
|
$file = $_FILES['_CsvFile']['tmp_name']; |
219
|
|
|
$name = $_FILES['_CsvFile']['name']; |
220
|
|
|
$inst = new $handler(); |
221
|
|
|
|
222
|
|
|
if (!empty($_POST['OnlyUpdateRecords']) && method_exists($handler, 'setOnlyUpdate')) { |
223
|
|
|
$inst->setOnlyUpdate(true); |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
/** @var BulkLoader_Result|string $results */ |
227
|
|
|
try { |
228
|
|
|
$results = $inst->load($file, $name); |
229
|
|
|
} catch (Exception $e) { |
230
|
|
|
$form->sessionMessage($e->getMessage(), 'bad'); |
231
|
|
|
return $controller->redirectBack(); |
232
|
|
|
} |
233
|
|
|
|
234
|
|
|
$message = ''; |
235
|
|
|
if ($results instanceof BulkLoader_Result) { |
236
|
|
|
if ($results->CreatedCount()) { |
237
|
|
|
$message .= _t( |
238
|
|
|
'ModelAdmin.IMPORTEDRECORDS', |
239
|
|
|
"Imported {count} records.", |
240
|
|
|
['count' => $results->CreatedCount()] |
241
|
|
|
); |
242
|
|
|
} |
243
|
|
|
if ($results->UpdatedCount()) { |
244
|
|
|
$message .= _t( |
245
|
|
|
'ModelAdmin.UPDATEDRECORDS', |
246
|
|
|
"Updated {count} records.", |
247
|
|
|
['count' => $results->UpdatedCount()] |
248
|
|
|
); |
249
|
|
|
} |
250
|
|
|
if ($results->DeletedCount()) { |
251
|
|
|
$message .= _t( |
252
|
|
|
'ModelAdmin.DELETEDRECORDS', |
253
|
|
|
"Deleted {count} records.", |
254
|
|
|
['count' => $results->DeletedCount()] |
255
|
|
|
); |
256
|
|
|
} |
257
|
|
|
if (!$results->CreatedCount() && !$results->UpdatedCount()) { |
258
|
|
|
$message .= _t('ModelAdmin.NOIMPORT', "Nothing to import"); |
259
|
|
|
} |
260
|
|
|
} else { |
261
|
|
|
// Or we have a simple result |
262
|
|
|
$message = $results; |
263
|
|
|
} |
264
|
|
|
|
265
|
|
|
$form->sessionMessage($message, 'good'); |
266
|
|
|
return $controller->redirectBack(); |
267
|
|
|
} |
268
|
|
|
|
269
|
|
|
public static function getDefaultExtension() |
270
|
|
|
{ |
271
|
|
|
return self::config()->default_extension ?? 'xlsx'; |
272
|
|
|
} |
273
|
|
|
|
274
|
|
|
/** |
275
|
|
|
* Get default writer for PHPSpreadsheet if installed |
276
|
|
|
* |
277
|
|
|
* @param Spreadsheet $spreadsheet |
278
|
|
|
* @return IWriter |
279
|
|
|
*/ |
280
|
|
|
public static function getDefaultWriter(Spreadsheet $spreadsheet): IWriter |
281
|
|
|
{ |
282
|
|
|
$writer = ucfirst(self::getDefaultExtension()); |
283
|
|
|
return IOFactory::createWriter($spreadsheet, $writer); |
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
/** |
287
|
|
|
* Output excel headers |
288
|
|
|
* |
289
|
|
|
* @param string $fileName |
290
|
|
|
* @return void |
291
|
|
|
*/ |
292
|
|
|
public static function outputHeaders($fileName) |
293
|
|
|
{ |
294
|
|
|
$ext = pathinfo($fileName, PATHINFO_EXTENSION); |
295
|
|
|
switch ($ext) { |
296
|
|
|
case 'csv': |
297
|
|
|
header('Content-Type: text/csv'); |
298
|
|
|
break; |
299
|
|
|
case 'xlsx': |
300
|
|
|
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); |
301
|
|
|
break; |
302
|
|
|
default: |
303
|
|
|
header('Content-type: application/vnd.ms-excel'); |
304
|
|
|
break; |
305
|
|
|
} |
306
|
|
|
|
307
|
|
|
header('Content-Disposition: attachment; filename="' . $fileName . '"'); |
308
|
|
|
header('Cache-Control: max-age=0'); |
309
|
|
|
ob_clean(); |
310
|
|
|
} |
311
|
|
|
|
312
|
|
|
/** |
313
|
|
|
* Generate a default import file with all field name |
314
|
|
|
* @deprecated |
315
|
|
|
* @param string $class |
316
|
|
|
* @return string |
317
|
|
|
*/ |
318
|
|
|
public static function generateDefaultSampleFile($class) |
319
|
|
|
{ |
320
|
|
|
$opts = [ |
321
|
|
|
'creator' => "SilverStripe" |
322
|
|
|
]; |
323
|
|
|
$allFields = ExcelImportExport::importFieldsForClass($class); |
324
|
|
|
$tmpname = SpreadCompat::getTempFilename(); |
325
|
|
|
SpreadCompat::write([ |
326
|
|
|
$allFields |
327
|
|
|
], $tmpname, ...$opts); |
328
|
|
|
return $tmpname; |
329
|
|
|
} |
330
|
|
|
|
331
|
|
|
/** |
332
|
|
|
* Show valid extensions helper (for uploaders) |
333
|
|
|
* |
334
|
|
|
* @return string |
335
|
|
|
*/ |
336
|
|
|
public static function getValidExtensionsText() |
337
|
|
|
{ |
338
|
|
|
return _t( |
339
|
|
|
'ExcelImportExport.VALIDEXTENSIONS', |
340
|
|
|
"Allowed extensions: {extensions}", |
341
|
|
|
array('extensions' => implode(', ', self::getValidExtensions())) |
342
|
|
|
); |
343
|
|
|
} |
344
|
|
|
|
345
|
|
|
/** |
346
|
|
|
* Extracted from PHPSpreadsheet |
347
|
|
|
* |
348
|
|
|
* @param string $ext |
349
|
|
|
* @return string |
350
|
|
|
*/ |
351
|
|
|
public static function getReaderForExtension($ext) |
352
|
|
|
{ |
353
|
|
|
switch (strtolower($ext)) { |
354
|
|
|
case 'xlsx': // Excel (OfficeOpenXML) Spreadsheet |
355
|
|
|
case 'xlsm': // Excel (OfficeOpenXML) Macro Spreadsheet (macros will be discarded) |
356
|
|
|
case 'xltx': // Excel (OfficeOpenXML) Template |
357
|
|
|
case 'xltm': // Excel (OfficeOpenXML) Macro Template (macros will be discarded) |
358
|
|
|
return 'Xlsx'; |
359
|
|
|
case 'xls': // Excel (BIFF) Spreadsheet |
360
|
|
|
case 'xlt': // Excel (BIFF) Template |
361
|
|
|
return 'Xls'; |
362
|
|
|
case 'ods': // Open/Libre Offic Calc |
363
|
|
|
case 'ots': // Open/Libre Offic Calc Template |
364
|
|
|
return 'Ods'; |
365
|
|
|
case 'slk': |
366
|
|
|
return 'Slk'; |
367
|
|
|
case 'xml': // Excel 2003 SpreadSheetML |
368
|
|
|
return 'Xml'; |
369
|
|
|
case 'gnumeric': |
370
|
|
|
return 'Gnumeric'; |
371
|
|
|
case 'htm': |
372
|
|
|
case 'html': |
373
|
|
|
return 'Html'; |
374
|
|
|
case 'csv': |
375
|
|
|
return 'Csv'; |
376
|
|
|
case 'tmp': // Useful when importing uploaded files |
377
|
|
|
return self::$default_tmp_reader; |
378
|
|
|
default: |
379
|
|
|
throw new Exception("Unsupported file type : $ext"); |
380
|
|
|
} |
381
|
|
|
} |
382
|
|
|
|
383
|
|
|
/** |
384
|
|
|
* Get valid extensions |
385
|
|
|
* |
386
|
|
|
* @return array |
387
|
|
|
*/ |
388
|
|
|
public static function getValidExtensions() |
389
|
|
|
{ |
390
|
|
|
$v = self::config()->allowed_extensions; |
391
|
|
|
if (!$v || !is_array($v)) { |
392
|
|
|
return []; |
393
|
|
|
} |
394
|
|
|
return $v; |
395
|
|
|
} |
396
|
|
|
|
397
|
|
|
/** |
398
|
|
|
* Save content of an array to a file |
399
|
|
|
* |
400
|
|
|
* @param iterable $data |
401
|
|
|
* @param string $filepath |
402
|
|
|
* @return void |
403
|
|
|
*/ |
404
|
|
|
public static function arrayToFile($data, $filepath) |
405
|
|
|
{ |
406
|
|
|
SpreadCompat::write($data, $filepath); |
407
|
|
|
} |
408
|
|
|
|
409
|
|
|
/** |
410
|
|
|
* Fast saving to csv |
411
|
|
|
* |
412
|
|
|
* @param array $data |
413
|
|
|
* @param string $filepath |
414
|
|
|
* @param string $delimiter |
415
|
|
|
* @param string $enclosure |
416
|
|
|
* @param string $escapeChar |
417
|
|
|
*/ |
418
|
|
|
public static function arrayToCsv($data, $filepath, $delimiter = ',', $enclosure = '"', $escapeChar = '\\') |
419
|
|
|
{ |
420
|
|
|
if (is_file($filepath)) { |
421
|
|
|
unlink($filepath); |
422
|
|
|
} |
423
|
|
|
$options = new Options(); |
424
|
|
|
$options->separator = $delimiter; |
425
|
|
|
$options->enclosure = $enclosure; |
426
|
|
|
$options->escape = $escapeChar; |
427
|
|
|
SpreadCompat::write($data, $filepath, $options); |
428
|
|
|
} |
429
|
|
|
|
430
|
|
|
|
431
|
|
|
/** |
432
|
|
|
* @param IReader $reader |
433
|
|
|
* @return Csv |
434
|
|
|
*/ |
435
|
|
|
protected static function getCsvReader(IReader $reader) |
436
|
|
|
{ |
437
|
|
|
return $reader; |
438
|
|
|
} |
439
|
|
|
|
440
|
|
|
public static function excelColumnRange(string $lower = 'A', string $upper = 'ZZ'): Generator |
441
|
|
|
{ |
442
|
|
|
++$upper; |
443
|
|
|
for ($i = $lower; $i !== $upper; ++$i) { |
444
|
|
|
yield $i; |
445
|
|
|
} |
446
|
|
|
} |
447
|
|
|
|
448
|
|
|
/** |
449
|
|
|
* String from column index. |
450
|
|
|
* |
451
|
|
|
* @param int $index Column index (1 = A) |
452
|
|
|
* @param $fallback |
453
|
|
|
* @return string |
454
|
|
|
*/ |
455
|
|
|
public static function getLetter($index) |
456
|
|
|
{ |
457
|
|
|
foreach (self::excelColumnRange() as $letter) { |
458
|
|
|
$index--; |
459
|
|
|
if ($index <= 0) { |
460
|
|
|
return $letter; |
461
|
|
|
} |
462
|
|
|
} |
463
|
|
|
} |
464
|
|
|
|
465
|
|
|
|
466
|
|
|
/** |
467
|
|
|
* Convert a file to an array |
468
|
|
|
* |
469
|
|
|
* @param string $filepath |
470
|
|
|
* @param string $delimiter (csv only) |
471
|
|
|
* @param string $enclosure (csv only) |
472
|
|
|
* @param string $ext if extension cannot be deducted from filepath (eg temp files) |
473
|
|
|
* @return array |
474
|
|
|
*/ |
475
|
|
|
public static function fileToArray($filepath, $delimiter = ';', $enclosure = '', $ext = null) |
476
|
|
|
{ |
477
|
|
|
if ($ext === null) { |
478
|
|
|
$ext = pathinfo($filepath, PATHINFO_EXTENSION); |
479
|
|
|
} |
480
|
|
|
$readerType = self::getReaderForExtension($ext); |
|
|
|
|
481
|
|
|
$reader = IOFactory::createReader($readerType); |
482
|
|
|
if ($readerType == 'Csv') { |
483
|
|
|
// @link https://phpspreadsheet.readthedocs.io/en/latest/topics/reading-and-writing-to-file/#setting-csv-options_1 |
484
|
|
|
$reader = self::getCsvReader($reader); |
485
|
|
|
$reader->setDelimiter($delimiter); |
486
|
|
|
$reader->setEnclosure($enclosure); |
487
|
|
|
} else { |
488
|
|
|
// Does not apply to CSV |
489
|
|
|
$reader->setReadDataOnly(true); |
490
|
|
|
} |
491
|
|
|
$data = []; |
|
|
|
|
492
|
|
|
if ($reader->canRead($filepath)) { |
493
|
|
|
$excel = $reader->load($filepath); |
494
|
|
|
$data = $excel->getActiveSheet()->toArray(null, true, false, false); |
495
|
|
|
} else { |
496
|
|
|
throw new Exception("Cannot read $filepath"); |
497
|
|
|
} |
498
|
|
|
return $data; |
499
|
|
|
} |
500
|
|
|
|
501
|
|
|
/** |
502
|
|
|
* Convert an excel file to an array |
503
|
|
|
* |
504
|
|
|
* @param string $filepath |
505
|
|
|
* @param string $sheetname Load a specific worksheet by name |
506
|
|
|
* @param true $onlyExisting Avoid reading empty columns |
507
|
|
|
* @param string $ext if extension cannot be deducted from filepath (eg temp files) |
508
|
|
|
* @return array |
509
|
|
|
*/ |
510
|
|
|
public static function excelToArray($filepath, $sheetname = null, $onlyExisting = true, $ext = null) |
511
|
|
|
{ |
512
|
|
|
if ($ext === null) { |
513
|
|
|
$ext = pathinfo($filepath, PATHINFO_EXTENSION); |
|
|
|
|
514
|
|
|
} |
515
|
|
|
$ext = pathinfo($filepath, PATHINFO_EXTENSION); |
516
|
|
|
$readerType = self::getReaderForExtension($ext); |
|
|
|
|
517
|
|
|
$reader = IOFactory::createReader($readerType); |
518
|
|
|
$reader->setReadDataOnly(true); |
519
|
|
|
if ($sheetname) { |
520
|
|
|
$reader->setLoadSheetsOnly($sheetname); |
521
|
|
|
} |
522
|
|
|
$data = []; |
523
|
|
|
if ($reader->canRead($filepath)) { |
524
|
|
|
$excel = $reader->load($filepath); |
525
|
|
|
if ($onlyExisting) { |
|
|
|
|
526
|
|
|
$data = []; |
527
|
|
|
foreach ($excel->getActiveSheet()->getRowIterator() as $row) { |
528
|
|
|
$cellIterator = $row->getCellIterator(); |
529
|
|
|
if (self::$iterate_only_existing_cells) { |
530
|
|
|
$cellIterator->setIterateOnlyExistingCells(true); |
531
|
|
|
} |
532
|
|
|
$cells = []; |
533
|
|
|
foreach ($cellIterator as $cell) { |
534
|
|
|
if (self::$use_old_calculated_value) { |
535
|
|
|
$cells[] = $cell->getOldCalculatedValue(); |
|
|
|
|
536
|
|
|
} else { |
537
|
|
|
$cells[] = $cell->getFormattedValue(); |
538
|
|
|
} |
539
|
|
|
} |
540
|
|
|
$data[] = $cells; |
541
|
|
|
} |
542
|
|
|
} else { |
543
|
|
|
$data = $excel->getActiveSheet()->toArray(null, true, false, false); |
544
|
|
|
} |
545
|
|
|
} else { |
546
|
|
|
throw new Exception("Cannot read $filepath"); |
547
|
|
|
} |
548
|
|
|
return $data; |
549
|
|
|
} |
550
|
|
|
|
551
|
|
|
/** |
552
|
|
|
* @link https://stackoverflow.com/questions/44304795/how-to-retrieve-date-from-table-cell-using-phpspreadsheet#44304796 |
553
|
|
|
* @param int $v |
554
|
|
|
* @return string |
555
|
|
|
*/ |
556
|
|
|
public static function convertExcelDate($v) |
557
|
|
|
{ |
558
|
|
|
if (!is_numeric($v)) { |
|
|
|
|
559
|
|
|
return ''; |
560
|
|
|
} |
561
|
|
|
return date('Y-m-d', Date::excelToTimestamp($v)); |
562
|
|
|
} |
563
|
|
|
|
564
|
|
|
/** |
565
|
|
|
* Convert an excel file to an associative array |
566
|
|
|
* |
567
|
|
|
* Suppose the first line are the headers of the file |
568
|
|
|
* Headers are trimmed in case you have crappy whitespace in your files |
569
|
|
|
* |
570
|
|
|
* @param string $filepath |
571
|
|
|
* @param string $sheetname Load a specific worksheet by name |
572
|
|
|
* @param string $ext if extension cannot be deducted from filepath (eg temp files) |
573
|
|
|
* @return array |
574
|
|
|
*/ |
575
|
|
|
public static function excelToAssocArray($filepath, $sheetname = null, $ext = null) |
576
|
|
|
{ |
577
|
|
|
if ($ext === null) { |
578
|
|
|
$ext = pathinfo($filepath, PATHINFO_EXTENSION); |
579
|
|
|
} |
580
|
|
|
$readerType = self::getReaderForExtension($ext); |
|
|
|
|
581
|
|
|
$reader = IOFactory::createReader($readerType); |
582
|
|
|
$reader->setReadDataOnly(true); |
583
|
|
|
if ($sheetname) { |
584
|
|
|
$reader->setLoadSheetsOnly($sheetname); |
585
|
|
|
} |
586
|
|
|
$data = []; |
|
|
|
|
587
|
|
|
if ($reader->canRead($filepath)) { |
588
|
|
|
$excel = $reader->load($filepath); |
589
|
|
|
$data = []; |
590
|
|
|
$headers = []; |
591
|
|
|
$headersCount = 0; |
592
|
|
|
foreach ($excel->getActiveSheet()->getRowIterator() as $row) { |
593
|
|
|
$cellIterator = $row->getCellIterator(); |
594
|
|
|
if (self::$iterate_only_existing_cells) { |
595
|
|
|
$cellIterator->setIterateOnlyExistingCells(true); |
596
|
|
|
} |
597
|
|
|
$cells = []; |
598
|
|
|
foreach ($cellIterator as $cell) { |
599
|
|
|
if (self::$use_old_calculated_value) { |
600
|
|
|
$cells[] = $cell->getOldCalculatedValue(); |
601
|
|
|
} else { |
602
|
|
|
$cells[] = $cell->getFormattedValue(); |
603
|
|
|
} |
604
|
|
|
} |
605
|
|
|
if (empty($headers)) { |
606
|
|
|
$headers = $cells; |
607
|
|
|
// Some crappy excel file may need this |
608
|
|
|
if (self::$process_headers) { |
609
|
|
|
$headers = array_map(function ($v) { |
610
|
|
|
// Numeric headers are most of the time dates |
611
|
|
|
if (is_numeric($v)) { |
612
|
|
|
$v = self::convertExcelDate($v); |
613
|
|
|
} |
614
|
|
|
// trim does not always work great and headers can contain utf8 stuff |
615
|
|
|
return is_string($v) ? preg_replace('/(^\s+)|(\s+$)/us', '', $v) : $v; |
616
|
|
|
}, $headers); |
617
|
|
|
} |
618
|
|
|
$headersCount = count($headers); |
619
|
|
|
} else { |
620
|
|
|
$diff = count($cells) - $headersCount; |
621
|
|
|
if ($diff != 0) { |
622
|
|
|
if ($diff > 0) { |
623
|
|
|
// we have too many cells |
624
|
|
|
$cells = array_slice($cells, 0, $headersCount); |
625
|
|
|
} else { |
626
|
|
|
// we are missing some cells |
627
|
|
|
for ($i = 0; $i < abs($diff); $i++) { |
628
|
|
|
$cells[] = null; |
629
|
|
|
} |
630
|
|
|
} |
631
|
|
|
} |
632
|
|
|
$data[] = array_combine($headers, $cells); |
633
|
|
|
} |
634
|
|
|
} |
635
|
|
|
} else { |
636
|
|
|
throw new Exception("Cannot read $filepath"); |
637
|
|
|
} |
638
|
|
|
return $data; |
639
|
|
|
} |
640
|
|
|
} |
641
|
|
|
|
The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g.
excluded_paths: ["lib/*"]
, you can move it to the dependency path list as follows:For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths