Пользовательская сортировка в сводной таблице
** Как установить пользовательскую сортировку в сводной таблице с использованием библиотеки Aspose.Cells для Excel Python**
С помощью API Aspose.Cells для Python via .NET вы можете сортировать сводные таблицы по значениям полей. В следующем фрагменте кода загружается образец excel-файла и добавляются три сводные таблицы. Первая сводная таблица без пользовательской сортировки, вторая сводная таблица отсортирована по значениям поля строк “Морепродукты”, а третья сводная таблица отсортирована по значениям поля столбцов “28/07/2000”.
Образец исходного файла и выходных файлов можно загрузить отсюда для тестирования образца кода:
Образец кода
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) |