操作Excel的Xlwings教程（九）-透视表的刷新

11 个月前 · 来自专栏 Python操作Excel

那个百分十先生

微信公众号：Python知识学堂

最近在公众号后台，有一些小伙伴询问怎么在VBA中怎么调用Python脚本的方法，大家可以参考我知乎的文章：

由于微信公众号的历史文章不能修改，大家就直接看知乎中的文章吧。

本次文章我们来看一下怎么使用Python来进行透视表的刷新，透视表的刷新也是最近一个读者提出来的需求，在我们进行操作之前，大家提前看看一下这两篇文章：

（1）

（2）

一、创建透视表

我们先准备一些简单的数据，并创建一个透视表，数据如下：

其中文件名称为：数据透视表.xlsx，数据所在的sheet名称为：数据透视表

接着我们基于此表的数据创建一个透视表，创建步骤如下：

【选择数据】->点击【插入】->【数据透视表】->【表格和区域】

然后我们在“数据透视表”对话框中，填写相应的信息，如数据透视表的位置等等。然后点击【确定】，我们得到下图界面：

接着我们选择透视表的字段，这里选择的结果如下：

选择之后，我们得到的透视表的结果如下：

二、透视表刷新-手动刷新

在源数据发生改变的时候，我们透视表是要进行“刷新”的以保持两边的数据保持一致。

假设我们修改第1行的数据的数量为100，然后选中透视表进行刷新：

刷新后的数据如下所示：

以上的刷新透视表的操作是基于手动刷新的，接下来我们借用Python来进行刷新。

三、透视表刷新-xlwings

我们先录制一个刷新透视表的宏，步骤如下：

Step_1 : 点击Excel的“开发工具”，点击“录制宏”，点击“确定”。

Step_2 : 在鼠标置于透视表上，点击刷新。

Step_3 : 停止录制，查看宏

我们得到一下的VBA代码：

将代码复制下来：

Sub refresh()
' refresh 宏
    Range("G3").Select
    ActiveSheet.PivotTables("数据透视表1").PivotCache.refresh
End Sub

主要的代码就是其中的两句，表示进行透视表的选择和刷新，详细的说明我们在后文进行说明。接下来我们借助xlwings使用Python来进行刷新，代码如下：

import os
import psutil
import xlwings as xw
def kill_excel_by_pid():
    # 先清理一下可能存在的Excel进程
    pids = psutil.pids()
    for pid in pids:
        try:
            p = psutil.Process(pid)
            # print('pid=%s,pname=%s' % (pid, p.name()))
            # 关闭excel进程
            if p.name() == 'EXCEL.EXE':
                cmd = 'taskkill /F /IM EXCEL.EXE'
                os.system(cmd)
        except Exception as e:
            print(e)
def refresh_ptable():
    App = xw.App(visible=False, add_book=False)
    wb = App.books.open('数据透视表.xlsx')
    sheet = wb.sheets('数据透视表')
    print('sheet:',sheet)
    # 进行透视表的刷新
    res = sheet.api.PivotTables("数据透视表1").PivotCache().Refresh()
# res = sheet.api.PivotTables(1).PivotCache().Refresh()
    print(res)
    #保存刷新的结果
    wb.save()
    wb.close()
    App.quit()
kill_excel_by_pid()
refresh_ptable()
kill_excel_by_pid()

上述refresh_ptable()函数是刷新透视表的代码，kill_excel_by_pid()为EXCEl进程结束的代码。

在VBA中刷新透视表的代码为：

ActiveSheet.PivotTables("数据透视表1").PivotCache.refresh

即，在激活的sheet页签中选择名称为"数据透视表1"的透视表，基于透视表缓存PivotCache进行刷新即可。

对应到xlwings中的代码为：

res = sheet.api.PivotTables("数据透视表1").PivotCache().Refresh()

还是很清晰的，当然我们将代码更改为下述代码也是可以的。

res = sheet.api.PivotTables(1).PivotCache().Refresh()

四、多个透视表的刷新

如果我们先乣刷新一个表格中的多个透视表，那么我们该怎么进行刷新的操作呢？

我们可以这样来更改一下我们的代码即可：

def refresh_multi_pivottable():
    App = xw.App(visible=False, add_book=False)
    wb = App.books.open('数据透视表_多个透视表.xlsx')
    sheet = wb.sheets('数据透视表')
 print('sheet:', sheet)
 # 获取透视表的个数
 num = sheet.api.PivotTables().Count
 # 循环进行透视表的刷新
 for i in range(1, num+1):
        sheet.api.PivotTables(i).PivotCache().Refresh()