Live SpreadsheetGear API Samples

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(@"Files\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);
}