折腾手记

运维！！运维！！

0%

Excel调用PlayWright

发表于 更新于

Excel调用PlayWright

这是前两篇文章的继续，第一篇文章使用Playwright抓取动态网页，第二篇文章PowerBI抓取动态网页。今天是Excel通过Playwright抓取网页。

PlayWright本身支持C#和python，本来想使用C#的VSTO，结果VSTO只支持.net标准版，Playwright只支持.net core。

后来就找到了可以在Excel中调用python的xlwings。

安装xlwings

1
2
conda create -n excel python xlwings pandas
conda create -n excel python=3.9 xlwings pandas

新建一个conda环境，取名excel，安装xlwings和pandas两个包。

xlwings架起了excel和python的桥梁，使得excel通过VBA调用python，python也可以调用操作excel。
pandas处理网页返回的数据，并将数据通过xlwing写入excel。

环境搭建

1
2
3
4
5
conda activate excel # 激活虚拟环境
cd path/to/project # 你要工作的目录
xlwings quickstart pyexcel -s # 这三句见下面的解释
打开pyexcel.xlsm，将sheet“_xlwings.conf”重命名为“xlwings.conf”
xlwings.conf中，填入conda path，虚拟环境选excel

xlwings quickstart pyexcel 新建名为pyexcel工程，-s 是将xlwing的excel插件写进文件里，可以独立运行，不需要xlwing插件，参见https://docs.xlwings.org/zh_CN/latest/addin.html 。

xlwings生成两个文件，pyexcel.xlsm和pyexcel.py。

将pyexcel.xlsm的_xlwings.conf页签更名为xlwings.conf，这是xlwing的配置文件。

xlwings配置文件有三个级别，全局、文件簿目录级、文件簿级，参见https://docs.xlwings.org/zh_CN/latest/addin.html
全局配置文件为.xlwings\xlwings.conf in your home folder, that is usually C:\Users<username>
文件簿目录配置文件为项目所在目录的xlwings.conf
文件簿级的配置文件为xlwings.conf页签
优先级别 文件簿 > 文件簿项目 > 全局

pyexcel.xlsm默认调用pyexcel.py中的main()，代码可以写在这里。

主要代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

def main():
    wb = xw.Book.caller() # 调用python的excel实例
    wb.app.api.StatusBar = "抓取数据...." # wb.app.api对应了excel.Application
    GetPETTM() # 核心函数，抓取动态网页数据，并写进excel的table
    wb.app.api.StatusBar = False

    # excel刷新、透视表刷新
    # CalculateUntilAsyncQueriesDone等待异步刷新完成
    print("Excel refresh all")
    wb.api.RefreshAll()
    wb.app.api.CalculateUntilAsyncQueriesDone()
    print("Excel refresh pivottable")
    wb.sheets["主页"].api.PivotTables("数据透视表1").RefreshTable() #PivotCache.Refresh
    wb.save() # 存盘

wb.app.api和wb.api返回一个标准excel对象，后面调用的属性方法和VBA的相同。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
def response(res):
    global KeyResponse
    if TargetKeyword in res.url:
        KeyResponse = res


def GetPETTM():

    # 标准用法，参见xlwings官方文档。调用的excel或者Mock_caller
    wb = xw.Book.caller()
    pettm = wb.sheets["PETTM"]

    # playwrigth标准用法
    playwright = sync_playwright().start()
    browser = playwright.chromium.launch()
    page = browser.new_page()
    page.on("response", response)  # response将返回结果写入KeyResponse全局变量
    page.goto(URL)

    # 如果找到了Response
    if KeyResponse :

        # 取出其中的data
        p=KeyResponse.json()
        df=pd.DataFrame(p["data"])

        # 处理数据
        # df[date]是unix时间戳，毫秒计算，utc时区
        # 需要转换成datetime类型，时区转换要加上8小时
        df["date2"]=pd.to_datetime((df["date"]+8*3600*1000+24*3600*1000),unit='ms')
        # 转换成str，避免写入excel出现科学计数法
        df["date"] = "'" + df["date"].astype(str)

        # 写入excel，直接生成excel table
        # 这也是标准用法，在xlwings官方文档中有提及
        table_name = "MiddlePETTM"
        if table_name in [table.name for table in pettm.tables]:
            pettm.tables[table_name].update(df, index=False)
        else:
            pettm.tables.add(source=pettm['A1'].expand('table'), name=table_name).update(df, index=False)

    browser.close()
    playwright.stop()

在excel中，运行宏SampleCall()即可调用上面代码，最后生成MiddlePETTM的execl table。