|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataValidation; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xlsx; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; |
|
9
|
|
|
use SimpleXMLElement; |
|
10
|
|
|
|
|
11
|
|
|
class DataValidations |
|
12
|
|
|
{ |
|
13
|
|
|
private Worksheet $worksheet; |
|
14
|
|
|
|
|
15
|
|
|
private SimpleXMLElement $worksheetXml; |
|
16
|
|
|
|
|
17
|
20 |
|
public function __construct(Worksheet $workSheet, SimpleXMLElement $worksheetXml) |
|
18
|
|
|
{ |
|
19
|
20 |
|
$this->worksheet = $workSheet; |
|
20
|
20 |
|
$this->worksheetXml = $worksheetXml; |
|
21
|
|
|
} |
|
22
|
|
|
|
|
23
|
20 |
|
public function load(): void |
|
24
|
|
|
{ |
|
25
|
20 |
|
foreach ($this->worksheetXml->dataValidations->dataValidation as $dataValidation) { |
|
26
|
|
|
// Uppercase coordinate |
|
27
|
20 |
|
$range = strtoupper((string) $dataValidation['sqref']); |
|
28
|
20 |
|
$rangeSet = explode(' ', $range); |
|
29
|
20 |
|
foreach ($rangeSet as $range) { |
|
30
|
20 |
|
if (preg_match('/^[A-Z]{1,3}\\d{1,7}/', $range, $matches) === 1) { |
|
31
|
|
|
// Ensure left/top row of range exists, thereby |
|
32
|
|
|
// adjusting high row/column. |
|
33
|
20 |
|
$this->worksheet->getCell($matches[0]); |
|
34
|
|
|
} |
|
35
|
|
|
} |
|
36
|
|
|
} |
|
37
|
20 |
|
foreach ($this->worksheetXml->dataValidations->dataValidation as $dataValidation) { |
|
38
|
|
|
// Uppercase coordinate |
|
39
|
20 |
|
$range = strtoupper((string) $dataValidation['sqref']); |
|
40
|
20 |
|
$docValidation = new DataValidation(); |
|
41
|
20 |
|
$docValidation->setType((string) $dataValidation['type']); |
|
42
|
20 |
|
$docValidation->setErrorStyle((string) $dataValidation['errorStyle']); |
|
43
|
20 |
|
$docValidation->setOperator((string) $dataValidation['operator']); |
|
44
|
20 |
|
$docValidation->setAllowBlank(filter_var($dataValidation['allowBlank'], FILTER_VALIDATE_BOOLEAN)); |
|
45
|
|
|
// showDropDown is inverted (works as hideDropDown if true) |
|
46
|
20 |
|
$docValidation->setShowDropDown(!filter_var($dataValidation['showDropDown'], FILTER_VALIDATE_BOOLEAN)); |
|
47
|
20 |
|
$docValidation->setShowInputMessage(filter_var($dataValidation['showInputMessage'], FILTER_VALIDATE_BOOLEAN)); |
|
48
|
20 |
|
$docValidation->setShowErrorMessage(filter_var($dataValidation['showErrorMessage'], FILTER_VALIDATE_BOOLEAN)); |
|
49
|
20 |
|
$docValidation->setErrorTitle((string) $dataValidation['errorTitle']); |
|
50
|
20 |
|
$docValidation->setError((string) $dataValidation['error']); |
|
51
|
20 |
|
$docValidation->setPromptTitle((string) $dataValidation['promptTitle']); |
|
52
|
20 |
|
$docValidation->setPrompt((string) $dataValidation['prompt']); |
|
53
|
20 |
|
$docValidation->setFormula1(Xlsx::replacePrefixes((string) $dataValidation->formula1)); |
|
54
|
20 |
|
$docValidation->setFormula2(Xlsx::replacePrefixes((string) $dataValidation->formula2)); |
|
55
|
20 |
|
$this->worksheet->setDataValidation($range, $docValidation); |
|
56
|
|
|
} |
|
57
|
|
|
} |
|
58
|
|
|
} |
|
59
|
|
|
|
PHPOffice /
PhpSpreadsheet
