ReferenceHelperDVTest::testInsertRowsWithDataValidation() - Code Metrics - Inspection of "Breaking Change for DataValidation" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#4240)

by Owen

created 2025-01-12 06:52 UTC

testInsertRowsWithDataValidation() A

↳ Parent: ReferenceHelperDVTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	18
Code Lines	12

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
eloc	12
c	2
b	0
f	0
dl	0
loc	18
rs	9.8666
cc	1
nc	1
nop	0

<?php

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests;

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PHPUnit\Framework\TestCase;

class ReferenceHelperDVTest extends TestCase
{
    public function testInsertRowsWithDataValidation(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
        $cellAddress = 'E5';
        $this->setDataValidation($sheet, $cellAddress);

        $sheet->insertNewRowBefore(2, 2);

        self::assertFalse(
            $sheet->getCell($cellAddress)->hasDataValidation()
        );
        self::assertTrue($sheet->getCell('E7')->hasDataValidation());
        self::assertSame('E7', $sheet->getDataValidation('E7')->getSqref());
        self::assertSame('$A$7:$A$10', $sheet->getDataValidation('E7')->getFormula1());
        $spreadsheet->disconnectWorksheets();
    }

    public function testDeleteRowsWithDataValidation(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
        $cellAddress = 'E5';
        $this->setDataValidation($sheet, $cellAddress);

        $sheet->removeRow(2, 2);

        self::assertFalse(
            $sheet->getCell($cellAddress)->hasDataValidation()
        );
        self::assertTrue($sheet->getCell('E3')->hasDataValidation());
        self::assertSame('E3', $sheet->getDataValidation('E3')->getSqref());
        self::assertSame('$A$3:$A$6', $sheet->getDataValidation('E3')->getFormula1());

        $spreadsheet->disconnectWorksheets();
    }

    public function testDeleteColumnsWithDataValidation(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
        $cellAddress = 'E5';
        $this->setDataValidation($sheet, $cellAddress);

        $sheet->removeColumn('B', 2);

        self::assertFalse(
            $sheet->getCell($cellAddress)->hasDataValidation()
        );
        self::assertTrue($sheet->getCell('C5')->hasDataValidation());
        self::assertSame('C5', $sheet->getDataValidation('C5')->getSqref());
        self::assertSame('$A$5:$A$8', $sheet->getDataValidation('C5')->getFormula1());
        $spreadsheet->disconnectWorksheets();
    }

    public function testInsertColumnsWithDataValidation(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
        $cellAddress = 'E5';
        $this->setDataValidation($sheet, $cellAddress);

        $sheet->insertNewColumnBefore('C', 2);

        self::assertFalse(
            $sheet->getCell($cellAddress)->hasDataValidation()
        );
        self::assertTrue($sheet->getCell('G5')->hasDataValidation());
        self::assertSame('G5', $sheet->getDataValidation('G5')->getSqref());
        self::assertSame('$A$5:$A$8', $sheet->getDataValidation('G5')->getFormula1());
        $spreadsheet->disconnectWorksheets();
    }

    public function testInsertColumnsWithDataValidation2(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
        $cellAddress = 'E5';
        $this->setDataValidation($sheet, $cellAddress);

        $sheet->insertNewColumnBefore('A', 2);

        self::assertFalse(
            $sheet->getCell($cellAddress)->hasDataValidation()
        );
        self::assertTrue($sheet->getCell('G5')->hasDataValidation());
        self::assertSame('G5', $sheet->getDataValidation('G5')->getSqref());
        self::assertSame('$C$5:$C$8', $sheet->getDataValidation('G5')->getFormula1());
        $spreadsheet->disconnectWorksheets();
    }

    private function setDataValidation(Worksheet $sheet, string $cellAddress): void
    {
        $validation = $sheet->getCell($cellAddress)
            ->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(
            DataValidation::STYLE_STOP
        );
        $validation->setAllowBlank(false);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Input error');
        $validation->setError('Value is not in list.');
        $validation->setPromptTitle('Pick from list');
        $validation->setPrompt('Please pick a value from the drop-down list.');
        $validation->setFormula1('$A$5:$A$8');
    }

    public function testMultipleRanges(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getCell('C1')->setValue(1);
        $sheet->getCell('C2')->setValue(2);
        $sheet->getCell('C3')->setValue(3);
        $dv = $sheet->getDataValidation('A1:A4 D5 E6:E7');
        $dv->setType(DataValidation::TYPE_LIST)
            ->setShowDropDown(true)
            ->setFormula1('$C$1:$C$3')
            ->setErrorStyle(DataValidation::STYLE_STOP)
            ->setShowErrorMessage(true)
            ->setErrorTitle('Input Error')
            ->setError('Value is not a member of allowed list');
        $sheet->insertNewColumnBefore('B');
        $dvs = $sheet->getDataValidationCollection();
        self::assertCount(1, $dvs);
        $expected = 'A1:A4 E5 F6:F7';
        self::assertSame([$expected], array_keys($dvs));
        $dv = $dvs[$expected];
        self::assertSame($expected, $dv->getSqref());
        self::assertSame('$D$1:$D$3', $dv->getFormula1());
        $sheet->getCell('A3')->setValue(8);
        self::assertFalse($sheet->getCell('A3')->hasValidValue());
        $sheet->getCell('E5')->setValue(7);
        self::assertFalse($sheet->getCell('E5')->hasValidValue());
        $sheet->getCell('F6')->setValue(7);
        self::assertFalse($sheet->getCell('F6')->hasValidValue());
        $sheet->getCell('F7')->setValue(1);
        self::assertTrue($sheet->getCell('F7')->hasValidValue());
        $spreadsheet->disconnectWorksheets();
    }

    public function testWholeColumn(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $dv = new DataValidation();
        $dv->setType(DataValidation::TYPE_NONE);
        $sheet->setDataValidation('A5:A7', $dv);
        $dv = new DataValidation();
        $dv->setType(DataValidation::TYPE_LIST)
            ->setShowDropDown(true)
            ->setFormula1('"Item A,Item B,Item C"')
            ->setErrorStyle(DataValidation::STYLE_STOP)
            ->setShowErrorMessage(true)
            ->setErrorTitle('Input Error')
            ->setError('Value is not a member of allowed list');
        $sheet->setDataValidation('A:A', $dv);
        $dv = new DataValidation();
        $dv->setType(DataValidation::TYPE_NONE);
        $sheet->setDataValidation('A9', $dv);
        self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('A4')->getType());
        self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('A10')->getType());
        self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('A6')->getType());
        self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('A9')->getType());
        $spreadsheet->disconnectWorksheets();
    }

    public function testWholeRow(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $dv = new DataValidation();
        $dv->setType(DataValidation::TYPE_NONE);
        $sheet->setDataValidation('C1:F1', $dv);
        $dv = new DataValidation();
        $dv->setType(DataValidation::TYPE_LIST)
            ->setShowDropDown(true)
            ->setFormula1('"Item A,Item B,Item C"')
            ->setErrorStyle(DataValidation::STYLE_STOP)
            ->setShowErrorMessage(true)
            ->setErrorTitle('Input Error')
            ->setError('Value is not a member of allowed list');
        $sheet->setDataValidation('1:1', $dv);
        $dv = new DataValidation();
        $dv->setType(DataValidation::TYPE_NONE);
        $sheet->setDataValidation('H1', $dv);
        self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('B1')->getType());
        self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('J1')->getType());
        self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('D1')->getType());
        self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('H1')->getType());
        $spreadsheet->disconnectWorksheets();
    }

    public function testFormula2(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getCell('A1')->setValue(5);
        $sheet->getCell('A5')->setValue(9);
        $dv = new DataValidation();
        $dv->setType(DataValidation::TYPE_WHOLE)
            ->setOperator(DataValidation::OPERATOR_BETWEEN)
            ->setFormula1('$A$1')
            ->setFormula2('$A$5')
            ->setErrorStyle(DataValidation::STYLE_STOP)
            ->setShowErrorMessage(true)
            ->setErrorTitle('Input Error')
            ->setError('Value is not whole number within bounds');
        $sheet->setDataValidation('B2', $dv);
        $sheet->insertNewRowBefore(2);
        $dv2 = $sheet->getCell('B3')->getDataValidation();
        self::assertSame('$A$1', $dv2->getFormula1());
        self::assertSame('$A$6', $dv2->getFormula2());

        $sheet->getCell('B3')->setValue(7);
        self::assertTrue($sheet->getCell('B3')->hasValidValue());
        $sheet->getCell('B3')->setValue(1);
        self::assertFalse($sheet->getCell('B3')->hasValidValue());
        $spreadsheet->disconnectWorksheets();
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace PhpOffice\PhpSpreadsheetTests;
6
7			use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
8			use PhpOffice\PhpSpreadsheet\Spreadsheet;
9			use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
10			use PHPUnit\Framework\TestCase;
11
12			class ReferenceHelperDVTest extends TestCase
13			{
14			public function testInsertRowsWithDataValidation(): void
15			{
16			$spreadsheet = new Spreadsheet();
17			$sheet = $spreadsheet->getActiveSheet();
18
19			$sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
20			$cellAddress = 'E5';
21			$this->setDataValidation($sheet, $cellAddress);
22
23			$sheet->insertNewRowBefore(2, 2);
24
25			self::assertFalse(
26			$sheet->getCell($cellAddress)->hasDataValidation()
27			);
28			self::assertTrue($sheet->getCell('E7')->hasDataValidation());
29			self::assertSame('E7', $sheet->getDataValidation('E7')->getSqref());
30			self::assertSame('$A$7:$A$10', $sheet->getDataValidation('E7')->getFormula1());
31			$spreadsheet->disconnectWorksheets();
32			}
33
34			public function testDeleteRowsWithDataValidation(): void
35			{
36			$spreadsheet = new Spreadsheet();
37			$sheet = $spreadsheet->getActiveSheet();
38
39			$sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
40			$cellAddress = 'E5';
41			$this->setDataValidation($sheet, $cellAddress);
42
43			$sheet->removeRow(2, 2);
44
45			self::assertFalse(
46			$sheet->getCell($cellAddress)->hasDataValidation()
47			);
48			self::assertTrue($sheet->getCell('E3')->hasDataValidation());
49			self::assertSame('E3', $sheet->getDataValidation('E3')->getSqref());
50			self::assertSame('$A$3:$A$6', $sheet->getDataValidation('E3')->getFormula1());
51
52			$spreadsheet->disconnectWorksheets();
53			}
54
55			public function testDeleteColumnsWithDataValidation(): void
56			{
57			$spreadsheet = new Spreadsheet();
58			$sheet = $spreadsheet->getActiveSheet();
59
60			$sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
61			$cellAddress = 'E5';
62			$this->setDataValidation($sheet, $cellAddress);
63
64			$sheet->removeColumn('B', 2);
65
66			self::assertFalse(
67			$sheet->getCell($cellAddress)->hasDataValidation()
68			);
69			self::assertTrue($sheet->getCell('C5')->hasDataValidation());
70			self::assertSame('C5', $sheet->getDataValidation('C5')->getSqref());
71			self::assertSame('$A$5:$A$8', $sheet->getDataValidation('C5')->getFormula1());
72			$spreadsheet->disconnectWorksheets();
73			}
74
75			public function testInsertColumnsWithDataValidation(): void
76			{
77			$spreadsheet = new Spreadsheet();
78			$sheet = $spreadsheet->getActiveSheet();
79
80			$sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
81			$cellAddress = 'E5';
82			$this->setDataValidation($sheet, $cellAddress);
83
84			$sheet->insertNewColumnBefore('C', 2);
85
86			self::assertFalse(
87			$sheet->getCell($cellAddress)->hasDataValidation()
88			);
89			self::assertTrue($sheet->getCell('G5')->hasDataValidation());
90			self::assertSame('G5', $sheet->getDataValidation('G5')->getSqref());
91			self::assertSame('$A$5:$A$8', $sheet->getDataValidation('G5')->getFormula1());
92			$spreadsheet->disconnectWorksheets();
93			}
94
95			public function testInsertColumnsWithDataValidation2(): void
96			{
97			$spreadsheet = new Spreadsheet();
98			$sheet = $spreadsheet->getActiveSheet();
99
100			$sheet->fromArray([['First'], ['Second'], ['Third'], ['Fourth']], null, 'A5', true);
101			$cellAddress = 'E5';
102			$this->setDataValidation($sheet, $cellAddress);
103
104			$sheet->insertNewColumnBefore('A', 2);
105
106			self::assertFalse(
107			$sheet->getCell($cellAddress)->hasDataValidation()
108			);
109			self::assertTrue($sheet->getCell('G5')->hasDataValidation());
110			self::assertSame('G5', $sheet->getDataValidation('G5')->getSqref());
111			self::assertSame('$C$5:$C$8', $sheet->getDataValidation('G5')->getFormula1());
112			$spreadsheet->disconnectWorksheets();
113			}
114
115			private function setDataValidation(Worksheet $sheet, string $cellAddress): void
116			{
117			$validation = $sheet->getCell($cellAddress)
118			->getDataValidation();
119			$validation->setType(DataValidation::TYPE_LIST);
120			$validation->setErrorStyle(
121			DataValidation::STYLE_STOP
122			);
123			$validation->setAllowBlank(false);
124			$validation->setShowInputMessage(true);
125			$validation->setShowErrorMessage(true);
126			$validation->setShowDropDown(true);
127			$validation->setErrorTitle('Input error');
128			$validation->setError('Value is not in list.');
129			$validation->setPromptTitle('Pick from list');
130			$validation->setPrompt('Please pick a value from the drop-down list.');
131			$validation->setFormula1('$A$5:$A$8');
132			}
133
134			public function testMultipleRanges(): void
135			{
136			$spreadsheet = new Spreadsheet();
137			$sheet = $spreadsheet->getActiveSheet();
138			$sheet->getCell('C1')->setValue(1);
139			$sheet->getCell('C2')->setValue(2);
140			$sheet->getCell('C3')->setValue(3);
141			$dv = $sheet->getDataValidation('A1:A4 D5 E6:E7');
142			$dv->setType(DataValidation::TYPE_LIST)
143			->setShowDropDown(true)
144			->setFormula1('$C$1:$C$3')
145			->setErrorStyle(DataValidation::STYLE_STOP)
146			->setShowErrorMessage(true)
147			->setErrorTitle('Input Error')
148			->setError('Value is not a member of allowed list');
149			$sheet->insertNewColumnBefore('B');
150			$dvs = $sheet->getDataValidationCollection();
151			self::assertCount(1, $dvs);
152			$expected = 'A1:A4 E5 F6:F7';
153			self::assertSame([$expected], array_keys($dvs));
154			$dv = $dvs[$expected];
155			self::assertSame($expected, $dv->getSqref());
156			self::assertSame('$D$1:$D$3', $dv->getFormula1());
157			$sheet->getCell('A3')->setValue(8);
158			self::assertFalse($sheet->getCell('A3')->hasValidValue());
159			$sheet->getCell('E5')->setValue(7);
160			self::assertFalse($sheet->getCell('E5')->hasValidValue());
161			$sheet->getCell('F6')->setValue(7);
162			self::assertFalse($sheet->getCell('F6')->hasValidValue());
163			$sheet->getCell('F7')->setValue(1);
164			self::assertTrue($sheet->getCell('F7')->hasValidValue());
165			$spreadsheet->disconnectWorksheets();
166			}
167
168			public function testWholeColumn(): void
169			{
170			$spreadsheet = new Spreadsheet();
171			$sheet = $spreadsheet->getActiveSheet();
172			$dv = new DataValidation();
173			$dv->setType(DataValidation::TYPE_NONE);
174			$sheet->setDataValidation('A5:A7', $dv);
175			$dv = new DataValidation();
176			$dv->setType(DataValidation::TYPE_LIST)
177			->setShowDropDown(true)
178			->setFormula1('"Item A,Item B,Item C"')
179			->setErrorStyle(DataValidation::STYLE_STOP)
180			->setShowErrorMessage(true)
181			->setErrorTitle('Input Error')
182			->setError('Value is not a member of allowed list');
183			$sheet->setDataValidation('A:A', $dv);
184			$dv = new DataValidation();
185			$dv->setType(DataValidation::TYPE_NONE);
186			$sheet->setDataValidation('A9', $dv);
187			self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('A4')->getType());
188			self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('A10')->getType());
189			self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('A6')->getType());
190			self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('A9')->getType());
191			$spreadsheet->disconnectWorksheets();
192			}
193
194			public function testWholeRow(): void
195			{
196			$spreadsheet = new Spreadsheet();
197			$sheet = $spreadsheet->getActiveSheet();
198			$dv = new DataValidation();
199			$dv->setType(DataValidation::TYPE_NONE);
200			$sheet->setDataValidation('C1:F1', $dv);
201			$dv = new DataValidation();
202			$dv->setType(DataValidation::TYPE_LIST)
203			->setShowDropDown(true)
204			->setFormula1('"Item A,Item B,Item C"')
205			->setErrorStyle(DataValidation::STYLE_STOP)
206			->setShowErrorMessage(true)
207			->setErrorTitle('Input Error')
208			->setError('Value is not a member of allowed list');
209			$sheet->setDataValidation('1:1', $dv);
210			$dv = new DataValidation();
211			$dv->setType(DataValidation::TYPE_NONE);
212			$sheet->setDataValidation('H1', $dv);
213			self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('B1')->getType());
214			self::assertSame(DataValidation::TYPE_LIST, $sheet->getDataValidation('J1')->getType());
215			self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('D1')->getType());
216			self::assertSame(DataValidation::TYPE_NONE, $sheet->getDataValidation('H1')->getType());
217			$spreadsheet->disconnectWorksheets();
218			}
219
220			public function testFormula2(): void
221			{
222			$spreadsheet = new Spreadsheet();
223			$sheet = $spreadsheet->getActiveSheet();
224			$sheet->getCell('A1')->setValue(5);
225			$sheet->getCell('A5')->setValue(9);
226			$dv = new DataValidation();
227			$dv->setType(DataValidation::TYPE_WHOLE)
228			->setOperator(DataValidation::OPERATOR_BETWEEN)
229			->setFormula1('$A$1')
230			->setFormula2('$A$5')
231			->setErrorStyle(DataValidation::STYLE_STOP)
232			->setShowErrorMessage(true)
233			->setErrorTitle('Input Error')
234			->setError('Value is not whole number within bounds');
235			$sheet->setDataValidation('B2', $dv);
236			$sheet->insertNewRowBefore(2);
237			$dv2 = $sheet->getCell('B3')->getDataValidation();
238			self::assertSame('$A$1', $dv2->getFormula1());
239			self::assertSame('$A$6', $dv2->getFormula2());
240
241			$sheet->getCell('B3')->setValue(7);
242			self::assertTrue($sheet->getCell('B3')->hasValidValue());
243			$sheet->getCell('B3')->setValue(1);
244			self::assertFalse($sheet->getCell('B3')->hasValidValue());
245			$spreadsheet->disconnectWorksheets();
246			}
247			}
248

PHPOffice / PhpSpreadsheet

Pull Request — master (#4240)

testInsertRowsWithDataValidation() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like