Shulou(Shulou.com)06/01 Report--

This article mainly analyzes Python+xlwings how to make weather forecast table related knowledge points, detailed and easy to understand, reasonable operation details, with certain reference value. If you are interested, you may wish to follow Xiao Bian to have a look. Let's follow Xiao Bian to learn more about "Python+xlwings how to make weather forecast tables".

preface

Interesting combat project, using Python+xlwings module to make weather forecast table

Let's get started happily ~

development tools

Python version: 3.6.4

Related modules:

requests module;

xlwings module;

json module;

And some Python modules.

environment construction

Install Python and add it to the environment variables, pip install the relevant modules you need.

Since it was a weather forecast, it definitely required weather data.

Looking for a circle of domestic open weather API interfaces, most of them need to register, small F decisively give up.

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

Finally, a foreign weather API interface was selected

Not all cities in the country are available, only 10 cities are currently available.

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

There are 10 weather conditions and pictures of the weather conditions are available for us to use.

implementation steps

First, get the ID value of the city by querying.

Then, according to the ID value, the corresponding weather information is obtained.

Related names in Chinese and English

#Weather--Chinese and English name contrast weather = { 'Snow':' Snow', 'Sleet':' sleet', 'Hail':' Hail', 'Thunderstorm':' thunderstorm', 'Heavy Rain':' Heavy Rain', 'Light Rain':' Light Rain', 'Showers':' Showers', 'Heavy Cloud':' Yin', 'Light Cloud':' Cloudy ', 'Clear':' Sunny'}#City--Chinese-English name contrast citys = { 'Beijing':' Beijing', Chengdu: Chengdu, 'Dongguan':' Dongguan', 'Guangzhou':' Guangzhou', Hangzhou: Hangzhou, 'Hong Kong':'Hong Kong', 'Shanghai':' Shanghai', 'Shenzhen':' Shenzhen', 'Tianjin':' Tianjin', 'Wuhan':' Wuhan'}

create a table

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

#install xlwingspip install xlwings -i https://mirror.baidu.com/pypi/simple/# command line run xlwings quickstart weatherapp --standalone

This generates two files, Python and Excel.

where weatherapp.py's file content

import xlwings as xwdef 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 is nothing, open will prompt whether to enable macros, select Yes

Then you need to open the Excel development tool, which will be used later to insert some elements

The above picture shows the settings of Mac computer. Windows computer is also very simple to set up. The specific settings can be Baidu.

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

And then I insert a click button in the form.

Select macro name SampleCall, macro location is current workbook

Click on button 1 and cell A1 will show Hello xlwings!

Click again and the contents of cell A1 change to Bye xlwings!

This means that by modifying the code in the weatherapp.py file, Excel interoperability can be achieved.

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

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

Set the name of cell C3 to city_name, insert 6 sun pictures, arrange them in cells C9~H9, align them in the center, and rename the pictures no.1~no.6.

Modify the weatherapp.py file code

import jsonfrom pathlib import Pathimport requestsimport xlwings as xw#Weather--Chinese English Name Contrast weather = { 'Snow':' Snow', 'Sleet':' sleet', 'Hail':' Hail', 'Thunderstorm':' thunderstorm', 'Heavy Rain':' Heavy Rain', 'Light Rain':' Light Rain', 'Showers':' Showers', 'Heavy Cloud':' Yin', 'Light Cloud':' Cloudy ', 'Clear':' Sunny'}#City--Chinese-English name contrast 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 ID value of city, i.e. 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 weather information for the city 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 empty list, 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 conditions Abbreviations weather_state_abbr.append(weather_data[index]["weather_state_abbr"]) #Date applicable_date.append(weather_data[index]["applicable_date"]) #Fill in Excel with the acquired values 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 Weather Picture Path icon_path = Path(__file__).parent / "images" #Match weather conditions to weather pictures, update tables 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 excel file for debugging caller(), which can be run directly in python xw.Book("weatherapp.xlsm").set_mock_caller() main()

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

Open an Excel spreadsheet, enter one of the 10 cities in the City field, and click the Query button. The weather will update.

1. Python is simple and easy to use. Compared with C/C++, Java, C#and other traditional languages, Python has less strict requirements for code format;2. Python is open source, everyone can see the source code, and it can be ported to many platforms;3. Python is object-oriented and can support process-oriented programming and object-oriented programming; Python is an interpreted language, Python programs do not need to be compiled into binary code, you can run programs directly from the source code;5, Python powerful, has a large number of modules, basically able to achieve all common functions.

About "Python+xlwings how to make weather forecast table" introduced to this, more related content can be searched for previous articles, hope to help you answer questions, please support the website more!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.