|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
declare(strict_types=1); |
|
4
|
|
|
|
|
5
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Reader\Xml; |
|
6
|
|
|
|
|
7
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataValidation; |
|
8
|
|
|
use PhpOffice\PhpSpreadsheet\Reader\Xml; |
|
9
|
|
|
use PhpOffice\PhpSpreadsheet\Shared\Date; |
|
10
|
|
|
use PhpOffice\PhpSpreadsheetTests\Functional\AbstractFunctional; |
|
11
|
|
|
|
|
12
|
|
|
class DataValidationsTest extends AbstractFunctional |
|
13
|
|
|
{ |
|
14
|
|
|
private string $filename = 'tests/data/Reader/Xml/datavalidations.xml'; |
|
15
|
|
|
|
|
16
|
|
|
private string $filename2 = 'tests/data/Reader/Xml/datavalidations.wholerow.xml'; |
|
17
|
|
|
|
|
18
|
|
|
public function testValidation(): void |
|
19
|
|
|
{ |
|
20
|
|
|
$reader = new Xml(); |
|
21
|
|
|
$spreadsheet = $reader->load($this->filename); |
|
22
|
|
|
$sheet = $spreadsheet->getActiveSheet(); |
|
23
|
|
|
$assertions = $this->validationAssertions(); |
|
24
|
|
|
$validation = $sheet->getCell('A1')->getDataValidation(); |
|
25
|
|
|
self::assertSame('A:A', $validation->getSqref()); |
|
26
|
|
|
$validation = $sheet->getCell('B3')->getDataValidation(); |
|
27
|
|
|
self::assertSame('B2:B1048576', $validation->getSqref()); |
|
28
|
|
|
|
|
29
|
|
|
foreach ($assertions as $title => $assertion) { |
|
30
|
|
|
$sheet->getCell($assertion[1])->setValue($assertion[2]); |
|
31
|
|
|
self::assertSame($assertion[0], $sheet->getCell($assertion[1])->hasValidValue(), $title); |
|
32
|
|
|
} |
|
33
|
|
|
$sheet->getCell('F1')->getDataValidation()->setType(DataValidation::TYPE_NONE); |
|
34
|
|
|
$sheet->getCell('F1')->setValue(1); |
|
35
|
|
|
self::assertTrue($sheet->getCell('F1')->hasValidValue(), 'validation type is NONE'); |
|
36
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
37
|
|
|
} |
|
38
|
|
|
|
|
39
|
|
|
public function testValidationWholeRow(): void |
|
40
|
|
|
{ |
|
41
|
|
|
$reader = new Xml(); |
|
42
|
|
|
$spreadsheet = $reader->load($this->filename2); |
|
43
|
|
|
$sheet = $spreadsheet->getActiveSheet(); |
|
44
|
|
|
$collection = $sheet->getDataValidationCollection(); |
|
45
|
|
|
self::assertSame(['A1', '1:1'], array_keys($collection)); |
|
46
|
|
|
$dv = $collection['A1']; |
|
47
|
|
|
self::assertSame('"Item A,Item B,Item D"', $dv->getFormula1()); |
|
48
|
|
|
self::assertSame('warn', $dv->getErrorStyle()); |
|
49
|
|
|
self::assertFalse($dv->getShowDropDown()); |
|
50
|
|
|
self::assertFalse($dv->getShowErrorMessage()); |
|
51
|
|
|
|
|
52
|
|
|
$dv = $collection['1:1']; |
|
53
|
|
|
self::assertSame('"Item A,Item B,Item C"', $dv->getFormula1()); |
|
54
|
|
|
self::assertSame('stop', $dv->getErrorStyle()); |
|
55
|
|
|
self::assertTrue($dv->getShowDropDown()); |
|
56
|
|
|
self::assertTrue($dv->getShowErrorMessage()); |
|
57
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
58
|
|
|
} |
|
59
|
|
|
|
|
60
|
|
|
public function testValidationXlsx(): void |
|
61
|
|
|
{ |
|
62
|
|
|
$reader = new Xml(); |
|
63
|
|
|
$oldspreadsheet = $reader->load($this->filename); |
|
64
|
|
|
$spreadsheet = $this->writeAndReload($oldspreadsheet, 'Xlsx'); |
|
65
|
|
|
$oldspreadsheet->disconnectWorksheets(); |
|
66
|
|
|
|
|
67
|
|
|
$sheet = $spreadsheet->getActiveSheet(); |
|
68
|
|
|
$assertions = $this->validationAssertions(); |
|
69
|
|
|
$validation = $sheet->getCell('A1')->getDataValidation(); |
|
70
|
|
|
self::assertSame('A:A', $validation->getSqref()); |
|
71
|
|
|
$validation = $sheet->getCell('B3')->getDataValidation(); |
|
72
|
|
|
self::assertSame('B2:B1048576', $validation->getSqref()); |
|
73
|
|
|
|
|
74
|
|
|
foreach ($assertions as $title => $assertion) { |
|
75
|
|
|
$sheet->getCell($assertion[1])->setValue($assertion[2]); |
|
76
|
|
|
self::assertSame($assertion[0], $sheet->getCell($assertion[1])->hasValidValue(), $title); |
|
77
|
|
|
} |
|
78
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
79
|
|
|
} |
|
80
|
|
|
|
|
81
|
|
|
public function testValidationXls(): void |
|
82
|
|
|
{ |
|
83
|
|
|
$reader = new Xml(); |
|
84
|
|
|
$oldspreadsheet = $reader->load($this->filename); |
|
85
|
|
|
$spreadsheet = $this->writeAndReload($oldspreadsheet, 'Xls'); |
|
86
|
|
|
$oldspreadsheet->disconnectWorksheets(); |
|
87
|
|
|
|
|
88
|
|
|
$sheet = $spreadsheet->getActiveSheet(); |
|
89
|
|
|
$assertions = $this->validationAssertions(); |
|
90
|
|
|
$validation = $sheet->getCell('A1')->getDataValidation(); |
|
91
|
|
|
self::assertSame('A:A', $validation->getSqref(), 'limited number of rows in Xls'); |
|
92
|
|
|
$validation = $sheet->getCell('B3')->getDataValidation(); |
|
93
|
|
|
self::assertSame('B2:B1048576', $validation->getSqref()); |
|
94
|
|
|
|
|
95
|
|
|
foreach ($assertions as $title => $assertion) { |
|
96
|
|
|
$sheet->getCell($assertion[1])->setValue($assertion[2]); |
|
97
|
|
|
self::assertSame($assertion[0], $sheet->getCell($assertion[1])->hasValidValue(), $title); |
|
98
|
|
|
} |
|
99
|
|
|
$spreadsheet->disconnectWorksheets(); |
|
100
|
|
|
} |
|
101
|
|
|
|
|
102
|
|
|
private function validationAssertions(): array |
|
103
|
|
|
{ |
|
104
|
|
|
return [ |
|
105
|
|
|
// Numeric tests |
|
106
|
|
|
'Integer between 2 and 5: x' => [false, 'F1', 'x'], |
|
107
|
|
|
'Integer between 2 and 5: 3.1' => [false, 'F1', 3.1], |
|
108
|
|
|
'Integer between 2 and 5: 3' => [true, 'F1', 3], |
|
109
|
|
|
'Integer between 2 and 5: 1' => [false, 'F1', 1], |
|
110
|
|
|
'Integer between 2 and 5: 7' => [false, 'F1', 7], |
|
111
|
|
|
'Float between 2 and 5: x' => [false, 'G1', 'x'], |
|
112
|
|
|
'Float between 2 and 5: 3.1' => [true, 'G1', 3.1], |
|
113
|
|
|
'Float between 2 and 5: 3' => [true, 'G1', 3], |
|
114
|
|
|
'Float between 2 and 5: 1' => [false, 'G1', 1], |
|
115
|
|
|
'Float between 2 and 5: 7' => [false, 'G1', 7], |
|
116
|
|
|
'Integer not between -5 and 5: 3' => [false, 'F2', 3], |
|
117
|
|
|
'Integer not between -5 and 5: -1' => [false, 'F2', -1], |
|
118
|
|
|
'Integer not between -5 and 5: 7' => [true, 'F2', 7], |
|
119
|
|
|
'Any integer except 7: -1' => [true, 'F3', -1], |
|
120
|
|
|
'Any integer except 7: 7' => [false, 'F3', 7], |
|
121
|
|
|
'Only -3: -1' => [false, 'F4', -1], |
|
122
|
|
|
'Only -3: -3' => [true, 'F4', -3], |
|
123
|
|
|
'Integer less than 8: 8' => [false, 'F5', 8], |
|
124
|
|
|
'Integer less than 8: 7' => [true, 'F5', 7], |
|
125
|
|
|
'Integer less than 8: 9' => [false, 'F5', 9], |
|
126
|
|
|
'Integer less than or equal 12: 12' => [true, 'F6', 12], |
|
127
|
|
|
'Integer less than or equal 12: 7' => [true, 'F6', 7], |
|
128
|
|
|
'Integer less than or equal 12: 13' => [false, 'F6', 13], |
|
129
|
|
|
'Integer greater than or equal -6: -6' => [true, 'F7', -6], |
|
130
|
|
|
'Integer greater than or equal -6: -7' => [false, 'F7', -7], |
|
131
|
|
|
'Integer greater than or equal -6: -5' => [true, 'F7', -5], |
|
132
|
|
|
'Integer greater than 5: 5' => [false, 'F8', 5], |
|
133
|
|
|
'Integer greater than 5: 6' => [true, 'F8', 6], |
|
134
|
|
|
'Integer greater than 5: 3' => [false, 'F8', 3], |
|
135
|
|
|
// Text tests |
|
136
|
|
|
'a,b,c,d,e: a' => [true, 'C4', 'a'], |
|
137
|
|
|
'a,b,c,d,e: c' => [true, 'C4', 'c'], |
|
138
|
|
|
'a,b,c,d,e: e' => [true, 'C4', 'e'], |
|
139
|
|
|
'a,b,c,d,e: x' => [false, 'C4', 'x'], |
|
140
|
|
|
'a,b,c,d,e: aa' => [false, 'C4', 'aa'], |
|
141
|
|
|
'less than 8 characters: abcdefg' => [true, 'C3', 'abcdefg'], |
|
142
|
|
|
'less than 8 characters: abcdefgh' => [false, 'C3', 'abcdefgh'], |
|
143
|
|
|
'texts in e1 to e5: ccc' => [true, 'D2', 'ccc'], |
|
144
|
|
|
'texts in e1 to e5: ffffff' => [false, 'D2', 'ffffff'], |
|
145
|
|
|
'date from 20230101: 20221231' => [false, 'C1', Date::convertIsoDate('20221231')], |
|
146
|
|
|
'date from 20230101: 20230101' => [true, 'C1', Date::convertIsoDate('20230101')], |
|
147
|
|
|
'date from 20230101: 20240507' => [true, 'C1', Date::convertIsoDate('20240507')], |
|
148
|
|
|
'date from 20230101: 20240507 10:00:00' => [true, 'C1', Date::convertIsoDate('20240507 10:00:00')], |
|
149
|
|
|
'time from 12:00-14:00: 2023-01-01 13:00:00' => [false, 'C2', Date::convertIsoDate('2023-01-01 13:00:00')], |
|
150
|
|
|
'time from 8:00-14:00: 13:00' => [true, 'C2', Date::convertIsoDate('13:00')], |
|
151
|
|
|
'time from 8:00-14:00: 07:00:00' => [false, 'C2', Date::convertIsoDate('07:00:00')], |
|
152
|
|
|
'time from 8:00-14:00: 15:00:00' => [false, 'C2', Date::convertIsoDate('15:00:00')], |
|
153
|
|
|
'time from 8:00-14:00: 1:13 am' => [false, 'C2', Date::convertIsoDate('1:13 am')], |
|
154
|
|
|
'time from 8:00-14:00: 1:13 pm' => [true, 'C2', Date::convertIsoDate('1:13 pm')], |
|
155
|
|
|
'time from 8:00-14:00: 9:13' => [true, 'C2', Date::convertIsoDate('9:13')], |
|
156
|
|
|
'time from 8:00-14:00: 9:13 am' => [true, 'C2', Date::convertIsoDate('9:13 am')], |
|
157
|
|
|
'time from 8:00-14:00: 9:13 pm' => [false, 'C2', Date::convertIsoDate('9:13 pm')], |
|
158
|
|
|
]; |
|
159
|
|
|
} |
|
160
|
|
|
} |
|
161
|
|
|
|
PHPOffice /
PhpSpreadsheet
