Insert Pivot Table

Create Pivot Table

It is possible to use Aspose.Cells for Python via .NET to add pivot tables to spreadsheets programmatically.

Pivot Table Object Model

Aspose.Cells for Python via .NET provides a special set of classes in the aspose.cells.pivot namespace that are used to create and control pivot tables. These classes are used to create and set PivotTable objects, the building blocks of a pivot table. The objects are:

• PivotField represents a field in a PivotTable.
• PivotFieldCollection represents a collection of all the PivotField objects in the PivotTable.
• PivotTable represents a PivotTable on a worksheet.
• PivotTableCollection represents a collection of all the PivotTable objects on a worksheet.

Creating a Simple Pivot Table Using Aspose.Cells

1. Add data to a worksheet using the Cell object’s put_value method. This data will be used as the pivot table’s data source.
2. Add a pivot table to the worksheet by calling the PivotTables collection’s add method, which is encapsulated in the Worksheet object.
3. Access the new PivotTable object from the PivotTables collection by passing the PivotTable index.
4. Use any of the PivotTable objects (explained above) to manage the pivot table.

After executing the example code, a pivot table is added to the worksheet.

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")

When assigning a range of cells as the data source, the range must go from top left to bottom right. For example, “A1:C3” is valid but “C3:A1” is not.

Advance topics

• Consolidation Function
• Custom sorting in Pivot Table
• Customize Globalization Settings for Pivot Table
• Disable Pivot Table Ribbons
• Find and Refresh the Nested or Children Pivot Tables of Parent Pivot Table
• Formatting Pivot Table
• Get External Connection Data Source of Pivot Table
• Get Pivot Table refresh date and refresh by who information
• Group Pivot Fields in the Pivot Table
• Parsing Pivot Cached Records while loading Excel file
• Pivot Table and Source Data
• Pivot Table Hide and Sort data
• Refresh and Calculate Pivot Table having Calculated Items
• Save Pivot Table in ODS file
• Show report filter pages option
• Working with data display formats of DataField in Pivot Table