注意:请提前申请好百度地图密钥
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')#另存