How to remove AutoFilter Using C# in EPPlus

Question

I have tried the below C# CODING:

wsDt.Cells["A10:G10"].AutoFilter = false;

but the filter is not removed from my excel.

Any other way to remove it.

Thanks...

Penholder

Can you give us a little more to work with? – Chavarria

Answer 1

This seems to be an EPPlus bug and I don't think it has been resolved as of the latest release (4.04), at least I could figure out a solution. My workaround is to simply load the spreadsheet values a row at a time with a loop:

int sheetRow = 3;
for (int outer = 0; outer < outerSourceTable.Rows.Count; outer++)
{
    var outerThingId = Convert.ToInt32(outerSourceTable.Rows[outer]["OuterThingId"]);
    var outerThingName = Convert.ToString(outerSourceTable.Rows[outer]["OuterThing"]);
    var innerThingsTable = _repository.GetInnerThings(outerThingId);
    if (innerThingsTable.Rows.Count > 0)
    {
        myWorksheet.Cells[sheetRow, 1].Value = outerThingName;

        // Load the data into the worksheet. We need to load a row at a time
        // to avoid the auto-filter bug
        for (int inner = 0; inner < innerThingsTable.Rows.Count; inner++)
        {
            var innerName = Convert.ToString(innerThingsTable.Rows[inner]["Name"]);
            var innerDescr = Convert.ToString(innerThingsTable.Rows[inner]["Description"]);
            myWorksheet.Cells[sheetRow, 2].Value = innerName;
            myWorksheet.Cells[sheetRow, 3].Value = innerDescr;
            sheetRow++;
        }
        sheetRow++;
    }
}

Llovera

Answer 2

In Excel, when you use the Format as Table option it will not only style the data but will also create a Named Range - Table1. This option also automatically enables the Filter Buttons. After formatting as a table, you can uncheck Filter Buttons in the Table Tools -> Table Styles Options.

What works for me is doing the same programmatically.

1. LoadFromDataTable(DataTable, bool, TableStyles) basically
   • pastes data to the worksheet starting at the ExcelRange
   • applies a Table Format
   • uses the DataTable.TableName to name the range
   • enables Filter Button

2. Disable the Filter Button

   • use the DataTable.TableName to reference the named range

   • set ShowFilter to false enter code here

     //imagine a table with 5 columns
     DataTable dt = new DataTable();
     dt.TableName = "UniqueTableName";
     
     //define the cells where the headers will appear
     int topRow = 1;
     int leftMostColumn = 1;
     int rightMostColumn = 5;
     
     //bind the DataTable using LoadFromDataTable()
     OfficeOpenXml.ExcelRange excelRange = worksheet.Cells[topRow, leftMostColumn, topRow, rightMostColumn];
     excelRange.LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Light8);
     
     //turn of the filtering
     OfficeOpenXml.Table.ExcelTable table = worksheet.Tables[dt.TableName];
     table.ShowFilter = false;
     

Hootchykootchy

If you dont use the LoadFromDataTable() call but instead use something like LoadFromCollection() the tables default name is 'Table1'. So you can reference it as follows, ExcelTable table = worksheet.Tables["Table1"]; – Injun

Answer 3

If you populate your excel data using the LoadFromCollection() call. You can then reference it using the default Excel table name of "Table 1".

This is the same idea as Patricks answer but demonstrates the use without DataTable.

excelWorksheet.Cells.LoadFromCollection(myCollection);
ExcelTable table = excelWorksheet.Tables["Table1"];
table.ShowFilter = false;

Injun

Answer 4

Sometimes excel creates the Table as a named range. In my instance the Table was the only thing in the first worksheet, so the following helped me:

var ws = wb.Worksheets.First();
ws.NamedRanges.FirstOrDefault()?.Ranges.FirstOrDefault()?.SetAutoFilter(false);

Plutonium