Tri personnalisé dans la table pivotante

Comment définir un tri personnalisé dans la table pivotante à l’aide de la bibliothèque Excel Aspose.Cells for Python

En utilisant l’API Aspose.Cells for Python via .NET, vous pouvez trier les tables pivotantes sur les valeurs de champ. L’extrait de code suivant charge le fichier Excel d’exemple et ajoute trois tables pivotantes. La première table pivotante ne comporte pas de tri personnalisé, la deuxième table pivotante est triée sur les valeurs de champ “SeaFood” et la troisième table pivotante est triée sur les valeurs de champ “28/07/2000”.

Le fichier source d’exemple et les fichiers de sortie peuvent être téléchargés ici pour tester le code d’exemple :

Fichier Excel source

Fichier Excel de sortie

Fichier PDF de sortie

Code d’exemple

from aspose.cells import PdfSaveOptions, Workbook
from aspose.cells.pivot import PivotFieldType

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
# Source directory
sourceDir = RunExamples.Get_SourceDirectory()
outputDir = RunExamples.Get_OutputDirectory()
wb = Workbook(sourceDir + "SamplePivotSort.xlsx")
#  Obtaining the reference of the newly added worksheet
sheet = wb.worksheets[0]
pivotTables = sheet.pivot_tables
#  source PivotTable
#  Adding a PivotTable to the worksheet
index = pivotTables.add("=Sheet1!A1:C10", "E3", "PivotTable2")
# Accessing the instance of the newly added PivotTable
pivotTable = pivotTables[index]
#  Unshowing grand totals for rows.
pivotTable.row_grand = False
pivotTable.column_grand = False
#  Dragging the first field to the row area.
pivotTable.add_field_to_area(PivotFieldType.ROW, 1)
rowField = pivotTable.row_fields[0]
rowField.is_auto_sort = True
rowField.is_ascend_sort = True
#  Dragging the second field to the column area.
pivotTable.add_field_to_area(PivotFieldType.COLUMN, 0)
colField = pivotTable.column_fields[0]
colField.number_format = "dd/mm/yyyy"
colField.is_auto_sort = True
colField.is_ascend_sort = True
#  Dragging the third field to the data area.
pivotTable.add_field_to_area(PivotFieldType.DATA, 2)
pivotTable.refresh_data()
pivotTable.calculate_data()
#  end of source PivotTable
#  sort the PivotTable on "SeaFood" row field values
#  Adding a PivotTable to the worksheet
index = pivotTables.add("=Sheet1!A1:C10", "E10", "PivotTable2")
#  Accessing the instance of the newly added PivotTable
pivotTable = pivotTables[index]
#  Unshowing grand totals for rows.
pivotTable.row_grand = False
pivotTable.column_grand = False
#  Dragging the first field to the row area.
pivotTable.add_field_to_area(PivotFieldType.ROW, 1)
rowField = pivotTable.row_fields[0]
rowField.is_auto_sort = True
rowField.is_ascend_sort = True
#  Dragging the second field to the column area.
pivotTable.add_field_to_area(PivotFieldType.COLUMN, 0)
colField = pivotTable.column_fields[0]
colField.number_format = "dd/mm/yyyy"
colField.is_auto_sort = True
colField.is_ascend_sort = True
colField.auto_sort_field = 0
# Dragging the third field to the data area.
pivotTable.add_field_to_area(PivotFieldType.DATA, 2)
pivotTable.refresh_data()
pivotTable.calculate_data()
#  end of sort the PivotTable on "SeaFood" row field values
#  sort the PivotTable on "28/07/2000" column field values
#  Adding a PivotTable to the worksheet
index = pivotTables.add("=Sheet1!A1:C10", "E18", "PivotTable2")
#  Accessing the instance of the newly added PivotTable
pivotTable = pivotTables[index]
#  Unshowing grand totals for rows.
pivotTable.row_grand = False
pivotTable.column_grand = False
#  Dragging the first field to the row area.
pivotTable.add_field_to_area(PivotFieldType.ROW, 1)
rowField = pivotTable.row_fields[0]
rowField.is_auto_sort = True
rowField.is_ascend_sort = True
rowField.auto_sort_field = 0
#  Dragging the second field to the column area.
pivotTable.add_field_to_area(PivotFieldType.COLUMN, 0)
colField = pivotTable.column_fields[0]
colField.number_format = "dd/mm/yyyy"
colField.is_auto_sort = True
colField.is_ascend_sort = True
#  Dragging the third field to the data area.
pivotTable.add_field_to_area(PivotFieldType.DATA, 2)
pivotTable.refresh_data()
pivotTable.calculate_data()
#  end of sort the PivotTable on "28/07/2000" column field values
# Saving the Excel file
wb.save(outputDir + "out_java.xlsx")
options = PdfSaveOptions()
options.one_page_per_sheet = True
wb.save(outputDir + "out_java.pdf", options)