插入数据透视表

创建数据透视表

可以使用Aspose.Cells for Python via .NET以程序方式向电子表格添加数据透视表。

数据透视表对象模型

Aspose.Cells for Python via .NET在aspose.cells.pivot命名空间中提供了一组特殊的类,用于创建和控制数据透视表。这些类用于创建和设置PivotTable对象,数据透视表的构建模块。对象包括:

使用 Aspose.Cells 创建一个简单的数据透视表

  1. 使用 Cell 对象的 put_value 方法向工作表添加数据。 这些数据将被用作数据透视表的数据源。
  2. 通过调用 PivotTables 集合的 add 方法(封装在工作表对象中)向工作表添加一个数据透视表。
  3. 通过传递数据透视表索引从 PivotTables 集合中访问新的 PivotTable 对象。
  4. 使用上面解释的任何 PivotTable 对象来管理数据透视表。

执行示例代码后,数据透视表将被添加到工作表中。

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(".")
# Instantiating a Workbook object
workbook = Workbook()
# Obtaining the reference of the newly added worksheet
sheet = workbook.worksheets[0]
cells = sheet.cells
# Setting the value to the cells
cell = cells.get("A1")
cell.put_value("Sport")
cell = cells.get("B1")
cell.put_value("Quarter")
cell = cells.get("C1")
cell.put_value("Sales")
cell = cells.get("A2")
cell.put_value("Golf")
cell = cells.get("A3")
cell.put_value("Golf")
cell = cells.get("A4")
cell.put_value("Tennis")
cell = cells.get("A5")
cell.put_value("Tennis")
cell = cells.get("A6")
cell.put_value("Tennis")
cell = cells.get("A7")
cell.put_value("Tennis")
cell = cells.get("A8")
cell.put_value("Golf")
cell = cells.get("B2")
cell.put_value("Qtr3")
cell = cells.get("B3")
cell.put_value("Qtr4")
cell = cells.get("B4")
cell.put_value("Qtr3")
cell = cells.get("B5")
cell.put_value("Qtr4")
cell = cells.get("B6")
cell.put_value("Qtr3")
cell = cells.get("B7")
cell.put_value("Qtr4")
cell = cells.get("B8")
cell.put_value("Qtr3")
cell = cells.get("C2")
cell.put_value(1500)
cell = cells.get("C3")
cell.put_value(2000)
cell = cells.get("C4")
cell.put_value(600)
cell = cells.get("C5")
cell.put_value(1500)
cell = cells.get("C6")
cell.put_value(4070)
cell = cells.get("C7")
cell.put_value(5000)
cell = cells.get("C8")
cell.put_value(6430)
pivotTables = sheet.pivot_tables
# Adding a PivotTable to the worksheet
index = pivotTables.add("=A1:C8", "E3", "PivotTable2")
# Accessing the instance of the newly added PivotTable
pivotTable = pivotTables[index]
# Unshowing grand totals for rows.
pivotTable.row_grand = False
# Draging the first field to the row area.
pivotTable.add_field_to_area(PivotFieldType.ROW, 0)
# Draging the second field to the column area.
pivotTable.add_field_to_area(PivotFieldType.COLUMN, 1)
# Draging the third field to the data area.
pivotTable.add_field_to_area(PivotFieldType.DATA, 2)
# Saving the Excel file
workbook.save(dataDir + "pivotTable_test_out.xls")

高级主题