'''
未使用GeoPy库，排版混乱请自行缩进。
先读取模版,格式要是.xlsx,文字编码请先转成Unicode，模版要一样
高德个人用户KEY一天6000次，设置了1到2秒随机延迟防止被封
如果该点位高德上找不到就默认坐标为'0.00,0.00'需要手工查找
'''
#! conding = 'UTF-8'
import requests
from xlrd import open_workbook
import re
import math
import time
import random
import xlwings as xw
import tkinter as tk
from tkinter import filedialog,ttk,HORIZONTAL
# 打开excel
def read_excel(File_path,sheet_index):
# 打开文件
workbook = open_workbook(File_path)
# 获取所有sheet
sheet2_name = workbook.sheet_names()[sheet_index] # 获取序列号相关的表
# 根据sheet索引或者名称获取sheet内容
sheet2 = workbook.sheet_by_name(sheet2_name)
# 获取整行和整列的值（数组）
cols = sheet2.col_values(2) # 获取第三列内容
addrs_list=[] # 新建一个空表格来存清洗好的地址
for i in cols:
addr = re.findall(r'.-(.*)-.', i)
if addr != [] or None:
addrs_list.append(addr)
return addrs_list
# 高德正向解析 注意个人KEY可以解析6000次每天
def PSGIapi(site):
headers = {
'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.100 Safari/537.36',
'Cookie': 填你自己的Cookie,
'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3',
'Accept-Encoding': 'gzip, deflate',
'Accept-Language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
'Cache-Control': 'max-age=0',
'Connection': 'keep-alive'
}
parameters = {'address': site, 'city': '你的城市', 'key': '这里填自己申请的key'}
url = 'http://restapi.amap.com/v3/geocode/geo'
response = requests.get(url, parameters,headers=headers)
info_site = response.json()
if info_site['count'] == '1':
lat_lng = info_site['geocodes'][0]['location']
else:
# 如果该点位高德上找不到就默认坐标为0，0需要手工比对
lat_lng = [0.00,0.00]
return lat_lng
# 将高德坐标（GCJ02）转换为GPS（WGS84）坐标
def GCJ2WGS(location,date_lists,site):
# location格式如下：locations[1] = "113.923745,22.530824"
lon = float(location[0:location.find(",")])
lat = float(location[location.find(",") + 1:len(location)])
a = 6378245.0 # 克拉索夫斯基椭球参数长半轴a
ee = 0.00669342162296594323 #克拉索夫斯基椭球参数第一偏心率平方
PI = 3.14159265358979324 # 圆周率
# 以下为转换公式
x = lon - 105.0
y = lat - 35.0
# 经度
dLon = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * math.sqrt(abs(x))
dLon += (20.0 * math.sin(6.0 * x * PI) + 20.0 * math.sin(2.0 * x * PI)) * 2.0 / 3.0
dLon += (20.0 * math.sin(x * PI) + 40.0 * math.sin(x / 3.0 * PI)) * 2.0 / 3.0
dLon += (150.0 * math.sin(x / 12.0 * PI) + 300.0 * math.sin(x / 30.0 * PI)) * 2.0 / 3.0
#纬度
dLat = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * math.sqrt(abs(x))
dLat += (20.0 * math.sin(6.0 * x * PI) + 20.0 * math.sin(2.0 * x * PI)) * 2.0 / 3.0
dLat += (20.0 * math.sin(y * PI) + 40.0 * math.sin(y / 3.0 * PI)) * 2.0 / 3.0
dLat += (160.0 * math.sin(y / 12.0 * PI) + 320 * math.sin(y * PI / 30.0)) * 2.0 / 3.0
radLat = lat / 180.0 * PI
magic = math.sin(radLat)
magic = 1 - ee * magic * magic
sqrtMagic = math.sqrt(magic)
dLat = (dLat * 180.0) / ((a * (1 - ee)) / (magic * sqrtMagic) * PI)
dLon = (dLon * 180.0) / (a / sqrtMagic * math.cos(radLat) * PI)
wgsLon = lon - dLon
wgsLat = lat - dLat
date=[wgsLon,wgsLat] # 将名称坐标封装进列表等待后面写入
date_lists.append(date)
return date_lists
# askopenfilename 1次上传1个；askopenfilenames1次上传多个
def upload_file():
global File_path
File_path = tk.filedialog.askopenfilename()
p1["maximum"] = len_rows(File_path)
main(File_path)
# 获取行数
def len_rows(File_path):
app = xw.App(visible=False,add_book=False)
global wb
wb = app.books.open(File_path)
Total_rows=0 # 获取所有表的总函数用来做进度表
sht_len=len(wb.sheets)
for sheet_index in range(sht_len):
sheet=wb.sheets[sheet_index]
rng=sheet.range('A4').expand('table')
nrows=rng.rows.count
# ncols=rng.columns.count #列
Total_rows += nrows
return Total_rows
def main(File_path):
# 需要上传的excel文件,需要查询的sheet表,需要另存为的save_excel名称
p1["value"] = 0
for sheet_index in range(len(wb.sheets)):
address=read_excel(File_path,sheet_index)
date_lists= [] # 最后数据表格
rep_addr = None # 去重复地址请求减少服务器压力
for site in address:
# 如果地址是一样的直接复制列表前面的经纬度
if site[0] == rep_addr:
old_lat = date_lists[-1]
date_lists.append(old_lat)
else:
try:
rep_addr = site[0]
location = PSGIapi(site[0])
# 判断是否是废点或者是默认的温州市人民政府坐标
if location == [0.00,0.00] or location == '120.699366,27.994267':
date_lists.append([0.00,0.00])
result=(rep_addr,'点位名称不正确请手工确认')
print(result)
else:
GCJ2WGS(location,date_lists,site)
result=('新发现地址'+ site[0])
print(result)
except Exception as e:
print('Reason:', e)
continue
finally:
time.sleep(random.randint(1,2))
# 进度条增加1次
p1["value"] +=1
Windous.update()
sht = wb.sheets[sheet_index]
sht.range('D:E').api.Insert()
sht.range('D3').value = ['经度','纬度']
# 直接写入列表，注意是按照行写的，列请.options(transpose=True)
sht.range('D4').value = date_lists
# save_excel(date_lists,excel,sheet_index)
wb.save(File_path)
print('转换完成，关闭窗口。')
Windous.destroy()
if __name__ == '__main__':
Windous = tk.Tk()
Windous.title('点位坐标采集（PSGI地图）')
btn = tk.Button(Windous, text='上传转换', command=upload_file)
btn.grid(row=1, column=0)
#进度条
p1 = ttk.Progressbar(Windous, length=200, mode="determinate", orient=HORIZONTAL)
p1.grid(row=1,column=1)
Windous.mainloop()
其实如果改成GeoPy库应该会更简单，大神勿喷。
