使用 Python 在 Excel 中创建、更新和删除数据透视表

摘要：数据透视表是 Excel 最强大的功能之一，允许用户快速汇总、分析和可视化数据。无论您是处理大型数据集还是需要生成详细报告，数据透视表都提供了一种灵活有效的方式，将原始数据转化为有意义的见解。

数据透视表是 Excel 最强大的功能之一，允许用户快速汇总、分析和可视化数据。无论您是处理大型数据集还是需要生成详细报告，数据透视表都提供了一种灵活有效的方式，将原始数据转化为有意义的见解。

为了在 Python 中创建、更新和删除 Excel 中的数据透视表，本文使用 Python Excel 库：Spire.XLS for Python。

Spire.XLS for Python 是一个多功能且易于使用的库，用于在 Python 应用程序中创建、读取、编辑和转换 Excel 文件。使用此库，您可以轻松处理许多电子表格格式，例如 XLS、XLSX、XLSB、XLSM 和 ODS。此外，您还可以将 Excel 文件渲染为其他类型的文件格式，例如 PDF、HTML、CSV、文本、图像、XML、SVG、ODS、PostScript 和 XPS。

您可以通过在终端中运行以下命令从 pypi 轻松安装 Spire.XLS for Python：

pip install Spire.Xls

有关安装的更多详细信息，您可以查看此官方文档：如何在 VS Code 中安装 Spire.XLS for Python。

数据透视表不直接使用工作表中的数据;相反，它从 Pivot 缓存中提取数据，该缓存存储数据的快照。数据透视表可以包括行字段、列字段和数据字段。

要创建数据透视表，请执行以下步骤：

添加 Pivot 缓存：这将捕获并保存工作表中的相关数据。创建数据透视表：使用数据透视缓存中存储的数据设置数据透视表。添加字段：包括字段，例如行字段、列字段和数据字段，以组织和分析数据。

这是一个简单的示例，展示了如何使用 Python 在 Excel 中创建数据透视表：

from spire.xls import *from spire.xls.common import *# Create a workbook objectWorkbook = Workbook# Load an Excel Fileworkbook.LoadFromFile("Sample.xlsx")# Get the first worksheetsheet = workbook.Worksheets[0]# Define the data source rangedata_range = sheet.Range["A1:C9"]# Add a pivot cache from the given data source rangepivot_cache = workbook.PivotCaches.Add(data_range)# Create a pivot table from the pivot cache and add it to a specific location of the worksheetpivot_table = sheet.PivotTables.Add("Pivot Table", sheet.Range["E2:H8"], pivot_cache)# Add a row field to the pivot tablerow = pivot_table.PivotFields["Region"]row.Axis = AxisTypes.Rowpivot_table.Options.RowHeaderCaption = "Region"# Add a column field to the pivot tablecolumn = pivot_table.PivotFields["Product"]column.Axis = AxisTypes.Columnpivot_table.Options.ColumnHeaderCaption = "Product"# Add a data field with the required subtotal function to the pivot tablepivot_table.DataFields.Add(pivot_table.PivotFields["Sales"], "Sum of Sales", SubtotalTypes.Sum)# Set pivot table stylepivot_table.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium14 # Set column widthsheet.Range["E2:H8"].ColumnWidth = 12.0# Save the resultant workbook to a fileworkbook.SaveToFile("CreatePivotTable.xlsx", ExcelVersion.Version2016)workbook.Dispose

使用 Python 在 Excel 中创建数据透视表

有时，您可能需要在分析中包含其他数据字段或数据集。通过更改数据源，您可以向数据透视表添加更全面的信息。

这是一个简单的示例，展示了如何使用 Python 在 Excel 中更改数据透视表的数据源：

from spire.xls import *from spire.xls.common import *# Create a Workbook objectworkbook = Workbook# Load an Excel fileworkbook.LoadFromFile("CreatePivotTable.xlsx")# Get the first worksheetsheet = workbook.Worksheets[0]# Get the first pivot table in the worksheetpivot_table = sheet.PivotTables[0]# Define the new data source range of the pivot tabledata_range = sheet.Range["B1:C9"]# Change the data source range of the pivot tablepivot_table.ChangeDataSource(data_range)# Refresh the pivot tablepivot_table.Cache.IsRefreshOnLoad = True# Save the resultant workbook to a fileworkbook.SaveToFile("ChangeDataSourceOfPivotTable.xlsx", ExcelVersion.Version2016)workbook.Dispose

您可以通过数字格式代码将各种类型的数字格式（例如数字、货币、会计、日期、百分比、科学、分数和文本）应用于数据透视表中的数据。

这是一个简单的示例，展示了如何使用 Python 为 Excel 中的数据透视表中特定数据透视字段的数据设置数字格式：

from spire.xls import *from spire.xls.common import *# Create a Workbook objectworkbook = Workbook# Load an Excel fileworkbook.LoadFromFile("CreatePivotTable.xlsx")# Get the first worksheetsheet = workbook.Worksheets[0]# Get the first pivot table in the worksheetpivot_table = sheet.PivotTables[0]# Set currency number format for the pivot field "Sales"pivot_table.PivotFields["Sales"].NumberFormat = "\"$\"#,##0.00"# Save the resultant workbook to a fileworkbook.SaveToFile("SetNumberFormatForPivotField.xlsx", ExcelVersion.Version2016)workbook.Dispose

在 Python 的 Excel 中设置数据透视表数据的数字格式

排序可以帮助您更快地找到特定信息。例如，如果您正在寻找最低的销售数字或最早的日期，则对数据透视表进行排序会将该数据置于顶部。

这是一个简单的示例，展示了如何使用 Python 在 Excel 中的数据透视表中的数据进行排序：

from spire.xls import *from spire.xls.common import *# Create a Workbook objectworkbook = Workbook# Load an Excel fileworkbook.LoadFromFile("CreatePivotTable.xlsx")# Get the first worksheetsheet = workbook.Worksheets[0]# Get the first pivot table in the worksheetpivot_table = sheet.PivotTables[0]# Sort data of the pivot field "Region" in descending orderpivot_table.PivotFields["Region"].SortType = PivotFieldSortType.Descending# Save the resultant workbook to a fileworkbook.SaveToFile("SortDataInPivotField.xlsx", ExcelVersion.Version2016)workbook.Dispose

在 Python 的 Excel 中对数据透视表数据进行排序

通过提取数据源，您可以查看数据透视表摘要背后的原始数据。当您想要确保所汇总的数据准确且完整时，这非常有用。

这是一个简单的示例，展示了如何在 Python 的 Excel 中提取数据透视表的数据源：

from spire.xls import *from spire.xls.common import *# Create a Workbook objectworkbook = Workbook# Load an Excel fileworkbook.LoadFromFile("CreatePivotTable.xlsx")# Get the first worksheetsheet = workbook.Worksheets[0]# Get the first pivot table in the worksheetpivot_table = sheet.PivotTables[0]# Get the cell rangedata_range = pivot_table.Location # Iterate through the rowsfor i in range(len(data_range.Rows)): # Iterate through the columns for j in range(len(data_range.Rows[i].Columns)): # print value of the current cell print(data_range[i + 1, j + 1].Value + " ", end='') print("")workbook.Dispose

您可以使用数据透视表的索引或名称删除数据透视表，也可以从工作表中删除所有数据透视表。

这是一个简单的示例，展示了如何使用 Python 从 Excel 工作表中删除数据透视表：

from spire.xls import *from spire.xls.common import *# Create a Workbook objectworkbook = Workbook# Load an Excel fileworkbook.LoadFromFile("CreatePivotTable.xlsx")# Get the first worksheetsheet = workbook.Worksheets[0]# Remove a specific pivot table by its indexsheet.PivotTables.RemoveAt(0)# Remove a specific pivot table by its name# sheet.PivotTables.Remove("Pivot Table")# Remove all pivot tables from the worksheet# sheet.PivotTables.Clear# Save the resultant workbook to a fileworkbook.SaveToFile("RemovePivotTable.xlsx", ExcelVersion.Version2016)workbook.Dispose