Working with data display formats of DataField in Pivot Table
“Rank Smallest to Largest” and “Rank Largest to Smallest” display format option
ASpose.Cells provides the ability to set the display format option for pivot fields. For this, the API provides the PivotField.ShowValuesSetting.CalculationType property. To rank largest to smallest, you may set the PivotField.ShowValuesSetting.CalculationType property to PivotFieldDataDisplayFormat.RankLargestToSmallest. The following code snippet demonstrates setting the display format options.
Sample source and output files can be downloaded from here for testing the sample code:
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// directories | |
string sourceDir = RunExamples.Get_SourceDirectory(); | |
string outputDir = RunExamples.Get_OutputDirectory(); | |
// Load a template file | |
Workbook workbook = new Workbook(sourceDir + "PivotTableSample.xlsx"); | |
// Get the first worksheet | |
Worksheet worksheet = workbook.Worksheets[0]; | |
int pivotIndex = 0; | |
// Accessing the PivotTable | |
PivotTable pivotTable = worksheet.PivotTables[pivotIndex]; | |
// Accessing the data fields. | |
PivotFieldCollection pivotFields = pivotTable.DataFields; | |
// Accessing the first data field in the data fields. | |
PivotField pivotField = pivotFields[0]; | |
// Setting data display format | |
pivotField.ShowValuesSetting.CalculationType = PivotFieldDataDisplayFormat.RankLargestToSmallest; | |
pivotTable.CalculateData(); | |
// Saving the Excel file | |
workbook.Save(outputDir + "PivotTableDataDisplayFormatRanking_out.xlsx"); |