ArrayFunctionsTest::tearDown() - Code Metrics - Inspection of "Xlsx Writer Duplicate ContentTypes Entry for Backg..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#4180)

by Owen

created 2024-10-02 00:43 UTC

ArrayFunctionsTest::tearDown() A

↳ Parent: ArrayFunctionsTest

Complexity

Conditions	2
Paths	2

Size

Total Lines	5
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	3
c	1
b	0
f	0
dl	0
loc	5
rs	10
cc	2
nc	2
nop	0

<?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 = '';

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

PHPOffice / PhpSpreadsheet

Pull Request — master (#4180)

ArrayFunctionsTest::tearDown() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like