Hide subtotal in Dynaic Piovt

HI Team,

I find some code to hide subtotal in Pivot, But in my Pivot i am gating dynamic data, Its difficult to find Particular cell to hide subtotal,

my code is ::

if(dataValues.get(i) == 10){//SOW
int indexFormat = pivotTable.addFieldToArea(PivotFieldType.DATA, dataValues.get(i));//geting dynamic column
pivotTable.getDataFields().get(indexFormat).setNumberFormat(“0.000%”);
// pivotTable.getDataFields().get(indexFormat).setShowSubtotalAtTop(false);//hide subtotal
// pivotTable.getRowFields().get(indexFormat).setSubtotals(PivotFieldSubtotalType.NONE,true);
}


And By using this code i can able to hide subtotal row completely,

pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
for(int i = 0; i< pivotTable.getRowFields().getCount(); i++)
{
pivotTable.getRowFields().get(i).setSubtotals(PivotFieldSubtotalType.AUTOMATIC, false);
}


my requirement is ::

Need to hide subTotal for rank column or particular columns in Dynamic Pivot table.


Hi,


Thanks for your posting and using Aspose.Cells.

First you will find the cell containing your data. For example, you search the cell containing the value

Banks & Intermediary Total

and it will give you cell A28.

Then you will get the row number of cell Cell.getRow() property and hide it using

Worksheet.getCells().hideRow(cell.getRow());

You can search cells data as described in this article. Please use FindOptions class to search data.

Reference Articles:


Hi ,

Thank u for solution, But in my pivot am not sure which data will come and on which index.

By using below code, I can hide complete subtotal row,


pivotTable.setRowGrand(false);
pivotTable.setColumnGrand(false);
for(int i = 0; i< pivotTable.getRowFields().getCount(); i++)
{
pivotTable.getRowFields().get(i).setSubtotals(PivotFieldSubtotalType.AUTOMATIC, false);
}

I want to hide subtotal bases on column name where ever i get rank column , need to hide subtotal for rank column, And here rank column also not fix it will be come or may not be.

Thank you.


Hi,


Thanks for your posting and using Aspose.Cells.

Please check the following code. It dynamically finds the cell based on the value you specify in the searchCellString variable. Once, it finds the cell, it hides that row.

I have attached two output excel files generated by this code based on the value inside the the searchCellString variable.


Please read the comments inside the code. It should help you resolve your issue.

Java
//Load your source excel file
Workbook wb = new Workbook(dirPath + “RankColumn.xlsx”);

//Access the worksheet with pivot table
Worksheet ws = wb.getWorksheets().get(“Advanced CrossTab”);

//Access the pivot table
PivotTable pt = ws.getPivotTables().get(0);

//Different search cell string will hide different rows
String searchCellString = “”;
searchCellString = “Banks & Intermediary Total”;
searchCellString = “Asset & Wealth Managers Total”;

//Create find options, we want to search cell that contains our string
//We only want to search cell values not formulas
FindOptions opts = new FindOptions();
opts.setLookAtType(LookAtType.CONTAINS);
opts.setLookInType(LookInType.VALUES);

//Find your cell based on your search string
Cell cell = ws.getCells().find(searchCellString, null, opts);

//Hide the searched cell row
ws.getCells().hideRow(cell.getRow());

//Save the output excel file
wb.save(dirPath + “output-Hide-” + searchCellString + “.xlsx”);

Hi,


I think you not get my requirement,
I can able to hide subtotal columns completely and of course i can’t hide particular subtotal row by search string, because its dynamic data that same string name may be or may not .

And i need to hide subtotal for particular column only. as per column name from header name, Please find attached screen shot for reference.


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