Too strong, use Python+Excel to make weather forecast table!

See the following figure for specific operations. The discussion group on group entry technology is provided at the end of the article. Welcome to join

Enter Hangzhou in the city column and click the query button. The data in the table will change. It is indeed the weather forecast of Hangzhou.

① Data acquisition

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

After looking for a circle of domestic open weather API interfaces, most of them need to be registered and give up decisively.

Tencent has a good, but unfortunately the interface information is not complete and there is no corresponding data description.

Address: https://tianqi.qq.com/

Interface address: https://wis.qq.com/weather/common

Finally, a foreign weather API interface was selected.

Address: https://www.metaweather.com/zh/

It does not provide all cities in China. At present, there are only 10 cities.

So if you want more cities, Tencent's weather interface can still be considered.

There are 10 weather states in total, and relevant weather state pictures are provided for us to use.

The picture has been downloaded. You can get it at the end of the article if you need it!

First, obtain the ID value of the city through query.

Then get the corresponding weather information according to the ID value.

The Chinese and English versions of relevant names are as follows.

# Weather -- comparison of Chinese and English names weather = { 'Snow': 'snow', 'Sleet': 'sleet', 'Hail': 'hail', 'Thunderstorm': 'thunder shower', 'Heavy Rain': 'heavy rain', 'Light Rain': 'light rain', 'Showers': 'shower', 'Heavy Cloud': 'Yin', 'Light Cloud': 'cloudy', 'Clear': 'Sunny' } # City -- comparison of 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' }

② Create table

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

# Install xlwings pip install xlwings -i https://mirror.baidu.com/pypi/simple/ # Command line run xlwings quickstart weatherapp --standalone

This generates two files, Python and Excel.

The contents of weatherapp.py are as follows.

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()

Excel has no content. When you open it, you will be prompted whether to enable macros. Select Yes.

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

The above figure shows the settings of Mac computer. The settings of Windows computer are also very simple. You can Baidu for details.

By clicking the development tool option, we can use Excel's Visual Basic editor (VBA) and insert buttons (query buttons).

Then I insert a click button in the table.

The selected macro name is SampleCall, and the macro location is the current workbook.

Click button 1, and the content Hello xlwings! Appears in cell A1!.

Click again, and the content of cell A1 will change to Bye xlwings!.

That means that the interactive operation of Excel can be realized by modifying the code of weatherapp.py file.

The following page design of the table, after all, to make the table look good.

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

Set the cell C3 name to city_name, insert 6 sun pictures, arrange them in cells C9 ~ H9, align them in the middle, and the picture is also renamed no.1~no.6.

Modify the weatherapp.py file code as follows.

import json from pathlib import Path import requests import xlwings as xw # Weather -- comparison of Chinese and English names weather = { 'Snow': 'snow', 'Sleet': 'sleet', 'Hail': 'hail', 'Thunderstorm': 'thunder shower', 'Heavy Rain': 'heavy rain', 'Light Rain': 'light rain', 'Showers': 'shower', 'Heavy Cloud': 'Yin', 'Light Cloud': 'cloudy', 'Clear': 'Sunny' } # City -- comparison of 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(): # Calling python function from excel through runpython wb = xw.Book.caller() sht = wb.sheets[0] # Read city information from Excel city_name = citys[sht.range("city_name").value] # Get the ID value of the city, i.e. woeid URL_CITY = f"https://www.metaweather.com/api/location/search/?query=" 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 the weather information of the city URL_WEATHER = f"https://www.metaweather.com/api/location//" 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 condition weather_state_name.append(weather[weather_data[index]["weather_state_name"]]) # Weather abbreviation weather_state_abbr.append(weather_data[index]["weather_state_abbr"]) # date applicable_date.append(weather_data[index]["applicable_date"]) # Fill the obtained value 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 list icon_names = ["no.1", "no.2", "no.3", "no.4", "no.5", "no.6"] # Set weather picture path icon_path = Path(__file__).parent / "images" # Match the weather conditions with the weather picture and 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__": # Set the excel file for debugging caller(), which can be run directly in python xw.Book("weatherapp.xlsm").set_mock_caller() main()

At this time, we open the Excel table, enter one of the 10 cities in the city column, and then click the query button to update the weather.

Well, the sharing of this issue is over. Interested partners can practice and learn by themselves.

Welcome to reprint, collect, gain, praise and support!

At present, a technical exchange group has been opened, with more than 2000 group friends. The best way to add notes is: source + Interest direction, which is convenient to find like-minded friends

• Method ① send the following pictures to wechat, long press identification, and the background replies: add group;
• Mode ②. Add micro signal: dkl88191, remarks: from CSDN
• WeChat search official account: Python learning and data mining, background reply: add group

9 October 2021, 21:19 | Views: 2055