CODEWITHSUNDEEP
c#asp.netexcelepplus
Joe Mike
Joe Mike

Reputation: 1170

How to remove AutoFilter Using C# in EPPlus

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

Upvotes: 7

Views: 6065

Answers (4)

dalcam
dalcam

Reputation: 1077

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);

Upvotes: 0

GER
GER

Reputation: 2042

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;

Upvotes: 0

Patrick
Patrick

Reputation: 61

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;

Upvotes: 6

GDB
GDB

Reputation: 3579

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

Upvotes: 1

Related Questions