Hi,
Thanks for your posting and using Aspose.Cells.
We have looked into your requirement and found, it is not supported by Microsoft Excel. If you think, it is supported then provide us your expected excel file and let us know the steps you took to achieve your requirement.
You cannot hide some of the data item from the PivotTable.DataFields using Microsoft Excel.
So the solution for your problem is to delete DataField named “Max of Rank” from the PivotTable.DataFields. Let us know if you encounter any issue and have a good day.
Please check the following code and its
output excel file. If you open the output excel file, you will see Max of Rank is no more present.
Java
Workbook wb = new Workbook(dirPath + “RankColumn.xlsx”);
PivotTable pt = wb.getWorksheets().get(“Advanced CrossTab”).getPivotTables().get(0);
PivotFieldCollection dataFields = pt.getDataFields();
int count = dataFields.getCount();
ArrayList baseIndexArr = new ArrayList();
for (int i = 0; i < count; i++)
{
PivotField f = dataFields.get(i);
if (“Max of Rank”.equals(f.getDisplayName()))
{
pt.removeField(PivotFieldType.DATA, f);
}
else
{
baseIndexArr.add(f.getBaseIndex());
}
}
for (int j = 0; j < baseIndexArr.size(); j++)
{
pt.addFieldToArea(PivotFieldType.DATA, (int)baseIndexArr.get(j));
}
pt.addFieldToArea(PivotFieldType.COLUMN, pt.getDataField());
pt.setRefreshDataFlag(true);
pt.refreshData();
pt.calculateData();
pt.setRefreshDataFlag(false);
wb.save(dirPath + “out.xlsx”);
C#
Workbook wb = new Workbook(dirPath + “RankColumn.xlsx”);
PivotTable pt = wb.Worksheets[“Advanced CrossTab”].PivotTables[0];
PivotFieldCollection dataFields = pt.DataFields;
int count = dataFields.Count;
ArrayList baseIndexArr = new ArrayList();
for (int i = 0; i < count; i++)
{
PivotField f = dataFields[i];
if (“Max of Rank”.Equals(f.DisplayName))
{
pt.RemoveField(PivotFieldType.Data, f);
}
else
{
baseIndexArr.Add(f.BaseIndex);
}
}
for (int j = 0; j < baseIndexArr.Count; j++)
{
pt.AddFieldToArea(PivotFieldType.Data, (int)baseIndexArr[j]);
}
pt.AddFieldToArea(PivotFieldType.Column, pt.DataField);
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
wb.Save(dirPath + “out.xlsx”);