创建数据透视表和数据透视图

数据透视表是记录的互动摘要。例如，您可能有数百个工作表中列表中的发票条目。数据透视表可以按客户、产品或日期总计发票。使用Microsoft Excel，可以通过将按钮拖到新位置，快速重新排列数据透视表中的信息。

数据透视图图表是数据透视表中数据的交互式图形表示。数据透视图图表在Excel 2000中引入。使用数据透视图图表使数据更容易理解，因为数据透视表会自动创建小计和总计。

Aspose.Cells for Python via .NET支持pivot tables 和pivot charts。

使用Aspose.Cells for Python Excel库添加数据透视表和数据透视图

Aspose.Cells for Python via .NET提供了一组特殊的类，用于创建数据透视表。这些类用于创建和设置PivotTable对象，作为PivotTable对象的基本构建块：

PivotField，数据透视表报告中的字段。
PivotFields，数据透视表中所有 PivotField 对象的集合。
一个工作表上的数据透视表。
数据透视表是工作表上所有数据透视表对象的集合。

准备使用Aspose.Cells for Python via .NET

从 pypi 安装 Aspose.Cells for Python via .NET，请使用如下命令：$ pip install aspose-cells-python。

您还可以按照逐步说明，将“Aspose.Cells for Python via .NET”安装到您的开发环境中。

使用Aspose.Cells for Python Excel库添加数据透视表的方法

使用Aspose.Cells for Python via .NET创建数据透视表：

使用Cell对象的put_value方法向工作表单元格添加一些数据。也可以使用填充了数据的模板文件。这些数据将被用作数据透视表的数据源。
通过调用PivotTables集合的add方法（封装在Worksheet对象中）向工作表添加一个数据透视表。
通过传入索引从PivotTables集合中访问新的 PivotTable 对象。# 使用 PivotTable 对象中封装的任何数据透视表对象来管理表。

下面是代码示例。

	from aspose.cells import Workbook
	from aspose.cells.pivot import PivotFieldType, 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(".")
	# Instantiating an Workbook object
	workbook = Workbook()
	# Obtaining the reference of the first worksheet
	sheet = workbook.worksheets[0]
	# Name the sheet
	sheet.name = "Data"
	cells = sheet.cells
	# Setting the values to the cells
	cell = cells.get("A1")
	cell.put_value("Employee")
	cell = cells.get("B1")
	cell.put_value("Quarter")
	cell = cells.get("C1")
	cell.put_value("Product")
	cell = cells.get("D1")
	cell.put_value("Continent")
	cell = cells.get("E1")
	cell.put_value("Country")
	cell = cells.get("F1")
	cell.put_value("Sale")
	cell = cells.get("A2")
	cell.put_value("David")
	cell = cells.get("A3")
	cell.put_value("David")
	cell = cells.get("A4")
	cell.put_value("David")
	cell = cells.get("A5")
	cell.put_value("David")
	cell = cells.get("A6")
	cell.put_value("James")
	cell = cells.get("A7")
	cell.put_value("James")
	cell = cells.get("A8")
	cell.put_value("James")
	cell = cells.get("A9")
	cell.put_value("James")
	cell = cells.get("A10")
	cell.put_value("James")
	cell = cells.get("A11")
	cell.put_value("Miya")
	cell = cells.get("A12")
	cell.put_value("Miya")
	cell = cells.get("A13")
	cell.put_value("Miya")
	cell = cells.get("A14")
	cell.put_value("Miya")
	cell = cells.get("A15")
	cell.put_value("Miya")
	cell = cells.get("A16")
	cell.put_value("Miya")
	cell = cells.get("A17")
	cell.put_value("Miya")
	cell = cells.get("A18")
	cell.put_value("Elvis")
	cell = cells.get("A19")
	cell.put_value("Elvis")
	cell = cells.get("A20")
	cell.put_value("Elvis")
	cell = cells.get("A21")
	cell.put_value("Elvis")
	cell = cells.get("A22")
	cell.put_value("Elvis")
	cell = cells.get("A23")
	cell.put_value("Elvis")
	cell = cells.get("A24")
	cell.put_value("Elvis")
	cell = cells.get("A25")
	cell.put_value("Jean")
	cell = cells.get("A26")
	cell.put_value("Jean")
	cell = cells.get("A27")
	cell.put_value("Jean")
	cell = cells.get("A28")
	cell.put_value("Ada")
	cell = cells.get("A29")
	cell.put_value("Ada")
	cell = cells.get("A30")
	cell.put_value("Ada")
	cell = cells.get("B2")
	cell.put_value("1")
	cell = cells.get("B3")
	cell.put_value("2")
	cell = cells.get("B4")
	cell.put_value("3")
	cell = cells.get("B5")
	cell.put_value("4")
	cell = cells.get("B6")
	cell.put_value("1")
	cell = cells.get("B7")
	cell.put_value("2")
	cell = cells.get("B8")
	cell.put_value("3")
	cell = cells.get("B9")
	cell.put_value("4")
	cell = cells.get("B10")
	cell.put_value("4")
	cell = cells.get("B11")
	cell.put_value("1")
	cell = cells.get("B12")
	cell.put_value("1")
	cell = cells.get("B13")
	cell.put_value("2")
	cell = cells.get("B14")
	cell.put_value("2")
	cell = cells.get("B15")
	cell.put_value("3")
	cell = cells.get("B16")
	cell.put_value("4")
	cell = cells.get("B17")
	cell.put_value("4")
	cell = cells.get("B18")
	cell.put_value("1")
	cell = cells.get("B19")
	cell.put_value("1")
	cell = cells.get("B20")
	cell.put_value("2")
	cell = cells.get("B21")
	cell.put_value("3")
	cell = cells.get("B22")
	cell.put_value("3")
	cell = cells.get("B23")
	cell.put_value("4")
	cell = cells.get("B24")
	cell.put_value("4")
	cell = cells.get("B25")
	cell.put_value("1")
	cell = cells.get("B26")
	cell.put_value("2")
	cell = cells.get("B27")
	cell.put_value("3")
	cell = cells.get("B28")
	cell.put_value("1")
	cell = cells.get("B29")
	cell.put_value("2")
	cell = cells.get("B30")
	cell.put_value("3")
	cell = cells.get("C2")
	cell.put_value("Maxilaku")
	cell = cells.get("C3")
	cell.put_value("Maxilaku")
	cell = cells.get("C4")
	cell.put_value("Chai")
	cell = cells.get("C5")
	cell.put_value("Maxilaku")
	cell = cells.get("C6")
	cell.put_value("Chang")
	cell = cells.get("C7")
	cell.put_value("Chang")
	cell = cells.get("C8")
	cell.put_value("Chang")
	cell = cells.get("C9")
	cell.put_value("Chang")
	cell = cells.get("C10")
	cell.put_value("Chang")
	cell = cells.get("C11")
	cell.put_value("Geitost")
	cell = cells.get("C12")
	cell.put_value("Chai")
	cell = cells.get("C13")
	cell.put_value("Geitost")
	cell = cells.get("C14")
	cell.put_value("Geitost")
	cell = cells.get("C15")
	cell.put_value("Maxilaku")
	cell = cells.get("C16")
	cell.put_value("Geitost")
	cell = cells.get("C17")
	cell.put_value("Geitost")
	cell = cells.get("C18")
	cell.put_value("Ikuru")
	cell = cells.get("C19")
	cell.put_value("Ikuru")
	cell = cells.get("C20")
	cell.put_value("Ikuru")
	cell = cells.get("C21")
	cell.put_value("Ikuru")
	cell = cells.get("C22")
	cell.put_value("Ipoh Coffee")
	cell = cells.get("C23")
	cell.put_value("Ipoh Coffee")
	cell = cells.get("C24")
	cell.put_value("Ipoh Coffee")
	cell = cells.get("C25")
	cell.put_value("Chocolade")
	cell = cells.get("C26")
	cell.put_value("Chocolade")
	cell = cells.get("C27")
	cell.put_value("Chocolade")
	cell = cells.get("C28")
	cell.put_value("Chocolade")
	cell = cells.get("C29")
	cell.put_value("Chocolade")
	cell = cells.get("C30")
	cell.put_value("Chocolade")
	cell = cells.get("D2")
	cell.put_value("Asia")
	cell = cells.get("D3")
	cell.put_value("Asia")
	cell = cells.get("D4")
	cell.put_value("Asia")
	cell = cells.get("D5")
	cell.put_value("Asia")
	cell = cells.get("D6")
	cell.put_value("Europe")
	cell = cells.get("D7")
	cell.put_value("Europe")
	cell = cells.get("D8")
	cell.put_value("Europe")
	cell = cells.get("D9")
	cell.put_value("Europe")
	cell = cells.get("D10")
	cell.put_value("Europe")
	cell = cells.get("D11")
	cell.put_value("America")
	cell = cells.get("D12")
	cell.put_value("America")
	cell = cells.get("D13")
	cell.put_value("America")
	cell = cells.get("D14")
	cell.put_value("America")
	cell = cells.get("D15")
	cell.put_value("America")
	cell = cells.get("D16")
	cell.put_value("America")
	cell = cells.get("D17")
	cell.put_value("America")
	cell = cells.get("D18")
	cell.put_value("Europe")
	cell = cells.get("D19")
	cell.put_value("Europe")
	cell = cells.get("D20")
	cell.put_value("Europe")
	cell = cells.get("D21")
	cell.put_value("Oceania")
	cell = cells.get("D22")
	cell.put_value("Oceania")
	cell = cells.get("D23")
	cell.put_value("Oceania")
	cell = cells.get("D24")
	cell.put_value("Oceania")
	cell = cells.get("D25")
	cell.put_value("Africa")
	cell = cells.get("D26")
	cell.put_value("Africa")
	cell = cells.get("D27")
	cell.put_value("Africa")
	cell = cells.get("D28")
	cell.put_value("Africa")
	cell = cells.get("D29")
	cell.put_value("Africa")
	cell = cells.get("D30")
	cell.put_value("Africa")
	cell = cells.get("E2")
	cell.put_value("China")
	cell = cells.get("E3")
	cell.put_value("India")
	cell = cells.get("E4")
	cell.put_value("Korea")
	cell = cells.get("E5")
	cell.put_value("India")
	cell = cells.get("E6")
	cell.put_value("France")
	cell = cells.get("E7")
	cell.put_value("France")
	cell = cells.get("E8")
	cell.put_value("Germany")
	cell = cells.get("E9")
	cell.put_value("Italy")
	cell = cells.get("E10")
	cell.put_value("France")
	cell = cells.get("E11")
	cell.put_value("U.S.")
	cell = cells.get("E12")
	cell.put_value("U.S.")
	cell = cells.get("E13")
	cell.put_value("Brazil")
	cell = cells.get("E14")
	cell.put_value("U.S.")
	cell = cells.get("E15")
	cell.put_value("U.S.")
	cell = cells.get("E16")
	cell.put_value("Canada")
	cell = cells.get("E17")
	cell.put_value("U.S.")
	cell = cells.get("E18")
	cell.put_value("Italy")
	cell = cells.get("E19")
	cell.put_value("France")
	cell = cells.get("E20")
	cell.put_value("Italy")
	cell = cells.get("E21")
	cell.put_value("New Zealand")
	cell = cells.get("E22")
	cell.put_value("Australia")
	cell = cells.get("E23")
	cell.put_value("Australia")
	cell = cells.get("E24")
	cell.put_value("New Zealand")
	cell = cells.get("E25")
	cell.put_value("S.Africa")
	cell = cells.get("E26")
	cell.put_value("S.Africa")
	cell = cells.get("E27")
	cell.put_value("S.Africa")
	cell = cells.get("E28")
	cell.put_value("Egypt")
	cell = cells.get("E29")
	cell.put_value("Egypt")
	cell = cells.get("E30")
	cell.put_value("Egypt")
	cell = cells.get("F2")
	cell.put_value(2000)
	cell = cells.get("F3")
	cell.put_value(500)
	cell = cells.get("F4")
	cell.put_value(1200)
	cell = cells.get("F5")
	cell.put_value(1500)
	cell = cells.get("F6")
	cell.put_value(500)
	cell = cells.get("F7")
	cell.put_value(1500)
	cell = cells.get("F8")
	cell.put_value(800)
	cell = cells.get("F9")
	cell.put_value(900)
	cell = cells.get("F10")
	cell.put_value(500)
	cell = cells.get("F11")
	cell.put_value(1600)
	cell = cells.get("F12")
	cell.put_value(600)
	cell = cells.get("F13")
	cell.put_value(2000)
	cell = cells.get("F14")
	cell.put_value(500)
	cell = cells.get("F15")
	cell.put_value(900)
	cell = cells.get("F16")
	cell.put_value(700)
	cell = cells.get("F17")
	cell.put_value(1400)
	cell = cells.get("F18")
	cell.put_value(1350)
	cell = cells.get("F19")
	cell.put_value(300)
	cell = cells.get("F20")
	cell.put_value(500)
	cell = cells.get("F21")
	cell.put_value(1000)
	cell = cells.get("F22")
	cell.put_value(1500)
	cell = cells.get("F23")
	cell.put_value(1500)
	cell = cells.get("F24")
	cell.put_value(1600)
	cell = cells.get("F25")
	cell.put_value(1000)
	cell = cells.get("F26")
	cell.put_value(1200)
	cell = cells.get("F27")
	cell.put_value(1300)
	cell = cells.get("F28")
	cell.put_value(1500)
	cell = cells.get("F29")
	cell.put_value(1400)
	cell = cells.get("F30")
	cell.put_value(1000)
	# Adding a new sheet
	sheet2 = workbook.worksheets[workbook.worksheets.add()]
	# Naming the sheet
	sheet2.name = "PivotTable"
	# Getting the pivottables collection in the sheet
	pivotTables = sheet2.pivot_tables
	# Adding a PivotTable to the worksheet
	index = pivotTables.add("=Data!A1:F30", "B3", "PivotTable1")
	# Accessing the instance of the newly added PivotTable
	pivotTable = pivotTables[index]
	# Showing the grand totals
	pivotTable.row_grand = True
	pivotTable.column_grand = True
	# Setting the PivotTable report is automatically formatted
	pivotTable.is_auto_format = True
	# Setting the PivotTable autoformat type.
	pivotTable.auto_format_type = PivotTableAutoFormatType.REPORT6
	# Draging the first field to the row area.
	pivotTable.add_field_to_area(PivotFieldType.ROW, 0)
	# Draging the third field to the row area.
	pivotTable.add_field_to_area(PivotFieldType.ROW, 2)
	# Draging the second field to the row area.
	pivotTable.add_field_to_area(PivotFieldType.ROW, 1)
	# Draging the fourth field to the column area.
	pivotTable.add_field_to_area(PivotFieldType.COLUMN, 3)
	# Draging the fifth field to the data area.
	pivotTable.add_field_to_area(PivotFieldType.DATA, 5)
	# Setting the number format of the first data field
	pivotTable.data_fields[0].number_format = "$#,##0.00"
	# Saving the Excel file
	workbook.save(dataDir + "pivotTable_test.out.xlsx")

view raw PivotTables-CreatePivotTablesPivotCharts-CreatePivotTable-1.py hosted with ❤ by GitHub

使用Aspose.Cells for Python Excel库添加数据透视图的方法

使用Aspose.Cells for Python via .NET创建数据透视图：

添加图表。
将图表的数据源设置为引用电子表格中的现有数据透视表。
设置其他属性。

	from aspose.cells import SheetType, Workbook
	from aspose.cells.charts import ChartType

	# 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(".")
	# Instantiating an Workbook object
	# Opening the excel file
	workbook = Workbook(dataDir + "pivotTable_test.xlsx")
	# Adding a new sheet
	sheet3 = workbook.worksheets[workbook.worksheets.add(SheetType.CHART)]
	# Naming the sheet
	sheet3.name = "PivotChart"
	# Adding a column chart
	index = sheet3.charts.add(ChartType.COLUMN, 0, 5, 28, 16)
	# Setting the pivot chart data source
	sheet3.charts[index].pivot_source = "PivotTable!PivotTable1"
	sheet3.charts[index].hide_pivot_field_buttons = False
	# Saving the Excel file
	workbook.save(dataDir + "pivotChart_test_out.xlsx")

view raw PivotTables-CreatePivotTablesPivotCharts-CreatePivotChart-1.py hosted with ❤ by GitHub

格式化数据透视表透视表和源数据