Interface IPivotFilters
Represents a collection of IPivotFilter in the pivot table.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.dll
Syntax
public interface IPivotFilters
Properties
Item[Int32]
Gets IPivotFilter from the collection with the specified index. Read-only.
Declaration
IPivotFilter this[int index] { get; }
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | index | Index of the IPivotFiter. |
Property Value
Type |
---|
IPivotFilter |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code snippet illustrates how to add filter using add method in the pivot field.
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];
field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");
field.PivotFilters.Add();
//Sets the value for filter
field.PivotFilters[0].Value1 = "1";
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}
Parent
Gets the Parent IPivotField of the filter. Read-only.
Declaration
IPivotField Parent { get; }
Property Value
Type |
---|
IPivotField |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code snippet illustrates how to use Parent 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];
//Gets the Parent field of the filter
IPivotField parent = field.PivotFilters.Parent;
if(parent != null)
{
//Your Code Here
}
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}
Methods
Add()
Adds a IPivotFilter to the collection.
Declaration
IPivotFilter Add()
Returns
Type | Description |
---|---|
IPivotFilter | Returns an IPivotFilter object. |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code snippet illustrates how to add filter using add method in the pivot field.
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];
field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");
//Adds filter to the pivot field
field.PivotFilters.Add();
field.PivotFilters[0].Value1 = "1";
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}
Add(PivotFilterType, IPivotField, String, String)
Adds filter with Value/Label based on the specified filter values.
Declaration
IPivotValueLableFilter Add(PivotFilterType filterType, IPivotField dataField, string Value1, string Value2)
Parameters
Type | Name | Description |
---|---|---|
PivotFilterType | filterType | Type of the filter. |
IPivotField | dataField | Data field to which filter is applied (Data Field must not be null for Value filter). |
System.String | Value1 | Filter value 1. |
System.String | Value2 | Filter value 2. |
Returns
Type | Description |
---|---|
IPivotValueLableFilter | Returns an IPivotValueLableFilter object. |
Remarks
To know more about Pivot Tables refer this link.
Examples
Following code snippet illustrates how to add filter using add method in the pivot field.
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];
//Adds filter with Value/Label based on the specified filter values
field.PivotFilters.Add(PivotFilterType.ValueBetween, field, "50", "80");
workbook.SaveAs("PivotTables.xlsx");
workbook.Close();
}
Remove()
Remove the filters from a pivot field.
Declaration
void Remove()
Examples
The following code illustrates how to remove pivot filters from a pivot field.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];
IPivotTable pivotTable = worksheet.PivotTables[0];
//Apply page field filter
IPivotField pageField = pivotTable.Fields[0];
//Remove Pivot filters
pageField.PivotFilters.Remove();
//Save and dispose
workbook.SaveAs("Output.xlsx");
workbook.Close();
}