ArrayFunctionsSpillTest::testArrayOutput() - Code Metrics - Inspection of "Excel Dynamic Arrays (Avoid Adding At-Signs to For..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Pull Request — master (#3962)

by Owen

created 2024-08-02 17:49 UTC

ArrayFunctionsSpillTest::testArrayOutput() B

↳ Parent: ArrayFunctionsSpillTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	70
Code Lines	60

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	60
c	1
b	0
f	0
dl	0
loc	70
rs	8.8727
cc	1
nc	1
nop	0

How to fix Long Method

<?php

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests\Functional;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PHPUnit\Framework\TestCase;

class ArrayFunctionsSpillTest extends TestCase
{
    public function testArrayOutput(): void
    {
        $spreadsheet = new Spreadsheet();
        $calculation = Calculation::getInstance($spreadsheet);
        $calculation->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B5', 'OCCUPIED');

        $columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [5]];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
        $expected = [['#SPILL!'], [null], [null], [null], ['OCCUPIED']];
        self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'spill with B5 unchanged');
        self::assertFalse($sheet->isCellInSpillRange('B1'));
        self::assertFalse($sheet->isCellInSpillRange('B2'));
        self::assertFalse($sheet->isCellInSpillRange('B3'));
        self::assertFalse($sheet->isCellInSpillRange('B4'));
        self::assertFalse($sheet->isCellInSpillRange('B5'));
        self::assertFalse($sheet->isCellInSpillRange('Z9'));
        $calculation->clearCalculationCache();

        $columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [4]];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
        $expected = [[1], [2], [3], [4], ['OCCUPIED']];
        self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'fill B1:B4 with B5 unchanged');
        self::assertFalse($sheet->isCellInSpillRange('B1'));
        self::assertTrue($sheet->isCellInSpillRange('B2'));
        self::assertTrue($sheet->isCellInSpillRange('B3'));
        self::assertTrue($sheet->isCellInSpillRange('B4'));
        self::assertFalse($sheet->isCellInSpillRange('B5'));
        self::assertFalse($sheet->isCellInSpillRange('Z9'));
        $calculation->clearCalculationCache();

        $columnArray = [[1], [3], [3], [3], [3], [3], [3], [3], [3], [3], [3], [3]];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
        $expected = [[1], [3], [null], [null], ['OCCUPIED']];
        self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'fill B1:B2(changed from prior) set B3:B4 to null B5 unchanged');
        self::assertFalse($sheet->isCellInSpillRange('B1'));
        self::assertTrue($sheet->isCellInSpillRange('B2'));
        self::assertFalse($sheet->isCellInSpillRange('B3'));
        self::assertFalse($sheet->isCellInSpillRange('B4'));
        self::assertFalse($sheet->isCellInSpillRange('B5'));
        self::assertFalse($sheet->isCellInSpillRange('Z9'));
        $calculation->clearCalculationCache();

        $columnArray = [[1], [2], [3], [3], [3], [3], [3], [3], [3], [3], [3], [3]];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
        $expected = [[1], [2], [3], [null], ['OCCUPIED']];
        self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'fill B1:B3(B2 changed from prior) set B4 to null B5 unchanged');
        $calculation->clearCalculationCache();

        $columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [5]];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
        $expected = [['#SPILL!'], [null], [null], [null], ['OCCUPIED']];
        self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'spill clears B2:B4 with B5 unchanged');
        $calculation->clearCalculationCache();

        $sheet->setCellValue('Z1', '=SORT({7;5;1})');
        $sheet->getCell('Z1')->getCalculatedValue(); // populates Z1-Z3
        self::assertTrue($sheet->isCellInSpillRange('Z2'));
        self::assertTrue($sheet->isCellInSpillRange('Z3'));
        self::assertFalse($sheet->isCellInSpillRange('Z4'));
        self::assertFalse($sheet->isCellInSpillRange('Z1'));

        $spreadsheet->disconnectWorksheets();
    }

    public function testNonArrayOutput(): void
    {
        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B5', 'OCCUPIED');

        $columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [4]];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
        $expected = [[1], [null], [null], [null], ['OCCUPIED']];
        self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'only fill B1');
        self::assertFalse($sheet->isCellInSpillRange('B1'));
        self::assertFalse($sheet->isCellInSpillRange('B2'));
        self::assertFalse($sheet->isCellInSpillRange('B3'));
        self::assertFalse($sheet->isCellInSpillRange('B4'));
        self::assertFalse($sheet->isCellInSpillRange('B5'));
        self::assertFalse($sheet->isCellInSpillRange('Z9'));

        $spreadsheet->disconnectWorksheets();
    }

    public function testSpillOperator(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray([
            ['Product', 'Quantity', 'Price', 'Cost'],
            ['Apple', 20, 0.75],
            ['Kiwi', 8, 0.80],
            ['Lemon', 12, 0.70],
            ['Mango', 5, 1.75],
            ['Pineapple', 2, 2.00],
            ['Total'],
        ]);
        $sheet->getCell('D2')->setValue('=B2:B6*C2:C6');
        $sheet->getCell('D7')->setValue('=SUM(D2#)');
        $sheet->getStyle('A1:D1')->getFont()->setBold(true);
        $sheet->getStyle('C2:D6')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);
        self::assertEqualsWithDelta(
            [
                ['Cost'],
                [15.0],
                [6.4],
                [8.4],
                [8.75],
                [4.0],
                [42.55],
            ],
            $sheet->rangeToArray('D1:D7', calculateFormulas: true, formatData: false, reduceArrays: true),
            1.0e-10
        );
        $sheet->getCell('G2')->setValue('=B2#');
        self::assertSame('#REF!', $sheet->getCell('G2')->getCalculatedValue());
        $spreadsheet->disconnectWorksheets();
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace PhpOffice\PhpSpreadsheetTests\Functional;
6
7			use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
8			use PhpOffice\PhpSpreadsheet\Spreadsheet;
9			use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
10			use PHPUnit\Framework\TestCase;
11
12			class ArrayFunctionsSpillTest extends TestCase
13			{
14			public function testArrayOutput(): void
15			{
16			$spreadsheet = new Spreadsheet();
17			$calculation = Calculation::getInstance($spreadsheet);
18			$calculation->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
19
20			$sheet = $spreadsheet->getActiveSheet();
21			$sheet->setCellValue('B5', 'OCCUPIED');
22
23			$columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [5]];
24			$sheet->fromArray($columnArray, 'A1');
25			$sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
26			$expected = [['#SPILL!'], [null], [null], [null], ['OCCUPIED']];
27			self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'spill with B5 unchanged');
28			self::assertFalse($sheet->isCellInSpillRange('B1'));
29			self::assertFalse($sheet->isCellInSpillRange('B2'));
30			self::assertFalse($sheet->isCellInSpillRange('B3'));
31			self::assertFalse($sheet->isCellInSpillRange('B4'));
32			self::assertFalse($sheet->isCellInSpillRange('B5'));
33			self::assertFalse($sheet->isCellInSpillRange('Z9'));
34			$calculation->clearCalculationCache();
35
36			$columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [4]];
37			$sheet->fromArray($columnArray, 'A1');
38			$sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
39			$expected = [[1], [2], [3], [4], ['OCCUPIED']];
40			self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'fill B1:B4 with B5 unchanged');
41			self::assertFalse($sheet->isCellInSpillRange('B1'));
42			self::assertTrue($sheet->isCellInSpillRange('B2'));
43			self::assertTrue($sheet->isCellInSpillRange('B3'));
44			self::assertTrue($sheet->isCellInSpillRange('B4'));
45			self::assertFalse($sheet->isCellInSpillRange('B5'));
46			self::assertFalse($sheet->isCellInSpillRange('Z9'));
47			$calculation->clearCalculationCache();
48
49			$columnArray = [[1], [3], [3], [3], [3], [3], [3], [3], [3], [3], [3], [3]];
50			$sheet->fromArray($columnArray, 'A1');
51			$sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
52			$expected = [[1], [3], [null], [null], ['OCCUPIED']];
53			self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'fill B1:B2(changed from prior) set B3:B4 to null B5 unchanged');
54			self::assertFalse($sheet->isCellInSpillRange('B1'));
55			self::assertTrue($sheet->isCellInSpillRange('B2'));
56			self::assertFalse($sheet->isCellInSpillRange('B3'));
57			self::assertFalse($sheet->isCellInSpillRange('B4'));
58			self::assertFalse($sheet->isCellInSpillRange('B5'));
59			self::assertFalse($sheet->isCellInSpillRange('Z9'));
60			$calculation->clearCalculationCache();
61
62			$columnArray = [[1], [2], [3], [3], [3], [3], [3], [3], [3], [3], [3], [3]];
63			$sheet->fromArray($columnArray, 'A1');
64			$sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
65			$expected = [[1], [2], [3], [null], ['OCCUPIED']];
66			self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'fill B1:B3(B2 changed from prior) set B4 to null B5 unchanged');
67			$calculation->clearCalculationCache();
68
69			$columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [5]];
70			$sheet->fromArray($columnArray, 'A1');
71			$sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
72			$expected = [['#SPILL!'], [null], [null], [null], ['OCCUPIED']];
73			self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'spill clears B2:B4 with B5 unchanged');
74			$calculation->clearCalculationCache();
75
76			$sheet->setCellValue('Z1', '=SORT({7;5;1})');
77			$sheet->getCell('Z1')->getCalculatedValue(); // populates Z1-Z3
78			self::assertTrue($sheet->isCellInSpillRange('Z2'));
79			self::assertTrue($sheet->isCellInSpillRange('Z3'));
80			self::assertFalse($sheet->isCellInSpillRange('Z4'));
81			self::assertFalse($sheet->isCellInSpillRange('Z1'));
82
83			$spreadsheet->disconnectWorksheets();
84			}
85
86			public function testNonArrayOutput(): void
87			{
88			$spreadsheet = new Spreadsheet();
89
90			$sheet = $spreadsheet->getActiveSheet();
91			$sheet->setCellValue('B5', 'OCCUPIED');
92
93			$columnArray = [[1], [2], [2], [2], [3], [3], [3], [3], [4], [4], [4], [4]];
94			$sheet->fromArray($columnArray, 'A1');
95			$sheet->setCellValue('B1', '=UNIQUE(A1:A12)');
96			$expected = [[1], [null], [null], [null], ['OCCUPIED']];
97			self::assertSame($expected, $sheet->rangeToArray('B1:B5', calculateFormulas: true, formatData: false, reduceArrays: true), 'only fill B1');
98			self::assertFalse($sheet->isCellInSpillRange('B1'));
99			self::assertFalse($sheet->isCellInSpillRange('B2'));
100			self::assertFalse($sheet->isCellInSpillRange('B3'));
101			self::assertFalse($sheet->isCellInSpillRange('B4'));
102			self::assertFalse($sheet->isCellInSpillRange('B5'));
103			self::assertFalse($sheet->isCellInSpillRange('Z9'));
104
105			$spreadsheet->disconnectWorksheets();
106			}
107
108			public function testSpillOperator(): void
109			{
110			$spreadsheet = new Spreadsheet();
111			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
112			$sheet = $spreadsheet->getActiveSheet();
113			$sheet->fromArray([
114			['Product', 'Quantity', 'Price', 'Cost'],
115			['Apple', 20, 0.75],
116			['Kiwi', 8, 0.80],
117			['Lemon', 12, 0.70],
118			['Mango', 5, 1.75],
119			['Pineapple', 2, 2.00],
120			['Total'],
121			]);
122			$sheet->getCell('D2')->setValue('=B2:B6*C2:C6');
123			$sheet->getCell('D7')->setValue('=SUM(D2#)');
124			$sheet->getStyle('A1:D1')->getFont()->setBold(true);
125			$sheet->getStyle('C2:D6')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);
126			self::assertEqualsWithDelta(
127			[
128			['Cost'],
129			[15.0],
130			[6.4],
131			[8.4],
132			[8.75],
133			[4.0],
134			[42.55],
135			],
136			$sheet->rangeToArray('D1:D7', calculateFormulas: true, formatData: false, reduceArrays: true),
137			1.0e-10
138			);
139			$sheet->getCell('G2')->setValue('=B2#');
140			self::assertSame('#REF!', $sheet->getCell('G2')->getCalculatedValue());
141			$spreadsheet->disconnectWorksheets();
142			}
143			}
144

PHPOffice / PhpSpreadsheet

Pull Request — master (#3962)

ArrayFunctionsSpillTest::testArrayOutput() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like