数据透视表中的自定义排序

使用Aspose.Cells for Python Excel库设置数据透视表的自定义排序方法

通过使用Aspose.Cells for Python via .NET API,您可以对字段值进行数据透视表排序。以下代码片段加载了示例excel文件并添加了三个数据透视表。第一个数据透视表没有自定义排序,第二个数据透视表按"SeaFood"行字段值排序,第三个数据透视表按"28/07/2000"列字段值排序。

可从此处下载示例源文件和输出文件以测试示例代码:

源Excel文件

输出Excel文件

输出PDF文件

示例代码

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)