VStackTest::testTables() - Code Metrics - Inspection of "Merge pull request #4492 from oleibman/vstack" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 1825c7...bd792e )

unknown

created 2025-06-01 16:07 UTC

VStackTest::testTables() A

↳ Parent: VStackTest

Complexity

Conditions	1
Paths	1

Size

Total Lines	61
Code Lines	50

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	50
c	1
b	0
f	0
dl	0
loc	61
rs	9.0909
cc	1
nc	1
nop	0

How to fix Long Method

<?php

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\LookupRef;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Table;

/**
 * Many of these tests are derived from
 * https://exceljet.net/functions/vstack-function.
 */
class VStackTest extends AllSetupTeardown
{
    public static function testVstack1(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)
            ->setInstanceArrayReturnType(
                Calculation::RETURN_ARRAY_AS_ARRAY
            );
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B2', 'Array1');
        $sheet->setCellValue('B3', 'Red');
        $sheet->setCellValue('B4', 'Blue');
        $sheet->setCellValue('B5', 'Green');
        $sheet->setCellValue('B7', 'Array2');
        $sheet->setCellValue('B8', 'Green');
        $sheet->setCellValue('B9', 'Red');
        $sheet->setCellValue('D2', 'Result');
        $sheet->setCellValue('D3', '=VSTACK(B3:B5,B8:B9)');
        $expected = [
            ['Red'],
            ['Blue'],
            ['Green'],
            ['Green'],
            ['Red'],
        ];
        self::assertSame($expected, $sheet->getCell('D3')->getCalculatedValue());

        $sheet->setCellValue('F2', '=VSTACK(B2,B8:B9)');
        $expected = [
            ['Array1'],
            ['Green'],
            ['Red'],
        ];
        self::assertSame($expected, $sheet->getCell('F2')->getCalculatedValue(), 'one single-cell argument');

        $spreadsheet->disconnectWorksheets();
    }

    public static function testVstack2(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)
            ->setInstanceArrayReturnType(
                Calculation::RETURN_ARRAY_AS_ARRAY
            );
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B3', 'Red');
        $sheet->setCellValue('B4', 'Blue');
        $sheet->setCellValue('B5', 'Green');
        $sheet->setCellValue('B6', 'Blue');
        $sheet->setCellValue('B7', 'Red');
        $sheet->setCellValue('C3', 12);
        $sheet->setCellValue('C4', 9);
        $sheet->setCellValue('C5', 10);
        $sheet->setCellValue('C5', 10);
        $sheet->setCellValue('C6', 11);
        $sheet->setCellValue('C7', 8);
        $sheet->setCellValue('E3', '=VSTACK({"Color","Qty"},B3:C7)');
        $expected = [
            ['Color', 'Qty'],
            ['Red', 12],
            ['Blue', 9],
            ['Green', 10],
            ['Blue', 11],
            ['Red', 8],
        ];
        self::assertSame($expected, $sheet->getCell('E3')->getCalculatedValue());
        $sheet->setCellValue('A1', 'Purple');
        $sheet->setCellValue('A2', 'Orange');
        $sheet->setCellValue('H3', '=VSTACK({"Color","Qty"},A1:A2,B3:C7)');
        $expected = [
            ['Color', 'Qty'],
            ['Purple', '#N/A'],
            ['Orange', '#N/A'],
            ['Red', 12],
            ['Blue', 9],
            ['Green', 10],
            ['Blue', 11],
            ['Red', 8],
        ];
        self::assertSame($expected, $sheet->getCell('H3')->getCalculatedValue());
        $spreadsheet->disconnectWorksheets();
    }

    /**
     * Excel has a problem here.
     * If it reads VSTACK(Table1, Table2) without square
     * brackets after Table1/2, it calculates the result as #NAME?.
     * You can then just edit the formula without making
     * any changes, and it will calculate it correctly,
     * but it will add in the brackets when saving.
     * This seems pretty buggy to me.
     * PhpSpreadsheet will handle the formula with or without
     * the brackets, but you should specify them to avoid
     * problems with Excel.
     */
    public static function testTables(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)
            ->setInstanceArrayReturnType(
                Calculation::RETURN_ARRAY_AS_ARRAY
            );
        $sheet = $spreadsheet->getActiveSheet();

        $data = [
            ['Date', 'Color', 'Qty'],
            [Date::stringToExcel('2021-04-03'), 'Red', 12],
            [Date::stringToExcel('2021-04-07'), 'Blue', 9],
            [Date::stringToExcel('2021-04-11'), 'Green', 10],
            [Date::stringToExcel('2021-04-15'), 'Blue', 11],
            [Date::stringToExcel('2021-04-20'), 'Red', 8],
        ];
        $sheet->fromArray($data, null, 'B4', true);
        $table = new Table('B4:D9', 'Table1');
        $sheet->addTable($table);

        $data = [
            ['Date', 'Color', 'Qty'],
            [Date::stringToExcel('2021-05-05'), 'Red', 12],
            [Date::stringToExcel('2021-05-12'), 'Blue', 9],
            [Date::stringToExcel('2021-05-18'), 'Green', 10],
            [Date::stringToExcel('2021-05-21'), 'Blue', 11],
            [Date::stringToExcel('2021-05-28'), 'Green', 6],
        ];
        $sheet->fromArray($data, null, 'B11', true);
        $table = new Table('B11:D16', 'Table2');
        $sheet->addTable($table);

        $sheet->setCellValue('G4', 'Date');
        $sheet->setCellValue('H4', 'Color');
        $sheet->setCellValue('I4', 'Qty');
        $sheet->setCellValue('G5', '=VSTACK(Table1[],Table2[])');
        $sheet->getCell('G5')->getCalculatedValue();

        $sheet->getStyle('B4:B16')
            ->getNumberFormat()
            ->setFormatCode('d-mmm');
        $sheet->getStyle('G5:G14')
            ->getNumberFormat()
            ->setFormatCode('d-mmm');
        $expected = [
            ['3-Apr', 'Red', '12'],
            ['7-Apr', 'Blue', '9'],
            ['11-Apr', 'Green', '10'],
            ['15-Apr', 'Blue', '11'],
            ['20-Apr', 'Red', '8'],
            ['5-May', 'Red', '12'],
            ['12-May', 'Blue', '9'],
            ['18-May', 'Green', '10'],
            ['21-May', 'Blue', '11'],
            ['28-May', 'Green', '6'],
        ];
        $actual = $sheet->rangeToArray('G5:I14', null, true, true);
        self::assertSame($expected, $actual);

        $spreadsheet->disconnectWorksheets();
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace PhpOffice\PhpSpreadsheetTests\Calculation\Functions\LookupRef;
6
7			use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
8			use PhpOffice\PhpSpreadsheet\Shared\Date;
9			use PhpOffice\PhpSpreadsheet\Spreadsheet;
10			use PhpOffice\PhpSpreadsheet\Worksheet\Table;
11
12			/**
13			* Many of these tests are derived from
14			* https://exceljet.net/functions/vstack-function.
15			*/
16			class VStackTest extends AllSetupTeardown
17			{
18			public static function testVstack1(): void
19			{
20			$spreadsheet = new Spreadsheet();
21			Calculation::getInstance($spreadsheet)
22			->setInstanceArrayReturnType(
23			Calculation::RETURN_ARRAY_AS_ARRAY
24			);
25			$sheet = $spreadsheet->getActiveSheet();
26			$sheet->setCellValue('B2', 'Array1');
27			$sheet->setCellValue('B3', 'Red');
28			$sheet->setCellValue('B4', 'Blue');
29			$sheet->setCellValue('B5', 'Green');
30			$sheet->setCellValue('B7', 'Array2');
31			$sheet->setCellValue('B8', 'Green');
32			$sheet->setCellValue('B9', 'Red');
33			$sheet->setCellValue('D2', 'Result');
34			$sheet->setCellValue('D3', '=VSTACK(B3:B5,B8:B9)');
35			$expected = [
36			['Red'],
37			['Blue'],
38			['Green'],
39			['Green'],
40			['Red'],
41			];
42			self::assertSame($expected, $sheet->getCell('D3')->getCalculatedValue());
43
44			$sheet->setCellValue('F2', '=VSTACK(B2,B8:B9)');
45			$expected = [
46			['Array1'],
47			['Green'],
48			['Red'],
49			];
50			self::assertSame($expected, $sheet->getCell('F2')->getCalculatedValue(), 'one single-cell argument');
51
52			$spreadsheet->disconnectWorksheets();
53			}
54
55			public static function testVstack2(): void
56			{
57			$spreadsheet = new Spreadsheet();
58			Calculation::getInstance($spreadsheet)
59			->setInstanceArrayReturnType(
60			Calculation::RETURN_ARRAY_AS_ARRAY
61			);
62			$sheet = $spreadsheet->getActiveSheet();
63			$sheet->setCellValue('B3', 'Red');
64			$sheet->setCellValue('B4', 'Blue');
65			$sheet->setCellValue('B5', 'Green');
66			$sheet->setCellValue('B6', 'Blue');
67			$sheet->setCellValue('B7', 'Red');
68			$sheet->setCellValue('C3', 12);
69			$sheet->setCellValue('C4', 9);
70			$sheet->setCellValue('C5', 10);
71			$sheet->setCellValue('C5', 10);
72			$sheet->setCellValue('C6', 11);
73			$sheet->setCellValue('C7', 8);
74			$sheet->setCellValue('E3', '=VSTACK({"Color","Qty"},B3:C7)');
75			$expected = [
76			['Color', 'Qty'],
77			['Red', 12],
78			['Blue', 9],
79			['Green', 10],
80			['Blue', 11],
81			['Red', 8],
82			];
83			self::assertSame($expected, $sheet->getCell('E3')->getCalculatedValue());
84			$sheet->setCellValue('A1', 'Purple');
85			$sheet->setCellValue('A2', 'Orange');
86			$sheet->setCellValue('H3', '=VSTACK({"Color","Qty"},A1:A2,B3:C7)');
87			$expected = [
88			['Color', 'Qty'],
89			['Purple', '#N/A'],
90			['Orange', '#N/A'],
91			['Red', 12],
92			['Blue', 9],
93			['Green', 10],
94			['Blue', 11],
95			['Red', 8],
96			];
97			self::assertSame($expected, $sheet->getCell('H3')->getCalculatedValue());
98			$spreadsheet->disconnectWorksheets();
99			}
100
101			/**
102			* Excel has a problem here.
103			* If it reads VSTACK(Table1, Table2) without square
104			* brackets after Table1/2, it calculates the result as #NAME?.
105			* You can then just edit the formula without making
106			* any changes, and it will calculate it correctly,
107			* but it will add in the brackets when saving.
108			* This seems pretty buggy to me.
109			* PhpSpreadsheet will handle the formula with or without
110			* the brackets, but you should specify them to avoid
111			* problems with Excel.
112			*/
113			public static function testTables(): void
114			{
115			$spreadsheet = new Spreadsheet();
116			Calculation::getInstance($spreadsheet)
117			->setInstanceArrayReturnType(
118			Calculation::RETURN_ARRAY_AS_ARRAY
119			);
120			$sheet = $spreadsheet->getActiveSheet();
121
122			$data = [
123			['Date', 'Color', 'Qty'],
124			[Date::stringToExcel('2021-04-03'), 'Red', 12],
125			[Date::stringToExcel('2021-04-07'), 'Blue', 9],
126			[Date::stringToExcel('2021-04-11'), 'Green', 10],
127			[Date::stringToExcel('2021-04-15'), 'Blue', 11],
128			[Date::stringToExcel('2021-04-20'), 'Red', 8],
129			];
130			$sheet->fromArray($data, null, 'B4', true);
131			$table = new Table('B4:D9', 'Table1');
132			$sheet->addTable($table);
133
134			$data = [
135			['Date', 'Color', 'Qty'],
136			[Date::stringToExcel('2021-05-05'), 'Red', 12],
137			[Date::stringToExcel('2021-05-12'), 'Blue', 9],
138			[Date::stringToExcel('2021-05-18'), 'Green', 10],
139			[Date::stringToExcel('2021-05-21'), 'Blue', 11],
140			[Date::stringToExcel('2021-05-28'), 'Green', 6],
141			];
142			$sheet->fromArray($data, null, 'B11', true);
143			$table = new Table('B11:D16', 'Table2');
144			$sheet->addTable($table);
145
146			$sheet->setCellValue('G4', 'Date');
147			$sheet->setCellValue('H4', 'Color');
148			$sheet->setCellValue('I4', 'Qty');
149			$sheet->setCellValue('G5', '=VSTACK(Table1[],Table2[])');
150			$sheet->getCell('G5')->getCalculatedValue();
151
152			$sheet->getStyle('B4:B16')
153			->getNumberFormat()
154			->setFormatCode('d-mmm');
155			$sheet->getStyle('G5:G14')
156			->getNumberFormat()
157			->setFormatCode('d-mmm');
158			$expected = [
159			['3-Apr', 'Red', '12'],
160			['7-Apr', 'Blue', '9'],
161			['11-Apr', 'Green', '10'],
162			['15-Apr', 'Blue', '11'],
163			['20-Apr', 'Red', '8'],
164			['5-May', 'Red', '12'],
165			['12-May', 'Blue', '9'],
166			['18-May', 'Green', '10'],
167			['21-May', 'Blue', '11'],
168			['28-May', 'Green', '6'],
169			];
170			$actual = $sheet->rangeToArray('G5:I14', null, true, true);
171			self::assertSame($expected, $actual);
172
173			$spreadsheet->disconnectWorksheets();
174			}
175			}
176

PHPOffice / PhpSpreadsheet

Push — master ( 1825c7...bd792e )

VStackTest::testTables() A

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like