```python
from openpyxl import load_workbook
import requests

#读取excel内容
def do_excel(filepath):
    ws = load_workbook(filepath)
    sh= ws.active
    all_data = []
    for row in range(2,sh.max_row+1):
        all_data.append(sh.cell(row=row,column=2).value)
    return all_data

#通过内容获取行政区域和经纬度
def gaud_map(address):
    url ="https://restapi.amap.com/v3/geocode/geo"
    data={
        "key": "高德的key",
        "address":address
    }
    r = requests.get(url,params=data).json()
    return r["geocodes"][0]["district"],r["geocodes"][0]["location"]

# 将数据写进excel表格里面-注意，需要将excel关闭下
def w_excel(filepath,row,administrative_division,Latitude_longitude):
    ws = load_workbook(filepath)
    sh = ws.active
    # 写入行政区域
    sh.cell(row=row,column=3,value=administrative_division)
    # 写入经纬度
    sh.cell(row=row,column=4,value=Latitude_longitude)
    ws.save(filepath)


file_route =r'D:\服务地点1.xlsx'
j =2
for i in  do_excel(file_route):
    gaud_map(i)
    print("行政区域和经纬度",gaud_map(i))
    w_excel(file_route,j,gaud_map(i)[0],gaud_map(i)[1])
    j+=1