Disable AutoFilter in Table

Jun 24, 2011 at 12:37 AM

I used the InsertTable method to create a table in a sheet. The autofilter is automatically turned on for the table. How do I turn off this AutoFilter?

 

I have tried the options in the AutoFilter Documentation, but none of them seem to remove the filter.

 

Thank you for your time.

Coordinator
Jun 24, 2011 at 5:55 AM

I see two options:

1) Insert the data but not create the Excel table:

                cell.InsertTable(theData, false);
               

2) Turn off AutoFilter after creating the Excel table:

            ws.AutoFilterRange = null; // I know it's clunky but it's kinda late, I'll come up with a more elegant solution tomorrow. 
Jun 24, 2011 at 5:22 PM

Option 1 did get rid of the autofilter, but it also removed the theme, which I would like to keep.

Option 2 didn't do anything. Immediately after inserting the table, I evaluated the ws.AutoFilterRange property and it was already null.

 

So, I started poking around a little and found the solution that worked for me:

            var table = sheet.Cell(1, 1).InsertTable(dataTable);
            table.ShowAutoFilter = false;

 

Next time, I'll stop being lazy and look around a bit more before posting a question. :)

 

Coordinator
Jun 24, 2011 at 5:29 PM

Don't worry. It's more embarrassing for me when you guys have to remind me of how to do things...

May 29, 2014 at 9:26 PM
Is this the most current information?

I tried using

wb.Worksheet(worksheetNum).AutoFilter.Enabled = false ;
wb.Worksheet(worksheetNum).AutoFilter.clear();

The first one did nothing, the second has a error.

Please let me know how to disable the autofilter, thanks!
Coordinator
May 29, 2014 at 10:26 PM
I assume you're talking about worksheet's autofilter and not a table filter...
var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("A")
    .CellBelow().SetValue(1)
    .CellBelow().SetValue(2);

ws.RangeUsed().SetAutoFilter().Column(1).EqualTo(1);

ws.AutoFilter.Column(1).Clear(); // Clear the filter for a column
ws.AutoFilter.Clear(); // Remove the autofilter from the worksheet
Dec 10, 2014 at 12:06 PM
Edited Dec 10, 2014 at 12:06 PM
This is throwing an "Object reference not set to an instance of an object" exception:
ws.AutoFilter.Clear();
I'm using version 0.75.0.0.
Jun 25, 2015 at 11:12 PM
st21chris wrote:
Option 1 did get rid of the autofilter, but it also removed the theme, which I would like to keep. Option 2 didn't do anything. Immediately after inserting the table, I evaluated the ws.AutoFilterRange property and it was already null.   So, I started poking around a little and found the solution that worked for me: var table = sheet.Cell(1, 1).InsertTable(dataTable); table.ShowAutoFilter = false;   Next time, I'll stop being lazy and look around a bit more before posting a question. :)  
Thanks for this, it worked great for me, I've been hours trying to get rid of the AutoFilter
Jun 29, 2015 at 8:41 AM
<a href=http://www.massageenvy.com/regions/LA/New-Orleans.aspx>Massage envy</a> Professional Asian body work and Health spa advisor