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
Hi,
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!
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.
Hi,
pivotSheet.PivotTables[0].RefreshData();
pivotSheet.PivotTables[0].CalculateData();
pivotSheet.PivotTables[0].CalculateRange();
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
Hi,