Pivot tables provide a flexible way to organize, manipulate, and summarize data from different perspectives, enabling users to gain valuable insights and make informed decisions. With pivot tables, you can easily rearrange and summarize data based on various criteria, such as categories, dates, or numerical values. This feature is particularly useful when dealing with complex datasets or when you need to compare and analyze data from different angles. In this article, you will learn how to create or operate pivot tables in an Excel document using Spire.XLS for Python.

• Create a Pivot Table in Excel in Python
• Sort Pivot Table by Column Values in Python
• Expand or Collapse Rows in Pivot Table in Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.

pip install Spire.XLS

If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows

Create a Pivot Table in Excel in Python

Spire.XLS for Python offers the PivotTable class to work with pivot tables in an Excel document. To create a pivot table based on the data in an existing Excel worksheet, follow the steps below.

• Create a Workbook object.
• Load a sample Excel document using Workbook.LoadFromFile() method.
• Get a specified worksheet through Workbook.Worksheets[index] property.
• Specify the range of cells on which the pivot table will be created using Worksheet.Range property
• Create an object of PivotCache using Workbook.PivotCaches.Add() method.
• Add a pivot table to the worksheet using Worksheet.PivotTables.Add() method.
• Add fields to rows area.
• Add fields to values area.
• Save the result document using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load a sample Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Data.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Select the data source range
cellRange = sheet.Range["C1:F11"]
piVotCache = workbook.PivotCaches.Add(cellRange)

# Add a PivotTable to the worksheet and set the location and cache of it
pivotTable = sheet.PivotTables.Add("Pivot Table", sheet.Range["H1"], piVotCache)

# Add "Region" and "Product" fields to rows area
regionField = pivotTable.PivotFields["Region"]
regionField.Axis = AxisTypes.Row
pivotTable.Options.RowHeaderCaption = "Region"
productField = pivotTable.PivotFields["Product"]
productField.Axis = AxisTypes.Row

# Add "Quantity" and "Amount" fields to values area
pivotTable.DataFields.Add(pivotTable.PivotFields["Quantity"], "SUM of Quantity", SubtotalTypes.Sum)
pivotTable.DataFields.Add(pivotTable.PivotFields["Amount"], "SUM of Amount", SubtotalTypes.Sum)

# Apply a built-in style to the pivot table
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium11

# Set column width
sheet.SetColumnWidth(8, 16);
sheet.SetColumnWidth(9, 16);
sheet.SetColumnWidth(10, 16);

# Save the document
workbook.SaveToFile("output/PivotTable.xlsx", ExcelVersion.Version2016)

Sort Pivot Table by Column Values in Python

A specific field can be accessed through the PivotTable.PivotFields[index] property, and then you can set its sort type using the PivotField.SortType property. The following are the steps to sort pivot table by the values of a specific field.

• Create a Workbook object.
• Load an Excel document using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[index] property.
• Get a specific pivot table from the worksheet through Worksheet.PivotTables[index] property.
• Get a specific field through PivotTable.PivotFields[fieldName] property.
• Sort data in the field through PivotField.SortType property.
• Save the workbook to a different file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\PivotTable.xlsx");

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the specified pivot table
pivotTable = sheet.PivotTables[0]

# Get the specified field
idField = pivotTable.PivotFields["Order ID"]

# Sort data in the column of "Order ID" field
idField.SortType = PivotFieldSortType.Descending

# Save the document
workbook.SaveToFile("output/SortData.xlsx", ExcelVersion.Version2016)

Expand or Collapse Rows in Pivot Table in Python

To collapse the details under a certain pivot field, use PivotField.HideItemDetail(string itemValue, bool isHiddenDetail) method and set the second parameter to true; to show the details, set the second parameter to false. The detailed steps are as follows.

• Create a Workbook object.
• Load an Excel document using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets[index] property.
• Get a specific pivot table from the worksheet through Worksheet.PivotTables[index] property.
• Get a specific field through PivotTable.PivotFields[fieldName] property.
• Collapse or expand rows of the field using PivotField.HideItemDetail(string itemValue, bool isHiddenDetail) method.
• Save the workbook to a different file using Workbook.SaveToFile() method.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load a sample Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\PivotTable.xlsx");

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Get the specified pivot table
pivotTable = sheet.PivotTables[0]

# Get the specified field
regoinField = pivotTable.PivotFields["Region"]

# Hide details under the selected item of the region
regoinField.HideItemDetail("West", True)
regoinField.HideItemDetail("East", True)

# Save the document
workbook.SaveToFile("output/CollapseRows.xlsx", ExcelVersion.Version2016)

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.