WorksheetTest::testRemoveCellsCorrectlyWhenRemovingRow() - Code Metrics - Inspection of "Merge pull request #3494 from PHPOffice/Worksheet-..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( f4e897...378d46 )

by Mark

created 2023-04-01 20:39 UTC

testRemoveCellsCorrectlyWhenRemovingRow() A

↳ Parent: WorksheetTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	10
nc	1
nop	0
dl	0
loc	13
rs	9.9332
c	0
b	0
f	0

<?php

namespace PhpOffice\PhpSpreadsheetTests\Worksheet;

use Exception;
use PhpOffice\PhpSpreadsheet\Cell\CellAddress;
use PhpOffice\PhpSpreadsheet\Cell\CellRange;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\CellIterator;
use PhpOffice\PhpSpreadsheet\Worksheet\Table;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PHPUnit\Framework\TestCase;

class WorksheetTest extends TestCase
{
    public function testSetTitle(): void
    {
        $testTitle = str_repeat('a', 31);

        $worksheet = new Worksheet();
        $worksheet->setTitle($testTitle);
        self::assertSame($testTitle, $worksheet->getTitle());
    }

    public function setTitleInvalidProvider(): array
    {
        return [
            [str_repeat('a', 32), 'Maximum 31 characters allowed in sheet title.'],
            ['invalid*title', 'Invalid character found in sheet title'],
        ];
    }

    /**
     * @param string $title
     * @param string $expectMessage
     *
     * @dataProvider setTitleInvalidProvider
     */
    public function testSetTitleInvalid($title, $expectMessage): void
    {
        // First, test setting title with validation disabled -- should be successful
        $worksheet = new Worksheet();
        $worksheet->setTitle($title, true, false);

        // Next, test again with validation enabled -- this time we should fail
        $worksheet = new Worksheet();
        $this->expectException(Exception::class);
        $this->expectExceptionMessage($expectMessage);
        $worksheet->setTitle($title);
    }

    public function testSetTitleDuplicate(): void
    {
        // Create a Spreadsheet with three Worksheets (the first is created automatically)
        $spreadsheet = new Spreadsheet();
        $spreadsheet->createSheet();
        $spreadsheet->createSheet();

        // Set unique title -- should be unchanged
        $sheet = $spreadsheet->getSheet(0);
        $sheet->setTitle('Test Title');
        self::assertSame('Test Title', $sheet->getTitle());

        // Set duplicate title -- should have numeric suffix appended
        $sheet = $spreadsheet->getSheet(1);
        $sheet->setTitle('Test Title');
        self::assertSame('Test Title 1', $sheet->getTitle());

        // Set duplicate title with validation disabled -- should be unchanged
        $sheet = $spreadsheet->getSheet(2);
        $sheet->setTitle('Test Title', true, false);
        self::assertSame('Test Title', $sheet->getTitle());
    }

    public function testSetCodeName(): void
    {
        $testCodeName = str_repeat('a', 31);

        $worksheet = new Worksheet();
        $worksheet->setCodeName($testCodeName);
        self::assertSame($testCodeName, $worksheet->getCodeName());
    }

    public function setCodeNameInvalidProvider(): array
    {
        return [
            [str_repeat('a', 32), 'Maximum 31 characters allowed in sheet code name.'],
            ['invalid*code*name', 'Invalid character found in sheet code name'],
            ['', 'Sheet code name cannot be empty'],
        ];
    }

    /**
     * @param string $codeName
     * @param string $expectMessage
     *
     * @dataProvider setCodeNameInvalidProvider
     */
    public function testSetCodeNameInvalid($codeName, $expectMessage): void
    {
        // First, test setting code name with validation disabled -- should be successful
        $worksheet = new Worksheet();
        $worksheet->setCodeName($codeName, false);

        // Next, test again with validation enabled -- this time we should fail
        $worksheet = new Worksheet();
        $this->expectException(Exception::class);
        $this->expectExceptionMessage($expectMessage);
        $worksheet->setCodeName($codeName);
    }

    public function testSetCodeNameDuplicate(): void
    {
        // Create a Spreadsheet with three Worksheets (the first is created automatically)
        $spreadsheet = new Spreadsheet();
        $spreadsheet->createSheet();
        $spreadsheet->createSheet();

        // Set unique code name -- should be massaged to Snake_Case
        $sheet = $spreadsheet->getSheet(0);
        $sheet->setCodeName('Test Code Name');
        self::assertSame('Test_Code_Name', $sheet->getCodeName());

        // Set duplicate code name -- should be massaged and have numeric suffix appended
        $sheet = $spreadsheet->getSheet(1);
        $sheet->setCodeName('Test Code Name');
        self::assertSame('Test_Code_Name_1', $sheet->getCodeName());

        // Set duplicate code name with validation disabled -- should be unchanged, and unmassaged
        $sheet = $spreadsheet->getSheet(2);
        $sheet->setCodeName('Test Code Name', false);
        self::assertSame('Test Code Name', $sheet->getCodeName());
    }

    public function testFreezePaneSelectedCell(): void
    {
        $worksheet = new Worksheet();
        $worksheet->freezePane('B2');
        self::assertSame('B2', $worksheet->getTopLeftCell());
    }

    public function extractSheetTitleProvider(): array
    {
        return [
            ['B2', '', '', 'B2'],
            ['testTitle!B2', 'testTitle', 'B2', 'B2'],
            ['test!Title!B2', 'test!Title', 'B2', 'B2'],
            ['test Title!B2', 'test Title', 'B2', 'B2'],
            ['test!Title!B2', 'test!Title', 'B2', 'B2'],
            ["'testSheet 1'!A3", "'testSheet 1'", 'A3', 'A3'],
            ["'testSheet1'!A2", "'testSheet1'", 'A2', 'A2'],
            ["'testSheet 2'!A1", "'testSheet 2'", 'A1', 'A1'],
        ];
    }

    /**
     * @param string $range
     * @param string $expectTitle
     * @param string $expectCell
     * @param string $expectCell2
     *
     * @dataProvider extractSheetTitleProvider
     */
    public function testExtractSheetTitle($range, $expectTitle, $expectCell, $expectCell2): void
    {
        // only cell reference
        self::assertSame($expectCell, Worksheet::extractSheetTitle($range));
        // with title in array
        $arRange = Worksheet::extractSheetTitle($range, true);
        self::assertSame($expectTitle, $arRange[0]);
        self::assertSame($expectCell2, $arRange[1]);
    }

    /**
     * Fix https://github.com/PHPOffice/PhpSpreadsheet/issues/868 when cells are not removed correctly
     * on row deletion.
     */
    public function testRemoveCellsCorrectlyWhenRemovingRow(): void
    {
        $workbook = new Spreadsheet();
        $worksheet = $workbook->getActiveSheet();
        $worksheet->getCell('A2')->setValue('A2');
        $worksheet->getCell('C1')->setValue('C1');
        $worksheet->removeRow(1);
        self::assertEquals(
            'A2',
            $worksheet->getCell('A1')->getValue()
        );
        self::assertNull(
            $worksheet->getCell('C1')->getValue()
        );
    }

    public function removeColumnProvider(): array
    {
        return [
            'Remove first column' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                ],
                'A',
                1,
                [
                    ['B1', 'C1'],
                    ['B2', 'C2'],
                ],
                'B',
            ],
            'Remove middle column' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                ],
                'B',
                1,
                [
                    ['A1', 'C1'],
                    ['A2', 'C2'],
                ],
                'B',
            ],
            'Remove last column' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                ],
                'C',
                1,
                [
                    ['A1', 'B1'],
                    ['A2', 'B2'],
                ],
                'B',
            ],
            'Remove a column out of range' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                ],
                'D',
                1,
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                ],
                'C',
            ],
            'Remove multiple columns' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                ],
                'B',
                5,
                [
                    ['A1'],
                    ['A2'],
                ],
                'A',
            ],
        ];
    }

    /**
     * @dataProvider removeColumnProvider
     */
    public function testRemoveColumn(
        array $initialData,
        string $columnToBeRemoved,
        int $columnsToBeRemoved,
        array $expectedData,
        string $expectedHighestColumn
    ): void {
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();
        $worksheet->fromArray($initialData);

        $worksheet->removeColumn($columnToBeRemoved, $columnsToBeRemoved);

        self::assertSame($expectedHighestColumn, $worksheet->getHighestColumn());
        self::assertSame($expectedData, $worksheet->toArray());
    }

    public function removeRowsProvider(): array
    {
        return [
            'Remove all rows except first one' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                2,
                3,
                [
                    ['A1', 'B1', 'C1'],
                ],
                1,
            ],
            'Remove all rows except last one' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                1,
                3,
                [
                    ['A4', 'B4', 'C4'],
                ],
                1,
            ],
            'Remove last row' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                4,
                1,
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                ],
                3,
            ],
            'Remove first row' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                1,
                1,
                [
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                3,
            ],
            'Remove all rows except first and last' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                2,
                2,
                [
                    ['A1', 'B1', 'C1'],
                    ['A4', 'B4', 'C4'],
                ],
                2,
            ],
            'Remove non existing rows' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                2,
                10,
                [
                    ['A1', 'B1', 'C1'],
                ],
                1,
            ],
            'Remove only non existing rows' => [
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                5,
                10,
                [
                    ['A1', 'B1', 'C1'],
                    ['A2', 'B2', 'C2'],
                    ['A3', 'B3', 'C3'],
                    ['A4', 'B4', 'C4'],
                ],
                4,
            ],
        ];
    }

    /**
     * @dataProvider removeRowsProvider
     */
    public function testRemoveRows(
        array $initialData,
        int $rowToRemove,
        int $rowsQtyToRemove,
        array $expectedData,
        int $expectedHighestRow
    ): void {
        $workbook = new Spreadsheet();
        $worksheet = $workbook->getActiveSheet();
        $worksheet->fromArray($initialData);

        $worksheet->removeRow($rowToRemove, $rowsQtyToRemove);

        self::assertSame($expectedData, $worksheet->toArray());
        self::assertSame($expectedHighestRow, $worksheet->getHighestRow());
    }

    private static function getPopulatedSheetForEmptyRowTest(Spreadsheet $spreadsheet): Worksheet
    {
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValueExplicit('A1', 'Hello World', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('B3', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('B4', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('B5', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('C5', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('B6', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('C6', 'PHP', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('B7', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('C7', 'PHP', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('B8', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('C8', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('D8', 'PHP', DataType::TYPE_STRING);

        return $sheet;
    }

    private static function getPopulatedSheetForEmptyColumnTest(Spreadsheet $spreadsheet): Worksheet
    {
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValueExplicit('A1', 'Hello World', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('C2', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('D2', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('E2', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('E3', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('F2', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('F3', 'PHP', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('G2', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('G3', 'PHP', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('H2', null, DataType::TYPE_NULL);
        $sheet->setCellValueExplicit('H3', '', DataType::TYPE_STRING);
        $sheet->setCellValueExplicit('H4', 'PHP', DataType::TYPE_STRING);

        return $sheet;
    }

    /**
     * @dataProvider emptyRowProvider
     */
    public function testIsEmptyRow(int $rowId, bool $expectedEmpty): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = self::getPopulatedSheetForEmptyRowTest($spreadsheet);

        $isEmpty = $sheet->isEmptyRow($rowId, CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL | CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL);

        self::assertSame($expectedEmpty, $isEmpty);
        $spreadsheet->disconnectWorksheets();
    }

    public function emptyRowProvider(): array
    {
        return [
            [1, false],
            [2, true],
            [3, true],
            [4, true],
            [5, true],
            [6, false],
            [7, false],
            [8, false],
            [9, true],
        ];
    }

    /**
     * @dataProvider emptyColumnProvider
     */
    public function testIsEmptyColumn(string $columnId, bool $expectedEmpty): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = self::getPopulatedSheetForEmptyColumnTest($spreadsheet);

        $isEmpty = $sheet->isEmptyColumn($columnId, CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL | CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL);

        self::assertSame($expectedEmpty, $isEmpty);
        $spreadsheet->disconnectWorksheets();
    }

    public function emptyColumnProvider(): array
    {
        return [
            ['A', false],
            ['B', true],
            ['C', true],
            ['D', true],
            ['E', true],
            ['F', false],
            ['G', false],
            ['H', false],
            ['I', true],
        ];
    }

    public function testGetTableNames(): void
    {
        $reader = new Xlsx();
        $spreadsheet = $reader->load('tests/data/Worksheet/Table/TableFormulae.xlsx');
        $worksheet = $spreadsheet->getActiveSheet();

        $tables = $worksheet->getTableNames();
        self::assertSame(['DeptSales'], $tables);
    }

    public function testGetTableByName(): void
    {
        $reader = new Xlsx();
        $spreadsheet = $reader->load('tests/data/Worksheet/Table/TableFormulae.xlsx');
        $worksheet = $spreadsheet->getActiveSheet();

        $table = $worksheet->getTableByName('Non-existent Table');
        self::assertNull($table);

        $table = $worksheet->getTableByName('DeptSales');
        self::assertInstanceOf(Table::class, $table);
    }

    /**
     * @dataProvider toArrayHiddenRowsProvider
     */
    public function testHiddenRows(
        array $initialData,
        array $hiddenRows,
        array $expectedData
    ): void {
        $workbook = new Spreadsheet();
        $worksheet = $workbook->getActiveSheet();
        $worksheet->fromArray($initialData);

        foreach ($hiddenRows as $hiddenRow) {
            $worksheet->getRowDimension($hiddenRow)->setVisible(false);
        }

        self::assertSame($expectedData, $worksheet->toArray(null, false, false, true, true));
    }

    public function toArrayHiddenRowsProvider(): array
    {
        return [
            [
                [[1], [2], [3], [4], [5], [6]],
                [2, 3, 5],
                [1 => ['A' => 1], 4 => ['A' => 4], 6 => ['A' => 6]],
            ],
            [
                [[1], [2], [3], [4], [5], [6]],
                [1, 3, 6],
                [2 => ['A' => 2], 4 => ['A' => 4], 5 => ['A' => 5]],
            ],
        ];
    }

    /**
     * @dataProvider toArrayHiddenColumnsProvider
     */
    public function testHiddenColumns(
        array $initialData,
        array $hiddenColumns,
        array $expectedData
    ): void {
        $workbook = new Spreadsheet();
        $worksheet = $workbook->getActiveSheet();
        $worksheet->fromArray($initialData);

        foreach ($hiddenColumns as $hiddenColumn) {
            $worksheet->getColumnDimension($hiddenColumn)->setVisible(false);
        }

        self::assertSame($expectedData, $worksheet->toArray(null, false, false, true, true));
    }

    public function toArrayHiddenColumnsProvider(): array
    {
        return [
            [
                ['A', 'B', 'C', 'D', 'E', 'F'],
                ['B', 'C', 'E'],
                [1 => ['A' => 'A', 'D' => 'D', 'F' => 'F']],
            ],
            [
                ['A', 'B', 'C', 'D', 'E', 'F'],
                ['A', 'C', 'F'],
                [1 => ['B' => 'B', 'D' => 'D', 'E' => 'E']],
            ],
        ];
    }

    /**
     * @dataProvider rangeToArrayProvider
     */
    public function testRangeToArrayWithCellRangeObject(array $expected, string $fromCell, string $toCell): void
    {
        $initialData = array_chunk(range('A', 'Y'), 5);

        $workbook = new Spreadsheet();
        $worksheet = $workbook->getActiveSheet();
        $worksheet->fromArray($initialData);

        $cellRange = new CellRange(new CellAddress($fromCell), new CellAddress($toCell));

        self::assertSame($expected, $worksheet->rangeToArray($cellRange));
    }

    public function rangeToArrayProvider(): array
    {
        return [
            [
                [['A', 'B'], ['F', 'G']],
                'A1', 'B2',
            ],
            [
                [['G', 'H', 'I'], ['L', 'M', 'N'], ['Q', 'R', 'S']],
                'B2', 'D4',
            ],
        ];
    }
}


1			<?php
2
3			namespace PhpOffice\PhpSpreadsheetTests\Worksheet;
4
5			use Exception;
6			use PhpOffice\PhpSpreadsheet\Cell\CellAddress;
7			use PhpOffice\PhpSpreadsheet\Cell\CellRange;
8			use PhpOffice\PhpSpreadsheet\Cell\DataType;
9			use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
10			use PhpOffice\PhpSpreadsheet\Spreadsheet;
11			use PhpOffice\PhpSpreadsheet\Worksheet\CellIterator;
12			use PhpOffice\PhpSpreadsheet\Worksheet\Table;
13			use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
14			use PHPUnit\Framework\TestCase;
15
16			class WorksheetTest extends TestCase
17			{
18			public function testSetTitle(): void
19			{
20			$testTitle = str_repeat('a', 31);
21
22			$worksheet = new Worksheet();
23			$worksheet->setTitle($testTitle);
24			self::assertSame($testTitle, $worksheet->getTitle());
25			}
26
27			public function setTitleInvalidProvider(): array
28			{
29			return [
30			[str_repeat('a', 32), 'Maximum 31 characters allowed in sheet title.'],
31			['invalid*title', 'Invalid character found in sheet title'],
32			];
33			}
34
35			/**
36			* @param string $title
37			* @param string $expectMessage
38			*
39			* @dataProvider setTitleInvalidProvider
40			*/
41			public function testSetTitleInvalid($title, $expectMessage): void
42			{
43			// First, test setting title with validation disabled -- should be successful
44			$worksheet = new Worksheet();
45			$worksheet->setTitle($title, true, false);
46
47			// Next, test again with validation enabled -- this time we should fail
48			$worksheet = new Worksheet();
49			$this->expectException(Exception::class);
50			$this->expectExceptionMessage($expectMessage);
51			$worksheet->setTitle($title);
52			}
53
54			public function testSetTitleDuplicate(): void
55			{
56			// Create a Spreadsheet with three Worksheets (the first is created automatically)
57			$spreadsheet = new Spreadsheet();
58			$spreadsheet->createSheet();
59			$spreadsheet->createSheet();
60
61			// Set unique title -- should be unchanged
62			$sheet = $spreadsheet->getSheet(0);
63			$sheet->setTitle('Test Title');
64			self::assertSame('Test Title', $sheet->getTitle());
65
66			// Set duplicate title -- should have numeric suffix appended
67			$sheet = $spreadsheet->getSheet(1);
68			$sheet->setTitle('Test Title');
69			self::assertSame('Test Title 1', $sheet->getTitle());
70
71			// Set duplicate title with validation disabled -- should be unchanged
72			$sheet = $spreadsheet->getSheet(2);
73			$sheet->setTitle('Test Title', true, false);
74			self::assertSame('Test Title', $sheet->getTitle());
75			}
76
77			public function testSetCodeName(): void
78			{
79			$testCodeName = str_repeat('a', 31);
80
81			$worksheet = new Worksheet();
82			$worksheet->setCodeName($testCodeName);
83			self::assertSame($testCodeName, $worksheet->getCodeName());
84			}
85
86			public function setCodeNameInvalidProvider(): array
87			{
88			return [
89			[str_repeat('a', 32), 'Maximum 31 characters allowed in sheet code name.'],
90			['invalidcodename', 'Invalid character found in sheet code name'],
91			['', 'Sheet code name cannot be empty'],
92			];
93			}
94
95			/**
96			* @param string $codeName
97			* @param string $expectMessage
98			*
99			* @dataProvider setCodeNameInvalidProvider
100			*/
101			public function testSetCodeNameInvalid($codeName, $expectMessage): void
102			{
103			// First, test setting code name with validation disabled -- should be successful
104			$worksheet = new Worksheet();
105			$worksheet->setCodeName($codeName, false);
106
107			// Next, test again with validation enabled -- this time we should fail
108			$worksheet = new Worksheet();
109			$this->expectException(Exception::class);
110			$this->expectExceptionMessage($expectMessage);
111			$worksheet->setCodeName($codeName);
112			}
113
114			public function testSetCodeNameDuplicate(): void
115			{
116			// Create a Spreadsheet with three Worksheets (the first is created automatically)
117			$spreadsheet = new Spreadsheet();
118			$spreadsheet->createSheet();
119			$spreadsheet->createSheet();
120
121			// Set unique code name -- should be massaged to Snake_Case
122			$sheet = $spreadsheet->getSheet(0);
123			$sheet->setCodeName('Test Code Name');
124			self::assertSame('Test_Code_Name', $sheet->getCodeName());
125
126			// Set duplicate code name -- should be massaged and have numeric suffix appended
127			$sheet = $spreadsheet->getSheet(1);
128			$sheet->setCodeName('Test Code Name');
129			self::assertSame('Test_Code_Name_1', $sheet->getCodeName());
130
131			// Set duplicate code name with validation disabled -- should be unchanged, and unmassaged
132			$sheet = $spreadsheet->getSheet(2);
133			$sheet->setCodeName('Test Code Name', false);
134			self::assertSame('Test Code Name', $sheet->getCodeName());
135			}
136
137			public function testFreezePaneSelectedCell(): void
138			{
139			$worksheet = new Worksheet();
140			$worksheet->freezePane('B2');
141			self::assertSame('B2', $worksheet->getTopLeftCell());
142			}
143
144			public function extractSheetTitleProvider(): array
145			{
146			return [
147			['B2', '', '', 'B2'],
148			['testTitle!B2', 'testTitle', 'B2', 'B2'],
149			['test!Title!B2', 'test!Title', 'B2', 'B2'],
150			['test Title!B2', 'test Title', 'B2', 'B2'],
151			['test!Title!B2', 'test!Title', 'B2', 'B2'],
152			["'testSheet 1'!A3", "'testSheet 1'", 'A3', 'A3'],
153			["'testSheet1'!A2", "'testSheet1'", 'A2', 'A2'],
154			["'testSheet 2'!A1", "'testSheet 2'", 'A1', 'A1'],
155			];
156			}
157
158			/**
159			* @param string $range
160			* @param string $expectTitle
161			* @param string $expectCell
162			* @param string $expectCell2
163			*
164			* @dataProvider extractSheetTitleProvider
165			*/
166			public function testExtractSheetTitle($range, $expectTitle, $expectCell, $expectCell2): void
167			{
168			// only cell reference
169			self::assertSame($expectCell, Worksheet::extractSheetTitle($range));
170			// with title in array
171			$arRange = Worksheet::extractSheetTitle($range, true);
172			self::assertSame($expectTitle, $arRange[0]);
173			self::assertSame($expectCell2, $arRange[1]);
174			}
175
176			/**
177			* Fix https://github.com/PHPOffice/PhpSpreadsheet/issues/868 when cells are not removed correctly
178			* on row deletion.
179			*/
180			public function testRemoveCellsCorrectlyWhenRemovingRow(): void
181			{
182			$workbook = new Spreadsheet();
183			$worksheet = $workbook->getActiveSheet();
184			$worksheet->getCell('A2')->setValue('A2');
185			$worksheet->getCell('C1')->setValue('C1');
186			$worksheet->removeRow(1);
187			self::assertEquals(
188			'A2',
189			$worksheet->getCell('A1')->getValue()
190			);
191			self::assertNull(
192			$worksheet->getCell('C1')->getValue()
193			);
194			}
195
196			public function removeColumnProvider(): array
197			{
198			return [
199			'Remove first column' => [
200			[
201			['A1', 'B1', 'C1'],
202			['A2', 'B2', 'C2'],
203			],
204			'A',
205			1,
206			[
207			['B1', 'C1'],
208			['B2', 'C2'],
209			],
210			'B',
211			],
212			'Remove middle column' => [
213			[
214			['A1', 'B1', 'C1'],
215			['A2', 'B2', 'C2'],
216			],
217			'B',
218			1,
219			[
220			['A1', 'C1'],
221			['A2', 'C2'],
222			],
223			'B',
224			],
225			'Remove last column' => [
226			[
227			['A1', 'B1', 'C1'],
228			['A2', 'B2', 'C2'],
229			],
230			'C',
231			1,
232			[
233			['A1', 'B1'],
234			['A2', 'B2'],
235			],
236			'B',
237			],
238			'Remove a column out of range' => [
239			[
240			['A1', 'B1', 'C1'],
241			['A2', 'B2', 'C2'],
242			],
243			'D',
244			1,
245			[
246			['A1', 'B1', 'C1'],
247			['A2', 'B2', 'C2'],
248			],
249			'C',
250			],
251			'Remove multiple columns' => [
252			[
253			['A1', 'B1', 'C1'],
254			['A2', 'B2', 'C2'],
255			],
256			'B',
257			5,
258			[
259			['A1'],
260			['A2'],
261			],
262			'A',
263			],
264			];
265			}
266
267			/**
268			* @dataProvider removeColumnProvider
269			*/
270			public function testRemoveColumn(
271			array $initialData,
272			string $columnToBeRemoved,
273			int $columnsToBeRemoved,
274			array $expectedData,
275			string $expectedHighestColumn
276			): void {
277			$spreadsheet = new Spreadsheet();
278			$worksheet = $spreadsheet->getActiveSheet();
279			$worksheet->fromArray($initialData);
280
281			$worksheet->removeColumn($columnToBeRemoved, $columnsToBeRemoved);
282
283			self::assertSame($expectedHighestColumn, $worksheet->getHighestColumn());
284			self::assertSame($expectedData, $worksheet->toArray());
285			}
286
287			public function removeRowsProvider(): array
288			{
289			return [
290			'Remove all rows except first one' => [
291			[
292			['A1', 'B1', 'C1'],
293			['A2', 'B2', 'C2'],
294			['A3', 'B3', 'C3'],
295			['A4', 'B4', 'C4'],
296			],
297			2,
298			3,
299			[
300			['A1', 'B1', 'C1'],
301			],
302			1,
303			],
304			'Remove all rows except last one' => [
305			[
306			['A1', 'B1', 'C1'],
307			['A2', 'B2', 'C2'],
308			['A3', 'B3', 'C3'],
309			['A4', 'B4', 'C4'],
310			],
311			1,
312			3,
313			[
314			['A4', 'B4', 'C4'],
315			],
316			1,
317			],
318			'Remove last row' => [
319			[
320			['A1', 'B1', 'C1'],
321			['A2', 'B2', 'C2'],
322			['A3', 'B3', 'C3'],
323			['A4', 'B4', 'C4'],
324			],
325			4,
326			1,
327			[
328			['A1', 'B1', 'C1'],
329			['A2', 'B2', 'C2'],
330			['A3', 'B3', 'C3'],
331			],
332			3,
333			],
334			'Remove first row' => [
335			[
336			['A1', 'B1', 'C1'],
337			['A2', 'B2', 'C2'],
338			['A3', 'B3', 'C3'],
339			['A4', 'B4', 'C4'],
340			],
341			1,
342			1,
343			[
344			['A2', 'B2', 'C2'],
345			['A3', 'B3', 'C3'],
346			['A4', 'B4', 'C4'],
347			],
348			3,
349			],
350			'Remove all rows except first and last' => [
351			[
352			['A1', 'B1', 'C1'],
353			['A2', 'B2', 'C2'],
354			['A3', 'B3', 'C3'],
355			['A4', 'B4', 'C4'],
356			],
357			2,
358			2,
359			[
360			['A1', 'B1', 'C1'],
361			['A4', 'B4', 'C4'],
362			],
363			2,
364			],
365			'Remove non existing rows' => [
366			[
367			['A1', 'B1', 'C1'],
368			['A2', 'B2', 'C2'],
369			['A3', 'B3', 'C3'],
370			['A4', 'B4', 'C4'],
371			],
372			2,
373			10,
374			[
375			['A1', 'B1', 'C1'],
376			],
377			1,
378			],
379			'Remove only non existing rows' => [
380			[
381			['A1', 'B1', 'C1'],
382			['A2', 'B2', 'C2'],
383			['A3', 'B3', 'C3'],
384			['A4', 'B4', 'C4'],
385			],
386			5,
387			10,
388			[
389			['A1', 'B1', 'C1'],
390			['A2', 'B2', 'C2'],
391			['A3', 'B3', 'C3'],
392			['A4', 'B4', 'C4'],
393			],
394			4,
395			],
396			];
397			}
398
399			/**
400			* @dataProvider removeRowsProvider
401			*/
402			public function testRemoveRows(
403			array $initialData,
404			int $rowToRemove,
405			int $rowsQtyToRemove,
406			array $expectedData,
407			int $expectedHighestRow
408			): void {
409			$workbook = new Spreadsheet();
410			$worksheet = $workbook->getActiveSheet();
411			$worksheet->fromArray($initialData);
412
413			$worksheet->removeRow($rowToRemove, $rowsQtyToRemove);
414
415			self::assertSame($expectedData, $worksheet->toArray());
416			self::assertSame($expectedHighestRow, $worksheet->getHighestRow());
417			}
418
419			private static function getPopulatedSheetForEmptyRowTest(Spreadsheet $spreadsheet): Worksheet
420			{
421			$sheet = $spreadsheet->getActiveSheet();
422			$sheet->setCellValueExplicit('A1', 'Hello World', DataType::TYPE_STRING);
423			$sheet->setCellValueExplicit('B3', null, DataType::TYPE_NULL);
424			$sheet->setCellValueExplicit('B4', '', DataType::TYPE_STRING);
425			$sheet->setCellValueExplicit('B5', null, DataType::TYPE_NULL);
426			$sheet->setCellValueExplicit('C5', '', DataType::TYPE_STRING);
427			$sheet->setCellValueExplicit('B6', null, DataType::TYPE_NULL);
428			$sheet->setCellValueExplicit('C6', 'PHP', DataType::TYPE_STRING);
429			$sheet->setCellValueExplicit('B7', '', DataType::TYPE_STRING);
430			$sheet->setCellValueExplicit('C7', 'PHP', DataType::TYPE_STRING);
431			$sheet->setCellValueExplicit('B8', null, DataType::TYPE_NULL);
432			$sheet->setCellValueExplicit('C8', '', DataType::TYPE_STRING);
433			$sheet->setCellValueExplicit('D8', 'PHP', DataType::TYPE_STRING);
434
435			return $sheet;
436			}
437
438			private static function getPopulatedSheetForEmptyColumnTest(Spreadsheet $spreadsheet): Worksheet
439			{
440			$sheet = $spreadsheet->getActiveSheet();
441			$sheet->setCellValueExplicit('A1', 'Hello World', DataType::TYPE_STRING);
442			$sheet->setCellValueExplicit('C2', null, DataType::TYPE_NULL);
443			$sheet->setCellValueExplicit('D2', '', DataType::TYPE_STRING);
444			$sheet->setCellValueExplicit('E2', null, DataType::TYPE_NULL);
445			$sheet->setCellValueExplicit('E3', '', DataType::TYPE_STRING);
446			$sheet->setCellValueExplicit('F2', null, DataType::TYPE_NULL);
447			$sheet->setCellValueExplicit('F3', 'PHP', DataType::TYPE_STRING);
448			$sheet->setCellValueExplicit('G2', '', DataType::TYPE_STRING);
449			$sheet->setCellValueExplicit('G3', 'PHP', DataType::TYPE_STRING);
450			$sheet->setCellValueExplicit('H2', null, DataType::TYPE_NULL);
451			$sheet->setCellValueExplicit('H3', '', DataType::TYPE_STRING);
452			$sheet->setCellValueExplicit('H4', 'PHP', DataType::TYPE_STRING);
453
454			return $sheet;
455			}
456
457			/**
458			* @dataProvider emptyRowProvider
459			*/
460			public function testIsEmptyRow(int $rowId, bool $expectedEmpty): void
461			{
462			$spreadsheet = new Spreadsheet();
463			$sheet = self::getPopulatedSheetForEmptyRowTest($spreadsheet);
464
465			$isEmpty = $sheet->isEmptyRow($rowId, CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL \| CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL);
466
467			self::assertSame($expectedEmpty, $isEmpty);
468			$spreadsheet->disconnectWorksheets();
469			}
470
471			public function emptyRowProvider(): array
472			{
473			return [
474			[1, false],
475			[2, true],
476			[3, true],
477			[4, true],
478			[5, true],
479			[6, false],
480			[7, false],
481			[8, false],
482			[9, true],
483			];
484			}
485
486			/**
487			* @dataProvider emptyColumnProvider
488			*/
489			public function testIsEmptyColumn(string $columnId, bool $expectedEmpty): void
490			{
491			$spreadsheet = new Spreadsheet();
492			$sheet = self::getPopulatedSheetForEmptyColumnTest($spreadsheet);
493
494			$isEmpty = $sheet->isEmptyColumn($columnId, CellIterator::TREAT_EMPTY_STRING_AS_EMPTY_CELL \| CellIterator::TREAT_NULL_VALUE_AS_EMPTY_CELL);
495
496			self::assertSame($expectedEmpty, $isEmpty);
497			$spreadsheet->disconnectWorksheets();
498			}
499
500			public function emptyColumnProvider(): array
501			{
502			return [
503			['A', false],
504			['B', true],
505			['C', true],
506			['D', true],
507			['E', true],
508			['F', false],
509			['G', false],
510			['H', false],
511			['I', true],
512			];
513			}
514
515			public function testGetTableNames(): void
516			{
517			$reader = new Xlsx();
518			$spreadsheet = $reader->load('tests/data/Worksheet/Table/TableFormulae.xlsx');
519			$worksheet = $spreadsheet->getActiveSheet();
520
521			$tables = $worksheet->getTableNames();
522			self::assertSame(['DeptSales'], $tables);
523			}
524
525			public function testGetTableByName(): void
526			{
527			$reader = new Xlsx();
528			$spreadsheet = $reader->load('tests/data/Worksheet/Table/TableFormulae.xlsx');
529			$worksheet = $spreadsheet->getActiveSheet();
530
531			$table = $worksheet->getTableByName('Non-existent Table');
532			self::assertNull($table);
533
534			$table = $worksheet->getTableByName('DeptSales');
535			self::assertInstanceOf(Table::class, $table);
536			}
537
538			/**
539			* @dataProvider toArrayHiddenRowsProvider
540			*/
541			public function testHiddenRows(
542			array $initialData,
543			array $hiddenRows,
544			array $expectedData
545			): void {
546			$workbook = new Spreadsheet();
547			$worksheet = $workbook->getActiveSheet();
548			$worksheet->fromArray($initialData);
549
550			foreach ($hiddenRows as $hiddenRow) {
551			$worksheet->getRowDimension($hiddenRow)->setVisible(false);
552			}
553
554			self::assertSame($expectedData, $worksheet->toArray(null, false, false, true, true));
555			}
556
557			public function toArrayHiddenRowsProvider(): array
558			{
559			return [
560			[
561			[[1], [2], [3], [4], [5], [6]],
562			[2, 3, 5],
563			[1 => ['A' => 1], 4 => ['A' => 4], 6 => ['A' => 6]],
564			],
565			[
566			[[1], [2], [3], [4], [5], [6]],
567			[1, 3, 6],
568			[2 => ['A' => 2], 4 => ['A' => 4], 5 => ['A' => 5]],
569			],
570			];
571			}
572
573			/**
574			* @dataProvider toArrayHiddenColumnsProvider
575			*/
576			public function testHiddenColumns(
577			array $initialData,
578			array $hiddenColumns,
579			array $expectedData
580			): void {
581			$workbook = new Spreadsheet();
582			$worksheet = $workbook->getActiveSheet();
583			$worksheet->fromArray($initialData);
584
585			foreach ($hiddenColumns as $hiddenColumn) {
586			$worksheet->getColumnDimension($hiddenColumn)->setVisible(false);
587			}
588
589			self::assertSame($expectedData, $worksheet->toArray(null, false, false, true, true));
590			}
591
592			public function toArrayHiddenColumnsProvider(): array
593			{
594			return [
595			[
596			['A', 'B', 'C', 'D', 'E', 'F'],
597			['B', 'C', 'E'],
598			[1 => ['A' => 'A', 'D' => 'D', 'F' => 'F']],
599			],
600			[
601			['A', 'B', 'C', 'D', 'E', 'F'],
602			['A', 'C', 'F'],
603			[1 => ['B' => 'B', 'D' => 'D', 'E' => 'E']],
604			],
605			];
606			}
607
608			/**
609			* @dataProvider rangeToArrayProvider
610			*/
611			public function testRangeToArrayWithCellRangeObject(array $expected, string $fromCell, string $toCell): void
612			{
613			$initialData = array_chunk(range('A', 'Y'), 5);
614
615			$workbook = new Spreadsheet();
616			$worksheet = $workbook->getActiveSheet();
617			$worksheet->fromArray($initialData);
618
619			$cellRange = new CellRange(new CellAddress($fromCell), new CellAddress($toCell));
620
621			self::assertSame($expected, $worksheet->rangeToArray($cellRange));
622			}
623
624			public function rangeToArrayProvider(): array
625			{
626			return [
627			[
628			[['A', 'B'], ['F', 'G']],
629			'A1', 'B2',
630			],
631			[
632			[['G', 'H', 'I'], ['L', 'M', 'N'], ['Q', 'R', 'S']],
633			'B2', 'D4',
634			],
635			];
636			}
637			}
638

PHPOffice / PhpSpreadsheet

Push — master ( f4e897...378d46 )

testRemoveCellsCorrectlyWhenRemovingRow() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like