I am using Aspose.Cells for .Net version 7.2.1.0 and am trying to set the number format for the data values section of a pivot table. However, when I open the Excel file everything is in the right place, but the data in the column on the pivot table has the General number format and does not have the custom number format.
Here is the relevant C# code that is setting the NumberFormat for the column named "Total_For_Year"
PivotField pf = ptable.BaseFields[“Total_For_Year”];
pf.Function = ConsolidationFunction.Sum;
// Note: this does not appear to be working
pf.NumberFormat = “#,##0.00”;
ptable.AddFieldToArea(PivotFieldType.Data, pf);
Thank you.
Hi Robert,
Thank you for using Aspose products.
Please give a try to our latest fix version of Aspose.Cells for .NET 7.6.0.7 as it should fix the problem. In case the issue persists, please share your complete source code, most preferably a runnable sample application along with your template file (if any). We need these details to properly analyze the issue on our end.
Thank you. I tried the new version that you recommended, but it didn’t fix the problem.
It seems that even though I set the NumberFormat in the PivotField object before it is added to the Data area, once it is in the Data area the NumberFormat is no longer set.
However, if I reference the PivotField object from the DataFields collection after it has been added, and then set the NumberFormat property it works.
This is what the working C# code looks like:
PivotField pf = ptable.BaseFields[“Total_For_Year”];
pf.Function = ConsolidationFunction.Sum;
ptable.AddFieldToArea(PivotFieldType.Data, pf);
// Note: this has to be done after field is added to the data area
ptable.DataFields[“Total_For_Year”].NumberFormat = “#,##0.00”;
Comment 1: Normally, I would expect that if I set a property on an object and then add that object to a collection, the property would stay set. However, it appears that not all the properties are copied from the original object to the object in the collection. So in this case the NumberFormat property needs to be set after it is put into the DataFields collection.
Comment 2: It looks as if our license for Aspose.Cells does not include new versions, because when I used the new version, I got the “Evaluation Warning” sheets.
Hi Robert,
First of all, please accept my apology for a bit delayed response.
I have evaluated your present scenario on my end while using the latest version of Aspose.Cells for .NET 7.6.1.0. I can confirm that if the NumberFormat is set for the PivotField and then same PivotField is added to the DataField collection, the NumberFormat settings are lost. Whereas, if the NumberFormat settings are applied to the PivotField previously added to the DataField collection then these settings are preserved, and therefore rendered correctly in the resultant file. A ticket has been logged in this reference so we could properly analyze the problem cause on our end before we could provide a fix for it. The ticket Id for your future reference is CELLSNET-42155.
- You are correct, if some property is set for an object and then the object is added to a collection, the settings should remain intact even in the collection. I have logged your comments to the ticket referenced above.
- If you are getting the Evaluation watermark with the latest assembly, this means your subscription has expired therefore you will not be able to use the latest releases (including the one providing the fix for said issue) in licensed mode without renewing your subscription.
Please feel free to write back in case you have more questions.
Hi,
Sorry for the delay.
We did evaluate your issue “CELLSNET-42155” further. We think it is not an issue of the product. You may confirm it in MS-Excel. First of all, please drag PivotField to column area and set NumberFormat of this PivotField, then drag this PivotField to data area, you will discover that the NumberFormat of this PivotField is cleaned up too. Aspose.Cells follows MS Excel standards, so it is not an issue with the component.
Thank you.