使用xlwings插件在Excel中调用Python

xlwings官方文档：

• 英文版：https://docs.xlwings.org/en/stable/#
• 中文版：https://www.kancloud.cn/gnefnuy/xlwings-docs/1127450

安装xlwings

首先我们需要先在Python中安装xlwings:

pip install xlwings

目前测试时，我安装的版本为0.27.6

安装后，由于xlwings修改了pywin32的版本导致jupyter出现故障，于是将pywin32降低到xlwings最低要求版本：

pip install pywin32==224

此时jupyter虽然已经可以正常使用，但是每次创建新的python进程都报出类似这样的错误：

本人报错的路径为 D:\Miniconda3\Library\bin\pythoncom37.dll ，在手工删除该文件后，python终于恢复正常。

当然这是本人在使用xlwings时遇到的问题，其他人遇到的问题可能不一样，解决方案也不同。

启动Excel宏

设置功能区显示开发工具：

然后启动宏：

安装xlwings插件

可以尝试用命令安装：

>xlwings addin install
xlwings version: 0.27.6
Successfully installed the xlwings add-in! Please restart Excel.

可以看到这边已经安装成功，在C:\Users\ASUS\AppData\Roaming\Microsoft\Excel\XLSTART目录下增加了一个xlwings.xlam文件。

假如手工安装失败，我们可以自行下载宏加载项，在https://github.com/xlwings/xlwings/releases查找一个可以直接下载xlwings.xlam文件的版本（与xlwings版本一致最佳）：

然后将该文件移动到C:\Users\ASUS\AppData\Roaming\Microsoft\Excel\XLSTART目录下：

ASUS为你自己的windows用户名。

Python on Excel Demo

有了以上的准备操作，我们就可以开始开发了，首先从一个基础demo开始。首先我们打开Excel：

可以看到xlwings插件已经顺利被加载。

相关参数保存在C:\Users\ASUS\.xlwings\xlwings.conf文件中。

按下Alt+F11快捷键打开VBE，然后编写如下代码：

Sub test()
    RunPython "import hello;hello.speak()"
End Sub

技巧：

添加xlwings引用：

在英文输入状态下按下ctrl+空格，可以进行代码提示，看到RunPython函数。

然后添加按钮并指定宏：

此时将当前Excel工作簿保存为xlsm格式，例如hello.xlsm。

此时在hello.xlsm同目录下保存文件hello.py，内容如下：

import xlwings as xw

def speak():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = 'Hello World!'

然后测试一下，点击按钮后A1单元格成功出现Hello World!字样：

注意：需要ADD_WORKBOOK_TO_PYTHONPATH参数设置为True，才能保证Excel中能顺利找到同一目录下的python文件。

制作天气报表

下面我们玩玩小F分享的案例：《用Python+Excel制作天气预报表！》

本人经测试后，编码如下：

import pandas as pd
from pathlib import Path
import requests
import xlwings as xw

# 天气--中英文名对照
weather = pd.Series({
    'Snow': '雪',
    'Sleet': '雨夹雪',
    'Hail': '冰雹',
    'Thunderstorm': '雷阵雨',
    'Heavy Rain': '大雨',
    'Light Rain': '小雨',
    'Showers': '阵雨',
    'Heavy Cloud': '阴',
    'Light Cloud': '多云',
    'Clear': '晴'
})

# 城市--中英文名对照
citys = {
    '北京': 'Beijing',
    '成都': 'Chengdu',
    '东莞': 'Dongguan',
    '广州': 'Guangzhou',
    '杭州': 'Hangzhou',
    '香港': 'Hong Kong',
    '上海': 'Shanghai',
    '深圳': 'Shenzhen',
    '天津': 'Tianjin',
    '武汉': 'Wuhan'
}


def main():
    # 通过runpython从excel中调用python函数
    wb = xw.Book.caller()
    sht = wb.sheets[0]
    # 从Excel中读取城市信息
    city_name = citys[sht.range("city_name").value]

    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36'
    }
    # 获取城市的ID值, 即woeid
    URL_CITY = f"https://www.metaweather.com/api/location/search/?query={city_name}"
    response_city = requests.get(URL_CITY, headers=headers).json()
    city_id = response_city[0]["woeid"]
    # 获取城市的天气信息
    URL_WEATHER = f"https://www.metaweather.com/api/location/{city_id}/"
    response_weather = requests.get(URL_WEATHER, headers=headers).json()
    df = pd.DataFrame(response_weather["consolidated_weather"])
    df = df[["applicable_date", "weather_state_name",
             "max_temp", "min_temp", "weather_state_abbr"]].copy()
    df.weather_state_name = weather[df.weather_state_name].values
    df.max_temp = df.max_temp.round(1)
    df.min_temp = df.min_temp.round(1)

    # 将获取到的值填充到Excel中
    sht.range("C5").options(transpose=True).value = df.values[:, :-1]
    sht.range("D3").value = city_name

    # 创建列表
    icon_names = ["no.1", "no.2", "no.3", "no.4", "no.5", "no.6"]
    # 设置天气图片路径
    icon_path = Path(__file__).parent / "images"
    
    # 将天气情况与天气图片进行匹配，更新表格
    for icon, abbr in zip(icon_names, df.weather_state_abbr):
        image_path = icon_path / f"{abbr}.png"
        sht.pictures.add(image_path, name=icon, update=True)

使用pandas可以使得原本的代码大幅度简化。

Excel中的vba代码为：

Sub weather()
    RunPython "import weatherapp;weatherapp.main()"
End Sub

运行效果：

可以使用如下命令生成官方示例：

xlwings quickstart demo

关于options的更多用法可以参考：

https://docs.xlwings.org/en/stable/converters.html

本节相关数据和代码：https://gitcode.net/as604049322/vba