Pivot charts are a powerful tool in Excel that allows you to visualize data from pivot tables in an easy-to-understand format. They enable users to summarize large datasets, highlight trends, and make data-driven decisions through interactive graphs. Whether you're analyzing sales figures, performance metrics, or any other form of data, pivot charts provide a dynamic way to represent complex data visually. In this article, we will demonstrate how to create pivot charts in Excel in Python using Spire.XLS for Python.

• Create Pivot Charts in Excel in Python
• Show or Hide Field Buttons in Pivot Charts in Excel in Python
• Format Pivot Chart Series in Excel 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 Pivot Charts in Excel in Python

Spire.XLS for Python provides the Worksheet.Charts.Add(pivotChartType:ExcelChartType, pivotTable:IPivotTable) method to create a pivot chart based on a specific pivot table in Excel. The detailed steps are as follows:

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet in the Excel file using Workbook.Worksheets[index] property.
• Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
• Add a pivot chart based on the pivot table to the worksheet using Worksheet.Charts.Add(pivotChartType:ExcelChartType, pivotTable:IPivotTable) method.
• Set the position and title of the pivot chart.
• Save the resulting file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("PivotTable.xlsx")

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

# Get the first pivot table in the worksheet
pivotTable = sheet.PivotTables[0]

# Create a clustered column chart based on the pivot table
pivotChart = sheet.Charts.Add(ExcelChartType.ColumnClustered, pivotTable)
# Set chart position
pivotChart.TopRow = 1
pivotChart.LeftColumn = 11
pivotChart.RightColumn = 20
pivotChart.BottomRow = 15

# Set chart title to null
pivotChart.ChartTitle = ""

# Save the resulting file
workbook.SaveToFile("CreatePivotChart.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Show or Hide Field Buttons in Pivot Charts in Excel in Python

You can show or hide the following field buttons in a pivot chart with Spire.XLS for Python:

• Entire Field Buttons
• Report Filter Field Buttons
• Legend Field Buttons
• Axis Field Buttons
• Value Field Buttons

The detailed steps are as follows:

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet in the Excel file using Workbook.Worksheets[index] property.
• Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
• Add a pivot chart based on the pivot table to the worksheet using Worksheet.Charts.Add(pivotChartType:ExcelChartType, pivotTable:IPivotTable) method.
• Set the position and title of the pivot chart.
• Hide specific field buttons in the pivot chart, such as the axis field buttons and the value field buttons, using Chart.DisplayAxisFieldButtons and Chart.DisplayValueFieldButtons properties.
• Save the resulting file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("CreatePivotChart.xlsx")

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

# Get the first pivot table in the worksheet
pivotTable = sheet.PivotTables[0]

# Create a clustered column chart based on the pivot table
pivotChart = sheet.Charts.Add(ExcelChartType.ColumnClustered, pivotTable)
# Set chart position
pivotChart.TopRow = 1
pivotChart.LeftColumn = 11
pivotChart.RightColumn = 20
pivotChart.BottomRow = 15

# Set chart title to null
pivotChart.ChartTitle = ""

# Hide specific field buttons
pivotChart.DisplayAxisFieldButtons = False
pivotChart.DisplayValueFieldButtons = False
# pivotChart.DisplayLegendFieldButtons = False
# pivotChart.ShowReportFilterFieldButtons = False
# pivotChart.DisplayEntireFieldButtons = False

# Save the resulting file
workbook.SaveToFile("HideFieldButtons.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Format Pivot Chart Series in Excel in Python

When generating a pivot chart using a pivot table as the data source with Spire.XLS for Python, the chart series are not automatically created. You need to add the series to the pivot chart and then apply the desired formatting. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet in the Excel file using Workbook.Worksheets[index] property.
• Get a specific pivot table in the worksheet using Worksheet.PivotTables[index] property.
• Add a pivot chart based on the pivot table to the worksheet using Worksheet.Charts.Add(pivotChartType:ExcelChartType, pivotTable:IPivotTable) method.
• Set the position and title of the pivot chart.
• Add series to the chart using Chart.Series.Add() method and then apply the desired formatting to the series.
• Save the resulting file using Workbook.SaveToFile() method.

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

# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("CreatePivotChart.xlsx")

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

# Get the first pivot table in the worksheet
pivotTable = sheet.PivotTables[0]

# Create a clustered column chart based on the pivot table
pivotChart = sheet.Charts.Add(ExcelChartType.ColumnClustered, pivotTable)
# Set chart position
pivotChart.TopRow = 1
pivotChart.LeftColumn = 11
pivotChart.RightColumn = 20
pivotChart.BottomRow = 15

# Set chart title to null
pivotChart.ChartTitle = ""

# Add chart series
series = pivotChart.Series.Add(ExcelChartType.ColumnClustered)
# Set bar width
series.GetCommonSerieFormat().GapWidth = 10
# Set overlap
# series.GetCommonSerieFormat().Overlap = 100

# Save the resulting file
workbook.SaveToFile("FormatChartSeries.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

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.

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.