Set filter choices for written data table

[jwhendy]

Is your feature request related to a problem? Please describe.
Using writeDataTable is great, however when sharing my work with colleagues, the expectation would be to have certain filters set. I cannot find a way to not only have filters, but also to set how I want them filtered when creating the workbook. Thus, I need to write it, open it, set the filters myself, then save again.

Describe the solution you'd like
I could envision this as part of writeDataTable, or a separate helper function similar to setColWidths (e.g. setDataTableOptions?).

So maybe something like this, assuming I write a table with colName1, with values value1, value2, value3, and NA (blank). This would generate the table such that the equivalent user action would be to click the column drop down, uncheck value3 and leave Blank selected.

writeDataTable(wb, sheet, x, ..., filters = list(colName1 = c(value1, value2, NA)), ...)

Describe alternatives you've considered
I've google around and found what I think is this question on SO, but the only answer is to use VBS, which I'm not interested in doing.

Additional context
Add any other context or screenshots about the feature request here.

[github-actions]

This issue is stale because it has been open 365 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[jwhendy]

Can I #bump via comment to remove this stale label? Bummer how the onus is on the person wanting help to keep it alive. Also wild that it's been a full year!

[JanMarvin]

Hi @jwhendy,

Thank you for raising this issue and for your interest in our package. I understand how this feature could be valuable from a user perspective. However, from a development standpoint, implementing it would be quite complex and is currently outside the scope of what we can realistically support.

To give you some context, implementing filtering in OOXML documents is not a straightforward task. It involves creating an XML structure for the table element and hiding unselected rows, which may sound simple at first, but quickly becomes challenging. For example:

• It is relatively simple when filtering by a single column in a standalone table.
• It becomes more complicated when multiple columns or multiple tables on a sheet are involved.
• It is even more complex when there are different behaviors between R and OOXML (e.g., case-sensitivity differences like "Berlin" vs. "BERLIN").
• The situation becomes further complicated if an autofilter is present on the page, which can cause unexpected behavior.
• What happens if a user requests to unhide a certain row? Do we have to update the filter, will it break?

Each of these scenarios requires thorough research and testing to ensure it works correctly, and implementing this feature would demand a amount of time and effort I am currently unwilling to invest.

I understand that you might find preparing spreadsheets manually for your colleagues tedious, but the development effort required on our side would involve a similarly tedious and time-consuming process before we could provide a solution.

It's important to clarify that open source development is primarily driven by the interests of the developers (or in some other other projects, by the employers of the developers). We have to prioritize what aligns with our own focus and capacity, and this particular feature request isn't something that matches our current priorities.

That said, I've implemented a partial solution for openxlsx2, which you might find helpful. If you'd like, you could try backporting it to openxlsx or prepare a pull request for openxlsx2 so we can discuss it further. Please be aware, however, that openxlsx is currently in a kind of maintenance mode. I've merged a few pull requests recently and submitted a release to CRAN, but overall, the package is reaching the end of its active development phase.

Thank you again for your understanding, and I hope this provides some clarity on our current position.

[jwhendy]

@JanMarvin fantastic response and totally understood on all points. As a non-expert, I didn't have a gauge for how difficult this was (my initial intuition is it might already exist), nor what the development backlog/queue looks like and where this fits.

This is an awesome reference that can serve to help any others with this idea understand and I appreciate the time taken to summarize all of this. I'll take a look at the partial solution, thanks for providing!