CellRange trimmed if from and to is equal, result in auto filter errors

[brainfoolong]

This is:

- [x] a bug report

I have found this line:

PhpSpreadsheet/src/PhpSpreadsheet/Cell/CellRange.php

Line 127 in 88bbac9

if ($this->from->cellAddress() === $this->to->cellAddress()) {

Which effectively convert a range into a single cell, which then causes errors in other functions that explicitely require a range cell name, even of the range consists of just one field.

So the result is, i cannot set AutoFilter with a range of one cell and it will throw an error

Cell get's converted here (__toString is called):

PhpSpreadsheet/src/PhpSpreadsheet/Worksheet/Validations.php

Line 32 in 88bbac9

return (string) $cellAddress;

Error thrown here

PhpSpreadsheet/src/PhpSpreadsheet/Worksheet/AutoFilter.php

Line 127 in 88bbac9

throw new PhpSpreadsheetException('Autofilter must be set on a range of cells.');

Filter isset with:

PhpSpreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php

Line 2143 in 88bbac9

public function setAutoFilterByColumnAndRow($columnIndex1, $row1, $columnIndex2, $row2)

The current workaround is to use setAutoFilter instead of setAutoFilterByColumnAndRow and providing a string range.

[MarkBaker]

I hate to ask, but why would you set autofilter on a single cell?

[brainfoolong]

Thanks for answer. To provide a interface where the user can filter, sort, etc... without the need to do anything. It's valid for just one column, or more. Anyway, a single cell works when using setAutoFilter, it's just not working with setAutoFilterByColumnAndRow.

[brainfoolong]

It creates that for the user, which is pretty handy, even on a single column.

[MarkBaker]

I'm still not sure that I understand the case for this: I'm trying to create a single cell autofilter in MS Excel itself, but it always seems to adjust it to the full data range

You are also aware that the *byColumnAndRow() methods are deprecated?

[brainfoolong]

Hm. Ok. That *byColumnAndRow() is deprecated is new to me. Seems that PhpStorm doesn't check that when @Depreceted where it's D is uppercase. Weird :)

Thanks for that excel info. Our internal use cases and codes always only used setAutoFilter with a single row and 1 or X columns. So we can have zero or more rows after.

So we have, for example, to mark row 1 as auto filter, with given numer of columns:

setAutoFilterByColumnAndRow(1, 1, 1, 1);
setAutoFilterByColumnAndRow(1, 1, 3, 1);
setAutoFilterByColumnAndRow(1, 1, 9, 1);

This always worked perfectly fine. However, the case with only one column is indeed pretty rare and can only be when user do some weird settings on the reports. But we never provide more then one row...

Are we doing this wrong, ever since? We have hundreds of phpspreadsheet reports using it like that.

[brainfoolong]

I have also tested in my excel, and no errors appear when using a filter settings for that.
Is auto filter in phpspreadsheet not just doing that?

See animation:

[MarkBaker]

Defining an AntoFilter in PhpSPreadsheet has always assumed a range of cells, but only ever validated that the provided string matched the expected pattern for a range. I'd never even considered that range might be E4:E4, and I suspect that would break a few of the AutoFilter methods if you tried setting filter values for that range.
It was purely oversimplistic validation that allowed a single cell range to be applied in the first place.

MS Excel actually stores this as a cell reference, and not as a range reference; so it stored E4, and not E4:E4. While I've not tested to confirm it yet, I imagine that most of the AutoFilter methods will throw some exception if they're called on an autofilter defined with only a single row.

But if I remove that validation check, and allowed a single cell reference to match MS Excel, then I see that it will break a few things in the save(), because we also created DefinedNames for the range, and that expects an actual range.

[brainfoolong]

Ok, i understand. So, what is your advice on how i should do it? Should i do it, the way i do, defining a range E4:E4 by hand, to overcome the check in autofilter?

[MarkBaker]

For the moment, define as E4:E4 by hand: it will take me a while to identify and find a resolution for all the knock-on effects, initially when saving a file, but also to prevent developers trying to set filters when they can't be set because of a single-cell range.

I'll probably also take a look at how MS Excel auto-adjusts the range as data is added after the autofilter has been set, to see if that's a behaviour that should be replicated.

[brainfoolong]

Great. Thanks for your replies.

[MarkBaker]

If you want to do some testing, take a look at #3111

[brainfoolong]

Thanks for the fast respond and code fixes. Unfortunetely i don't have time to test those changes.