ピボットテーブルのカスタムソート

Aspose.Cells for Python Excel ライブラリを使用してピボットテーブルでカスタムソーティングを設定する方法

Aspose.Cells for Python via .NET API を使用して、フィールド値でピボットテーブルをソートすることができます。以下のコードスニペットは、サンプルの Excel ファイルを読み込んで、3 つのピボットテーブルを追加しています。最初のピボットテーブルはカスタムソートなし、2 番目のピボットテーブルは「SeaFood」の行フィールド値でソートされ、3 番目のピボットテーブルは「28/07/2000」の列フィールド値でソートされています。

サンプルソースファイルと出力ファイルは、テスト用のサンプルコードをダウンロードできます:

元のExcelファイル

出力Excelファイル

[出力PDFファイル]（98107430.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)