PageFields function showing blank/giving no result

[cu202]

Hi

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()); }
}

[shakeel.faiz]

Hi,

Thanks for your posting and using Aspose.Cells.

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.

[cu202]

Hi

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.

Thanks

[cu202]

Any more ideas/solutions regarding this issue?

[shakeel.faiz]

Hi,

Thanks for your posting and using Aspose.Cells.

Please see the following sample code, its comments, its sample excel file, output excel file as well as the screenshot.

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”);

[cu202]

Hi

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();

[shakeel.faiz]

Hi,

Thanks for your posting and using Aspose.Cells.

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”);