We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

IPivotDataField NumberFormat missing.

Thread ID:

Created:

Updated:

Platform:

Replies:

146218 Jul 26,2019 02:06 AM UTC Jul 30,2019 02:03 PM UTC WinForms 2
loading
Tags: XlsIO
ReerayXia
Asked On July 26, 2019 02:06 AM UTC

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 [Syncfusion]
Replied On July 26, 2019 02:16 PM UTC

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 30th July 2019. 
 
Regards, 
Abirami 


Abirami Varadharajan [Syncfusion]
Replied On July 30, 2019 02:03 PM UTC

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. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon