IPivotDataField NumberFormat missing.

[ReerayXia]

Once we need to using a datafiled twice in a IPivotTable.

And show different style, control doesn't support this.

For example:

We want to show the sum of a data filed in two line.

1st show the total.

2nd show the percent.

Code as following:

                    IPivotCache cache = workbook.PivotCaches.Add(worksheet[iDatStart, 1, iLine - 1, iMax]);

                    IPivotTable pivotTable = pivotWs.PivotTables.Add("SUM", pivotWs[1, 1], cache);

                    pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;

                    //pivotTable.Fields[1].Axis = PivotAxisTypes.Page;
                    pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
                    pivotTable.Fields[9].Axis = PivotAxisTypes.Row;
                    pivotTable.Fields[15].Axis = PivotAxisTypes.Column;

                    IPivotField field = pivotTable.Fields[19];
                    pivotTable.DataFields.Add(field, "SUM", PivotSubtotalTypes.Sum);
                    field.NumberFormat = "#,##0.00 ";

                    worksheet.Calculate();

                    IPivotField field2 = pivotTable.Fields[19];
                    IPivotDataField dfSum2 = pivotTable.DataFields.Add(field2, "SUM%", PivotSubtotalTypes.Sum);

                    dfSum2.ShowDataAs = PivotFieldDataFormat.PercentageOfColumn;

                    field2.NumberFormat = "#,##0.00% ";
                    worksheet.Calculate();

But all Datafileds shows as percentage.

[Abirami Varadharajan]

Hi ReerayXia, 

 

Greetings from Syncfusion. 

 

We are able to reproduce issue with “Number format is incorrectly applied for pivot table data field” and validating it currently. We will update further details by 30^th July 2019. 

 

Regards, 

Abirami 

[Abirami Varadharajan]

Hi ReerayXia, 

Thanks for your patience!

If a list object (ie. pivotable.Fields[index]) is assigned to some other different variables (ie. field, field2), it only points to that object and no separate memory will be created. Since field and field2 points to the same object, the latest value assigned for NumberFormat will be updated in both references.  

So, please modify the below highlighted lines in your code to get the desired output. 

Original code

Modified code

IPivotCache cache = workbook.PivotCaches.Add(worksheet[iDatStart, 1, iLine - 1, iMax]);  IPivotTable pivotTable = pivotWs.PivotTables.Add("SUM", pivotWs[1, 1], cache);  pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;  //pivotTable.Fields[1].Axis = PivotAxisTypes.Page; pivotTable.Fields[1].Axis = PivotAxisTypes.Row; pivotTable.Fields[9].Axis = PivotAxisTypes.Row; pivotTable.Fields[15].Axis = PivotAxisTypes.Column;  IPivotField field = pivotTable.Fields[19]; pivotTable.DataFields.Add(field, "SUM", PivotSubtotalTypes.Sum); field.NumberFormat = "#,##0.00 ";  worksheet.Calculate();  IPivotField field2 = pivotTable.Fields[19]; IPivotDataField dfSum2 = pivotTable.DataFields.Add(field2, "SUM%", PivotSubtotalTypes.Sum);  dfSum2.ShowDataAs = PivotFieldDataFormat.PercentageOfColumn;  field2.NumberFormat = "#,##0.00% "; worksheet.Calculate();

IPivotCache cache = workbook.PivotCaches.Add(worksheet[iDatStart, 1, iLine - 1, iMax]);  IPivotTable pivotTable = pivotWs.PivotTables.Add("SUM", pivotWs[1, 1], cache);  pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;  //pivotTable.Fields[1].Axis = PivotAxisTypes.Page; pivotTable.Fields[1].Axis = PivotAxisTypes.Row; pivotTable.Fields[9].Axis = PivotAxisTypes.Row; pivotTable.Fields[15].Axis = PivotAxisTypes.Column;  PivotCacheFieldsCollection pivotCaches = (pivotTable as PivotTableImpl).Cache.CacheFields;  (pivotTable.Fields[19] as PivotFieldImpl).IsDataField = true;  IPivotField field = new PivotFieldImpl(pivotCaches[19], pivotTable as PivotTableImpl); pivotTable.DataFields.Add(field, "SUM", PivotSubtotalTypes.Sum); field.NumberFormat = "#,##0.00 ";  worksheet.Calculate();  IPivotField field2 = new PivotFieldImpl(pivotCaches[19], pivotTable as PivotTableImpl); IPivotDataField dfSum2 = pivotTable.DataFields.Add(field2, "SUM%", PivotSubtotalTypes.Sum);  dfSum2.ShowDataAs = PivotFieldDataFormat.PercentageOfColumn;  field2.NumberFormat = "#,##0.00% "; worksheet.Calculate();

Note: Please include the below namespaces. 

using Syncfusion.XlsIO.Implementation.Collections; 

using Syncfusion.XlsIO.Implementation.PivotTables; 

Kindly let us know if the solution helps. 

Regards, 

Abirami.