export to excel

Yahoo_WeatherAPI_crawler.py

# 請先新建一個Excel,命名為:Temperature.xlsx, 並增加sheets以配合38行 並命名為 "桃園","內壢","中壢","高雄","台東"
# 36行請改成自己Excel的路徑
import requests
import urllib.parse
import time
import openpyxl
import os
def weather_yahooAPI(Fcity,workbook):
    res=requests.get("https://query.yahooapis.com/v1/public/yql?q=SELECT%20woeid%20FROM%20geo.places%20WHERE%20text%20IN(%22"+urllib.parse.quote(Fcity)+"%22)%20AND%20country%20%3D%20%22Taiwan%22&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys")
    data=res.json()
    fdata=data["query"]["results"]["place"]
    
    if len(fdata)>=2:
        ffdata=data["query"]["results"]["place"][0]["woeid"]
    else:
        ffdata=data["query"]["results"]["place"]["woeid"]

    res2=requests.get("https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20weather.forecast%20where%20woeid%20%3D%20"+ffdata+"%20and%20u%3D%22c%22&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys")
    data=res2.json()
    fdata1=data["query"]["results"]["channel"]["item"]["forecast"]
    ndata=data["query"]["results"]["channel"]["item"]["condition"]["temp"]
    today=fdata1[0]
    
    str_="現在溫度 : "+ndata+" 最低溫 : "+today['low']+"  最高溫 : "+today['high']+"  天氣狀況 : "+today['text']
    print(str_)  
    
    sheet = workbook[Fcity]
    numOfRows = len(sheet['A']) 
    sheet.cell(row=numOfRows+1, column=1).value = time.strftime("%Y/%m/%d")
    sheet.cell(row=numOfRows+1, column=2).value = time.strftime("%H:%M:%S")
    sheet.cell(row=numOfRows+1, column=3).value = ndata # 現在溫度
    sheet.cell(row=numOfRows+1, column=4).value = today['low']
    sheet.cell(row=numOfRows+1, column=5).value = today['high']
    sheet.cell(row=numOfRows+1, column=6).value = today['text']

os.chdir(r"D:\YZU\大三下\MIS-2\Excel")#請自行更改成自己Excel的路徑
workbook = openpyxl.load_workbook('Temperature.xlsx')
city = ["桃園","內壢","中壢","高雄","台東"]
for i in city:
    weather_yahooAPI(i,workbook)
workbook.save('Temperature.xlsx')