How to disable autofilter in closedXml c#?

Question

I am facing a weird problem in closedXML library.

I am exporting a datatable to .xlsx (excel file) using closedXML library. By default, autofilter is enabled in the library.

I want to disable or remove the autofilter and export only the datatable.

Here is the code I tried but its not working

XLWorkbook wb = new XLWorkbook();
var ws = wb.Worksheets.Add("Test");
ws.Cell(1, 1).InsertTable(dataTable);
ws.AutoFilter.Enabled = false;
ws.Columns().AdjustToContents();
wb.SaveAs("Report.xlsx");

and I also tried

ws.AutoFilter.Clear();

Even the column wise clear filter is not working

ws.AutoFilter.Column(1).Clear();

score 23 · Accepted Answer · edited Dec 22 '15 at 19:19

23

Try to use below code and it should work fine

ws.Tables.FirstOrDefault().ShowAutoFilter = false;

edited Dec 22 '15 at 19:19

bozzmob

12,364
16
50
73

answered Dec 22 '15 at 18:22

Hamaresha

256
2
4

Welcome to StackOverflow! You might want to read this page before you post your next question. http://stackoverflow.com/help/how-to-ask – Peter Hornsby Dec 22 '15 at 18:31
2

Its working. And I also found how to disable autofilter all sheet. foreach (IXLWorksheet workSheet in wb.Worksheets) { foreach (IXLTable table in workSheet.Tables) { workSheet.Table(table.Name).ShowAutoFilter = false; workSheet.Columns().AdjustToContents(); } } – CST RAIZE Dec 23 '15 at 09:59
1

Great, note that AdjustToContents might takes time based on the amount of your data – Hamaresha Dec 24 '15 at 12:27

score 1 · Answer 2 · edited Oct 23 '20 at 17:21

1

I had the same problem, and the following code didn't work for me,

ws.Tables.FirstOrDefault().ShowAutoFilter = false;

I have ClosedXML version 0.93, what worked for me is:

ws.Tables.FirstOrDefault().SetShowAutoFilter(false);

edited Oct 23 '20 at 17:21

spottedmahn

14,823
13
108
178

answered Sep 30 '18 at 21:52

Yaspino

21
2

score 1 · Answer 3 · answered May 23 '21 at 10:22

1

You can use ws.Worksheet(1).AutoFilter.Clear() 'Vb.net

answered May 23 '21 at 10:22

user16007020

11
1

score 0 · Answer 4 · answered Jun 29 '17 at 16:36

0

this might also work:

ws.AutoFilterMode = False

answered Jun 29 '17 at 16:36

gregV

987
9
28

score 0 · Answer 5 · answered Sep 12 '18 at 10:20

0

to remove all tables filter you can use Linq ForEach.

ws.Tables.ForEach(x=>x.ShowAutoFilter = false);

answered Sep 12 '18 at 10:20

Manish Vadher

1,524
15
14

score 0 · Answer 6 · answered May 15 '23 at 13:03

In ClosedXML version 0.101.0 (.net core 7), I needed to use:

worksheet.RangeUsed().SetAutoFilter(false);

to remove the auto filter. Without this, ClosedXML was giving an error that didn't appear in previous versions:

The range 'worksheetname'!A1:F2 overlaps with the worksheet's autofilter.

How to disable autofilter in closedXml c#?

6 Answers6