Free Support Forum -

How to hide the DataField "Data" in pivot table

I want to make a pivot table contains one row field and two data fields, so I neet to add the “DataField” to column field, likes pivotTable[idx].AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, pivotTable[idx].DataField);
wrong.png (79.8 KB)

However, I not really want the datafield be displayed. As manually create pivot table won’t have this issue:
what i want.png (1.9 KB)

It is possible to hide “Data” or is there any other approach?

You may share the Excel file which contains expected pivot table created by MS Excel and your runnable test console application which is creating wrong output. We will observe the scenario here and share our feedback accordingly.

Looking forward to your feedback. Thanks.

Thanks for the data. We will share our feedback soon.

Thank you for the sample project. We have analyzed the requirement and logged it in our database for further investigation. We will write back here once any update is available for sharing.

This requirement is logged as:
CELLSNET-47748 - How to hide the DataField "Data" in pivot table

Please use the following code to test it:

// Instantiating a Workbook object
Workbook workbook = new Workbook(filePath + @“Source.xlsx”);

// Obtaining the reference of the newly added worksheet
Worksheet sheet = workbook.Worksheets[0];

Cells cells = sheet.Cells;

Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet.PivotTables;

// Adding a PivotTable to the worksheet
int idx = pivotTables.Add("=A1:K5", “M12”, “PivotTable3”);

// Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[idx];

// Unshowing grand totals for rows.
pivotTable.RowGrand = true;

// Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2);

// Draging the second field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 6);

// Draging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5);

var valueID = pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, pivotTable.DataField);
pivotTable.PivotTableStyleType = Aspose.Cells.Pivot.PivotTableStyleType.PivotTableStyleLight16;
pivotTable.PivotTableStyleName = “PivotStyleLight16”;

//add this line to implement the feature.
pivotTable.ShowValuesRow = false;

// Saving the Excel file
workbook.Save(filePath + “out.xlsx”);

Let us know your feedback.

Hi John,

I tried your code (aspose cell version 18.9) but that is not I want. The “Data” DataField still exist and others column header disappered. I only want the “Sum of Based HKD” and “Sum of Other Amount” in the column header.

Please try the latest Aspose.Cells 21.1 We can get the correct result.
Can you try it?
Let us know your feedback.