Unexpected Behavior Renaming Pivot Cell Fields

Aspose.Cells Product Family
1

I started out trying to rename the heading with a common name on all pivot tables across several worksheets and was having problems where most of the fields were not being renamed or not found I wasn’t sure… so I tried the following test:


foreach (Worksheet worksheet in sheets)
{

foreach (PivotTable pt in worksheet.PivotTables)
{

PivotFieldCollection fields = pt.BaseFields;

//rename Fields
for (int i = 0; i < fields.Count; i++)
{
fields[i].DisplayName = "HMMM" + i;
}

pt.RefreshDataOnOpeningFile = true;
pt.RefreshData();
pt.CalculateData();
}

}

The result is attached, all of the fields seem to have been renamed correctly as you can see in the field list, but the display text of the fields in both the table itself and the column listings near the bottom of the screenshot is only changed on the items in the row labels, not the rest of the column headings that are in values... if I manually remove one and add it back it adds it in as the new name with sum in front of it (Sum of HMMM27 in the screenshot).


Maybe I'm not doing this correctly (I was also unable to access those other columns by name); but I need to be able to change the headings on those other items as well.

Any help would be greatly appreciated.

2

Hi,


Could you attach your template file and also output file when using your code with Aspose.Cells, we will check your issue soon.

Moreover, please perform your steps as per your sample code in MS Excel manually for the pivot table, if you get the similar results or different results, also, please do attach your expected file (that you may manually create in MS Excel regarding pivot table), it will help us to evaluate your issue more precisely.

Thank you.
3

Hi Amjad,

Thanks for looking into this.

I will work on making a sample template, can’t send the one I am using. As for doing it manually, I don’t know of a way to do what this is doing manually, because it seems to be renaming the source fields name and not the display name, which is all I want to change. It also updates the display value on the row label columns, but does not update the column headings on the values at all.

To rename the headings manually you just double click the heading and enter another name into Custom name, or just type the new name into the value field when you have the heading on the table selected, which works fine.


I’ll make a sample template, but as far as I know, it will do this on any pivot table.

4

Example template attached.

5

Hi,


Thanks for the sample Excel file.

We found the DataField’s name is not changed in the output file using Aspose. Cells. Actually, we change the DataField’s name at the top of the field list, and MS Excel changes it in the bottom of the field list.

I have logged a ticket with an id “CELLSNET-41591” for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.

Thank you.
6

Thanks again for looking into this for me, I’m not sure if this helps, but the Custom Name field in the interface that I am attaching a screenshot of is really what I need to change.

7

Hi,

Thanks for providing the additional information.

We have logged your comments against this issue. We will look into it and resolve it. Once, we will have any update relating to it for you, we will share it with you asap.

8

Hi;


Please use this sample code to set datafield's display name.

pivottable.DataFields[0].DisplayName = "Current FY Funding";

9

Shakeel, sorry for the slow response, I must have missed my notification from he forum. That works perfectly, thank you very much for your help.

10

Hi,

Thanks for your feedback.

We are pleased to know your issue is resolved now. If you face any other issue, please feel free to post on our forums, we will be glad to help you further.