ArrayFunctionsTest::testArrayOutputCSE() - 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

ArrayFunctionsTest::testArrayOutputCSE() B

↳ Parent: ArrayFunctionsTest

Complexity

Conditions	3
Paths	4

Size

Total Lines	115
Code Lines	103

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	0

Metric	Value
eloc	103
c	2
b	0
f	0
dl	0
loc	115
rs	8
cc	3
nc	4
nop	0

How to fix Long Method

<?php

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Shared\File;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
use PHPUnit\Framework\TestCase;

class ArrayFunctionsTest extends TestCase
{
    private string $outputFile = '';

    public function testArrayOutput(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet = $spreadsheet->getActiveSheet();
        $columnArray = [
            [41],
            [57],
            [51],
            [54],
            [49],
            [43],
            [35],
            [35],
            [44],
            [47],
            [48],
            [26],
            [57],
            [34],
            [61],
            [34],
            [28],
            [29],
            [41],
        ];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('C1', '=UNIQUE(A1:A19)');
        $sheet->setCellValue('D1', '=SORT(A1:A19)');
        $writer = new XlsxWriter($spreadsheet);
        $this->outputFile = File::temporaryFilename();
        $writer->save($this->outputFile);
        $spreadsheet->disconnectWorksheets();

        $reader = new XlsxReader();
        $spreadsheet2 = $reader->load($this->outputFile);
        Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet2 = $spreadsheet2->getActiveSheet();
        $expectedUnique = [
            [41],
            [57],
            [51],
            [54],
            [49],
            [43],
            [35],
            [44],
            [47],
            [48],
            [26],
            [34],
            [61],
            [28],
            [29],
        ];
        self::assertCount(15, $expectedUnique);
        self::assertSame($expectedUnique, $sheet2->getCell('C1')->getCalculatedValue());
        for ($row = 2; $row <= 15; ++$row) {
            self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("C$row")->getCalculatedValue(), "cell C$row");
        }
        $expectedSort = [
            [26],
            [28],
            [29],
            [34],
            [34],
            [35],
            [35],
            [41],
            [41],
            [43],
            [44],
            [47],
            [48],
            [49],
            [51],
            [54],
            [57],
            [57],
            [61],
        ];
        self::assertCount(19, $expectedSort);
        self::assertCount(19, $columnArray);
        self::assertSame($expectedSort, $sheet2->getCell('D1')->getCalculatedValue());
        $spreadsheet2->disconnectWorksheets();

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/worksheets/sheet1.xml';
        $data = file_get_contents($file);
        if ($data === false) {
            self::fail('Unable to read file');
        } else {
            self::assertStringContainsString('<c r="C1" cm="1"><f t="array" ref="C1:C15" aca="1" ca="1">_xlfn.UNIQUE(A1:A19)</f><v>41</v></c>', $data, '15 results for UNIQUE');
            self::assertStringContainsString('<c r="D1" cm="1"><f t="array" ref="D1:D19" aca="1" ca="1">_xlfn._xlws.SORT(A1:A19)</f><v>26</v></c>', $data, '19 results for SORT');
        }

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/metadata.xml';
        $data = @file_get_contents($file);
        self::assertNotFalse($data, 'metadata.xml should exist');

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#[Content_Types].xml';
        $data = file_get_contents($file);
        self::assertStringContainsString('metadata', $data);

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/_rels/workbook.xml.rels';
        $data = file_get_contents($file);
        self::assertStringContainsString('metadata', $data);
    }

    public function testArrayOutputCSE(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet = $spreadsheet->getActiveSheet();
        $columnArray = [
            [41],
            [57],
            [51],
            [54],
            [49],
            [43],
            [35],
            [35],
            [44],
            [47],
            [48],
            [26],
            [57],
            [34],
            [61],
            [34],
            [28],
            [29],
            [41],
        ];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('C1', '=UNIQUE(A1:A19)');
        $sheet->setCellValue('D1', '=SORT(A1:A19)');
        $writer = new XlsxWriter($spreadsheet);
        $this->outputFile = File::temporaryFilename();
        $writer->setUseCSEArrays(true);
        $writer->save($this->outputFile);
        $spreadsheet->disconnectWorksheets();

        $reader = new XlsxReader();
        $spreadsheet2 = $reader->load($this->outputFile);
        Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet2 = $spreadsheet2->getActiveSheet();
        $expectedUnique = [
            [41],
            [57],
            [51],
            [54],
            [49],
            [43],
            [35],
            [44],
            [47],
            [48],
            [26],
            [34],
            [61],
            [28],
            [29],
        ];
        self::assertCount(15, $expectedUnique);
        self::assertSame($expectedUnique, $sheet2->getCell('C1')->getCalculatedValue());
        for ($row = 2; $row <= 15; ++$row) {
            self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("C$row")->getCalculatedValue(), "cell C$row");
        }
        $expectedSort = [
            [26],
            [28],
            [29],
            [34],
            [34],
            [35],
            [35],
            [41],
            [41],
            [43],
            [44],
            [47],
            [48],
            [49],
            [51],
            [54],
            [57],
            [57],
            [61],
        ];
        self::assertCount(19, $expectedSort);
        self::assertCount(19, $columnArray);
        self::assertSame($expectedSort, $sheet2->getCell('D1')->getCalculatedValue());
        $spreadsheet2->disconnectWorksheets();

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/worksheets/sheet1.xml';
        $data = file_get_contents($file);
        if ($data === false) {
            self::fail('Unable to read file');
        } else {
            self::assertStringContainsString('<c r="C1"><f t="array" ref="C1:C15" aca="1" ca="1">_xlfn.UNIQUE(A1:A19)</f><v>41</v></c>', $data, '15 results for UNIQUE');
            self::assertStringContainsString('<c r="D1"><f t="array" ref="D1:D19" aca="1" ca="1">_xlfn._xlws.SORT(A1:A19)</f><v>26</v></c>', $data, '19 results for SORT');
        }

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/metadata.xml';
        $data = @file_get_contents($file);
        self::assertFalse($data, 'metadata.xml should not exist');

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#[Content_Types].xml';
        $data = file_get_contents($file);
        self::assertStringNotContainsString('metadata', $data);

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/_rels/workbook.xml.rels';
        $data = file_get_contents($file);
        self::assertStringNotContainsString('metadata', $data);
    }

    public function testUnimplementedArrayOutput(): void
    {
        //Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY); // not required for this test
        $reader = new XlsxReader();
        $spreadsheet = $reader->load('tests/data/Reader/XLSX/atsign.choosecols.xlsx');
        $writer = new XlsxWriter($spreadsheet);
        $this->outputFile = File::temporaryFilename();
        $writer->save($this->outputFile);
        $spreadsheet->disconnectWorksheets();

        $reader = new XlsxReader();
        $spreadsheet2 = $reader->load($this->outputFile);
        $sheet2 = $spreadsheet2->getActiveSheet();
        self::assertSame('=CHOOSECOLS(A1:C5,3,1)', $sheet2->getCell('F1')->getValue());
        $expectedFG = [
            ['11', '1'],
            ['12', '2'],
            ['13', '3'],
            ['14', '4'],
            ['15', '5'],
        ];
        $actualFG = $sheet2->rangeToArray('F1:G5');
        self::assertSame($expectedFG, $actualFG);
        self::assertSame('=CELL("width")', $sheet2->getCell('I1')->getValue());
        self::assertSame(8, $sheet2->getCell('I1')->getCalculatedValue());
        self::assertTrue($sheet2->getCell('J1')->getValue());
        $spreadsheet2->disconnectWorksheets();

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/worksheets/sheet1.xml';
        $data = file_get_contents($file);
        if ($data === false) {
            self::fail('Unable to read file');
        } else {
            self::assertStringContainsString('<f t="array" ref="F1:G5" aca="1" ca="1">_xlfn.CHOOSECOLS(A1:C5,3,1)</f><v>11</v>', $data);
            self::assertStringContainsString('<f t="array" ref="I1:J1" aca="1" ca="1">CELL(&quot;width&quot;)</f><v>8</v></c><c r="J1" t="b"><v>1</v></c>', $data);
        }
    }

    public function testArrayMultipleColumns(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet = $spreadsheet->getActiveSheet();
        $columnArray = [
            [100, 91],
            [85, 1],
            [100, 92],
            [734, 12],
            [100, 91],
            [5, 2],
        ];
        $sheet->fromArray($columnArray);
        $sheet->setCellValue('H1', '=UNIQUE(A1:B6)');
        $writer = new XlsxWriter($spreadsheet);
        $this->outputFile = File::temporaryFilename();
        $writer->save($this->outputFile);
        $spreadsheet->disconnectWorksheets();

        $reader = new XlsxReader();
        $spreadsheet2 = $reader->load($this->outputFile);
        Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet2 = $spreadsheet2->getActiveSheet();
        $expectedUnique = [
            [100, 91],
            [85, 1],
            [100, 92],
            [734, 12],
            //[100, 91], // not unique
            [5, 2],
        ];
        self::assertCount(5, $expectedUnique);
        self::assertSame($expectedUnique, $sheet2->getCell('H1')->getCalculatedValue());
        for ($row = 1; $row <= 5; ++$row) {
            if ($row > 1) {
                self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("H$row")->getValue(), "cell H$row");
            } else {
                self::assertTrue($sheet2->getCell("H$row")->isFormula());
                self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("H$row")->getOldCalculatedValue(), "cell H$row");
            }
            self::assertSame($expectedUnique[$row - 1][1], $sheet2->getCell("I$row")->getValue(), "cell I$row");
        }
        $cellFormulaAttributes = $sheet2->getCell('H1')->getFormulaAttributes();
        self::assertArrayHasKey('t', $cellFormulaAttributes);
        self::assertSame('array', $cellFormulaAttributes['t']);
        self::assertArrayHasKey('ref', $cellFormulaAttributes);
        self::assertSame('H1:I5', $cellFormulaAttributes['ref']);
        $spreadsheet2->disconnectWorksheets();
    }

    public function testMetadataWritten(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $writer = new XlsxWriter($spreadsheet);
        $writerMetadata = new XlsxWriter\Metadata($writer);
        self::assertNotEquals('', $writerMetadata->writeMetaData());
        $writer->setUseCSEArrays(true);
        $writerMetadata2 = new XlsxWriter\Metadata($writer);
        self::assertSame('', $writerMetadata2->writeMetaData());
        $spreadsheet->disconnectWorksheets();
    }

    public function testSpill(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getCell('A3')->setValue('x');
        $sheet->getCell('A1')->setValue('=UNIQUE({1;2;3})');
        $writer = new XlsxWriter($spreadsheet);
        $this->outputFile = File::temporaryFilename();
        $writer->save($this->outputFile);
        $spreadsheet->disconnectWorksheets();

        $reader = new XlsxReader();
        $spreadsheet2 = $reader->load($this->outputFile);
        Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet2 = $spreadsheet2->getActiveSheet();
        self::assertSame('#SPILL!', $sheet2->getCell('A1')->getOldCalculatedValue());
        self::assertSame('=UNIQUE({1;2;3})', $sheet2->getCell('A1')->getValue());
        self::assertNull($sheet2->getCell('A2')->getValue());
        self::assertSame('x', $sheet2->getCell('A3')->getValue());
        $spreadsheet2->disconnectWorksheets();
    }

    public function testArrayStringOutput(): void
    {
        $spreadsheet = new Spreadsheet();
        Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet = $spreadsheet->getActiveSheet();
        $columnArray = [
            ['item1'],
            ['item2'],
            ['item3'],
            ['item1'],
            ['item1'],
            ['item6'],
            ['item7'],
            ['item1'],
            ['item9'],
            ['item1'],
        ];
        $sheet->fromArray($columnArray, 'A1');
        $sheet->setCellValue('C1', '=UNIQUE(A1:A10)');
        $writer = new XlsxWriter($spreadsheet);
        $this->outputFile = File::temporaryFilename();
        $writer->save($this->outputFile);
        $spreadsheet->disconnectWorksheets();

        $reader = new XlsxReader();
        $spreadsheet2 = $reader->load($this->outputFile);
        Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
        $sheet2 = $spreadsheet2->getActiveSheet();
        $expectedUnique = [
            ['item1'],
            ['item2'],
            ['item3'],
            ['item6'],
            ['item7'],
            ['item9'],
        ];
        self::assertCount(6, $expectedUnique);
        self::assertSame($expectedUnique, $sheet2->getCell('C1')->getCalculatedValue());
        self::assertSame($expectedUnique[0][0], $sheet2->getCell('C1')->getCalculatedValueString());
        for ($row = 2; $row <= 6; ++$row) {
            self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("C$row")->getCalculatedValue(), "cell C$row");
        }
        $spreadsheet2->disconnectWorksheets();

        $file = 'zip://';
        $file .= $this->outputFile;
        $file .= '#xl/worksheets/sheet1.xml';
        $data = file_get_contents($file);
        if ($data === false) {
            self::fail('Unable to read file');
        } else {
            self::assertStringContainsString('<c r="C1" cm="1" t="str"><f t="array" ref="C1:C6" aca="1" ca="1">_xlfn.UNIQUE(A1:A10)</f><v>item1</v></c>', $data, '6 results for UNIQUE');
        }
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;
6
7			use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
8			use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
9			use PhpOffice\PhpSpreadsheet\Shared\File;
10			use PhpOffice\PhpSpreadsheet\Spreadsheet;
11			use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
12			use PHPUnit\Framework\TestCase;
13
14			class ArrayFunctionsTest extends TestCase
15			{
16			private string $outputFile = '';
17
18			public function testArrayOutput(): void
19			{
20			$spreadsheet = new Spreadsheet();
21			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
22			$sheet = $spreadsheet->getActiveSheet();
23			$columnArray = [
24			[41],
25			[57],
26			[51],
27			[54],
28			[49],
29			[43],
30			[35],
31			[35],
32			[44],
33			[47],
34			[48],
35			[26],
36			[57],
37			[34],
38			[61],
39			[34],
40			[28],
41			[29],
42			[41],
43			];
44			$sheet->fromArray($columnArray, 'A1');
45			$sheet->setCellValue('C1', '=UNIQUE(A1:A19)');
46			$sheet->setCellValue('D1', '=SORT(A1:A19)');
47			$writer = new XlsxWriter($spreadsheet);
48			$this->outputFile = File::temporaryFilename();
49			$writer->save($this->outputFile);
50			$spreadsheet->disconnectWorksheets();
51
52			$reader = new XlsxReader();
53			$spreadsheet2 = $reader->load($this->outputFile);
54			Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
55			$sheet2 = $spreadsheet2->getActiveSheet();
56			$expectedUnique = [
57			[41],
58			[57],
59			[51],
60			[54],
61			[49],
62			[43],
63			[35],
64			[44],
65			[47],
66			[48],
67			[26],
68			[34],
69			[61],
70			[28],
71			[29],
72			];
73			self::assertCount(15, $expectedUnique);
74			self::assertSame($expectedUnique, $sheet2->getCell('C1')->getCalculatedValue());
75			for ($row = 2; $row <= 15; ++$row) {
76			self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("C$row")->getCalculatedValue(), "cell C$row");
77			}
78			$expectedSort = [
79			[26],
80			[28],
81			[29],
82			[34],
83			[34],
84			[35],
85			[35],
86			[41],
87			[41],
88			[43],
89			[44],
90			[47],
91			[48],
92			[49],
93			[51],
94			[54],
95			[57],
96			[57],
97			[61],
98			];
99			self::assertCount(19, $expectedSort);
100			self::assertCount(19, $columnArray);
101			self::assertSame($expectedSort, $sheet2->getCell('D1')->getCalculatedValue());
102			$spreadsheet2->disconnectWorksheets();
103
104			$file = 'zip://';
105			$file .= $this->outputFile;
106			$file .= '#xl/worksheets/sheet1.xml';
107			$data = file_get_contents($file);
108			if ($data === false) {
109			self::fail('Unable to read file');
110			} else {
111			self::assertStringContainsString('<c r="C1" cm="1"><f t="array" ref="C1:C15" aca="1" ca="1">_xlfn.UNIQUE(A1:A19)</f><v>41</v></c>', $data, '15 results for UNIQUE');
112			self::assertStringContainsString('<c r="D1" cm="1"><f t="array" ref="D1:D19" aca="1" ca="1">_xlfn._xlws.SORT(A1:A19)</f><v>26</v></c>', $data, '19 results for SORT');
113			}
114
115			$file = 'zip://';
116			$file .= $this->outputFile;
117			$file .= '#xl/metadata.xml';
118			$data = @file_get_contents($file);
119			self::assertNotFalse($data, 'metadata.xml should exist');
120
121			$file = 'zip://';
122			$file .= $this->outputFile;
123			$file .= '#[Content_Types].xml';
124			$data = file_get_contents($file);
125			self::assertStringContainsString('metadata', $data);
126
127			$file = 'zip://';
128			$file .= $this->outputFile;
129			$file .= '#xl/_rels/workbook.xml.rels';
130			$data = file_get_contents($file);
131			self::assertStringContainsString('metadata', $data);
132			}
133
134			public function testArrayOutputCSE(): void
135			{
136			$spreadsheet = new Spreadsheet();
137			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
138			$sheet = $spreadsheet->getActiveSheet();
139			$columnArray = [
140			[41],
141			[57],
142			[51],
143			[54],
144			[49],
145			[43],
146			[35],
147			[35],
148			[44],
149			[47],
150			[48],
151			[26],
152			[57],
153			[34],
154			[61],
155			[34],
156			[28],
157			[29],
158			[41],
159			];
160			$sheet->fromArray($columnArray, 'A1');
161			$sheet->setCellValue('C1', '=UNIQUE(A1:A19)');
162			$sheet->setCellValue('D1', '=SORT(A1:A19)');
163			$writer = new XlsxWriter($spreadsheet);
164			$this->outputFile = File::temporaryFilename();
165			$writer->setUseCSEArrays(true);
166			$writer->save($this->outputFile);
167			$spreadsheet->disconnectWorksheets();
168
169			$reader = new XlsxReader();
170			$spreadsheet2 = $reader->load($this->outputFile);
171			Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
172			$sheet2 = $spreadsheet2->getActiveSheet();
173			$expectedUnique = [
174			[41],
175			[57],
176			[51],
177			[54],
178			[49],
179			[43],
180			[35],
181			[44],
182			[47],
183			[48],
184			[26],
185			[34],
186			[61],
187			[28],
188			[29],
189			];
190			self::assertCount(15, $expectedUnique);
191			self::assertSame($expectedUnique, $sheet2->getCell('C1')->getCalculatedValue());
192			for ($row = 2; $row <= 15; ++$row) {
193			self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("C$row")->getCalculatedValue(), "cell C$row");
194			}
195			$expectedSort = [
196			[26],
197			[28],
198			[29],
199			[34],
200			[34],
201			[35],
202			[35],
203			[41],
204			[41],
205			[43],
206			[44],
207			[47],
208			[48],
209			[49],
210			[51],
211			[54],
212			[57],
213			[57],
214			[61],
215			];
216			self::assertCount(19, $expectedSort);
217			self::assertCount(19, $columnArray);
218			self::assertSame($expectedSort, $sheet2->getCell('D1')->getCalculatedValue());
219			$spreadsheet2->disconnectWorksheets();
220
221			$file = 'zip://';
222			$file .= $this->outputFile;
223			$file .= '#xl/worksheets/sheet1.xml';
224			$data = file_get_contents($file);
225			if ($data === false) {
226			self::fail('Unable to read file');
227			} else {
228			self::assertStringContainsString('<c r="C1"><f t="array" ref="C1:C15" aca="1" ca="1">_xlfn.UNIQUE(A1:A19)</f><v>41</v></c>', $data, '15 results for UNIQUE');
229			self::assertStringContainsString('<c r="D1"><f t="array" ref="D1:D19" aca="1" ca="1">_xlfn._xlws.SORT(A1:A19)</f><v>26</v></c>', $data, '19 results for SORT');
230			}
231
232			$file = 'zip://';
233			$file .= $this->outputFile;
234			$file .= '#xl/metadata.xml';
235			$data = @file_get_contents($file);
236			self::assertFalse($data, 'metadata.xml should not exist');
237
238			$file = 'zip://';
239			$file .= $this->outputFile;
240			$file .= '#[Content_Types].xml';
241			$data = file_get_contents($file);
242			self::assertStringNotContainsString('metadata', $data);
243
244			$file = 'zip://';
245			$file .= $this->outputFile;
246			$file .= '#xl/_rels/workbook.xml.rels';
247			$data = file_get_contents($file);
248			self::assertStringNotContainsString('metadata', $data);
249			}
250
251			public function testUnimplementedArrayOutput(): void
252			{
253			//Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY); // not required for this test
254			$reader = new XlsxReader();
255			$spreadsheet = $reader->load('tests/data/Reader/XLSX/atsign.choosecols.xlsx');
256			$writer = new XlsxWriter($spreadsheet);
257			$this->outputFile = File::temporaryFilename();
258			$writer->save($this->outputFile);
259			$spreadsheet->disconnectWorksheets();
260
261			$reader = new XlsxReader();
262			$spreadsheet2 = $reader->load($this->outputFile);
263			$sheet2 = $spreadsheet2->getActiveSheet();
264			self::assertSame('=CHOOSECOLS(A1:C5,3,1)', $sheet2->getCell('F1')->getValue());
265			$expectedFG = [
266			['11', '1'],
267			['12', '2'],
268			['13', '3'],
269			['14', '4'],
270			['15', '5'],
271			];
272			$actualFG = $sheet2->rangeToArray('F1:G5');
273			self::assertSame($expectedFG, $actualFG);
274			self::assertSame('=CELL("width")', $sheet2->getCell('I1')->getValue());
275			self::assertSame(8, $sheet2->getCell('I1')->getCalculatedValue());
276			self::assertTrue($sheet2->getCell('J1')->getValue());
277			$spreadsheet2->disconnectWorksheets();
278
279			$file = 'zip://';
280			$file .= $this->outputFile;
281			$file .= '#xl/worksheets/sheet1.xml';
282			$data = file_get_contents($file);
283			if ($data === false) {
284			self::fail('Unable to read file');
285			} else {
286			self::assertStringContainsString('<f t="array" ref="F1:G5" aca="1" ca="1">_xlfn.CHOOSECOLS(A1:C5,3,1)</f><v>11</v>', $data);
287			self::assertStringContainsString('<f t="array" ref="I1:J1" aca="1" ca="1">CELL("width")</f><v>8</v></c><c r="J1" t="b"><v>1</v></c>', $data);
288			}
289			}
290
291			public function testArrayMultipleColumns(): void
292			{
293			$spreadsheet = new Spreadsheet();
294			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
295			$sheet = $spreadsheet->getActiveSheet();
296			$columnArray = [
297			[100, 91],
298			[85, 1],
299			[100, 92],
300			[734, 12],
301			[100, 91],
302			[5, 2],
303			];
304			$sheet->fromArray($columnArray);
305			$sheet->setCellValue('H1', '=UNIQUE(A1:B6)');
306			$writer = new XlsxWriter($spreadsheet);
307			$this->outputFile = File::temporaryFilename();
308			$writer->save($this->outputFile);
309			$spreadsheet->disconnectWorksheets();
310
311			$reader = new XlsxReader();
312			$spreadsheet2 = $reader->load($this->outputFile);
313			Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
314			$sheet2 = $spreadsheet2->getActiveSheet();
315			$expectedUnique = [
316			[100, 91],
317			[85, 1],
318			[100, 92],
319			[734, 12],
320			//[100, 91], // not unique
321			[5, 2],
322			];
323			self::assertCount(5, $expectedUnique);
324			self::assertSame($expectedUnique, $sheet2->getCell('H1')->getCalculatedValue());
325			for ($row = 1; $row <= 5; ++$row) {
326			if ($row > 1) {
327			self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("H$row")->getValue(), "cell H$row");
328			} else {
329			self::assertTrue($sheet2->getCell("H$row")->isFormula());
330			self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("H$row")->getOldCalculatedValue(), "cell H$row");
331			}
332			self::assertSame($expectedUnique[$row - 1][1], $sheet2->getCell("I$row")->getValue(), "cell I$row");
333			}
334			$cellFormulaAttributes = $sheet2->getCell('H1')->getFormulaAttributes();
335			self::assertArrayHasKey('t', $cellFormulaAttributes);
336			self::assertSame('array', $cellFormulaAttributes['t']);
337			self::assertArrayHasKey('ref', $cellFormulaAttributes);
338			self::assertSame('H1:I5', $cellFormulaAttributes['ref']);
339			$spreadsheet2->disconnectWorksheets();
340			}
341
342			public function testMetadataWritten(): void
343			{
344			$spreadsheet = new Spreadsheet();
345			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
346			$writer = new XlsxWriter($spreadsheet);
347			$writerMetadata = new XlsxWriter\Metadata($writer);
348			self::assertNotEquals('', $writerMetadata->writeMetaData());
349			$writer->setUseCSEArrays(true);
350			$writerMetadata2 = new XlsxWriter\Metadata($writer);
351			self::assertSame('', $writerMetadata2->writeMetaData());
352			$spreadsheet->disconnectWorksheets();
353			}
354
355			public function testSpill(): void
356			{
357			$spreadsheet = new Spreadsheet();
358			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
359			$sheet = $spreadsheet->getActiveSheet();
360			$sheet->getCell('A3')->setValue('x');
361			$sheet->getCell('A1')->setValue('=UNIQUE({1;2;3})');
362			$writer = new XlsxWriter($spreadsheet);
363			$this->outputFile = File::temporaryFilename();
364			$writer->save($this->outputFile);
365			$spreadsheet->disconnectWorksheets();
366
367			$reader = new XlsxReader();
368			$spreadsheet2 = $reader->load($this->outputFile);
369			Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
370			$sheet2 = $spreadsheet2->getActiveSheet();
371			self::assertSame('#SPILL!', $sheet2->getCell('A1')->getOldCalculatedValue());
372			self::assertSame('=UNIQUE({1;2;3})', $sheet2->getCell('A1')->getValue());
373			self::assertNull($sheet2->getCell('A2')->getValue());
374			self::assertSame('x', $sheet2->getCell('A3')->getValue());
375			$spreadsheet2->disconnectWorksheets();
376			}
377
378			public function testArrayStringOutput(): void
379			{
380			$spreadsheet = new Spreadsheet();
381			Calculation::getInstance($spreadsheet)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
382			$sheet = $spreadsheet->getActiveSheet();
383			$columnArray = [
384			['item1'],
385			['item2'],
386			['item3'],
387			['item1'],
388			['item1'],
389			['item6'],
390			['item7'],
391			['item1'],
392			['item9'],
393			['item1'],
394			];
395			$sheet->fromArray($columnArray, 'A1');
396			$sheet->setCellValue('C1', '=UNIQUE(A1:A10)');
397			$writer = new XlsxWriter($spreadsheet);
398			$this->outputFile = File::temporaryFilename();
399			$writer->save($this->outputFile);
400			$spreadsheet->disconnectWorksheets();
401
402			$reader = new XlsxReader();
403			$spreadsheet2 = $reader->load($this->outputFile);
404			Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
405			$sheet2 = $spreadsheet2->getActiveSheet();
406			$expectedUnique = [
407			['item1'],
408			['item2'],
409			['item3'],
410			['item6'],
411			['item7'],
412			['item9'],
413			];
414			self::assertCount(6, $expectedUnique);
415			self::assertSame($expectedUnique, $sheet2->getCell('C1')->getCalculatedValue());
416			self::assertSame($expectedUnique[0][0], $sheet2->getCell('C1')->getCalculatedValueString());
417			for ($row = 2; $row <= 6; ++$row) {
418			self::assertSame($expectedUnique[$row - 1][0], $sheet2->getCell("C$row")->getCalculatedValue(), "cell C$row");
419			}
420			$spreadsheet2->disconnectWorksheets();
421
422			$file = 'zip://';
423			$file .= $this->outputFile;
424			$file .= '#xl/worksheets/sheet1.xml';
425			$data = file_get_contents($file);
426			if ($data === false) {
427			self::fail('Unable to read file');
428			} else {
429			self::assertStringContainsString('<c r="C1" cm="1" t="str"><f t="array" ref="C1:C6" aca="1" ca="1">_xlfn.UNIQUE(A1:A10)</f><v>item1</v></c>', $data, '6 results for UNIQUE');
430			}
431			}
432			}
433

PHPOffice / PhpSpreadsheet

Pull Request — master (#3962)

ArrayFunctionsTest::testArrayOutputCSE() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like