Live SpreadsheetGear API Samples
Range Samples Operations Auto Filter
Use auto filters to view subsets of data in a worksheet. Ten examples are provided, each one executing a different type of criteria on its own worksheet.
// Open workbook containing data that will be AutoFiltered. The workbook contains
// 10 different sheets--each will be AutoFiltered using different criteria.
SpreadsheetGear.IWorkbook workbook =
SpreadsheetGear.Factory.GetWorkbook(@"C:\Path\To\AutoFilterSampleData.xlsx");
// EXAMPLE 1: Single Product Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Single Product"];
// Initialize the zero-based field index within the auto filter range.
int field = 0;
// Initialize criteria1 as a single product name.
string criteria1 = "Thyme";
// Filter by a single product. SpreadsheetGear will automatically expand the
// A1 reference to include in the AutoFilter all cells in the surrounding region.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Or, null, true);
}
// EXAMPLE 2: Multiple Products Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Multiple Products"];
// Initialize the zero-based field index within the auto filter range.
int field = 0;
// Initialize criteria1 as an array of product names.
string[] criteria1 = new string[3] { "Marjoram", "Oregano", "Rosemary" };
// Filter by multiple products.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Values, null, true);
}
// EXAMPLE 3: Top 10 Items Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Top Items"];
// Initialize the field index within the auto filter range.
int field = 2;
// Initialize criteria1 as the number of top items.
int criteria1 = 5;
// Filter by Q1 Top Items.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Top10Items, null, true);
}
// EXAMPLE 4: Top 10% Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Top Percent"];
// Initialize the field index within the auto filter range.
int field = 3;
// Initialize criteria1 as a percentage of top items.
int criteria1 = 10;
// Filter by Q2 Top Percent.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Top10Percent, null, true);
}
// EXAMPLE 5: Bottom 10 Items Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Bottom Items"];
// Initialize the field index within the auto filter range.
int field = 4;
// Initialize criteria1 as the number of bottom items.
int criteria1 = 7;
// Filter by Q3 Bottom Items.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Bottom10Items, null, true);
}
// EXAMPLE 6: Bottom 10% Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Bottom Percent"];
// Initialize the field index within the auto filter range.
int field = 5;
// Initialize criteria1 as a percentage of bottom items.
int criteria1 = 10;
// Filter by Q4 Bottom Percent.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Bottom10Percent, null, true);
}
// EXAMPLE 7: Greater Than Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Greater Than"];
// Initialize the field index within the auto filter range.
int field = 2;
// Initialize criteria1 using the greater than operator.
string criteria1 = ">1850";
// Filter by Q1 Greater Than.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Or, null, true);
}
// EXAMPLE 8: Between Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Between"];
// Initialize the field index within the auto filter range.
int field = 3;
// Initialize criteria1 using the greater than or equal to operator.
string criteria1 = ">=1200";
// Initialize criteria2 using the less than or equal to operator.
string criteria2 = "<=1400";
// Filter by Q2 Between.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.And, criteria2, true);
}
// EXAMPLE 9: Cell Interior Color Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Green Color"];
// Initialize the field index within the auto filter range.
int field = 4;
// Initialize criteria1 with a color.
SpreadsheetGear.Color criteria1 = SpreadsheetGear.Colors.SeaGreen;
// Filter by Q3 Color.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.CellColor, null, true);
}
// EXAMPLE 10: Red Conditional Format Icon Filter
{
// Get a reference to the relevant worksheet.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Red Icon"];
// Initialize the field index within the auto filter range.
int field = 5;
// Initialize criteria1 with the first icon in the ThreeTrafficLights1 icon set.
SpreadsheetGear.IIcon criteria1 =
workbook.IconSets[SpreadsheetGear.IconSet.ThreeTrafficLights1][0];
// Filter by Q4 Icon.
worksheet.Cells["A1"].AutoFilter(field, criteria1,
SpreadsheetGear.AutoFilterOperator.Icon, null, true);
}
Run This Sample
Download File
Download an Excel Open XML Workbook (*.xlsx) file with the results of this sample.
Supporting Files
The following files are utilized by this sample:
Related Samples
Range Samples > Operations