“This is the fifth day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

preface

Interesting actual combat project, using Python+xlwings module to make weather reports

Let’s have a good time

The development tools

Python version: 3.6.4

Related modules:

Requests module;

Xlwings module;

Json module;

And some modules that come with Python.

Environment set up

Install Python and add it to the environment variables. PIP installs the required related modules.

Since it is a weather forecast, it must need weather data.

Looking for a circle of domestic open weather API interfaces, most of which require registration, F decided to give up.

Tencent is a good, but the interface information is not quite complete, there is no corresponding data.

Finally, a foreign weather API was selected

Not all of the cities in the country, only 10 cities at the moment.

So to the city more, Tencent weather interface or can be considered.

There are 10 kinds of weather conditions in total, and relevant pictures of weather conditions are provided for us to use.

First, obtain the ID value of the city through query

And then according to the ID value, to obtain the corresponding weather information

Relevant names in Both Chinese and English

# Weather
weather = {
    'Snow': 'snow'.'Sleet': 'Sleet'.'Hail': 'hail'.'Thunderstorm': 'Thunder shower'.'Heavy Rain': 'rain'.'Light Rain': 'rain'.'Showers': 'shower'.'Heavy Cloud': "Yin".'Light Cloud': 'cloudy'.'Clear': 'or'
}

# City -- Chinese and English names
citys = {
    'Beijing': 'Beijing'.'chengdu': 'Chengdu'.'dongguan': 'Dongguan'.'guangzhou': 'Guangzhou'.'hangzhou': 'Hangzhou'.'Hong Kong': 'Hong Kong'.'Shanghai': 'Shanghai'.'shenzhen': 'Shenzhen'.'tianjin': 'Tianjin'.'wuhan': 'Wuhan'
}
Copy the code

Create a table

Install the Xlwings library and create the project using the command line.

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

# command line run
xlwings quickstart weatherapp --standalone
Copy the code

This generates two files, Python and Excel

The weatherapp.py file contents

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()
Copy the code

If you open Excel, you will be prompted to enable macros. Select Yes

Then you need to open the Excel development tool and use it to insert some elements later

The above picture shows the Settings for A Mac. Windows is also very easy to set up.

By clicking on the Development tools option, we can use Excle’s Visual Basic Editor (VBA) and also insert buttons (query buttons).

Then I insert a click button into the table

Select the macro name SampleCall and the macro location for the current workbook

Click button 1 and cell A1 will say Hello Xlwings!

Click again and cell A1 changes to Bye Xlwings!

This means that you can modify the weatherapp.py file code to implement Excel interaction.

Let’s do some page design for the table, after all, to make the table look good

Set the table row height, column width, background color, fixed text content and other information.

Set cell C3 name to city_name and insert 6 sun images in cells C9 ~ H9 with center alignment. Rename the images no.1~ No.6.

Modify the weatherapp.py file code

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

# Weather
weather = {
    'Snow': 'snow'.'Sleet': 'Sleet'.'Hail': 'hail'.'Thunderstorm': 'Thunder shower'.'Heavy Rain': 'rain'.'Light Rain': 'rain'.'Showers': 'shower'.'Heavy Cloud': "Yin".'Light Cloud': 'cloudy'.'Clear': 'or'
}

# City -- Chinese and English names
citys = {
    'Beijing': 'Beijing'.'chengdu': 'Chengdu'.'dongguan': 'Dongguan'.'guangzhou': 'Guangzhou'.'hangzhou': 'Hangzhou'.'Hong Kong': 'Hong Kong'.'Shanghai': 'Shanghai'.'shenzhen': 'Shenzhen'.'tianjin': 'Tianjin'.'wuhan': 'Wuhan'
}


def main() :
    Call Python functions from Excel via runPython
    wb = xw.Book.caller()
    sht = wb.sheets[0]

    Read city information from Excel
    city_name = citys[sht.range("city_name").value]

    Get the city 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"]

    # Get city weather information
    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"]

    Create an empty list to store data
    min_temp = []
    max_temp = []
    weather_state_name = []
    weather_state_abbr = []
    applicable_date = []

    # Processing data
    for index, day in enumerate(weather_data):
        # Minimum temperature
        min_temp.append(weather_data[index]["min_temp"])
        # Maximum temperature
        max_temp.append(weather_data[index]["max_temp"])
        # Weather conditions
        weather_state_name.append(weather[weather_data[index]["weather_state_name"]])
        # Weather condition abbreviation
        weather_state_abbr.append(weather_data[index]["weather_state_abbr"])
        # date
        applicable_date.append(weather_data[index]["applicable_date"])

    Fill the obtained values into 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

    Create a list
    icon_names = ["no.1"."no.2"."no.3"."no.4"."no.5"."no.6"]

    # Set the weather picture path
    icon_path = Path(__file__).parent / "images"

    # Match weather conditions with weather images to update the table
    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__":
    Excel files for caller() debugging can be run directly in Python
    xw.Book("weatherapp.xlsm").set_mock_caller()
    main()
Copy the code

This article provides the code and data used, see the introduction of the home page.

Open the Excel spreadsheet, type in one of the 10 cities in the city bar, then click the query button and the weather will be updated