mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-09-13 03:26:26 +00:00
oleibman
d06230eaf4
Fix #4197. Overlooked in the introduction of Dynamic Arrays, Data Validation can specify a list to be a result of the spill operator, which is implemented via the ANCHORARRAY function. It appears that function `DataValidator::isValid` will not work if the list is specified in this manner, nor if it is specified as a defined name. Fixing those situations will be difficult (defined names probably easier than ANCHORARRAY), and there is no reason to delay this change waiting for those to be fixed. I will open a new issue when this PR is merged.
88 lines
3.1 KiB
PHP
88 lines
3.1 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Cell\DataType;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
use PhpOffice\PhpSpreadsheetTests\Functional\AbstractFunctional;
|
|
|
|
class ArrayFormulaPrefixTest extends AbstractFunctional
|
|
{
|
|
/**
|
|
* Test to ensure that xlfn prefix is being added to functions
|
|
* included in an array formula, if appropriate.
|
|
*
|
|
* @throws \PhpOffice\PhpSpreadsheet\Exception
|
|
*/
|
|
public function testWriteArrayFormulaTextJoin(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$worksheet = $spreadsheet->getActiveSheet();
|
|
|
|
//Write data
|
|
$worksheet->getCell('A1')->setValue('Some Text');
|
|
$worksheet->getCell('A2')->setValue('Some More Text');
|
|
$worksheet->getCell('A3')->setValue(14.56);
|
|
$worksheet->getCell('A4')->setValue(17.24);
|
|
$worksheet->getCell('A5')->setValue(9.4);
|
|
$worksheet->getCell('A6')->setValue(5);
|
|
|
|
//Write formula
|
|
$cell = $worksheet->getCell('A7');
|
|
$cell->setValueExplicit('=TEXTJOIN("",TRUE,IF(ISNUMBER(A1:A6), A1:A6,""))', DataType::TYPE_FORMULA);
|
|
/** @var array<string, string> */
|
|
$attrs = $cell->getFormulaAttributes();
|
|
$attrs['t'] = 'array';
|
|
$cell->setFormulaAttributes($attrs);
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
$writerWorksheet = new Xlsx\Worksheet($writer);
|
|
$data = $writerWorksheet->writeWorksheet($worksheet, []);
|
|
|
|
$expected = <<<XML
|
|
<f t="array" ref="A7" aca="1" ca="1">_xlfn.TEXTJOIN("",TRUE,IF(ISNUMBER(A1:A6), A1:A6,""))</f>
|
|
XML;
|
|
self::assertStringContainsString($expected, $data);
|
|
}
|
|
|
|
/**
|
|
* Certain functions do not have the xlfn prefix applied. Test an array formula
|
|
* that includes those functions to see if they are written properly.
|
|
*
|
|
* @throws \PhpOffice\PhpSpreadsheet\Exception
|
|
*/
|
|
public function testWriteArrayFormulaWithoutPrefix(): void
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$worksheet = $spreadsheet->getActiveSheet();
|
|
|
|
//Write data
|
|
$worksheet->getCell('A1')->setValue('orange');
|
|
$worksheet->getCell('A2')->setValue('green');
|
|
$worksheet->getCell('A3')->setValue('blue');
|
|
$worksheet->getCell('A4')->setValue('yellow');
|
|
$worksheet->getCell('A5')->setValue('pink');
|
|
$worksheet->getCell('A6')->setValue('red');
|
|
|
|
//Write formula
|
|
$cell = $worksheet->getCell('A7');
|
|
$cell->setValueExplicit('=SUM(LEN(A1:A6))', DataType::TYPE_FORMULA);
|
|
/** @var array<string, string> */
|
|
$attrs = $cell->getFormulaAttributes();
|
|
$attrs['t'] = 'array';
|
|
$cell->setFormulaAttributes($attrs);
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
$writerWorksheet = new Xlsx\Worksheet($writer);
|
|
$data = $writerWorksheet->writeWorksheet($worksheet, []);
|
|
|
|
$expected = <<<XML
|
|
<f t="array" ref="A7" aca="1" ca="1">SUM(LEN(A1:A6))</f>
|
|
XML;
|
|
self::assertStringContainsString($expected, $data);
|
|
}
|
|
}
|