# 天氣--中英文名對照
weather = {
    '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'
}

# 安裝xlwings
pip install xlwings -i https://mirror.baidu.com/pypi/simple/

# 命令行運行
xlwings quickstart weatherapp --standalone

import xlwings as xw


def main():
    wb = xw.Book.caller()
    sheet = wb.sheets[0]
    if sheet["A1"].value == "Hello xlwings!":
        sheet["A1"].value = "Bye xlwings!"
    else:
        sheet["A1"].value = "Hello xlwings!"


if __name__ == "__main__":
    xw.Book("weatherapp.xlsm").set_mock_caller()
    main()

import json
from pathlib import Path
import requests
import xlwings as xw

# 天氣--中英文名對照
weather = {
    '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中調(diào)用python函數(shù)
    wb = xw.Book.caller()
    sht = wb.sheets[0]

    # 從Excel中讀取城市信息
    city_name = citys[sht.range("city_name").value]

    # 獲取城市的ID值, 即woeid
    URL_CITY = f"https://www.metaweather.com/api/location/search/?query={city_name}"
    response_city = requests.request("GET", URL_CITY)
    city_title = json.loads(response_city.text)[0]["title"]
    city_id = json.loads(response_city.text)[0]["woeid"]

    # 獲取城市的天氣信息
    URL_WEATHER = f"https://www.metaweather.com/api/location/{city_id}/"
    response_weather = requests.request("GET", URL_WEATHER)
    weather_data = json.loads(response_weather.text)["consolidated_weather"]

    # 創(chuàng)建空列表, 存儲數(shù)據(jù)
    min_temp = []
    max_temp = []
    weather_state_name = []
    weather_state_abbr = []
    applicable_date = []

    # 處理數(shù)據(jù)
    for index, day in enumerate(weather_data):
        # 最低溫度
        min_temp.append(weather_data[index]["min_temp"])
        # 最高溫度
        max_temp.append(weather_data[index]["max_temp"])
        # 天氣情況
        weather_state_name.append(weather[weather_data[index]["weather_state_name"]])
        # 天氣情況縮寫
        weather_state_abbr.append(weather_data[index]["weather_state_abbr"])
        # 日期
        applicable_date.append(weather_data[index]["applicable_date"])

    # 將獲取到的值填充到Excel中
    sht.range("C5").value = applicable_date
    sht.range("C6").value = weather_state_name
    sht.range("C7").value = max_temp
    sht.range("C8").value = min_temp
    sht.range("D3").value = city_title

    # 創(chuàng)建列表
    icon_names = ["no.1", "no.2", "no.3", "no.4", "no.5", "no.6"]

    # 設(shè)置天氣圖片路徑
    icon_path = Path(__file__).parent / "images"

    # 將天氣情況與天氣圖片進行匹配，更新表格
    for icon, abbr in zip(icon_names, weather_state_abbr):
        image_path = Path(icon_path, abbr + ".png")
        sht.pictures.add(image_path, name=icon, update=True)


if __name__ == "__main__":
    # 設(shè)置用于調(diào)試caller()的excel文件，可以直接在python里運行
    xw.Book("weatherapp.xlsm").set_mock_caller()
    main()