How can I filter columns (rather than rows) in EPPlus?

Question

Filtering rows in a particular column is as easy as pie in EPPlus:

private ExcelWorksheet prodUsageWorksheet;
. . .
prodUsageWorksheet.Cells["A6:A6"].AutoFilter = true;

This allows me to filter the rows in column A:

I also need to filter certain columns out, such as the month columns (in the screenshot, Sep 15 and Oct 15, but are usually several more). For example, I want to generate the following programmatically with EPPlus:

Deselecting "Select All", selecting a subset of months, and then clicking the OK button makes the one[s] not selected collapse.

Looking at some legacy Excel Interop code, it would seem that there, it is done like this:

fld = ((PivotField) pvt.PivotFields("Month"));
fld.Orientation = XlPivotFieldOrientation.xlColumnField;
fld.NumberFormat = "MMM yy";

Specifically, the second block of code (with the orientation set to xlColumnField) is the column that sports the sort/filter button that, when manipulated, conditionally shows/hides various columns.

Does it determine which columns are showable/hideable based on the number format? That is to say, if the value is "Sep 15" or "Oct 16"?

I don't know, but I can't see anything else in the code that is more specifically setting the limits of the column filtering.

At any rate, if this is how Excel Interop accomplishes it, what is the equivalent in EPPlus?

You've probably already checked this post, but it seems like you EPPlus isn't capable of natively adding column filters.. The link does provide a workaround but I don't believe it matches your case — Innat3
Yes, I saw that, thanks; for this I'm having to resort back to Excel Interop, unfortunately. It's like booking the Troggs when the Rolling Stones are unavailable, because the Stones refuse to play "Wild Thang!" — B. Clay Shannon
Yes, I was going to suggest migrating to the Interop library I feel your pain :/ — Innat3
Unfortunately, what you are looking for would be very difficult in XML. There are many files you have to manipulate and I was never all to figure them all out before giving up since it was not THAT important for what I was doing. Good luck. — Ernie S
I had a similar problem, here's what I did: So basically an xlsx is just a zip file. Create your base workbook, unzip it to somewhere, modify the workbook in Excel as you wish, save it, unzip it again and check the difference of the the unzipped content. Then you can mimic the behavior of Excel by adding or editing the entries yourself from your code. Best part: no Excel-interop! I wrote up a blog post about it too, you can check it out here: dotnetfalcon.com/adding-custom-parts-to-an-xlsx-workbook If you like this approach, I'm happy to discuss it further, if you need help. — Akos Nagy

Unknown Unknown · Accepted Answer · 2017-04-04T16:51:10

This has been asked in another posting. Also, this is not what EPPlus is intended for, as column filtering is more a 'Dynamic' end user functionality.

Take a look at this and see if it answers your question:

Adding a specific autofilter on a column

How can I filter columns (rather than rows) in EPPlus?

1 Answers