目录
使用工具
Python 在 Excel 中创建数据透视表
Python 更改 Excel 数据透视表的数据源
Python 在 Excel 中为数据透视表字段设置数字格式
Python 在 Excel 中对数据透视表中的数据进行排序
Python 提取 Excel 中数据透视表的数据源
Python 从 Excel 中删除数据透视表
总结
Excel 数据透视表是一种强大的数据分析工具，用于对大规模数据进行汇总、分析和展示。通过数据透视表，用户可以轻松地对数据进行排序、过滤、分组、汇总和计算，帮助快速发现数据中的趋势和变化。在本文中，我们将探讨如何使用 Python 在 Excel 中创建、更新和删除数据透视表。
- Python 在 Excel 中创建数据透视表
- Python 更改 Excel 数据透视表的数据源
- Python 在 Excel 中为数据透视表字段设置数字格式
- Python 在 Excel 中对数据透视表中的数据进行排序
- Python 提取 Excel 中数据透视表的数据源
- Python 从 Excel 中删除数据透视表
使用工具
要使用Python在 Excel 中创建、更新和删除数据透视表，可以使用 Spire.XLS for Python 库。它支持在 Python 应用程序中创建、读取、编辑和转换 Excel 文件。使用该库，你可以在Excel中生成数据透视表，也可以编辑或删除现有数据透视表。
你可以通过在终端中运行以下命令从 PyPI 安装 Spire.XLS for Python：
pip install Spire.XlsPython 在 Excel 中创建数据透视表
数据透视表并不直接使用工作表中的数据，而是从存储数据快照的数据透视缓存（Pivot Cache）中获取数据。数据透视表可以包括行字段、列字段和值字段等。
创建数据透视表的核心步骤如下：
- 添加数据透视缓存：使用Workbook.PivotCaches.Add()方法，基于工作表中的指定数据生成数据透视缓存。
- 创建数据透视表：使用Worksheet.PivotTables.Add()方法，基于数据透视缓存中的数据生成数据透视表。
- 添加字段到数据透视表，如行字段、列字段和值字段。
以下是使用 Python 在 Excel 中创建数据透视表的实现代码：
from spire.xls import *
from spire.xls.common import *# 创建 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("示例.xlsx")# 获取第一个工作表
sheet = workbook.Worksheets[0]# 定义数据源范围
data_range = sheet.Range["A1:C9"]
# 根据给定的数据源范围添加数据透视缓存
pivot_cache = workbook.PivotCaches.Add(data_range)# 根据数据透视缓存中的数据创建数据透视表，并将其添加到工作表的指定位置
pivot_table = sheet.PivotTables.Add("数据透视表", sheet.Range["E2:H8"], pivot_cache)# 向数据透视表添加行字段
row = pivot_table.PivotFields["区域"]
row.Axis = AxisTypes.Row
pivot_table.Options.RowHeaderCaption = "区域"
# 向数据透视表添加列字段
column = pivot_table.PivotFields["产品"]
column.Axis = AxisTypes.Column
pivot_table.Options.ColumnHeaderCaption = "产品"
# 向数据透视表添加值字段
pivot_table.DataFields.Add(pivot_table.PivotFields["销售额"], "求和项：销售额", SubtotalTypes.Sum)# 设置数据透视表样式
pivot_table.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium14 # 设置列宽
sheet.Range["E2:H8"].ColumnWidth = 12.0# 将结果工作簿保存为文件
workbook.SaveToFile("创建数据透视表.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Python 更改 Excel 数据透视表的数据源
在创建数据透视表后，你可能需要更新它的数据源，以展示更多的数据或清除不必要的数据。
要更改数据透视表的数据源，你可以使用PivotTable.ChangeDataSource() 方法，更改后，还需要使用PivotTable.Cache.IsRefreshOnLoad属性刷新数据透视表。以下是使用 Python 更改 Excel 中数据透视表的数据源的实现代码：
from spire.xls import *
from spire.xls.common import *# 创建 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("创建数据透视表.xlsx")# 获取第一个工作表
sheet = workbook.Worksheets[0]
# 获取工作表中的第一个数据透视表
pivot_table = sheet.PivotTables[0]# 定义数据透视表的新数据源范围
data_range = sheet.Range["B1:C9"]# 更改数据透视表的数据源范围
pivot_table.ChangeDataSource(data_range)
# 刷新数据透视表
pivot_table.Cache.IsRefreshOnLoad = True# 将结果工作簿保存为文件
workbook.SaveToFile("更新数据透视表的数据源.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Python 在 Excel 中为数据透视表字段设置数字格式
你可以给数据透视表中的数据设置各种类型的数字格式，如数字、货币、会计、日期、百分比、科学、分数和文本等。
要给Excel数据透视表特定字段的数据设置数字格式，你可以使用PivotTable.PivotFields[fieldName].NumberFormat属性。以下是使用 Python为Excel数据透视表特定字段的数据设置数字格式的实现代码：
from spire.xls import *
from spire.xls.common import *# 创建 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("创建数据透视表.xlsx")# 获取第一个工作表
sheet = workbook.Worksheets[0]
# 获取工作表中的第一个数据透视表
pivot_table = sheet.PivotTables[0]# 为字段 "销售额" 设置数字格式
pivot_table.PivotFields["销售额"].NumberFormat = "#,##0.00"# 将结果工作簿保存为文件
workbook.SaveToFile("设置数字格式.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Python 在 Excel 中对数据透视表中的数据进行排序
排序可以帮助用户更快地找到需要的信息。例如，用户可以将不同区域的销售额进行排序，以识别表现最佳或最差的区域。
要对Excel中数据透视表特定字段的数据进行排序，你可以使用PivotTable.PivotFields[fieldName].SortType属性。以下是使用 Python对Excel数据透视表特定字段的数据进行排序的实现代码：
from spire.xls import *
from spire.xls.common import *# 创建 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("创建数据透视表.xlsx")# 获取第一个工作表
sheet = workbook.Worksheets[0]
# 获取工作表中的第一个数据透视表
pivot_table = sheet.PivotTables[0]# 将字段 "区域" 的数据按降序排序
pivot_table.PivotFields["区域"].SortType = PivotFieldSortType.Descending# 将结果工作簿保存为文件
workbook.SaveToFile("排序.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Python 提取 Excel 中数据透视表的数据源
数据透视表是基于数据源生成的。通过提取数据源，用户可以检查数据是否完整、无误，以及是否包含所有需要分析的信息。这样可以确保数据透视表中的汇总结果是准确的。
以下是使用 Python提取 Excel 中数据透视表的数据源的实现代码：
from spire.xls import *
from spire.xls.common import *# 创建 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("创建数据透视表.xlsx")# 获取第一个工作表
sheet = workbook.Worksheets[0]
# 获取工作表中的第一个数据透视表
pivot_table = sheet.PivotTables[0]# 获取单元格范围
data_range = pivot_table.Location # 遍历行
for i in range(len(data_range.Rows)):# 遍历列for j in range(len(data_range.Rows[i].Columns)):# 打印当前单元格的值print(data_range[i + 1, j + 1].Value + " ", end='')print("")workbook.Dispose()
Python 从 Excel 中删除数据透视表
如果你不再需要某个数据透视表，可以通过它的索引或名称将它从工作表中删除。此外，你还可以删除工作表中的所有数据透视表。
以下是使用 Python 从 Excel 工作表中删除数据透视表的实现代码：
from spire.xls import *
from spire.xls.common import *# 创建 Workbook 对象
workbook = Workbook()
# 加载 Excel 文件
workbook.LoadFromFile("创建数据透视表.xlsx")# 获取第一个工作表
sheet = workbook.Worksheets[0]# 通过索引删除特定的数据透视表
sheet.PivotTables.RemoveAt(0)# 通过名称删除特定的数据透视表
# sheet.PivotTables.Remove("数据透视表")# 从工作表中删除所有数据透视表
# sheet.PivotTables.Clear()# 将结果工作簿保存为文件
workbook.SaveToFile("删除数据透视表.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
总结
这篇文章详细讲解了如何使用Python在Excel中创建、更新和删除数据透视表。由于篇幅有限，本文仅涵盖了一部分数据透视表的功能。除了文中提到的操作外，你还可以对数据透视表进行筛选、分组等其他操作。如有需要可以自行尝试，具体细节在此不再逐一介绍。