插入数据透视表
Contents
[
Hide
]
创建数据透视表
可以使用Aspose.Cells for Python via .NET以程序方式向电子表格添加数据透视表。
数据透视表对象模型
Aspose.Cells for Python via .NET在aspose.cells.pivot命名空间中提供了一组特殊的类,用于创建和控制数据透视表。这些类用于创建和设置PivotTable对象,数据透视表的构建模块。对象包括:
- PivotField 代表 PivotTable 中的一个字段。
- PivotFieldCollection 代表 PivotTable 中的所有 PivotField 对象的集合。
- PivotTable 代表工作表上的数据透视表。
- PivotTableCollection 代表工作表上的所有 PivotTable 对象的集合。
使用 Aspose.Cells 创建一个简单的数据透视表
- 使用 Cell 对象的 put_value 方法向工作表添加数据。 这些数据将被用作数据透视表的数据源。
- 通过调用 PivotTables 集合的 add 方法(封装在工作表对象中)向工作表添加一个数据透视表。
- 通过传递数据透视表索引从 PivotTables 集合中访问新的 PivotTable 对象。
- 使用上面解释的任何 PivotTable 对象来管理数据透视表。
执行示例代码后,数据透视表将被添加到工作表中。
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 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") |
当将一系列单元格指定为数据源时,该范围必须从左上到右下。例如,“A1:C3”是有效的,但“C3:A1”是无效的。