Excel does not seem to respect any value used in a table column's setShowFilterButton() method

[kmitch-duke-edu]

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?

When a table column's setShowFilterButton() method is set to false/FALSE/0, then Excel should not show the filter-drop-down button on the column header.

What is the current behavior?

It does not seem to matter what value is passed to the table column's setShowFilterButton() method, as Excel shows the filter-drop-down buttons for every column in the table. This may be a limitation of Excel, though--I can't really tell.

What are the steps to reproduce?

As far as I can tell, this issue is present in the 01_Table sample in this repo

1. Run the script in PHPOffice/PhpSpreadsheet/master/samples/Table/01_Table.php
2. Open the .xslx file produced by the 01_Table.php sample script.
3. Note how all columns in the table have the drop-down filter buttons visible; Column D's drop-down filter button should be hidden/invisible.

The tricky part to this is that the .xslx output from this repo (and the 01_Table.php sample script specifically) appears to be correct, in that the underlying XML data for the Excel file appears to be set properly. In the .xslx file's XML structure, the xl/tables/table1.xml file has the following entry for column D:

<filterColumn colId="3" hiddenButton="1"/>

Yet Excel shows a drop-down filter button for all columns. (see attached screenshot)

I've also attached the output from the 01_Table.php script, in case that's helpful.
01_Table.xlsx

What features do you think are causing the issue

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

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

I only tried this with XLSX, but I'd guess XLS would have similar problems, as it's very likely that Excel itself doesn't actually support the hiddenButton attribute.

Which versions of PhpSpreadsheet and PHP are affected?

I tried this with PhpSpreadsheet v1.29.0 and PHP v8.1

[oleibman]

There appears to be a misplaced statement in Writer/Xlsx/Tables. Expect a fix in a day or two. Just because I think it's interesting, doing something like this in Excel itself seems possible only with VBA.

[kmitch-duke-edu]

Thanks so much, @oleibman -- that's fantastic to know that a fix is possible! And looking more closely at the MS documentation on the "hiddenButton" attribute, that makes total sense that the only way to adjust this within Excel is with VBA. (And it also explains why I couldn't find any threads out there describing how to hide the filter drop-downs in the regular Excel application interface.)

I also wanted to thank you and everybody else who's been developing this repository -- what an amazingly rich tool to interface with Excel/spreadsheet-applications!

[kmitch-duke-edu]

Thanks again for all your work and help on this, @oleibman !

I've tested out the fix from #3992 , and it works perfectly with both the test 01_Table.php script, and a locally developed Excel export, so it all looks great to me, and thanks for catching the ancillary changes as well that you identified in #3992 .

We're interfacing with this repository in a Drupal site that uses a module (xls_serialization) that's still using the 1.x branch of this repo, so it may take a while for your fix to make its way down to us on our site, but it's great to know that eventually we'll have the ability to control which columns get the filter drop-down feature.

Thanks again!