python批量读取Excel中地名提取其经纬度

注意：请提前申请好百度地图密钥

import requests
from bs4 import BeautifulSoup
import openpyxl

# 读取Excel文件
workbook=openpyxl.load_workbook('地名表.xlsx')
sheet_name="Sheet1"
ws=workbook[sheet_name]
#ws=workbook.active
latitudeCol = 4#纬度所在的列
longitudeCol =5#经度所在的列
ws.cell(1,longitudeCol).value='经度'
ws.cell(1,latitudeCol).value='纬度'
ak="自己申请的密钥"# 百度地图API密钥

for i in range(2,ws.max_row+1):
    try:
        address=ws.cell(i, 3).value#名称所在的列
        url = f"http://api.map.baidu.com/geocoding/v3/?address={address}&output=json&ak={ak}"
        response = requests.get(url).json()
        latitude = response["result"]["location"]["lat"]
        longitude = response["result"]["location"]["lng"]
        ws.cell(i,longitudeCol).value=longitude
        ws.cell(i,latitudeCol).value=latitude
        print(address,longitude,latitude)
    except:
        ws.cell(1,6).value='该地点目前未能获取，请重新尝试'
workbook.save('经纬度表.xlsx')#另存