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...

Answer 1

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;
     

Answer 2

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++;
    }
}