Add filter to specific columns in Pivot table in Excel worksheet using C#.NET

Aspose.Cells Product Family
1

I am trying to add filters to specific columns of a pivot table. At present all I can see is the AutoFilter function which is designed to add data to a range of column. Is this option available?

2

Hi,


Thank you for contacting support. Please note, the pivot filter would be applied to all columns of a pivot field. It is the default behavior of Microsoft Office Excel. We need to know the complete details of the use case, which you are seeking to implement. Please prepare a source Excel template with a pivot table, and then sort data using Microsoft Office Excel application. We need both source and resultant Excel files. It will help us to investigate and help you appropriately. You can apply a filter based on the specific value as shown in the code example below. This may also help you. Your response is awaited.

[.NET, C#]

// load an Excel workbook
string filePath = @“C:\Cells\filter.xlsx”; ;
Workbook workbook = new Workbook(filePath);
// retrieve a pivot table by index
PivotTable table = workbook.Worksheets[1].PivotTables[0];
// apply filter to pivot table by field index and type
int index = table.PivotFilters.Add(1, PivotFilterType.Count);
PivotFilter filter = table.PivotFilters[index];
// get top ten items
filter.AutoFilter.FilterTop10(0, true, false, 1);
// set measure field index
filter.MeasureFldIndex = 3;
// apply filter based on the value
index = table.PivotFilters.Add(1, PivotFilterType.CaptionGreaterThan);
filter = table.PivotFilters[index];
filter.Value1 = “1”;
filter.AutoFilter.Custom(0, FilterOperatorType.GreaterThan, 1);
// apply filter
index = table.PivotFilters.Add(0, PivotFilterType.Count);
filter = table.PivotFilters[index];
filter.AutoFilter.FilterTop10(0, true, false, 10);
filter.MeasureFldIndex = 0;
// save the output Excel
workbook.Save(filePath + “.out.xlsx”);
3

Hello Imram


What are the effect of the following lines?
filter.MeasureFldIndex = 3;
filter.Value1 = “1”;
filter.AutoFilter.Custom(0, FilterOperatorType.GreaterThan, 1);


Where are the tutorial/information pages for the use of the above filter methods?
4

Hi,


Thank you for the inquiry.
cu202:
filter.MeasureFldIndex = 3;
MeasureFldIndex property is the measure field index of the pivot filter. It is specifying the position index of DataField in PivotTable.DataFields.
cu202:
filter.Value1 = “1”;
filter.AutoFilter.Custom(0, FilterOperatorType.GreaterThan, 1);
The Custom method of the AutoFilter class takes parameters pivot field index (as 0), filter type, and then the criteria value. For example, a pivot field has values 1,2,3,4, then it will filter out 2,3,4 values. In that case, you do not need to set Value1 property. However, if you are not passing third parameter, then you will set Value1 property as below:

[.NET, C#]
…
index = table.PivotFilters.Add(1, PivotFilterType.CaptionGreaterThan);
filter = table.PivotFilters[index];
filter.Value1 = “1”;

Please let us know in case of any confusion or questions.

5

Hello Imran


After scouring the internet, I found the following as the easiest way/solution for adding fields to the filter area of a pivot. For anyone looking for the same solution try the following(where Dept is the field you are adding). Hope it saves someone time and headache:

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, “Dept”);
6

Hi,


Thank you for the details. Yes, it helps in dragging the pivot field to the specified area. It is nice to hear from you that the issue has been resolved. Please let us know in case of any further assistance or questions.