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.
p.s i am using the latest aspose.cells dll with c# language as coding
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!
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.
//Draging the “DATE” field to the column area.
//Draging the “Job3”, field to the data area.
mPivotTable.IsAutoFormat = true;
mPivotTable.PivotTableStyleType = PivotTableStyleType.PivotTableStyleLight16;
PivotFieldCollection dataPivotFields = mPivotTable.DataFields;
dataPivotFields.DisplayName = “Job”;
dataPivotFields.NumberFormat = “#,##0”;
mPivotTable.RowFields.IsAutoSort = true;
mPivotTable.ColumnFields.IsAutoSort = true;
mPivotTable.ColumnFields.IsAscendSort = false; // show dates in descending order
mPivotTable.IsGridDropZones = true;
PivotField pivotField = mPivotTable.RowFields;
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;
pItems[i].IsHidden = true;
mPivotTable.RefreshDataOnOpeningFile = true;
//Calculate data of all the pivot tables before copying.
I have tried this with these 3 dlls (7.4.3, 7.5.0 and 220.127.116.11). Do you have any idea why it is taking the first value. Please advise.
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
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.