Interface IPivotValueLableFilter

Represents pivot value filter or pivot label filter object.

Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public interface IPivotValueLableFilter

Properties

DataField

Gets data field using IPivotField to which the filter is applied. Read-only.

Declaration
IPivotField DataField { get; }
Property Value
Type Description
IPivotField
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use DataField property in the pivot table filters.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Create(2);
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];
        sheet.Range["A1"].Text = "Student ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark(Out of 100)";
        sheet.Range["D1"].Text = "Student City";
        sheet.Range["A1:D1"].AutofitColumns();
        sheet.Range["A2"].Number = 1;
        sheet.Range["A3"].Number = 2;
        sheet.Range["A4"].Number = 3;
        sheet.Range["A5"].Number = 4;
        sheet.Range["A6"].Number = 5;
        sheet.Range["B2"].Text = "Andrew";
        sheet.Range["B3"].Text = "Marson";
        sheet.Range["B4"].Text = "Jack";
        sheet.Range["B5"].Text = "Sam";
        sheet.Range["B6"].Text = "Breto";
        sheet.Range["C2"].Number = 77;
        sheet.Range["C3"].Number = 45;
        sheet.Range["C4"].Number = 92;
        sheet.Range["C5"].Number = 39;
        sheet.Range["C6"].Number = 55;
        sheet.Range["D2"].Text = "Chennai";
        sheet.Range["D3"].Text = "Bangalore";
        sheet.Range["D4"].Text = "Chennai";
        sheet.Range["D5"].Text = "Mysore";
        sheet.Range["D6"].Text = "Bangalore";
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);  
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);    
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        IPivotField field = pivotTable.Fields[1];
        IPivotValueLableFilter pivotLabelValue = field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");

        //Gets data field to which the filter is applied
        IPivotField filterField = pivotLabelValue.DataField;
        if(pivotLabelValue != null)
        {
            //Your Code Here
        }

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Type

Gets the PivotFilterType of the pivot filter applied to the data field. Read-only.

Declaration
PivotFilterType Type { get; }
Property Value
Type Description
PivotFilterType
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use filter Type property in the pivot table.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Create(2);
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];
        sheet.Range["A1"].Text = "Student ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark(Out of 100)";
        sheet.Range["D1"].Text = "Student City";
        sheet.Range["A1:D1"].AutofitColumns();
        sheet.Range["A2"].Number = 1;
        sheet.Range["A3"].Number = 2;
        sheet.Range["A4"].Number = 3;
        sheet.Range["A5"].Number = 4;
        sheet.Range["A6"].Number = 5;
        sheet.Range["B2"].Text = "Andrew";
        sheet.Range["B3"].Text = "Marson";
        sheet.Range["B4"].Text = "Jack";
        sheet.Range["B5"].Text = "Sam";
        sheet.Range["B6"].Text = "Breto";
        sheet.Range["C2"].Number = 77;
        sheet.Range["C3"].Number = 45;
        sheet.Range["C4"].Number = 92;
        sheet.Range["C5"].Number = 39;
        sheet.Range["C6"].Number = 55;
        sheet.Range["D2"].Text = "Chennai";
        sheet.Range["D3"].Text = "Bangalore";
        sheet.Range["D4"].Text = "Chennai";
        sheet.Range["D5"].Text = "Mysore";
        sheet.Range["D6"].Text = "Bangalore";
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);  
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);    
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        IPivotField field = pivotTable.Fields[1];
        IPivotValueLableFilter pivotLabelValue = field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");

        //Gets type of the pivot filter applied to the data field
        PivotFilterType filterType = pivotLabelValue.Type;
        if(filterType == PivotFilterType.ValueBetween)
        {
            //Your Code Here
        }

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Value1

Gets the first filter value of the pivot table.

Declaration
string Value1 { get; }
Property Value
Type Description
System.String
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use first value property in the pivot table filters.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Create(2);
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];
        sheet.Range["A1"].Text = "Student ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark(Out of 100)";
        sheet.Range["D1"].Text = "Student City";
        sheet.Range["A1:D1"].AutofitColumns();
        sheet.Range["A2"].Number = 1;
        sheet.Range["A3"].Number = 2;
        sheet.Range["A4"].Number = 3;
        sheet.Range["A5"].Number = 4;
        sheet.Range["A6"].Number = 5;
        sheet.Range["B2"].Text = "Andrew";
        sheet.Range["B3"].Text = "Marson";
        sheet.Range["B4"].Text = "Jack";
        sheet.Range["B5"].Text = "Sam";
        sheet.Range["B6"].Text = "Breto";
        sheet.Range["C2"].Number = 77;
        sheet.Range["C3"].Number = 45;
        sheet.Range["C4"].Number = 92;
        sheet.Range["C5"].Number = 39;
        sheet.Range["C6"].Number = 55;
        sheet.Range["D2"].Text = "Chennai";
        sheet.Range["D3"].Text = "Bangalore";
        sheet.Range["D4"].Text = "Chennai";
        sheet.Range["D5"].Text = "Mysore";
        sheet.Range["D6"].Text = "Bangalore";
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);  
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);    
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        IPivotField field = pivotTable.Fields[1];
        IPivotValueLableFilter pivotLabelValue = field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");

        //Gets the first filter value of the pivot table
        string value1 = pivotLabelValue.Value1;
        if(value1 != string.Empty)
        {
            //Your Code Here
        }

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Value2

Gets the second filter value of the pivot table.

Declaration
string Value2 { get; }
Property Value
Type Description
System.String
Remarks

To know more about Pivot Tables refer this link.

Examples

Following code snippet illustrates how to use second value property in the pivot table filters.

      using (ExcelEngine excelEngine = new ExcelEngine())
      {
        //Create a worksheet.        
        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2013;
        IWorkbook workbook = application.Workbooks.Create(2);
        IWorksheet sheet = workbook.Worksheets[0];
        IWorksheet pivotSheet = workbook.Worksheets[1];
        sheet.Range["A1"].Text = "Student ID";
        sheet.Range["B1"].Text = "Student Name";
        sheet.Range["C1"].Text = "Mark(Out of 100)";
        sheet.Range["D1"].Text = "Student City";
        sheet.Range["A1:D1"].AutofitColumns();
        sheet.Range["A2"].Number = 1;
        sheet.Range["A3"].Number = 2;
        sheet.Range["A4"].Number = 3;
        sheet.Range["A5"].Number = 4;
        sheet.Range["A6"].Number = 5;
        sheet.Range["B2"].Text = "Andrew";
        sheet.Range["B3"].Text = "Marson";
        sheet.Range["B4"].Text = "Jack";
        sheet.Range["B5"].Text = "Sam";
        sheet.Range["B6"].Text = "Breto";
        sheet.Range["C2"].Number = 77;
        sheet.Range["C3"].Number = 45;
        sheet.Range["C4"].Number = 92;
        sheet.Range["C5"].Number = 39;
        sheet.Range["C6"].Number = 55;
        sheet.Range["D2"].Text = "Chennai";
        sheet.Range["D3"].Text = "Bangalore";
        sheet.Range["D4"].Text = "Chennai";
        sheet.Range["D5"].Text = "Mysore";
        sheet.Range["D6"].Text = "Bangalore";
        IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:D6"]);
        IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);  
        pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
        pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
        IPivotField datafield = pivotTable.Fields[2];
        pivotTable.DataFields.Add(datafield, "Sum of Marks", PivotSubtotalTypes.Sum);    
        pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium20;
        IPivotField field = pivotTable.Fields[1];
        IPivotValueLableFilter pivotLabelValue = field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");

        //Gets the second filter value of the pivot table
        string value2 = pivotLabelValue.Value2;
        if(value2 != string.Empty)
        {
            //Your Code Here
        }

        workbook.SaveAs("PivotTables.xlsx");
        workbook.Close();
      }

Extension Methods