XLSX Writer - Array Formulas do not include function prefix; produce #NAME? errors after writing.

[fdjohnston]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Array formulae should be written to an XLSX file so that the value is computed when the XLSX is opened.

What is the current behavior?

When writing an array formula to a cell and then saving to XLSX, the resulting XLSX displays a #NAME? error when first opened. Editing the cell (but not changing the formula) and re-saving the file fixes the issue.

What are the steps to reproduce?

I know the formula is a bit odd, but it's related to something I'm working on.

<?php

require __DIR__ . '/vendor/autoload.php';

$spreadsheet = new Spreadsheet;
$sheet1 = $spreadsheet->getActiveSheet();

//Write data
$sheet1->getCell('A1')->setValue('Some Text');
$sheet1->getCell('A2')->setValue('Some More Text');
$sheet1->getCell('A3')->setValue(14.56);
$sheet1->getCell('A4')->setValue(17.24);
$sheet1->getCell('A5')->setValue(9.4);
$sheet1->getCell('A6')->setValue(5);

//Write formula
$cell = $sheet1->getCell('A7');
$cell->setValueExplicit('=TEXTJOIN("",TRUE,IF(ISNUMBER(A1:A6), A1:A6,""))', DataType::TYPE_FORMULA);
$attrs = $cell->getFormulaAttributes();
$attrs['t'] = 'array';
$cell->setFormulaAttributes($attrs);

//Save
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
//Some writable path
$writer->save('C:\Test.xlsx');

I have attached two sample XLSX Files. One demonstrating the issue, and one created after my proposed fix.

Test.xlsx
Test - Fixed.xlsx

The issue appears to be caused by this block of code in the writeCellFormula in phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php:

if (($attributes['t'] ?? null) === 'array') {
    $objWriter->startElement('f');
    $objWriter->writeAttribute('t', 'array');
    $objWriter->writeAttribute('ref', $cell->getCoordinate());
    $objWriter->writeAttribute('aca', '1');
    $objWriter->writeAttribute('ca', '1');
    $objWriter->text(substr($cellValue, 1));
    $objWriter->endElement();
}

I think it should be writing out the function prefixes, like in the else below. In my testing this change fixes the issue:

if (($attributes['t'] ?? null) === 'array') {
    $objWriter->startElement('f');
    $objWriter->writeAttribute('t', 'array');
    $objWriter->writeAttribute('ref', $cell->getCoordinate());
    $objWriter->writeAttribute('aca', '1');
    $objWriter->writeAttribute('ca', '1');
    $objWriter->text(FunctionPrefix::addFunctionPrefixStripEquals($cellValue));
    $objWriter->endElement();
}

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Only tested on XLSX

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.4, PhpSpreadsheet 1.27

[MarkBaker]

You're right, it should be calling FunctionPrefix::addFunctionPrefixStripEquals() before writing the cell formula with the _xlfn prefix.

[fdjohnston]

Happy to submit a PR if that would be helpful.

[MarkBaker]

If you'd like to do so

Note that MS have been changing the way that array formulae are managed in MS Excel, and that PhpSpreadsheet 2.0 will provide full support for all the new array formula features, rather than the limited partial support provided in the current version... when I can manage to complete all the merges from master since I created that 2.0-dev branch.