19

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:

enter image description here

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:

enter image description here

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?

Tom
  • 1,636
  • 2
  • 13
  • 21
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 2
    You've probably already checked [this](http://stackoverflow.com/questions/32723483/adding-a-specific-autofilter-on-a-column) 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 Oct 13 '16 at 15:45
  • 1
    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-B. Crow Raven Oct 13 '16 at 15:47
  • 2
    Yes, I was going to suggest migrating to the Interop library I feel your pain :/ – Innat3 Oct 13 '16 at 15:49
  • 1
    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 Oct 16 '16 at 11:30
  • @Ernie: XML? I'm not doing anything in XML; unfortunately, I had to revert to Excel Interop to create PivotTables programmatically. – B. Clay Shannon-B. Crow Raven Oct 17 '16 at 14:40
  • One of you (or somebody) may as well write an answer, so I can award the bounty; otherwise it will "go up in smoke" – B. Clay Shannon-B. Crow Raven Oct 18 '16 at 14:36
  • Did you try to use pivot table to achieve such results? http://stackoverflow.com/q/11650080/1437693 – Yuriy Tseretyan Jan 09 '17 at 20:35
  • @YuriTceretian: Yes, but I eventually found EPPlus to be wanting, PivotTable-wise, and switched to Aspose Cells for the report where a PivotTable was required. – B. Clay Shannon-B. Crow Raven Jan 09 '17 at 21:03
  • Why do not you just use C#? I am guessing you are writing data to excel sheet, use lists of objects that might represent your data, sort the list then write it to excel – Yahya Hussein Feb 22 '17 at 17:51
  • @YahyaHHussein: I am using C#; if you mean, why not use Excel Interop, I started with that, but it is WAY too slow. EPPlus is a vast improvement, and for heavier-duty work, Aspose has been the way to go for me. Such as, for PivotTables, Aspose "saved my bacon." YMMV. – B. Clay Shannon-B. Crow Raven Feb 22 '17 at 18:10
  • 2
    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: http://www.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 Mar 06 '17 at 16:45
  • Thanks, I had to resort to Aspose for this, and so I am "good" for now. – B. Clay Shannon-B. Crow Raven Mar 06 '17 at 16:56

1 Answers1

3

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

Community
  • 1
  • 1