|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace PhpOffice\PhpSpreadsheet\Worksheet; |
|
4
|
|
|
|
|
5
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\AddressRange; |
|
6
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\CellAddress; |
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\CellRange; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Exception as SpreadsheetException; |
|
9
|
|
|
|
|
10
|
|
|
class Validations |
|
11
|
|
|
{ |
|
12
|
|
|
/** |
|
13
|
|
|
* Validate a cell address. |
|
14
|
|
|
* |
|
15
|
|
|
* @param null|array<int>|CellAddress|string $cellAddress Coordinate of the cell as a string, eg: 'C5'; |
|
16
|
|
|
* or as an array of [$columnIndex, $row] (e.g. [3, 5]), or a CellAddress object. |
|
17
|
|
|
*/ |
|
18
|
9604 |
|
public static function validateCellAddress($cellAddress): string |
|
19
|
|
|
{ |
|
20
|
9604 |
|
if (is_string($cellAddress)) { |
|
21
|
9599 |
|
[$worksheet, $address] = Worksheet::extractSheetTitle($cellAddress, true); |
|
22
|
|
|
// if (!empty($worksheet) && $worksheet !== $this->getTitle()) { |
|
23
|
|
|
// throw new Exception('Reference is not for this worksheet'); |
|
24
|
|
|
// } |
|
25
|
|
|
|
|
26
|
9599 |
|
return empty($worksheet) ? strtoupper($address) : $worksheet . '!' . strtoupper($address); |
|
27
|
|
|
} |
|
28
|
|
|
|
|
29
|
795 |
|
if (is_array($cellAddress)) { |
|
30
|
795 |
|
$cellAddress = CellAddress::fromColumnRowArray($cellAddress); |
|
31
|
|
|
} |
|
32
|
|
|
|
|
33
|
795 |
|
return (string) $cellAddress; |
|
34
|
|
|
} |
|
35
|
|
|
|
|
36
|
|
|
/** |
|
37
|
|
|
* Validate a cell address or cell range. |
|
38
|
|
|
* |
|
39
|
|
|
* @param AddressRange|array<int>|CellAddress|int|string $cellRange Coordinate of the cells as a string, eg: 'C5:F12'; |
|
40
|
|
|
* or as an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 12]), |
|
41
|
|
|
* or as a CellAddress or AddressRange object. |
|
42
|
|
|
*/ |
|
43
|
9151 |
|
public static function validateCellOrCellRange($cellRange): string |
|
44
|
|
|
{ |
|
45
|
9151 |
|
if (is_string($cellRange) || is_numeric($cellRange)) { |
|
46
|
|
|
// Convert a single column reference like 'A' to 'A:A', |
|
47
|
|
|
// a single row reference like '1' to '1:1' |
|
48
|
9149 |
|
$cellRange = (string) preg_replace('/^([A-Z]+|\d+)$/', '${1}:${1}', (string) $cellRange); |
|
49
|
39 |
|
} elseif (is_object($cellRange) && $cellRange instanceof CellAddress) { |
|
50
|
1 |
|
$cellRange = new CellRange($cellRange, $cellRange); |
|
51
|
|
|
} |
|
52
|
|
|
|
|
53
|
9151 |
|
return self::validateCellRange($cellRange); |
|
54
|
|
|
} |
|
55
|
|
|
|
|
56
|
|
|
/** |
|
57
|
|
|
* Validate a cell range. |
|
58
|
|
|
* |
|
59
|
|
|
* @param AddressRange|array<int>|string $cellRange Coordinate of the cells as a string, eg: 'C5:F12'; |
|
60
|
|
|
* or as an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 12]), |
|
61
|
|
|
* or as an AddressRange object. |
|
62
|
|
|
*/ |
|
63
|
9331 |
|
public static function validateCellRange($cellRange): string |
|
64
|
|
|
{ |
|
65
|
9331 |
|
if (is_string($cellRange)) { |
|
66
|
9326 |
|
[$worksheet, $addressRange] = Worksheet::extractSheetTitle($cellRange, true); |
|
67
|
|
|
|
|
68
|
|
|
// Convert Column ranges like 'A:C' to 'A1:C1048576' |
|
69
|
|
|
// or Row ranges like '1:3' to 'A1:XFD3' |
|
70
|
9326 |
|
$addressRange = (string) preg_replace( |
|
71
|
9326 |
|
['/^([A-Z]+):([A-Z]+)$/i', '/^(\\d+):(\\d+)$/'], |
|
72
|
9326 |
|
['${1}1:${2}1048576', 'A${1}:XFD${2}'], |
|
73
|
|
|
$addressRange |
|
74
|
|
|
); |
|
75
|
|
|
|
|
76
|
9326 |
|
return empty($worksheet) ? strtoupper($addressRange) : $worksheet . '!' . strtoupper($addressRange); |
|
77
|
|
|
} |
|
78
|
|
|
|
|
79
|
52 |
|
if (is_array($cellRange)) { |
|
80
|
41 |
|
switch (count($cellRange)) { |
|
81
|
41 |
|
case 2: |
|
82
|
34 |
|
$from = [$cellRange[0], $cellRange[1]]; |
|
83
|
34 |
|
$to = [$cellRange[0], $cellRange[1]]; |
|
84
|
|
|
|
|
85
|
34 |
|
break; |
|
86
|
8 |
|
case 4: |
|
87
|
7 |
|
$from = [$cellRange[0], $cellRange[1]]; |
|
88
|
7 |
|
$to = [$cellRange[2], $cellRange[3]]; |
|
89
|
|
|
|
|
90
|
7 |
|
break; |
|
91
|
|
|
default: |
|
92
|
1 |
|
throw new SpreadsheetException('CellRange array length must be 2 or 4'); |
|
93
|
|
|
} |
|
94
|
40 |
|
$cellRange = new CellRange(CellAddress::fromColumnRowArray($from), CellAddress::fromColumnRowArray($to)); |
|
95
|
|
|
} |
|
96
|
|
|
|
|
97
|
51 |
|
return (string) $cellRange; |
|
98
|
|
|
} |
|
99
|
|
|
|
|
100
|
9147 |
|
public static function definedNameToCoordinate(string $coordinate, Worksheet $worksheet): string |
|
101
|
|
|
{ |
|
102
|
|
|
// Uppercase coordinate |
|
103
|
9147 |
|
$coordinate = strtoupper($coordinate); |
|
104
|
|
|
// Eliminate leading equal sign |
|
105
|
9147 |
|
$testCoordinate = (string) preg_replace('/^=/', '', $coordinate); |
|
106
|
9147 |
|
$defined = $worksheet->getParent()->getDefinedName($testCoordinate, $worksheet); |
|
107
|
9147 |
|
if ($defined !== null) { |
|
108
|
7 |
|
if ($defined->getWorksheet() === $worksheet && !$defined->isFormula()) { |
|
109
|
1 |
|
$coordinate = (string) preg_replace('/^=/', '', $defined->getValue()); |
|
110
|
|
|
} |
|
111
|
|
|
} |
|
112
|
|
|
|
|
113
|
9147 |
|
return $coordinate; |
|
114
|
|
|
} |
|
115
|
|
|
} |
|
116
|
|
|
|
PHPOffice /
PhpSpreadsheet
