Class PivotFilter

PivotFilter class

Represents a PivotFilter in PivotFilter Collection.

public class PivotFilter

Properties

NameDescription
AutoFilter { get; }(Obsolete.) Gets the autofilter of the pivot filter.
EvaluationOrder { get; set; }Gets the Evaluation Order of the pivot filter.
FieldIndex { get; }Gets the index of source field which this pivot filter is applied to.
FilterCategory { get; }Gets the category of this filter.
FilterType { get; }Gets the autofilter type of the pivot filter.
MeasureCubeFieldIndex { get; }Specifies the index of the measure cube field. this property is used only by filters in OLAP pivots and specifies on which measure a value filter should apply.
MeasureFldIndex { get; set; }(Obsolete.) Gets the measure field index of the pivot filter.
MemberPropertyFieldIndex { get; set; }Gets the member property field index of the pivot filter.
Name { get; set; }Gets the name of the pivot filter.
UseWholeDay { get; set; }Indicates whether uses whole days in its filtering criteria.
Value1 { get; set; }Gets the string value1 of the label pivot filter.
Value2 { get; set; }Gets the string value2 of the label pivot filter.
ValueFieldIndex { get; set; }Gets the index of value field in the value region.

Methods

NameDescription
GetDateTimeValues()Gets values of the number filter.
GetLabels()Gets labels of the caption filter.
GetNumberValues()Gets values of the number filter.
GetTop10Value()Gets top 10 setting of the filter.

Examples

namespace AsposeCellsExamples
{
    using Aspose.Cells;
    using Aspose.Cells.Pivot;
    using System;

    public class PivotFilterDemo
    {
        public static void PivotFilterExample()
        {
            // Create a new workbook
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;

            // Populate the worksheet with sample data
            cells[0, 0].Value = "fruit";
            cells[1, 0].Value = "grape";
            cells[2, 0].Value = "blueberry";
            cells[3, 0].Value = "kiwi";
            cells[4, 0].Value = "cherry";
            cells[5, 0].Value = "grape";
            cells[6, 0].Value = "blueberry";
            cells[7, 0].Value = "kiwi";
            cells[8, 0].Value = "cherry";

            cells[0, 1].Value = "year";
            cells[1, 1].Value = 2020;
            cells[2, 1].Value = 2020;
            cells[3, 1].Value = 2020;
            cells[4, 1].Value = 2020;
            cells[5, 1].Value = 2021;
            cells[6, 1].Value = 2021;
            cells[7, 1].Value = 2021;
            cells[8, 1].Value = 2021;

            cells[0, 2].Value = "amount";
            cells[1, 2].Value = 50;
            cells[2, 2].Value = 60;
            cells[3, 2].Value = 70;
            cells[4, 2].Value = 80;
            cells[5, 2].Value = 90;
            cells[6, 2].Value = 100;
            cells[7, 2].Value = 110;
            cells[8, 2].Value = 120;

            // Add a pivot table to the worksheet
            PivotTableCollection pivots = sheet.PivotTables;
            int pivotIndex = pivots.Add("=Sheet1!A1:C9", "A12", "TestPivotTable");
            PivotTable pivot = pivots[pivotIndex];
            pivot.AddFieldToArea(PivotFieldType.Row, "fruit");
            pivot.AddFieldToArea(PivotFieldType.Column, "year");
            pivot.AddFieldToArea(PivotFieldType.Data, "amount");

            // Set pivot table style
            pivot.PivotTableStyleType = PivotTableStyleType.PivotTableStyleMedium10;

            // Add a pivot filter
            int filterIndex = pivot.PivotFilters.Add(0, PivotFilterType.Count);
            PivotFilter filter = pivot.PivotFilters[filterIndex];
            filter.AutoFilter.FilterTop10(0, false, false, 2);

            // Set additional properties for the pivot filter
            filter.Value1 = "SampleValue1";
            filter.Value2 = "SampleValue2";
            filter.MeasureFldIndex = 1;
            filter.MemberPropertyFieldIndex = 2;
            filter.Name = "SampleFilter";
            filter.EvaluationOrder = 1;

            // Refresh and calculate the pivot table data
            pivot.RefreshData();
            pivot.CalculateData();

            // Save the workbook
            book.Save("PivotFilterExample.xlsx");
        }
    }
}

See Also