ssis 读取excel 卡住_使用win32com更新Excel数据透视表并排序

每周都要做报表，感觉手动更新数据再出图比较费时，想着报表制作脚本化，大体思路就是按照VBA录制宏代码再用win32com的API进行改造，但是遇到操作数据透视表更新数据的时候卡住了，百度谷歌都没有搜到合适的Demo参考，然后自己调试出来了，在这里分享一下。

说明：这篇文章是针对Python操作更新已有数据透视表的数据源，如果是针对Excel的简单操作推荐使用Openpyxl或者Xlwings（其中Xlwings可以直接把Pandas的Dataframe写入到指定的Excel Sheet页，代码如下；Pandas自带的df.to_excel默认会删除其它Sheet页，优化方法可以参考评论区网友分享）。

# df = pd.read_sql(sql, con=mysql_pd)  # 前面的数据处理省略了
# file_name = "07-23,1111@wk29报表.xlsx"
import xlwings as xw
xw_app = xw.App(visible=True, add_book=False)
xw_book = xw_app.books.open(file_name)
xw_sht = xw_book.sheets['查询1']
xw_sht.clear()  # 清空Sheet页“查询1”的旧数据
xw_sht.range('a1').value = df  # 把Pandas整理后的DataFrame写入Sheet页"查询1"，其它工作表不会删除
xw_book.save()  # 保存
xw_book.close()  # 关闭工作簿
xw_app.quit()  # 退出

如下是VBA录制宏产生的代码，涉及更改数据源和降序操作。

# 这里的R1C1:R267920C14，R代表行数，C代表列数
Sub 宏1()
' 宏1 宏
    ActiveSheet.PivotTables("数据透视表6").ChangePivotCache ActiveWorkbook.PivotCaches. _
        Create(SourceType:=xlDatabase, SourceData:= _
        "D:UsersDownloadsPython-Win32com-Excel[07-23,1111@wk29报表.xlsx]查询1!R1C1:R267920C14" _
        , Version:=6)
    Range("F8").Select
    ActiveSheet.PivotTables("数据透视表6").PivotFields("二级分类").AutoSort xlDescending, _
        "计数项:客服评估", ActiveSheet.PivotTables("数据透视表6").PivotColumnAxis.PivotLines(5), 1
End Sub

如下是通过win32com实现的完整代码和注释，涉及VBA代码改造的部分在第4段。

# 准备
win32c = win32.constants  # 操作透视表需要用到
file_name = "07-23,1111@wk29报表.xlsx"
win_app = win32.Dispatch('Excel.Application')
win_app.Visible = True  # 界面可见
win_app.DisplayAlerts = 0   # 不显示在最前端
win_book = win_app.Workbooks.Open(r"D:UsersDownloadsPython-Win32com-Excel" + "" + file_name)

# 查看数据透视表的数据源所在Sheet页的行数与列数，用于更新透视表时拼接单元格范围的字段
# 此Sheet页的数据就是通过Pandas读取数据库再通过Xlwings写入的
win_sht_query = win_book.Worksheets('查询1')  # 切换Sheet页
query_rows = win_sht_query.UsedRange.Rows.Count
print("query_rows:", query_rows)
query_cols = win_sht_query.UsedRange.Columns.Count
print("query_cols:", query_cols)

# 在刷新目标Sheet页的指定透视表之前，需要先配置几个参数
win_sht = win_book.Worksheets('①指定产品来访量')  # 指定目标Sheet页
# 配置SourceArea，路径要完整（这里的R1C1代表数据单元格区域从第1列的第1行也就是A1开始），小心别遗漏这里的中括号
SourceArea = r"D:UsersDownloadsPython-Win32com-Excel[" + file_name + "]查询1!R1C1:R" + str(query_rows) + "C" + str(query_cols) 
print("SourceArea:", SourceArea)
# 当数据量较大时，发现此处会出现报错，原因查了一下，设置休眠时间有改善。
time.sleep(10)

# 最重要的步骤（VBA代码的改造）
PivotCache_1 = win_book.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=SourceArea, Version=6)  # 指定数据源
win_sht.PivotTables("数据透视表6").ChangePivotCache(PivotCache_1)  # 执行更新数据源
# win_sht.PivotTables("数据透视表6").PivotCache().Refresh()  # 刷新透视表
# 执行排序：选定单元格，然后降序，降序参数是xlDescending
# 其中的“PivotLines(5)”代表计数区域第5列降序，因为这里选中的是F8，也就是F列
# win_sht.Range("F8").Select()  # 这里无需选中单元格
win_sht.PivotTables("数据透视表6").PivotFields("二级分类").AutoSort(win32c.xlDescending, "计数项:客服评估", win_sht.PivotTables(
    "数据透视表6").PivotColumnAxis.PivotLines(5), 1)  # 降序

# 关闭
win_book.Close(True)  # 关闭该文件，并保存。不保存就是False
win_app.Application.Quit()  # 关闭excel操作环境

如下是关于Python操作Excel效率的思考。

报表里面的数据透视图很多，通过查询数据库写入Excel文件再更新数据透视表的步骤很繁琐，最合适的方法应该是通过Pandas处理数据然后把最终的结论数据写入数据透视图所引用的单元格（透视图会自动更新）。
如果涉及到把Excel图片保存到本地，可以参考这个页面：如何用Python win32com处理Excel数据？