I am trying to preset the filter values of a pivot using the following code, but the pageFields method keeps showing blank/no items although I can see valid items/fields in the pivot’s filter (see the attachments). Is anything wrong with this code?
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "Locale");
//Preset values
PivotField pageField = null;
int pageItemCount = 0;
pageField = pivotTable.PageFields[2];
pageItemCount = pageField.PivotItems.Count;
for (int i = 0; i < pageItemCount; i++)
{
try
{
if ("South".Equals((pageField.PivotItems[i].Value).ToString()))
{
pageField.CurrentPageItem = (short)i;
}
}
catch (Exception ex) { reportLogger.Error("Null object in pivot filter settings" + ex.ToString()); }
}
Please provide us your source excel file as you have shown in your images as well as runnable sample application project or code which we could run at our end and replicate this issue. We will look into it and help you asap. Thanks for your cooperation in this regard and have a good day.
This code is part of a big project hence providing such a code might be tricky. What other options do you have for pre-setting a filter for a pivot table? A screenshot of the data used by the filter is attached.
It should help you resolve your issue. If you still face any problem, then please provide us some simple excel file and expected excel file, you can create both of them manually. We will then look into it further and provide you a related code that could resolve your issue.
C#
//Load source excel file
Workbook wb = new Workbook(“sample.xlsx”);
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Access pivot table
PivotTable pt = ws.PivotTables[0];
//Access page field and select the 2nd item
PivotField pf = pt.PageFields[0];
pf.CurrentPageItem = 1;
//Refresh and calculate data… it is important
pt.RefreshData();
pt.CalculateData();
//Save the workbook
wb.Save(“output.xlsx”);
The code you provided worked for pre-selecting values in one filter but when there are multiple filters for the pivot it doesn’t work. The following is part of a code in which I am trying to set the values in four filters. What is wrong in this code? Secondly what is the role of CalculateData()? Should CalculateData be used for each PageField being set:
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 45);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 30);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 30);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 38);
//pwkSheet.PivotTables[pvtIndex].RefreshDataFlag = true;
pwkSheet.PivotTables[pvtIndex].ColumnFields.Add(pwkSheet.PivotTables[pvtIndex].DataField);
// pwkSheet.PivotTables[pvtIndex].CalculateData();
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "A");
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "B");
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "C");
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "D");
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "E");
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "F");
//Access second pivot/pagefield
PivotField pf = pivotTable.PageFields[0];
//Set index of item to select
pf.CurrentPageItem = 1;
//Access second pivot/pagefield
PivotField pf1 = pivotTable.PageFields[1];
//Set index of item to select
pf1.CurrentPageItem = 1;
////Refresh and calculate data... it is important
pivotTable.RefreshData();
pivotTable.CalculateData();
After looking into this issue further, I found, calculate data is necessary and it should be called after refresh data. Because, if you will not do so, page field item will be selected but its effect will not be applied on the pivot table.
I have tested your issue with the new sample excel file which has multiple page fields and it is working good with all of them. I have also attached the output excel file as well as screenshot for your reference.
C#
//Load source excel file
Workbook wb = new Workbook(“sample2.xlsx”);
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
//Access pivot table
PivotTable pt = ws.PivotTables[0];
//Access 1st page field and select the 1st item
PivotField pf = pt.PageFields[0];
pf.CurrentPageItem = 0;
//Access 2nd page field and select the 3rd item
pf = pt.PageFields[1];
pf.CurrentPageItem = 2;
//Access 3rd page field and select the 2nd item
pf = pt.PageFields[2];
pf.CurrentPageItem = 1;
//Refresh and calculate data… it is important
pt.RefreshData();
pt.CalculateData();
//Save the workbook
wb.Save(“output2.xlsx”);