# author: YuJun.Wang
# date: 2020.08.12 20:17
import openpyxl
from openpyxl.styles import PatternFill, colors
import requests
import json
import os
import time
def main():
try:
# 打开文件
wb = openpyxl.load_workbook("py.xlsx")
# 选择活动表
ws = wb.active
# 单元格颜色
bgcolor = PatternFill("solid", fgColor="FFC1C1")
i = 1
max_rows = ws.max_row
GFirst = None
GFlag = 0
for cell in ws['G']:
os.system('cls')
print('正在处理第'+str(i)+'/'+str(max_rows)+'条........')
if i%100==0:
print('高德地图限制高并发等待中....')
wb.save("py.xlsx")
time.sleep(10)
cellI = 'I' + str(i)
cellJ = 'J' + str(i)
cellH = 'H' + str(i)
cellK = 'K' + str(i)
if cell.value == GFirst and GFlag == 1:
cellK2 = 'K' + str(i-1)
ws[cellK]= ws[cellK2].value
ws[cellK].fill = bgcolor
GFirst = cell.value
i = i + 1
continue
else:
GFlag = 0
GFirst = cell.value
i = i + 1
data = None
if '#' in cell.value:
continue
if cell.value is None:
break
if not cell.value is None:
cellValue = cell.value[4:]
data = search_address(cellValue)
if data != 0:
ws[cellK]= data
ws[cellK].fill = bgcolor
GFlag = 1
continue
if not ws[cellI].value is None:
cellValue = ws[cellH].value[2:]+ws[cellI].value
data = search_address(cellValue)
if data != 0:
ws[cellK]= data
ws[cellK].fill = bgcolor
GFlag = 1
continue
if ws[cellI].value is None and not ws[cellJ].value is None:
cellValue = ws[cellH].value[2:] + ws[cellJ].value[1:-1]
data = search_address(cellValue)
if data != 0:
ws[cellK]= data
ws[cellK].fill = bgcolor
GFlag = 1
continue
finally:
wb.save("py.xlsx")
print('文件处理完成!')
print(data)
def search_address(data):
agisurl = 'https://restapi.amap.com/v3/assistant/inputtips?keywords='+data+'&key=自己申请高德地图Key'
r = requests.get(agisurl)
decoded = json.loads(r.text)
if decoded['count'] == '0' :
return 0
return decoded['tips'][0]['name']
if __name__ == '__main__':
main()