ArrayFunctions2Test::doPartijen() - 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

ArrayFunctions2Test::doPartijen() C

↳ Parent: ArrayFunctions2Test

Complexity

Conditions	12
Paths	84

Size

Total Lines	109
Code Lines	80

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
eloc	80
c	2
b	0
f	0
dl	0
loc	109
rs	6.0096
cc	12
nc	84
nop	1

How to fix Long Method Complexity

<?php

// not yet ready for prime time

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PHPUnit\Framework\TestCase;

// TODO - I think the spreadsheet below is too difficult for PhpSpreadsheet to calculate correctly.

class ArrayFunctions2Test extends TestCase
{
    private const STYLESIZE14 = [
        'font' => [
            'size' => 14,
        ],
    ];
    private const STYLEBOLD = [
        'font' => [
            'bold' => true,
        ],
    ];
    private const STYLEBOLD14 = [
        'font' => [
            'bold' => true,
            'size' => 14,
        ],
    ];
    private const STYLECENTER = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
        ],
    ];

    private const STYLETHICKBORDER = [
        'borders' => [
            'outline' => [
                'borderStyle' => Border::BORDER_THICK,
                'color' => ['argb' => '00000000'],
            ],
        ],
    ];

    private array $trn;

    private string $outputFile = '';

    protected function tearDown(): void
    {
        if ($this->outputFile !== '') {
            unlink($this->outputFile);
            $this->outputFile = '';
        }
    }

    private function odd(int $i): bool
    {
        return ($i % 2) === 1;
    }

    private function doPartijen(Worksheet $ws): int
    {
        $saring = explode("\n", $this->trn['PARINGEN']);
        $s = $this->trn['PLAYERSNOID'];
        $g = $this->trn['RONDEGAMES'];
        $KD = $this->trn['KALENDERDATA'];
        $si = $this->trn['PLAYERSIDS'];

        $a = [
            ['Wit', null, null, null, 'Zwart', null, null, null, 'Wit', 'Uitslag', 'Zwart', 'Opmerking', 'Datum'],
            ['Winstpunten', 'Weerstandspunten', 'punten', 'Tegenpunten', 'Winstpunten', 'Weerstandspunten', 'punten', 'Tegenpunten'],

        ];
        $ws->fromArray($a, null, 'A1');

        $ws->getStyle('A1:L1')->applyFromArray(self::STYLEBOLD);
        $ws->getStyle('A1:L1')->applyFromArray(self::STYLESIZE14);

        $lijn = 1;
        for ($i = 1; $i <= $this->trn['RONDEN']; ++$i) {//aantal ronden oneven->heen en even->terug
            $countSaring = count($saring);
            for ($j = 0; $j < $countSaring; ++$j) {//subronden
                ++$lijn;
                if (isset($KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['RONDE'])) {
                    $ws->setCellValue([9, $lijn], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['RONDE']);
                } else {
                    $ws->setCellValue([9, $lijn], 'Kalenderdata zijn niet(volledig) ingevuld');
                }
                if (isset($KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['TXT'])) {
                    $ws->setCellValue([10, $lijn], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['TXT']);
                } else {
                    $ws->setCellValue([10, $lijn], 'Kalenderdata zijn niet(volledig) ingevuld');
                }

                $ws->getStyle('A' . $lijn . ':L' . $lijn . '')->applyFromArray(self::STYLEBOLD14);

                $s2 = explode(' ', $saring[$j]);
                $counts2 = count($s2);
                for ($k = 0; $k < $counts2; ++$k) {//borden
                    if (trim($s2[$k]) == '') {
                        continue;
                    }
                    $s3 = explode('-', $s2[$k]); //wit-zwart
                    $s3[0] = (int) $s3[0];
                    $s3[1] = (int) $s3[1];
                    ++$lijn;
                    $ws->setCellValue([1, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",XLOOKUP($K' . $lijn . ',Spelers!$B$2:$B$' . (count($s) + 1) . ',Spelers!$C$2:$C$' . (count($s) + 1) . ',FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,4,FALSE))');
                    $ws->setCellValue([2, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",VLOOKUP($K' . $lijn . ',Spelers!$B$2:$D$' . (count($s) + 1) . ',3,FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,5,FALSE))');
                    $ws->setCellValue([3, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$B$2:$B$50,0,0))');
                    $ws->setCellValue([4, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$G$2:$G$50,0,0))');

                    $ws->setCellValue([5, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",VLOOKUP($I' . $lijn . ',Spelers!$B$2:$D$' . (count($s) + 1) . ',3,FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,6,FALSE))');
                    $ws->setCellValue([6, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",VLOOKUP($I' . $lijn . ',Spelers!$B$2:$D$' . (count($s) + 1) . ',3,FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,5,FALSE))');
                    $ws->setCellValue([7, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$C$2:$C$50,0,0))');
                    $ws->setCellValue([8, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$H$2:$H$50,0,0))');

                    if ($this->odd($i)) {
                        if (
                            isset($g[$i][$si[((int) $s3[0]) - 1]][$si[((int) $s3[1]) - 1]])
                        ) {
                            $pw = $g[$i][$si[((int) $s3[0]) - 1]][$si[((int) $s3[1]) - 1]];
                        } else {
                            $pw = ['SYMBOOLWIT' => '', 'SYMBOOLZWART' => '', 'UITSLAG' => '', 'OPMERKING' => '', 'DATUM' => ''];
                        }
                        $ws->setCellValue([9, $lijn], '=Spelers!$B$' . ($s3[0] + 1));
                        $ws->setCellValue([11, $lijn], '=Spelers!$B$' . ($s3[1] + 1));
                    } else {
                        if (
                            isset($g[$i][$si[((int) $s3[1]) - 1]][$si[((int) $s3[0]) - 1]])
                        ) {
                            $pw = $g[$i][$si[((int) $s3[1]) - 1]][$si[((int) $s3[0]) - 1]];
                        } else {
                            $pw = ['SYMBOOLWIT' => '', 'SYMBOOLZWART' => '', 'UITSLAG' => '', 'OPMERKING' => '', 'DATUM' => ''];
                        }
                        $ws->setCellValue([9, $lijn], '=Spelers!$B$' . ($s3[1] + 1));
                        $ws->setCellValue([11, $lijn], '=Spelers!$B$' . ($s3[0] + 1));
                    }
                    if ($pw['SYMBOOLWIT'] != '') {
                        $ws->setCellValue([10, $lijn], $pw['SYMBOOLWIT'] . '-' . $pw['SYMBOOLZWART']);
                    }
                    $ws->setCellValue([13, $lijn], $pw['OPMERKING']);
                    $ws->setCellValue([14, $lijn], $pw['DATUM']);

                    $this->doValidationPunten($ws, 'J' . $lijn);

                    $ws->getRowDimension($lijn)->setOutlineLevel(1);
                }
                ++$lijn;
            }
            ++$lijn;
        }
        $ws->getStyle('J1:J' . $lijn)->applyFromArray(self::STYLECENTER);

        $ws->getColumnDimension('A')->setVisible(false);
        $ws->getColumnDimension('B')->setVisible(false);
        $ws->getColumnDimension('C')->setVisible(false);
        $ws->getColumnDimension('D')->setVisible(false);
        $ws->getColumnDimension('E')->setVisible(false);
        $ws->getColumnDimension('F')->setVisible(false);
        $ws->getColumnDimension('G')->setVisible(false);
        $ws->getColumnDimension('H')->setVisible(false);

        for ($i = 65; $i < ord('M'); ++$i) {
            $ws->getColumnDimension(chr($i))->setAutoSize(true);
        }
        $ws->setAutoFilter('A2:M' . $lijn);
        $ws->setSelectedCell('A1');

        return $lijn;
    }

    private function doValidationPunten(Worksheet $s, string $cel): void
    {
        $validation = $s->getCell($cel)->getDataValidation();
        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_STOP);
        $validation->setAllowBlank(false);
        $validation->setShowInputMessage(false);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setFormula1('=punten');
    }

    private function doPunten(Spreadsheet $ss, Worksheet $ws): void
    {
        $ws->fromArray(['Uitslag', 'Wit', 'Zwart', 'Winstverhouding WIT', 'Weerstandverhouding', 'Winstverhouding ZWART', 'Tegenpunten Wit', 'Tegenpunten Zwart'], null, 'A1');
        $ws->fromArray(['0-3', '0', '3', '0', '1', '1', '0', '0'], null, 'A2');
        $ws->fromArray(['1-3', '1', '3', '0', '1', '1', '0', '-1'], null, 'A3');
        $ws->fromArray(['2-3', '2', '3', '0', '1', '1', '0', '-2'], null, 'A4');
        $ws->fromArray(['3-0', '3', '0', '1', '1', '0', '0', '0'], null, 'A5');
        $ws->fromArray(['3-1', '3', '1', '1', '1', '0', '-1', '0'], null, 'A6');
        $ws->fromArray(['3-2', '3', '2', '1', '1', '0', '-2', '0'], null, 'A7');
        $ws->fromArray(['U-U', '0', '0', '0', '0', '0', '0', '0'], null, 'A8');

        $ss->addNamedRange(new NamedRange('Punten', $ws, '=$A$2:$A$8'));
        $ss->addNamedRange(new NamedRange('PuntenLijst', $ws, '=$A$2:$H$8'));

        $ws->getStyle('A1:H1')->applyFromArray(self::STYLETHICKBORDER);
        $ws->getStyle('A1:A8')->applyFromArray(self::STYLETHICKBORDER);
        $ws->getStyle('B2:H8')->applyFromArray(self::STYLETHICKBORDER);

        for ($i = 65; $i < ord('I'); ++$i) {
            $ws->getColumnDimension(chr($i))->setAutoSize(true);
        }
        $ws->setSelectedCell('A1');
    }

    private function doSpelers(Worksheet $ws, int $maxLijn): void
    {
        $this->doKoppen($ws);

        $i = 1;
        foreach ($this->trn['SPELERS'] as $speler) {
            $ws->setCellValue([1, ++$i], '=RANK(D' . $i . ',$D$2:$D$' . (count($this->trn['SPELERS']) + 1) . ',0)-1+COUNTIF($D$2:D' . $i . ',D' . $i . ')');
            $ws->setCellValue([2, $i], $speler);
            $ws->setCellValue([3, $i], '=COUNTIFS(Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ',Partijen!$J$3:$J$' . $maxLijn . ',"<>")+COUNTIFS(Partijen!$K$3:$K$' . $maxLijn . ',Spelers!$B' . $i . ',Partijen!$J$3:$J$' . $maxLijn . ',"<>")'); // - SUM(H' . $i . ':I' . $i . ')
            $ws->setCellValue([4, $i], '=SUMIFS(Partijen!C$3:C$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!G$3:G$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
            $ws->setCellValue([5, $i], '=SUMIFS(Partijen!D$3:D$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!H$3:H$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
            $ws->setCellValue([6, $i], '=SUMIFS(Partijen!A$3:A$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!E$3:E$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
            $ws->setCellValue([7, $i], '=SUMIFS(Partijen!B$3:B$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!F$3:F$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
            $ws->setCellValue([8, $i], '=C' . $i . '*MAX(Punten!$B$2:$B$50)-D' . $i . '');
        }
        $ws->setSelectedCell('A1');
    }

    private function doSort1(Worksheet $ws): void
    {
        $ws->setCellValue('A2', '=FILTER(SORTBY(Spelers!A2:H101,Spelers!D2:D101,-1,Spelers!E2:E101,1),(Spelers!B2:B101<>"Bye")*(NOT(ISBLANK(Spelers!B2:B101))))');
        $this->doKoppen($ws);
        $ws->setSelectedCell('A1');
    }

    private function doSort2(Worksheet $ws): void
    {
        $ws->setCellValue('A2', '=SORT(FILTER(Spelers!A2:H101,(Spelers!B2:B101<>"Bye") * (NOT(ISBLANK(Spelers!B2:B101)))),7,1,FALSE)');
        $this->doKoppen($ws);
        $ws->setSelectedCell('A1');
    }

    private function doKoppen(Worksheet $ws): void
    {
        $ws->setCellValue('A1', 'Plaats');
        $ws->setCellValue('B1', 'Naam');
        $ws->setCellValue('C1', 'Partijen');
        $ws->setCellValue('D1', 'Punten');
        $ws->setCellValue('E1', 'Tegenpunten');
        $ws->setCellValue('F1', 'Winstpunten');
        $ws->setCellValue('G1', 'Weerstandspunten');
        $ws->setCellValue('H1', 'Verlorenpunten');

        for ($i = 65; $i < ord('I'); ++$i) {
            $ws->getColumnDimension(chr($i))->setAutoSize(true);
        }

        $ws->getStyle('A1:H1')->applyFromArray(self::STYLEBOLD);
    }

    public function testManyArraysOutput(): void
    {
        $json = file_get_contents('tests/data/Writer/XLSX/ArrayFunctions2.json');
        self::assertNotFalse($json);
        $this->trn = json_decode($json, true);
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);

        $wsPartijen = $spreadsheet->getActiveSheet();
        $wsPartijen->setTitle('Partijen');
        $wsPunten = new Worksheet($spreadsheet, 'Punten');
        $wsSpelers = new Worksheet($spreadsheet, 'Spelers');
        $wsSort1 = new Worksheet($spreadsheet, 'Gesorteerd punten');
        $wsSort2 = new Worksheet($spreadsheet, 'Gesorteerd verlorenpunten');

        $wsPartijen->getTabColor()->setRGB('FF0000');
        $wsPunten->getTabColor()->setRGB('00FF00');
        $wsSpelers->getTabColor()->setRGB('0000FF');
        $wsSort1->getTabColor()->setRGB('FFFF00');
        $wsSort2->getTabColor()->setRGB('00FFFF');

        foreach ([$wsPunten, $wsSpelers, $wsSort1, $wsSort2] as $ws) {
            $spreadsheet->addSheet($ws);
        }

        $this->doPunten($spreadsheet, $wsPunten);
        $maxLijn = $this->doPartijen($wsPartijen);
        $this->doSpelers($wsSpelers, $maxLijn);
        $this->doSort1($wsSort1);
        $this->doSort2($wsSort2);

        self::assertSame('Dirk', $wsPartijen->getCell('I3')->getCalculatedValue());
        self::assertSame('Rudy', $wsPartijen->getCell('K4')->getCalculatedValue());
        $calcArray = $wsSort2->getCell('A2')->getCalculatedValue();
        self::assertCount(8, $calcArray);
        $spreadsheet->disconnectWorksheets();
    }
}


1			<?php
2
3			// not yet ready for prime time
4
5			declare(strict_types=1);
6
7			namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;
8
9			use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
10			use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
11			use PhpOffice\PhpSpreadsheet\NamedRange;
12			use PhpOffice\PhpSpreadsheet\Spreadsheet;
13			use PhpOffice\PhpSpreadsheet\Style\Alignment;
14			use PhpOffice\PhpSpreadsheet\Style\Border;
15			use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
16			use PHPUnit\Framework\TestCase;
17
18			// TODO - I think the spreadsheet below is too difficult for PhpSpreadsheet to calculate correctly.
19
20			class ArrayFunctions2Test extends TestCase
21			{
22			private const STYLESIZE14 = [
23			'font' => [
24			'size' => 14,
25			],
26			];
27			private const STYLEBOLD = [
28			'font' => [
29			'bold' => true,
30			],
31			];
32			private const STYLEBOLD14 = [
33			'font' => [
34			'bold' => true,
35			'size' => 14,
36			],
37			];
38			private const STYLECENTER = [
39			'alignment' => [
40			'horizontal' => Alignment::HORIZONTAL_CENTER,
41			],
42			];
43
44			private const STYLETHICKBORDER = [
45			'borders' => [
46			'outline' => [
47			'borderStyle' => Border::BORDER_THICK,
48			'color' => ['argb' => '00000000'],
49			],
50			],
51			];
52
53			private array $trn;
54
55			private string $outputFile = '';
56
57			protected function tearDown(): void
58			{
59			if ($this->outputFile !== '') {
60			unlink($this->outputFile);
61			$this->outputFile = '';
62			}
63			}
64
65			private function odd(int $i): bool
66			{
67			return ($i % 2) === 1;
68			}
69
70			private function doPartijen(Worksheet $ws): int
71			{
72			$saring = explode("\n", $this->trn['PARINGEN']);
73			$s = $this->trn['PLAYERSNOID'];
74			$g = $this->trn['RONDEGAMES'];
75			$KD = $this->trn['KALENDERDATA'];
76			$si = $this->trn['PLAYERSIDS'];
77
78			$a = [
79			['Wit', null, null, null, 'Zwart', null, null, null, 'Wit', 'Uitslag', 'Zwart', 'Opmerking', 'Datum'],
80			['Winstpunten', 'Weerstandspunten', 'punten', 'Tegenpunten', 'Winstpunten', 'Weerstandspunten', 'punten', 'Tegenpunten'],
81
82			];
83			$ws->fromArray($a, null, 'A1');
84
85			$ws->getStyle('A1:L1')->applyFromArray(self::STYLEBOLD);
86			$ws->getStyle('A1:L1')->applyFromArray(self::STYLESIZE14);
87
88			$lijn = 1;
89			for ($i = 1; $i <= $this->trn['RONDEN']; ++$i) {//aantal ronden oneven->heen en even->terug
90			$countSaring = count($saring);
91			for ($j = 0; $j < $countSaring; ++$j) {//subronden
92			++$lijn;
93			if (isset($KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['RONDE'])) {
94			$ws->setCellValue([9, $lijn], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['RONDE']);
95			} else {
96			$ws->setCellValue([9, $lijn], 'Kalenderdata zijn niet(volledig) ingevuld');
97			}
98			if (isset($KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['TXT'])) {
99			$ws->setCellValue([10, $lijn], $KD[(($i - 1) * $this->trn['SUB_RONDEN']) + $j]['TXT']);
100			} else {
101			$ws->setCellValue([10, $lijn], 'Kalenderdata zijn niet(volledig) ingevuld');
102			}
103
104			$ws->getStyle('A' . $lijn . ':L' . $lijn . '')->applyFromArray(self::STYLEBOLD14);
105
106			$s2 = explode(' ', $saring[$j]);
107			$counts2 = count($s2);
108			for ($k = 0; $k < $counts2; ++$k) {//borden
109			if (trim($s2[$k]) == '') {
110			continue;
111			}
112			$s3 = explode('-', $s2[$k]); //wit-zwart
113			$s3[0] = (int) $s3[0];
114			$s3[1] = (int) $s3[1];
115			++$lijn;
116			$ws->setCellValue([1, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",XLOOKUP($K' . $lijn . ',Spelers!$B$2:$B$' . (count($s) + 1) . ',Spelers!$C$2:$C$' . (count($s) + 1) . ',FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,4,FALSE))');
117			$ws->setCellValue([2, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",VLOOKUP($K' . $lijn . ',Spelers!$B$2:$D$' . (count($s) + 1) . ',3,FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,5,FALSE))');
118			$ws->setCellValue([3, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$B$2:$B$50,0,0))');
119			$ws->setCellValue([4, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$G$2:$G$50,0,0))');
120
121			$ws->setCellValue([5, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",VLOOKUP($I' . $lijn . ',Spelers!$B$2:$D$' . (count($s) + 1) . ',3,FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,6,FALSE))');
122			$ws->setCellValue([6, $lijn], '=IF(SUBSTITUTE(TRIM($J' . $lijn . '),"-","")="","",VLOOKUP($I' . $lijn . ',Spelers!$B$2:$D$' . (count($s) + 1) . ',3,FALSE) * VLOOKUP(J' . $lijn . ', PuntenLijst,5,FALSE))');
123			$ws->setCellValue([7, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$C$2:$C$50,0,0))');
124			$ws->setCellValue([8, $lijn], '=IF(TRIM($J' . $lijn . ')="","",XLOOKUP($J' . $lijn . ', Punten!$A$2:$A$50,Punten!$H$2:$H$50,0,0))');
125
126			if ($this->odd($i)) {
127			if (
128			isset($g[$i][$si[((int) $s3[0]) - 1]][$si[((int) $s3[1]) - 1]])
129			) {
130			$pw = $g[$i][$si[((int) $s3[0]) - 1]][$si[((int) $s3[1]) - 1]];
131			} else {
132			$pw = ['SYMBOOLWIT' => '', 'SYMBOOLZWART' => '', 'UITSLAG' => '', 'OPMERKING' => '', 'DATUM' => ''];
133			}
134			$ws->setCellValue([9, $lijn], '=Spelers!$B$' . ($s3[0] + 1));
135			$ws->setCellValue([11, $lijn], '=Spelers!$B$' . ($s3[1] + 1));
136			} else {
137			if (
138			isset($g[$i][$si[((int) $s3[1]) - 1]][$si[((int) $s3[0]) - 1]])
139			) {
140			$pw = $g[$i][$si[((int) $s3[1]) - 1]][$si[((int) $s3[0]) - 1]];
141			} else {
142			$pw = ['SYMBOOLWIT' => '', 'SYMBOOLZWART' => '', 'UITSLAG' => '', 'OPMERKING' => '', 'DATUM' => ''];
143			}
144			$ws->setCellValue([9, $lijn], '=Spelers!$B$' . ($s3[1] + 1));
145			$ws->setCellValue([11, $lijn], '=Spelers!$B$' . ($s3[0] + 1));
146			}
147			if ($pw['SYMBOOLWIT'] != '') {
148			$ws->setCellValue([10, $lijn], $pw['SYMBOOLWIT'] . '-' . $pw['SYMBOOLZWART']);
149			}
150			$ws->setCellValue([13, $lijn], $pw['OPMERKING']);
151			$ws->setCellValue([14, $lijn], $pw['DATUM']);
152
153			$this->doValidationPunten($ws, 'J' . $lijn);
154
155			$ws->getRowDimension($lijn)->setOutlineLevel(1);
156			}
157			++$lijn;
158			}
159			++$lijn;
160			}
161			$ws->getStyle('J1:J' . $lijn)->applyFromArray(self::STYLECENTER);
162
163			$ws->getColumnDimension('A')->setVisible(false);
164			$ws->getColumnDimension('B')->setVisible(false);
165			$ws->getColumnDimension('C')->setVisible(false);
166			$ws->getColumnDimension('D')->setVisible(false);
167			$ws->getColumnDimension('E')->setVisible(false);
168			$ws->getColumnDimension('F')->setVisible(false);
169			$ws->getColumnDimension('G')->setVisible(false);
170			$ws->getColumnDimension('H')->setVisible(false);
171
172			for ($i = 65; $i < ord('M'); ++$i) {
173			$ws->getColumnDimension(chr($i))->setAutoSize(true);
174			}
175			$ws->setAutoFilter('A2:M' . $lijn);
176			$ws->setSelectedCell('A1');
177
178			return $lijn;
179			}
180
181			private function doValidationPunten(Worksheet $s, string $cel): void
182			{
183			$validation = $s->getCell($cel)->getDataValidation();
184			$validation->setType(DataValidation::TYPE_LIST);
185			$validation->setErrorStyle(DataValidation::STYLE_STOP);
186			$validation->setAllowBlank(false);
187			$validation->setShowInputMessage(false);
188			$validation->setShowErrorMessage(true);
189			$validation->setShowDropDown(true);
190			$validation->setFormula1('=punten');
191			}
192
193			private function doPunten(Spreadsheet $ss, Worksheet $ws): void
194			{
195			$ws->fromArray(['Uitslag', 'Wit', 'Zwart', 'Winstverhouding WIT', 'Weerstandverhouding', 'Winstverhouding ZWART', 'Tegenpunten Wit', 'Tegenpunten Zwart'], null, 'A1');
196			$ws->fromArray(['0-3', '0', '3', '0', '1', '1', '0', '0'], null, 'A2');
197			$ws->fromArray(['1-3', '1', '3', '0', '1', '1', '0', '-1'], null, 'A3');
198			$ws->fromArray(['2-3', '2', '3', '0', '1', '1', '0', '-2'], null, 'A4');
199			$ws->fromArray(['3-0', '3', '0', '1', '1', '0', '0', '0'], null, 'A5');
200			$ws->fromArray(['3-1', '3', '1', '1', '1', '0', '-1', '0'], null, 'A6');
201			$ws->fromArray(['3-2', '3', '2', '1', '1', '0', '-2', '0'], null, 'A7');
202			$ws->fromArray(['U-U', '0', '0', '0', '0', '0', '0', '0'], null, 'A8');
203
204			$ss->addNamedRange(new NamedRange('Punten', $ws, '=$A$2:$A$8'));
205			$ss->addNamedRange(new NamedRange('PuntenLijst', $ws, '=$A$2:$H$8'));
206
207			$ws->getStyle('A1:H1')->applyFromArray(self::STYLETHICKBORDER);
208			$ws->getStyle('A1:A8')->applyFromArray(self::STYLETHICKBORDER);
209			$ws->getStyle('B2:H8')->applyFromArray(self::STYLETHICKBORDER);
210
211			for ($i = 65; $i < ord('I'); ++$i) {
212			$ws->getColumnDimension(chr($i))->setAutoSize(true);
213			}
214			$ws->setSelectedCell('A1');
215			}
216
217			private function doSpelers(Worksheet $ws, int $maxLijn): void
218			{
219			$this->doKoppen($ws);
220
221			$i = 1;
222			foreach ($this->trn['SPELERS'] as $speler) {
223			$ws->setCellValue([1, ++$i], '=RANK(D' . $i . ',$D$2:$D$' . (count($this->trn['SPELERS']) + 1) . ',0)-1+COUNTIF($D$2:D' . $i . ',D' . $i . ')');
224			$ws->setCellValue([2, $i], $speler);
225			$ws->setCellValue([3, $i], '=COUNTIFS(Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ',Partijen!$J$3:$J$' . $maxLijn . ',"<>")+COUNTIFS(Partijen!$K$3:$K$' . $maxLijn . ',Spelers!$B' . $i . ',Partijen!$J$3:$J$' . $maxLijn . ',"<>")'); // - SUM(H' . $i . ':I' . $i . ')
226			$ws->setCellValue([4, $i], '=SUMIFS(Partijen!C$3:C$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!G$3:G$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
227			$ws->setCellValue([5, $i], '=SUMIFS(Partijen!D$3:D$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!H$3:H$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
228			$ws->setCellValue([6, $i], '=SUMIFS(Partijen!A$3:A$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!E$3:E$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
229			$ws->setCellValue([7, $i], '=SUMIFS(Partijen!B$3:B$' . $maxLijn . ',Partijen!$I$3:$I$' . $maxLijn . ',$B' . $i . ')+SUMIFS(Partijen!F$3:F$' . $maxLijn . ',Partijen!$K$3:$K$' . $maxLijn . ',$B' . $i . ')');
230			$ws->setCellValue([8, $i], '=C' . $i . '*MAX(Punten!$B$2:$B$50)-D' . $i . '');
231			}
232			$ws->setSelectedCell('A1');
233			}
234
235			private function doSort1(Worksheet $ws): void
236			{
237			$ws->setCellValue('A2', '=FILTER(SORTBY(Spelers!A2:H101,Spelers!D2:D101,-1,Spelers!E2:E101,1),(Spelers!B2:B101<>"Bye")*(NOT(ISBLANK(Spelers!B2:B101))))');
238			$this->doKoppen($ws);
239			$ws->setSelectedCell('A1');
240			}
241
242			private function doSort2(Worksheet $ws): void
243			{
244			$ws->setCellValue('A2', '=SORT(FILTER(Spelers!A2:H101,(Spelers!B2:B101<>"Bye") * (NOT(ISBLANK(Spelers!B2:B101)))),7,1,FALSE)');
245			$this->doKoppen($ws);
246			$ws->setSelectedCell('A1');
247			}
248
249			private function doKoppen(Worksheet $ws): void
250			{
251			$ws->setCellValue('A1', 'Plaats');
252			$ws->setCellValue('B1', 'Naam');
253			$ws->setCellValue('C1', 'Partijen');
254			$ws->setCellValue('D1', 'Punten');
255			$ws->setCellValue('E1', 'Tegenpunten');
256			$ws->setCellValue('F1', 'Winstpunten');
257			$ws->setCellValue('G1', 'Weerstandspunten');
258			$ws->setCellValue('H1', 'Verlorenpunten');
259
260			for ($i = 65; $i < ord('I'); ++$i) {
261			$ws->getColumnDimension(chr($i))->setAutoSize(true);
262			}
263
264			$ws->getStyle('A1:H1')->applyFromArray(self::STYLEBOLD);
265			}
266
267			public function testManyArraysOutput(): void
268			{
269			$json = file_get_contents('tests/data/Writer/XLSX/ArrayFunctions2.json');
270			self::assertNotFalse($json);
271			$this->trn = json_decode($json, true);
272			$spreadsheet = new Spreadsheet();
273			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
274
275			$wsPartijen = $spreadsheet->getActiveSheet();
276			$wsPartijen->setTitle('Partijen');
277			$wsPunten = new Worksheet($spreadsheet, 'Punten');
278			$wsSpelers = new Worksheet($spreadsheet, 'Spelers');
279			$wsSort1 = new Worksheet($spreadsheet, 'Gesorteerd punten');
280			$wsSort2 = new Worksheet($spreadsheet, 'Gesorteerd verlorenpunten');
281
282			$wsPartijen->getTabColor()->setRGB('FF0000');
283			$wsPunten->getTabColor()->setRGB('00FF00');
284			$wsSpelers->getTabColor()->setRGB('0000FF');
285			$wsSort1->getTabColor()->setRGB('FFFF00');
286			$wsSort2->getTabColor()->setRGB('00FFFF');
287
288			foreach ([$wsPunten, $wsSpelers, $wsSort1, $wsSort2] as $ws) {
289			$spreadsheet->addSheet($ws);
290			}
291
292			$this->doPunten($spreadsheet, $wsPunten);
293			$maxLijn = $this->doPartijen($wsPartijen);
294			$this->doSpelers($wsSpelers, $maxLijn);
295			$this->doSort1($wsSort1);
296			$this->doSort2($wsSort2);
297
298			self::assertSame('Dirk', $wsPartijen->getCell('I3')->getCalculatedValue());
299			self::assertSame('Rudy', $wsPartijen->getCell('K4')->getCalculatedValue());
300			$calcArray = $wsSort2->getCell('A2')->getCalculatedValue();
301			self::assertCount(8, $calcArray);
302			$spreadsheet->disconnectWorksheets();
303			}
304			}
305

PHPOffice / PhpSpreadsheet

Pull Request — master (#3962)

ArrayFunctions2Test::doPartijen() C

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like