10 - Python调用Excel创建数据透视表 win32com

1. 建数据透视表

import win32com.client as win32
from win32com.client import constants

xls_app = win32.gencache.EnsureDispatch('Excel.Application')
# 打开excel文件
wb = xls_app.Workbooks.Open(file_path) 
# 显示excel界面，默认为隐藏
xls_app.Visible = True 
# 建数据透视表使用的源数据
ws = wb.Worksheets(1)
# 添加透视表的数据源
source_data = ws.Range("A1:D20") # 这里的A1:D20是ws中实际的数据部分
# 添加一个新的sheet，用于存放生成的透视表
pt_sht = wb.Worksheets.Add()
pt_sht.Name = "透视表"
# 设置透视表插入位置，从A1开始
start_pvt = pt_sht.Range("A1")
# 创建数据透视表
pt_cache = wb.PivotCaches().Create(SourceType=constants.xlDatabase, SourceData=source_data)
pt = pt_cache.CreatePivotTable(TableDestination=start_pvt,  TableName="PivotTable1")

2. 添加行标签

pt.AddFields(RowFields=["Id", "Name"])

2.1 设置行标签的显示方式

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

2.2 关闭分类汇总

# source_data_column_count - source data的字段的个数
for i in range(1, source_data_column_count):
	field = pt.PivotFields(i)
	# 全部设置成False --> Excel中的None
	field.Subtotals = [False] * len(list(field.Subtotals))

3. 添加列标签

field = pt.PivotFields('Date')
field.Orientation = constants.xlColumnField

3.1 如果是时间格式标签，可以按照不同的周期分组

参数的七个位置分别代表了不同的时间分组，分别是：秒、分、时、日、月、季度、年

# E4是列标签数据在透视表中起始的单元格位置
pt_sht.Range('E4').Group(Start=True, End=True, Periods=[False, False, False, False, True, True, True]) # 按照年、季度、月分组
pt.PivotFields(date_column).Orientation = constants.xlColumnField

3.2 关闭列标签中的折叠按钮 +/-

for field in pt.PivotFields():
	# 先确定是列标签
	if field.Orientation == constants.xlColumnField:
		# 循环遍历每个列标签中的每一个Item对其进行关闭
    	for item in range(1, field.PivotItems().Count + 1):
    		field.PivotItems(item).ShowDetail = False

4. 配置值对应的字段

# Qty作为值，设置格式为数值的千分位
pt.AddDataField(Field=pt.PivotFields("Qty")).NumberFormat='#,##0'

5. 添加筛选标签

pt.PivotFields("Date").Orientation = constants.xlPageField

5.1 单选

# 单选，只选择指定的唯一值
pt.PivotFields("Date").CurrentPage = target_value

5.2 多选

# 开启多选，默认是全选
pt.PivotFields("Date").EnableMultiplePageItems = True
# 去除掉某些值:visible = False, ignore_value_list是存储需要去掉的值的列表
for item in ignore_value_list:
	# 这里的item必须包含在列标签对应的数据中
	pt.PivotFields("Date").PivotItems(item).Visible = False

6. Excel右侧的配置栏的操作

# Hide Field List
pt_sht.Parent.ShowPivotTableFieldList = False
# Show Field List
pt_sht.Parent.ShowPivotTableFieldList = True

参考

• https://zhuanlan.zhihu.com/p/534040978