数据透视表中的自定义排序
Contents
[
Hide
]
使用Aspose.Cells for Python Excel库设置数据透视表的自定义排序方法
通过使用Aspose.Cells for Python via .NET API,您可以对字段值进行数据透视表排序。以下代码片段加载了示例excel文件并添加了三个数据透视表。第一个数据透视表没有自定义排序,第二个数据透视表按"SeaFood"行字段值排序,第三个数据透视表按"28/07/2000"列字段值排序。
可从此处下载示例源文件和输出文件以测试示例代码:
示例代码
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |