xny/vendor/phpoffice/phpspreadsheet/tests/PhpSpreadsheetTests/Worksheet/WorksheetTest.php

<?php

namespace PhpOffice\PhpSpreadsheetTests\Worksheet;

use Exception;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\CellIterator;
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'],
        ];
    }

    /**
     * @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],
        ];
    }
}