Pivot table - how to apply column filter

Aspose.Cells Product Family
1

Dear Support,

Please find my attached excel sheet. I have a “test” sheet from which i am creating a pivot table. I am able to create a pivot table (refer Sheet2). Now further to this pivot table I want to apply a column filter. Like for example I have a employee list A-Z in the pivot table. I would like to deselect “Select All” option and select only “C” and “D” employee. So the pivot table would show two rows i.e C and D employees data.

Please suggest how can i apply the column filter in pivots.

Thank you

p.s i am using the latest aspose.cells dll with c# language as coding

2

Hi,


Well, you may try to hide your undesired pivot items accordingly, see the sample code below for your reference:

Sample code:

Workbook workbook = new Workbook(“e:\test2\test.xlsx”);

PivotTable pivotTable = workbook.Worksheets[1].PivotTables[0];
//Get the first (the only)row field
PivotField pivotField = pivotTable.RowFields[0];
PivotItemCollection pItems = pivotField.PivotItems;
for (int i = 0; i < pItems.Count; i++)
{

if (pItems[i].Name == " C" || pItems[i].Name == " D")
{
pItems[i].IsHidden = false;
}
else
{
pItems[i].IsHidden = true;
}
}

pivotTable.RefreshDataOnOpeningFile = true;

workbook.Save(“e:\test2\outtestatet1.xlsx”);
3

Hello Amjad,

Thank you for the sample code. It works as desired but I was just wondering if there would be any property so that i need not have to loop through all the items to hide/unhide them, but i appreciate your reply. It solved my problem.

Thanks a lot!

4

Hello Amjad,

Please see my below code and the output i am getting. You would see that it shows one more row apart from C and D in the pivot. It is always taking first name of the employee list. I think it was working fine till yesterday but since this morning i am getting this output, not sure where i am wrong. I have debugged it but could not find the error. Here is my code:

Worksheet testSheet = wb.Worksheets[“test”];
Cells cells = testSheet.Cells;

Range cellRange = cells.MaxDisplayRange;
string sourcedata = cellRange.RefersTo.ToString();

//Obtaining the reference of the newly added worksheet
Worksheet pivotSheet = wb.Worksheets.Add(“M”);

PivotTableCollection pivotTables = pivotSheet.PivotTables;
int index = pivotTables.Add(sourcedata, 1, 0, “MPivot”);
//Accessing the instance of the newly added PivotTable
PivotTable mPivotTable = pivotTables[index];

//Draging the “Employee” field to the row area.
mPivotTable.AddFieldToArea(PivotFieldType.Row, 1);

//Draging the “DATE” field to the column area.
mPivotTable.AddFieldToArea(PivotFieldType.Column, 0);

//Draging the “Job3”, field to the data area.
mPivotTable.AddFieldToArea(PivotFieldType.Data, 2);

mPivotTable.IsAutoFormat = true;
mPivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;

PivotFieldCollection dataPivotFields = mPivotTable.DataFields;
dataPivotFields[0].DisplayName = “Job”;
dataPivotFields[0].NumberFormat = “#,##0”;

mPivotTable.RowFields[0].IsAutoSort = true;
mPivotTable.ColumnFields[0].IsAutoSort = true;

mPivotTable.ColumnFields[0].IsAscendSort = false; // show dates in descending order

mPivotTable.IsGridDropZones = true;

PivotField pivotField = mPivotTable.RowFields[0];
PivotItemCollection pItems = pivotField.PivotItems;
for (int i = 0; i < pItems.Count; i++)
{

if (pItems[i].Name == " C" || pItems[i].Name == " D")
{
pItems[i].IsHidden = false;
}
else
{
pItems[i].IsHidden = true;
}
}

mPivotTable.RefreshDataOnOpeningFile = true;

//Calculate data of all the pivot tables before copying.
pivotSheet.PivotTables[0].RefreshData();
pivotSheet.PivotTables[0].CalculateData();
pivotSheet.PivotTables[0].CalculateRange();

pivotSheet.AutoFitColumns();
wb.AcceptAllRevisions();

Output:





Job DAY




Employee 4/18/2013 4/17/2013 4/16/2013 4/15/2013 4/14/2013 Grand Total
A 10,410 103,760 87,078 10,410 41,288 252,945
C 2,288 238 170 2,288
4,984
D 34 34 77 34
179
Grand Total 12,732 104,032 87,325 12,732 41,288 258,109








I have tried this with these 3 dlls (7.4.3, 7.5.0 and 7.5.1.2). Do you have any idea why it is taking the first value. Please advise.

5

Hi,


I would suggest please do not use the following lines of code i.e.

//Calculate data of all the pivot tables before copying.
pivotSheet.PivotTables[0].RefreshData();
pivotSheet.PivotTables[0].CalculateData();
pivotSheet.PivotTables[0].CalculateRange();

Please comment out these lines. Well, we are working over enhancing refreshing pivot tables in the template files, so, you may skip the above lines and use only the line to let MS Excel to refresh it on opening the file into it:

" mPivotTable.RefreshDataOnOpeningFile = true;"

thank you.
6

Thank you Amjad,
This solution works only with this latest dll but it breaks my other code. Like for example i am creating a temp sheet and making a pivot from it. After the pivot table is created i am deleting the temp sheet. Since the temp sheet is deleted so to show the pivot table i had to write these lines
mSheet.PivotTables[0].RefreshData();
mSheet.PivotTables[0].CalculateData();
mSheet.PivotTables[0].CalculateRange();
If i don’t write these lines i get an empty pivot and if i write then it gives “Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index” with this new dll. With previous dll it was working fine, but once i use this new one it start giving me the error.
I think i would have to stick with the older dll and have to find some other solution to do the column filter. Any suggestion/advise would be appreciated.

Best regards,
Radha


7

Hi,


Which Aspose.Cells version/fix you are using? Please download and try this fix: Aspose.Cells for .NET v7.5.1.3 and let us know your feedback.

If you still find the issue, kindly paste your runnable sample code here and attach your template Excel file to reproduce the issue on our end. We may log a ticket for your issue to figure it out soon.

Thank you.