Add or Remove AutoFilter in Excel File (XLSX/XLS) using C#

readme.md

You can read more at: Add or Remove AutoFilter in Excel File (XLSX/XLS) using C#

ApplyAutofilter.cs

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

// Creating AutoFilter by giving the cells range of the heading row
worksheet.AutoFilter.Range = "A1:B1";

// Saving the modified Excel file
workbook.Save(dataDir + "output.xlsx");

ContainAutoFilter.cs

// Instantiating a Workbook object containing sample data
Workbook workbook = new Workbook(dataDir + "sourseSampleCountryNames.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Creating AutoFilter by giving the cells range
worksheet.AutoFilter.Range = "A1:A18";

// Initialize filter for rows containing string "Ba"
worksheet.AutoFilter.Custom(0, FilterOperatorType.Contains, "Ba");

//Refresh the filter to show/hide filtered rows
worksheet.AutoFilter.Refresh();

// Saving the modified Excel file
workbook.Save(dataDir + "outSourseSampleCountryNames.xlsx");

CustomNumberAutoFilter.cs

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(dataDir + "Number.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Call Custom function to apply the filter
worksheet.AutoFilter.Custom(0, FilterOperatorType.GreaterOrEqual, 5, true, FilterOperatorType.LessOrEqual, 10);

// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();

// Saving the modified Excel file
workbook.Save(dataDir + "FilteredNumber.xlsx");

DateAutoFilter.cs

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(dataDir + "Date.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Call AddDateFilter function to apply the filter
worksheet.AutoFilter.AddDateFilter(0, DateTimeGroupingType.Month, 2018, 1, 0, 0, 0, 0);

// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();

// Saving the modified Excel file
workbook.Save(dataDir + "FilteredDate.xlsx");

DynamicDateAutoFilter.cs

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(dataDir + "Date.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Call DynamicFilter function to apply the filter
worksheet.AutoFilter.DynamicFilter(0, DynamicFilterType.January);

// Call refresh function to update the worksheet
worksheet.AutoFilter.Refresh();

// Saving the modified Excel file
workbook.Save(dataDir + "FilteredDynamicDate.xlsx");

RemoveAutoFilter.cs

// Instantiating a Workbook object
// Opening the Excel file
Workbook workbook = new Workbook(dataDir + "output.xlsx");
// Iterate through each worksheet
foreach (var sheet in workbook.Worksheets)
{
    // Remove AutoFilters
    sheet.RemoveAutoFilter();
}
// Save the workbook
workbook.Save(dataDir + "AutoFilter_Removed.xlsx", SaveFormat.Xlsx);