Changing case of x-subject-token header

Never a nice feeling; though please don't be too hard on yourself (just saw your Tweets about this issue)! I suppose one of the wonderfully frustrating things about being a developer is that (much like these references) absolute certainty sometimes turns out not to be so absolute 😃

Understanding that 2.0 is coming soon, what does the support roadmap look like for 1.x? Is this something you think is worth fixing in both versions? I'd be happy to try to help on either version, though I have more experience with 1.x.

Sorry, @MarkBaker !

My understanding was that absolute references would remain the same when used in formulas that are dragged or copied to other cells, unlike relative references which will get updated. If absolute references don't update when the structure of the worksheet changes, any addition of columns or rows would instantly break all absolute references, would it not?

I don't want to call your last 20 years of understanding into question (lol), but I just tested Libre Office and Open Office and they both behave the same way.

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)

When inserting rows or columns that alter the position of cells referenced absolutely in a formula, the formula is not updated by PHPSpreadsheet. In Excel, absolute references are updated.

What is the expected behavior?

Absolute references should be updated when rows/columns are inserted/removed.

What is the current behavior?

Absolute references are not updated when rows/columns are inserted/removed. Normal references are updated.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

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

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$sheet1 = $spreadsheet->getActiveSheet();
$sheet1->getCell('A1')->setValue(0.24);
$sheet1->getCell('A2')->setValue(0.1);
$sheet1->getCell('A3')->setValue('=$A$1+$A$2');
$sheet1->insertNewRowBefore(1, 2);

var_dump($sheet1->getCell('A5')->getValue());

To align with the behaviour in Excel, the value of A5 should be =$A$3+$A$4.

What features do you think are causing the issue

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

Issue appears to be in ReferenceHelper. The updateFormulaReferences function includes an optional param for including absolute references when updating references, however it doesn't appear to ever get set to true.

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

I expect so since this is occurring without writing or opening a file.

Which versions of PhpSpreadsheet and PHP are affected?

1.27.0

But of course that's up to you. Happy to add others if you feel it's necessary.

Totally agree. Was just suggestion that a couple of simpler tests is probably adequate.

Thanks for the recommendation. I started with a couple simple cases. These can be expanded upon if necessary, though based on @MarkBaker comments back in issue #3337 it sounds like this is all getting overhauled in the next major version of PhpSpreadsheet anyway.

Unit tests for issue #3337

Added two unit tests for issue #3337: one that includes the xlfn prefix, and one that does not. These tests will ensure that the prefix is being added, when appropriate, within an array formula.

Using the FunctionPrefix::addFunctionPrefixStripEquals appears to fix the issue by adding the _xlfn prefix to functions in array formulas.

This is:

• [x] a bugfix
• [ ] a new feature
• [ ] refactoring
• [ ] additional unit tests

Checklist:

• [ ] Changes are covered by unit tests
  • [ ] Changes are covered by existing unit tests
  • [ ] New unit tests have been added
• [x] Code style is respected
• [x] Commit message explains why the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
• [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
• [ ] Documentation is updated as necessary

Why this change is needed?

Addresses issue #3337.

I didn't write any unit tests since I wasn't sure how to test the underlying XML output by the writer. Happy to take a crack at it if you have any tips.

Possible fix for issue #3337

Using the FunctionPrefix::addFunctionPrefixStripEquals appears to fix the issue by adding the _xlfn prefix to functions in array formulas.

Happy to submit a PR if that would be helpful.

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
• [x] 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

Just tried the code in the PR branch and it does appear to fix this issue in both my sample XLSX files and the more complex client file I was working on when I came across the issue. Thanks again, @MarkBaker for the extremely quick response and analysis.

Wondering if this is the issue the folks in #950 were having.

@MarkBaker absolutely. I'll try it out as soon as I see the PR.

I believe it's actually an issue with the style for the first row. Check out the attached XLSX. It produces the problem still, despite having no value in A1. I did have a quote-prefixed value in that cell, but deleted it. It seems like the style for A1 is still showing as quote-prefixed even though the cell is blank.

Test1.xlsx

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?

A simple formula on Sheet1 referencing a named cell on Sheet2 should calculate properly. However, the Formula Engine produces a #VALUE error if there is a quote prefixed value anywhere on Sheet2.
Expected behaviour is that the formula should calculate properly.

What is the current behavior?

A #VALUE error is produced by the formula engine if there is a quote prefixed value anywhere on Sheet2.

What are the steps to reproduce?

See attached Excel file for a minimal viable example spreadsheet.

<?php

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

// Create a new Reader of the type that has been identified
$reader = IOFactory::createReader( 'Xlsx' );

// Load file into a Spreadsheet Object
$currentSpreadsheet = $reader->load('Test.xlsx');
$actualWorksheet = $currentSpreadsheet->getSheetByName( 'Sheet1' );
$array = $actualWorksheet->toArray( null, true, false, false );
var_dump($array);
die();

Test.xlsx

Note that adding

$reader->setReadDataOnly(TRUE);

To the above resolves the issue, however I need this to be set to FALSE.

Oddly, if the cell containing the quoted value in the example XLSX is deleted, the issue persists until the entire row is deleted.

What features do you think are causing the issue

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

I think the root issue is a problem with the XLSX reader. I haven't fully debugged that code yet, but I suspect it is somewhere in the if block starting on line 596 of phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php. Within this if block, quotePrefix is set in two different for loops. I suspect the issue exists in those for loops, or in whatever code builds the $xfTags and $cellXfTags arrays. Perhaps they are being linked incorrectly.

Commenting out the first if block in _calculateFormulaValue in phpoffice/phpspreadsheet/src/PhpSpreadsheet/Calculation/Calculation.php also appears to fix the issue, which is why I believe that quotePrefix is being set incorrectly.

My note above regarding the issue persisting until the entire row is deleted leads me to believe that the XLSX reader is incorrectly assigning cell styles in some cases, and that is leading to the quotePrefix attribute being incorrectly set.

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

I have only tried XLSX files.

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.4, PhpSpreadsheet 1.27