Pivot Table Page Field Item Selection

Aspose.Cells Product Family
1

I would like to be able to set the item selected in my Pivot Table’s page field drop down as the first item after “(All)”. The following code is not accomplishing this:

PivotTable pivotTable = pivotTableSheet.PivotTables[0];

PivotFieldCollection pivotFieldCollection = pivotTable.PageFields;

for (int i = 0; i < pivotFieldCollection.Count; i++)

{

pivotFieldCollection[i].IsAutoSort = true;

pivotFieldCollection[i].IsAscendSort = true;

pivotFieldCollection[i].AutoSortField = -1;

pivotFieldCollection[i].BaseField = 1;

pivotFieldCollection[i].BaseItemPostion = PivotItemPosition.Next;

}
How would I accomplish this?

Thank you.
2

Hi,

I think you may try PivotField.HideItem() to hide your unwanted items e.g
e.g
for (int i = 1; i < pivotfield.ItemCount; i++)
{
//Hide all items but for the first one
pivotfield.HideItem(i, true);

}

Thank you.

3

Thanks Amjad but I do not want to hide anything.
I just want to select the first item in the list after “All”.
Can this be done?

4

Hi,


The source code provided by Amjad actually Deselects the indexed Item. You can deselect all the items except the one you require. Please check the snapshot attached and below source code for your reference. Give it a try or share your sample Excel file so we may provide you the solution for your need.

C#

var book = new Workbook(“c:\temp\pivotout.xls”);

var sheet = book.Worksheets[0];

var pivot = sheet.PivotTables[0];

PivotFieldCollection pivotFieldCollection = pivot.PageFields;


//De-select the first Item of first PivotField

pivotFieldCollection[0].HideItem(0, true);


book.Save(“C:\temp\out.xls”);

5

Hi Babar.
In your example you have “Select Multiple Items” enabled. I need that to be off.
So without Select Multiple Lines enabled, I would need the first item (Baker) to be the default item selected for that field when I open my EXCEL document.

6

Hi,


Thank you for your sample file.

Please use the following source code for your need. Also attached is my output file for your reference. Thank you

C# Code

var book = new Workbook(“c:\temp\MeaningfulDiff_XLHealth_2012_6_22_2011.xlsx”);

var sheet = book.Worksheets[1];

var pivot = sheet.PivotTables[0];

PivotFieldCollection pivotFieldCollection = pivot.PageFields;

//Disable Multiple Selection

pivotFieldCollection[0].IsMultipleItemSelectionAllowed = false;

//Select 1st Item

pivotFieldCollection[0].CurrentPageItem = 0;

book.Save(“C:\temp\out.xlsx”);

7

I want to select multiple items. How do I do that??

PTP1.IsMultipleItemSelectionAllowed = true;

Then how do i set CurrentPage Item

PTP1.CurrentPageItem = 1,2,3;

I should be able it filter 1,2 and 3 itens from the check boxes.

8

Hi,


I think you may deselect all the items except the ones you require, the items that are not set as hidden are selected: e.g
var pivot = sheet.PivotTables[0];
PivotFieldCollection pivotFieldCollection = pivot.PageFields;
//De-select the first Item of first PivotField
pivotFieldCollection[0].HideItem(0, true);