Error when reading FILTER function

[kory-]

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?

Can be read and output without error
Maybe same problem #390

What is the current behavior?

=FILTER(A:A, A:A<>"")

When reading an Excel file that uses the FILTER function, I get a "Formula Error: Unexpected ," error.

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

use PhpOffice\PhpSpreadsheet\Reader\Xlsx as Reader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as Writer;

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

$reader = new Reader;
$file_name = 'template.xlsx';
$spreadsheet = $reader->load($file_name);

$writer = new Writer($spreadsheet);
$outputPath = 'output.xlsx';
$writer->save( $outputPath );

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

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?

XLSX

Which versions of PhpSpreadsheet and PHP are affected?

phpspreadsheet: 1.25.2 php: 7.4.6
template.xlsx

[MarkBaker]

The problem isn't in loading/reading the file, it's in the calculation engine (and possibly requires an update to the Xlsx Writer as well).

As described in the MS documentation:

The newer functions in MS Excel, introduced since Excel 2010 are prefixed with _xlfn., so a function like SWITCH() (introduced in Excel 2016) is maintained internally in Excel as _xlfn.SWITCH(), and maintained in PhpSpreadsheet in the same way. When you see this function in Excel itself, it strips the prefix from the displayed function name if that version of Excel supports the function, but the prefix is visible in older versions of Excel, and the calculation cannot be executed in those older versions.
When we do a calculation in PhpSpreadsheet, we don't care if the prefix is present or not, the SWITCH() function will still be executed if it is SWITCH() or _xlfn.SWITCH().

If you add a new formula containing that function in your own code, you would probably write it as =SWITCH(...) rather than as =_xlfn.SWITCH(...) (we can't expect you to know about the internals of function names, or the list of functions that require the prefix), so the calculation engine will work with either.

We do have to ensure that the full function name (including the prefix) is used when we save an Xlsx file though, to ensure that MS Excel behaviour is maintained with that file.
So when we write the formula when you save a spreadsheet, we add the prefix if it isn't present; so if you create a formula with the function SWITCH(), we write it to the saved file as _xlfn.SWITCH(), so Excel behaviour will be maintained. We have a lookup table listing all the affected functions in /Writer/Xlsx/Xlfn.php

All of that works as expected.

---

However, MS have now decided to add a second prefix for two of the new functions that already existed and already required the _xlfn. prefix, but they haven't yet documented this. I don't know the purpose of the new _xlws. prefix, but it seems that it is used in addition to the existing _xlfn. prefix, so these two functions, SORT() and FILTER() may now appear internally in the Excel file as _xlfn.FILTER() or as _xlfn._xlws.FILTER(). Because PhpSpreadsheet is aware of the _xlfn prefix, it will work without issue in the former case; but because it isn't aware of the additional new _xlws. prefix; files that contain formula using SORT() or FILTER() with that additional new prefix aren't being handled.

Your file is created using the newest Excel, so it's using the double _xlfn._xlws.FILTER() rather than the simpler _xlfn.FILTER().

---

Fixing the calculation engine to handle the dual prefix is fairly straightforward, and I've already made that change locally; but I'll also need to modify the writer to add that second prefix for SORT() and FILTER() if it isn't present to match the latest Excel behaviour, and that's a bit more complicated to implement. It will take me a bit longer before I can submit a PR for the change; but I should have a fully tested PR to handle it later in the week. I'll delay the planned 1.26.0 release until I can include it.

[kory-]

I am relieved to hear that you are already aware of this and that it is being corrected.
I will wait for the one that includes it. Thank you.

[MarkBaker]

I am relieved to hear that you are already aware of this and that it is being corrected.

Thanks to the Python XlsxWriter library

[kory-]

Sorry to add, but if the FILTER function is specified, the written

=@FILTER(A:A, A:A<>"")

and the unintended result of the @mark being added is also a known issue?

[MarkBaker]

=@filter(A:A, A:A<>"")

PhpSpreadsheet doesn't yet fully support dynamic array functions, and cannot without a significant bc break; and that includes support for the new Spill Range Operator (#) and the Implicit Intersection Operator (@). At present, the calculation engine itself only works with fixed range functions; and while the implementations for dynamic range functions have been implemented, the fixed range limitation means that they only populate the first cell in the dynamic range. While this is (in part) the purpose of the @ operator, I can't provide support for it in version 1.x of PhpSpreadsheet, because full implementation of dynamic array formulae requires some major changes to the library that create a bc break.

I've been pushing to get the v2.0 release ready to support this, and the work is close to complete, and that includes supporting the Implicit Intersection Operator (@). But it is not yet ready for release; and recent updates to v1.x have created a merge conflict nightmare that will take some time to resolve, especially as I'm also trying to provide the functionality to support Structured References for tables (which also use the @ operator), and handling for the new Excel Lambda functions.

[kory-]

Thanks for the reply.
I see the problem is deeper rooted than I thought, I'll wait for v2.