使用前准备
- 删除表格的第一行“企查查”图片,在最后两列添加新的字段(经度和纬度),然后另存为xlsx格式。
- 代码可直接赋值粘贴使用,只需修改 可能要替换的位置
- 直接对原excel进行操作——谨慎操作.
from urllib.request import urlopen, quote
import time
from openpyxl.reader.excel import load_workbook
import re
from urllib import parse
import hashlib
from urllib.request import urlopen, quote
import json
#############################################打开excel
# excel文件绝对路径
file_home = "表格路径" ###替换##
sheet_name = 'sheet名称' ###替换##
ak = '你的ak' ###替换##
jingdu = 'z' #经度对应的excel的列号(如a1表示第一列,第一行的单元格). ###替换##
weidu = 'aa' ###替换## 同上
dizhi = 'w' ###替换## 中文地址对应的列号,如,江苏省南京市鼓楼区....
wb = load_workbook(filename=file_home) # 打开excel文件
#sheet_ranges = wb['市北'] #打开sheet
ws = wb[sheet_name] # 根据Sheet1这个sheet名字来获取该sheet
row=ws.max_row #获取行数
def get_urt(address):
# 以get请求为例http://api.map.baidu.com/geocoder/v2/?address=百度大厦&output=json&ak=你的ak
queryStr = '/geocoder/v2/?address=%s&output=json&ak=' + ak
queryStr = queryStr % address
# 对queryStr进行转码,safe内的保留字符不转换
encodedStr = parse.quote(queryStr, safe="/:=&?#+!$,;'@()*[]")
# 在最后直接追加上yoursk
rawStr = encodedStr + ak
#计算sn
sn = (hashlib.md5(parse.quote_plus(rawStr).encode("utf8")).hexdigest())
#由于URL里面含有中文,所以需要用parse.quote进行处理,然后返回最终可调用的url
url = parse.quote("http://api.map.baidu.com"+queryStr+"&sn="+sn, safe="/:=&?#+!$,;'@()*[]")
return url
import math
x_pi = float(3.14159265358979324 * 3000.0 / 180.0)
# //pai
pi = float(3.1415926535897932384626)
# //离心率
ee = float(0.00669342162296594323)
# //长半轴
a = float(6378245.0)
# //百度转国测局
def bd09togcj02(bd_lon, bd_lat):
x = (bd_lon - 0.0065)
y = (bd_lat - 0.006)
z = (math.sqrt(x * x + y * y)) - (0.00002 * math.sin(y * x_pi))
theta = math.atan2(y, x) - 0.000003 * math.cos(x * x_pi)
gg_lng = z * math.cos(theta)
gg_lat = z * math.sin(theta)
return gg_lng, gg_lat
# //国测局转百度
def gcj02tobd09(lng, lat):
z = math.sqrt(lng * lng + lat * lat) + 0.00002 * math.sin(lat * x_pi)
theta = math.atan2(lat, lng) + 0.000003 * math.cos(lng * x_pi)
bd_lng = z * math.cos(theta) + 0.0065
bd_lat = z * math.sin(theta) + 0.006
return bd_lng, bd_lat
# //国测局转84
def gcj02towgs84(lng, lat):
dlat = transformlat(lng - 105.0, lat - 35.0)
dlng = transformlng(lng - 105.0, lat - 35.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)
dlng = (dlng * 180.0) / (a / sqrtmagic * math.cos(radlat) * pi)
mglat = lat + dlat
mglng = lng + dlng
return [lng * 2 - mglng, lat * 2 - mglat]
# //经度转换
def transformlat(lng, lat):
ret = -100.0 + 2.0 * lng + 3.0 * lat + 0.2 * lat * lat + 0.1 * lng * lat + 0.2 * math.sqrt(abs(lng))
ret += (20.0 * math.sin(6.0 * lng * pi) + 20.0 * math.sin(2.0 * lng * pi)) * 2.0 / 3.0
ret += (20.0 * math.sin(lat * pi) + 40.0 * math.sin(lat / 3.0 * pi)) * 2.0 / 3.0
ret += (160.0 * math.sin(lat / 12.0 * pi) + 320 * math.sin(lat * pi / 30.0)) * 2.0 / 3.0
return ret
# //纬度转换
def transformlng(lng, lat):
ret = 300.0 + lng + 2.0 * lat + 0.1 * lng * lng + 0.1 * lng * lat + 0.1 * math.sqrt(abs(lng))
ret += (20.0 * math.sin(6.0 * lng * pi) + 20.0 * math.sin(2.0 * lng * pi)) * 2.0 / 3.0
ret += (20.0 * math.sin(lng * pi) + 40.0 * math.sin(lng / 3.0 * pi)) * 2.0 / 3.0
ret += (150.0 * math.sin(lng / 12.0 * pi) + 300.0 * math.sin(lng / 30.0 * pi)) * 2.0 / 3.0
return ret
def getWgs84xy(x, y):
# //先转 国测局坐标
doubles_gcj = bd09togcj02(x, y)
# //(x 117. y 36. )
# //国测局坐标转wgs84
doubles_wgs84 = gcj02towgs84(doubles_gcj[0], doubles_gcj[1])
# //返回 纠偏后 坐标
return doubles_wgs84
for i in range(2,row+1):
address = ws[dizhi+str(i)].value #获取表中的字符串地址
print(dizhi+str(i))
url = get_urt(address)
req = urlopen(url)
res = req.read().decode()
x=re.findall(r'lng":(.*?),"lat"',res)
y=re.findall(r'lat":(.*?)},',res)
print(x,y)
zuobiao = getWgs84xy(float(x[0]),float(y[0]))
ws[jingdu+str(i)],ws[weidu+str(i)] = zuobiao[0], zuobiao[1] # 修改单元格的值
print("第{0}个坐标获取成功".format(i))
wb.save(file_home) # 保存修改后的excel