格式化数据透视表
数据透视表外观
如何创建数据透视表介绍了如何创建简单的数据透视表。本文描述了如何通过设置各种属性来自定义数据透视表的外观:
- 数据透视表格式选项
- 数据透视字段格式选项
- 数据字段格式选项
如何设置数据透视表格式选项
PivotTable类控制整体数据透视表,可以以多种方式进行格式设置。
如何设置自动格式类型
Microsoft Excel提供许多不同的预设报告格式,Aspose.Cells for Python via .NET也支持这些格式选项。要访问它们:
- 将PivotTable.is_auto_format设置为true。
- 从PivotTableAutoFormatType枚举中分配一个格式选项。
from aspose.cells import Workbook | |
from aspose.cells.pivot import PivotTableAutoFormatType | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Load a template file | |
workbook = Workbook(dataDir + "Book1.xls") | |
pivotindex = 0 | |
# Get the first worksheet | |
worksheet = workbook.worksheets[0] | |
# Accessing the PivotTable | |
pivotTable = worksheet.pivot_tables[pivotindex] | |
# Setting the PivotTable report is automatically formatted | |
pivotTable.is_auto_format = True | |
# Setting the PivotTable atuoformat type. | |
pivotTable.auto_format_type = PivotTableAutoFormatType.REPORT5 | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xls") |
如何设置格式选项
下面的代码示例演示了如何格式化数据透视表以显示行和列的总计,以及如何设置报告的字段顺序。它还显示了如何为空值设置自定义字符串。
from aspose.cells import PrintOrderType, Workbook | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Load a template file | |
workbook = Workbook(dataDir + "Book1.xls") | |
# Get the first worksheet | |
worksheet = workbook.worksheets[0] | |
pivotindex = 0 | |
# Accessing the PivotTable | |
pivotTable = worksheet.pivot_tables[pivotindex] | |
# Setting the PivotTable report shows grand totals for rows. | |
pivotTable.row_grand = True | |
# Setting the PivotTable report shows grand totals for columns. | |
pivotTable.column_grand = True | |
# Setting the PivotTable report displays a custom string in cells that contain null values. | |
pivotTable.display_null_string = True | |
pivotTable.null_string = "null" | |
# Setting the PivotTable report's layout | |
pivotTable.page_field_order = PrintOrderType.DOWN_THEN_OVER | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xls") |
手动设置外观和感觉格式
要手动设置数据透视表报告的外观和感觉,而不是使用预设的报告格式,请使用PivotTable.format_all(style)和PivotTable.format(row, column, style)方法。为所需的格式创建样式对象,例如:
from aspose.cells import BackgroundType, Workbook | |
from aspose.cells.pivot import PivotTableStyleType | |
from aspose.pydrawing import Color | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Load a template file | |
workbook = Workbook(dataDir + "Book1.xls") | |
# Get the first worksheet | |
worksheet = workbook.worksheets[0] | |
pivot = workbook.worksheets[0].pivot_tables[0] | |
pivot.pivot_table_style_type = PivotTableStyleType.PIVOT_TABLE_STYLE_DARK1 | |
style = workbook.create_style() | |
style.font.name = "Arial Black" | |
style.foreground_color = Color.yellow | |
style.pattern = BackgroundType.SOLID | |
pivot.format_all(style) | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xls") |
如何设置数据透视表字段格式选项
PivotField类表示数据透视表中的字段,并可以以多种方式进行格式设置。下面的代码示例演示了如何:
- 访问行字段。
- 设置合计。
- 设置自动排序。
- 设置自动显示。
如何设置行/列/页面字段格式
from aspose.cells import Workbook | |
from aspose.cells.pivot import PivotFieldSubtotalType | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Load a template file | |
workbook = Workbook(dataDir + "Book1.xls") | |
# Get the first worksheet | |
worksheet = workbook.worksheets[0] | |
pivotindex = 0 | |
# Accessing the PivotTable | |
pivotTable = worksheet.pivot_tables[pivotindex] | |
# Setting the PivotTable report shows grand totals for rows. | |
pivotTable.row_grand = True | |
# Accessing the row fields. | |
pivotFields = pivotTable.row_fields | |
# Accessing the first row field in the row fields. | |
pivotField = pivotFields[0] | |
# Setting Subtotals. | |
pivotField.set_subtotals(PivotFieldSubtotalType.SUM, True) | |
pivotField.set_subtotals(PivotFieldSubtotalType.COUNT, True) | |
# Setting autosort options. | |
# Setting the field auto sort. | |
pivotField.is_auto_sort = True | |
# Setting the field auto sort ascend. | |
pivotField.is_ascend_sort = True | |
# Setting the field auto sort using the field itself. | |
pivotField.auto_sort_field = -5 | |
# Setting autoShow options. | |
# Setting the field auto show. | |
pivotField.is_auto_show = True | |
# Setting the field auto show ascend. | |
pivotField.is_ascend_show = False | |
# Setting the auto show using field(data field). | |
pivotField.auto_show_field = 0 | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xls") |
如何设置数据字段格式
以下代码示例显示如何设置数据字段的显示格式和数字格式。
from aspose.cells import Workbook | |
from aspose.cells.pivot import PivotFieldDataDisplayFormat, PivotItemPosition | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Load a template file | |
workbook = Workbook(dataDir + "Book1.xls") | |
# Get the first worksheet | |
worksheet = workbook.worksheets[0] | |
pivotindex = 0 | |
# Accessing the PivotTable | |
pivotTable = worksheet.pivot_tables[pivotindex] | |
# Accessing the data fields. | |
pivotFields = pivotTable.data_fields | |
# Accessing the first data field in the data fields. | |
pivotField = pivotFields[0] | |
# Setting data display format | |
pivotField.data_display_format = PivotFieldDataDisplayFormat.PERCENTAGE_OF | |
# Setting the base field. | |
pivotField.base_field_index = 1 | |
# Setting the base item. | |
pivotField.base_item_position = PivotItemPosition.NEXT | |
# Setting number format | |
pivotField.number = 10 | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xls") |
如何清除数据透视字段
PivotFieldCollection 有一个名为 clear() 的方法,允许您清除数据透视字段。当您想要清除区域中的所有数据透视字段时(例如页、列、行或数据),请使用它。 以下代码示例显示如何清除数据区域中的所有数据透视字段。
from aspose.cells import 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 | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Load a template file | |
workbook = Workbook(dataDir + "Book1.xls") | |
# Get the first worksheet | |
sheet = workbook.worksheets[0] | |
# Get the pivot tables in the sheet | |
pivotTables = sheet.pivot_tables | |
# Get the first PivotTable | |
pivotTable = pivotTables[0] | |
# Clear all the data fields | |
pivotTable.data_fields.clear() | |
# Add new data field | |
pivotTable.add_field_to_area(PivotFieldType.DATA, "Betrag Netto FW") | |
# Set the refresh data flag on | |
pivotTable.refresh_data_flag = False | |
# Refresh and calculate the pivot table data | |
pivotTable.refresh_data() | |
pivotTable.calculate_data() | |
# Saving the Excel file | |
workbook.save(dataDir + "output.xls") |