ENH: adding a filter (and bold) to header when writing to excel

[simonaubertbd]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

Hello,

One of the things I always do when opening an excel file is adding filter on header. Just like that

Feature Description

I'm not exactly sure

Alternative Solutions

I see that as an option to to_excel function or to class ExcelWriter, not sure of what would be the best.

Additional Context

No response

[rhshadrach]

Thanks for the request. Assuming all of our current engines can support this, this seems like a common desire; I'm positive here. I would recommend the argument:

autofilter: bool | list-like = False

allowing the user to control which index/columns get filters. However, we'd need to workout how this interacts with other arguments, namely startrow and mergecells.

[janeq6]

take

[janeq6]

take

[simonaubertbd]

@rhshadrach @janeq6 The most common usage, I think, is the bold format with what is called auto-filter on excel
https://xlsxwriter.readthedocs.io/working_with_autofilters.html
https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.filters.html#module-openpyxl.worksheet.filters

[DhruvBShetty]

In excel, since a filter can only be applied over a continuous range of cells, I think the argument

autofilter: bool | range = False

would be more optimal as it's more in line with how it's applied in excel and makes input validation far easier. As for merge cells, Excel isn't really designed to filter columns with merged cells.

I would recommend to not apply the autofilter if there are any merged cells if the argument is simply True, but when provided a range, to check if there any merged cells in that range of columns, if no, then apply it to that range, otherwise don't apply it and raise a warning.

[rhshadrach]

Unfortunately range(0, 5, 2) is not contiguous either. I wonder if we should just accept bool here.

[DhruvBShetty]

How about if it raises a warning and doesn't apply the filter if the step does not equal 1 and have it mentioned in the docs?

[rhshadrach]

I would be more for raising instead of warning - the user provided input which broken the API contract. Rethinking this, if we are going to accept more than bool, I think it should be a list-like (note: range is list-like) that we validate is contiguous. Requiring users provide a range is not ergonomic in my opinion, can validating it is contiguous is not a heavy lift.

[attack68]

I raised an opinion in other threads (unfortunately I searched but could not find) that DataFrame.to_excel should yield an unformatted raw excel file (no bold, underlines, cell formatting) etc, and Styler.to_excel should provide functionality to add Excel elements.

Styler.to_excel already converts the two pseudo-CSS styles "border-style" and "number-format" on a per-cell basis. It would be possible to add another pseudo-CSS style that would give per column control for adding these filters. It would also not impinge on the underlying DataFrame.to_excel