DataValidations.php
2.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
<?php
namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use SimpleXMLElement;
class DataValidations
{
private $worksheet;
private $worksheetXml;
public function __construct(Worksheet $workSheet, SimpleXMLElement $worksheetXml)
{
$this->worksheet = $workSheet;
$this->worksheetXml = $worksheetXml;
}
public function load(): void
{
foreach ($this->worksheetXml->dataValidations->dataValidation as $dataValidation) {
// Uppercase coordinate
$range = strtoupper($dataValidation['sqref']);
$rangeSet = explode(' ', $range);
foreach ($rangeSet as $range) {
$stRange = $this->worksheet->shrinkRangeToFit($range);
// Extract all cell references in $range
foreach (Coordinate::extractAllCellReferencesInRange($stRange) as $reference) {
// Create validation
$docValidation = $this->worksheet->getCell($reference)->getDataValidation();
$docValidation->setType((string) $dataValidation['type']);
$docValidation->setErrorStyle((string) $dataValidation['errorStyle']);
$docValidation->setOperator((string) $dataValidation['operator']);
$docValidation->setAllowBlank(filter_var($dataValidation['allowBlank'], FILTER_VALIDATE_BOOLEAN));
// showDropDown is inverted (works as hideDropDown if true)
$docValidation->setShowDropDown(!filter_var($dataValidation['showDropDown'], FILTER_VALIDATE_BOOLEAN));
$docValidation->setShowInputMessage(filter_var($dataValidation['showInputMessage'], FILTER_VALIDATE_BOOLEAN));
$docValidation->setShowErrorMessage(filter_var($dataValidation['showErrorMessage'], FILTER_VALIDATE_BOOLEAN));
$docValidation->setErrorTitle((string) $dataValidation['errorTitle']);
$docValidation->setError((string) $dataValidation['error']);
$docValidation->setPromptTitle((string) $dataValidation['promptTitle']);
$docValidation->setPrompt((string) $dataValidation['prompt']);
$docValidation->setFormula1((string) $dataValidation->formula1);
$docValidation->setFormula2((string) $dataValidation->formula2);
$docValidation->setSqref($range);
}
}
}
}
}