一篇文章教你用python玩转数据透视表PivotTable

目录

建数据透视表

配置行列字段

添加列字段

配置“值字段”

关闭分类汇总

设置表格形式显示

读取数据来透视表内容

读取rowfield的内容

读取value field的内容

筛选透视表

添加行标签筛选

添加筛选标签（Pagefield）

排序透视表

本文主要介绍excel的数据透视表常见的操作。基本上涵盖了基本操作。

下面，先看看我们到处理的表格中的数据：

建数据透视表

先打开一个准备数据的excel文件，然后插入一个新的数据透视表。

import win32com.client as win32
from win32com.client import constants
import os
xls_app = win32.gencache.EnsureDispatch('Excel.Application')
curr_path = os.getcwd()
## 打开excel文件
wb = xls_app.Workbooks.Open(r'%s\数据透视表示例.xlsx'%curr_path)
xls_app.Visible = True # 显示excel界面，默认为隐藏
## 
ws = wb.Worksheets(1)

## 添加透视表的数据源
SrcData = ws.Range("A1:D6")
## 添加一个新的sheet，用于存放生成的透视表
pt_sht = wb.Worksheets.Add()
pt_sht.Name = "透视表"
StartPvt = pt_sht.Range("A1") ## 设置透视表插入位置
## 创建数据透视表
pt_cache = wb.PivotCaches().Create(SourceType=constants.xlDatabase, SourceData=SrcData)
pt = pt_cache.CreatePivotTable(TableDestination=StartPvt,  TableName="PivotTable1")

到此，可以在excel中看到如下效果。

配置行列字段

## 添加行字段

pt.AddFields(RowFields=["部门","年龄"])

添加列字段

pt.AddFields(ColumnFields=["部门","年龄"])

配置“值字段”

## 添加值字段 
pt.AddDataField(Field=pt.PivotFields("姓名"))

关闭分类汇总

遍历每个域，逐个关闭分类汇总

for i in range(1,pt.RowFields.Count + 1):
	field = pt.PivotFields(i)
	subtotal_tuple = (field.Subtotals)
	subtotal_list = list(subtotal_tuple)
	for k in range(len(subtotal_tuple)):
		subtotal_list[k] = False
	field.Subtotals = subtotal_list

设置表格形式显示

# 设置为表格形式显示 
pt.RowAxisLayout (constants.xlTabularRow)

读取数据来透视表内容

读取rowfield的内容

可以直接通过读取sheet内容来读取透视表，还可以通过透视表的对象来读取。这里展示的是通过后者的方式

for pvtField in pt.RowFields:
	print(pvtField.Name)
	for item in pvtField.PivotItems():
		print('    '+item.Name)

示例中pvtField.Name为行field的标题栏，item.Name为行field的成员内容

执行结果为：

部门 
        采购 
        市场 
        行政 
        研发 
年龄 
        21 
        22 
        24 
        30 
        33 
        35 
        44

读取value field的内容

可以使用PivotValueCell直接读取透视表“值”的内容

print(pt.PivotValueCell(1, 1).Value)

这是读取第一个value单元格的内容，执行结果为：

1.0

筛选透视表

添加行标签筛选

pt_filter = pt.PivotFields("年龄").PivotFilters.Add2(Type=constants.xlCaptionIsLessThan,Value1="28")

参数还是写为立即数（即Valued=28），也OK。看起来对这个参数的数据类型比较宽泛。

筛选的效果：

注意：过滤类型种有一个关于Value的筛选类型，这个是试用于“值筛选”的功能。

添加筛选标签（Pagefield）

示例代码：

pt.PivotFields("日期").Orientation = constants.xlPageField

设置筛选值：

pt.PivotFields("日期").CurrentPage = '10/11/2022'

排序透视表

# 排序 
pt.ClearAllFilters()# 关闭所有的筛选 pt.PivotFields("年龄").AutoSort(Order=constants.xlAscending, Field='年龄')# 按照年龄升序排序

排序效果：

排序效果使用的autosort方法的参数如下图：