mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-09-13 03:26:26 +00:00
oleibman
898fa0bf60
We currently use Phpstan Release 1. Release 2 has been available for some time, and we would like to stay current if possible. In order to get to Release 2, we first have to pass the "bleeding edge" tests for Release 1. I will do this by generating a new baseline using bleeding edge, then fix the baseline errors over several different PR's. This is the first of those.
433 lines
15 KiB
PHP
433 lines
15 KiB
PHP
<?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::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::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::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::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("width")</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::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::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');
|
|
}
|
|
}
|
|
}
|