[GH-ISSUE #521] Feature request: Optionally hide the rows an autofilter excludes #519

New issue

Closed

opened 2026-10-02 23:11:47 -06:00 by gitea-mirror · 2 comments

gitea-mirror commented

2026-10-02 23:11:47 -06:00

Owner

Originally created by @billdenney on GitHub (Jul 28, 2026).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/521

What

An opt-in way to have libxlsxwriter hide the rows a filter excludes, rather
than requiring every caller to reimplement Excel's matching rules.

I want to be clear this is a request to revisit a deliberate decision, not a bug
report. The header documents the current contract four times, e.g.
worksheet.h:4204:

It isn't sufficient to just specify the filter condition. You must also hide
any rows that don't match the filter condition.

Why it might be worth revisiting

Excel does not apply a filter when a file is opened, so criteria alone produce
a sheet that shows the funnel icon set and every row visible — it looks filtered
and is not. Every binding therefore has to reimplement the matching rules, and
they are much subtler than they look.

Adding this to writexl took three rounds of measuring Excel's actual behaviour,
and two of my implementations were wrong in ways that produced plausible files.
Measured by writing criteria with no rows hidden, opening in Excel and pressing
Data → Reapply so Excel computed each match itself:

The rules are a property of the XML form, not the criteria. From
_set_custom_filter(), == without a wildcard and blanks become a
<filters> value list; everything else becomes <customFilters>. Excel matches
those two by entirely different rules:

Form	Excel matches on
`<filters>` value list	the text the cell displays, case-insensitively
`<customFilters>`	type — numeric if the value parses as a number, else text with `*`/`?` wildcards

Consequences that are not guessable from the criteria alone:

== "10" keeps the number 10 — a value list ignores how the cell is stored.
== "1*" keeps nothing on a numeric column: the wildcard flips the same
criteria into a typed comparison, and no number is text.
The C type of the value never reaches the file. != 10 and != "10" write
identical XML, so Excel parses val="10" as a number either way.
A text cell is not equal to a number, which makes != true for it.
A blank survives != and no other comparison — even though non-blanks is
itself written as notEqual val=" ".

Only the last of these is at all discoverable from the file format.

Suggested shape

Opt-in, so nothing changes for existing callers:

worksheet_filter_column(ws, 0, &rule);
worksheet_filter_apply(ws);   /* hide the rows the criteria exclude */

or a field on an options struct.

constant_memory mode need not be an obstacle: when the filter is set before
the rows are written, the decision can be made per row as it is streamed, which
is the same point worksheet_write_row() already decides everything else.

What I can contribute

The 27 measured cases behind the table above, as a test corpus — the workbooks,
the criteria, and what Excel actually did with each. Happy to supply them in
whatever form is useful, or to attempt a PR if the direction is welcome.

Originally created by @billdenney on GitHub (Jul 28, 2026). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/521 ## What An opt-in way to have libxlsxwriter hide the rows a filter excludes, rather than requiring every caller to reimplement Excel's matching rules. I want to be clear this is a request to revisit a deliberate decision, not a bug report. The header documents the current contract four times, e.g. `worksheet.h:4204`: > It isn't sufficient to just specify the filter condition. You must also hide > any rows that don't match the filter condition. ## Why it might be worth revisiting Excel does not apply a filter when a file is opened, so criteria alone produce a sheet that shows the funnel icon set and every row visible — it looks filtered and is not. Every binding therefore has to reimplement the matching rules, and they are much subtler than they look. Adding this to writexl took three rounds of measuring Excel's actual behaviour, and two of my implementations were wrong in ways that produced plausible files. Measured by writing criteria with no rows hidden, opening in Excel and pressing Data → Reapply so Excel computed each match itself: **The rules are a property of the XML form, not the criteria.** From `_set_custom_filter()`, `==` without a wildcard and `blanks` become a `<filters>` value list; everything else becomes `<customFilters>`. Excel matches those two by entirely different rules: | Form | Excel matches on | |---|---| | `<filters>` value list | the text the cell **displays**, case-insensitively | | `<customFilters>` | **type** — numeric if the value parses as a number, else text with `*`/`?` wildcards | Consequences that are not guessable from the criteria alone: * `== "10"` keeps the number `10` — a value list ignores how the cell is stored. * `== "1*"` keeps **nothing** on a numeric column: the wildcard flips the same criteria into a typed comparison, and no number is text. * The C type of the value never reaches the file. `!= 10` and `!= "10"` write identical XML, so Excel parses `val="10"` as a number either way. * A text cell is not equal to a number, which makes `!=` *true* for it. * A blank survives `!=` and no other comparison — even though `non-blanks` is itself written as `notEqual val=" "`. Only the last of these is at all discoverable from the file format. ## Suggested shape Opt-in, so nothing changes for existing callers: ```c worksheet_filter_column(ws, 0, &rule); worksheet_filter_apply(ws); /* hide the rows the criteria exclude */ ``` or a field on an options struct. `constant_memory` mode need not be an obstacle: when the filter is set before the rows are written, the decision can be made per row as it is streamed, which is the same point `worksheet_write_row()` already decides everything else. ## What I can contribute The 27 measured cases behind the table above, as a test corpus — the workbooks, the criteria, and what Excel actually did with each. Happy to supply them in whatever form is useful, or to attempt a PR if the direction is welcome.

gitea-mirror closed this issue

2026-10-02 23:11:47 -06:00

gitea-mirror commented

2026-10-02 23:11:48 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jul 29, 2026):

An opt-in way to have libxlsxwriter hide the rows a filter excludes, rather
than requiring every caller to reimplement Excel's matching rules.

The Rust version of this library rust_xlsxwriter does that. However, I am unlikely to add it to the C version due to the work involved.

Having said that, if you are implementing filtering from a Dataframe language like R then you already have an equivalent filtering language that you use to figure out which rows should be hidden. This could be something that you could implement in writexl without any changes in libxlsxwriter.

Closing as won't fix/not planned.

@jmcnamara commented on GitHub (Jul 29, 2026): > An opt-in way to have libxlsxwriter hide the rows a filter excludes, rather than requiring every caller to reimplement Excel's matching rules. The Rust version of this library [`rust_xlsxwriter`](https://github.com/jmcnamara/rust_xlsxwriter) does that. However, I am unlikely to add it to the C version due to the work involved. Having said that, if you are implementing filtering from a Dataframe language like R then you already have an equivalent filtering language that you use to figure out which rows should be hidden. This could be something that you could implement in `writexl` without any changes in `libxlsxwriter`. Closing as won't fix/not planned.

gitea-mirror commented

2026-10-02 23:11:48 -06:00

Author

Owner

@billdenney commented on GitHub (Jul 29, 2026):

Yes, I already implemented it on the R side. I was thinking it could be of general interest. Makes sense.

@billdenney commented on GitHub (Jul 29, 2026): Yes, I already implemented it on the R side. I was thinking it could be of general interest. Makes sense.