根据CSDN一位博主的代码改造而来,原博主是xml的方式进行解析。花了亿点点时间 调整为JSON代码。
原文名称:使用百度地图API读取EXCEL(xlsx)并返回行政区信息
原文地址:https://blog.csdn.net/qq_40454764/article/details/103378368
改进主要是,根据缺少数据轮询数据搜索最完整结果,如果没有最完整结果则默认第一数据。
我是第一次用python写东西,请海涵品鉴。谢谢!
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# coding=utf-8
from openpyxl import load_workbook
import requests
def get_infor(hospit_name, region="全国"): # return list
response = requests.get(
'http://api.map.baidu.com/place/v2/search?query=%s&tag=教育培训®ion=%s&output=json&ak=你的AK' % (
hospit_name, region))
data_info = response.json()
data_results = data_info['results']
json_key_name = ['name', 'address', 'province', 'city', 'area']
i = 0
for i in range(len(data_results)):
for key_name in json_key_name:
if not get_not_emtpy(data_results[i], key_name):
i += 1
else:
break
if i == len(data_results) - 1:
print('Don‘t find optimum result ,so select default number zero by list data!!!')
i = 0
data_one_result = data_results[i]
print('Count result size:', len(data_results))
print('select result number:', i)
print('Select result white Excel file:', data_results[i])
return data_one_result
def get_not_emtpy(result_json, key_name):
if result_json[key_name] is None:
return False
else:
return True
class Write_excel(object):
'''修改excel数据'''
def __init__(self, filename):
self.filename = filename
self.wb = load_workbook(self.filename)
self.ws = self.wb.active # 激活正在工作的sheet#######可能有bug
def write(self, row_n, col_n, value):
'''写入数据,如(2,3,"hello"),第二行第三列写入数据"hello"'''
self.ws.cell(row_n, col_n, value)
self.wb.save(self.filename)
def read_xlsx(path):
# return hospital_name list
excel = load_workbook(path)
# 读表操作:
table = excel['Sheet1']
# 求总有效行数
rows = 0
i = 1
while table.cell(i, 7).value:
rows += 1
i += 1
if i == 10000:
break
rows -= 1
print('一共需查找', rows, '条数据')
hospit_name = []
for i in range(rows):
hospit_name.append(table.cell(2 + i, 7).value)
return hospit_name
if __name__ == '__main__':
path = "D:\\school2.xlsx"
# input file path and read hospital list
hospital_name = read_xlsx(path)
# begian write
we = Write_excel(path) # 调用class
# 写所有数据
n = -1 # 行数计数变量
for hos in hospital_name:
print('Search Target Name:', hos) # test code
n += 1
rst = get_infor(hos)
try:
we.write(2 + n, 8, rst['name'])
except BaseException:
print("not name")
try:
we.write(2 + n, 9, rst['address'])
except BaseException:
print("not address")
try:
we.write(2 + n, 10, rst['province'])
except BaseException:
print("not province")
try:
we.write(2 + n, 11, rst['city'])
except BaseException:
print("not city")
try:
we.write(2 + n, 12, rst['area'])
except BaseException:
print("not Area")
try:
we.write(2 + n, 13, rst['location']['lat'])
except BaseException:
print("not lat")
try:
we.write(2 + n, 14, rst['location']['lng'])
except BaseException:
print("not lng")
print('done,', len(hospital_name) - 1 - n, 'next...')
'''python'''
使用注意事项:
Excel 的读取地址请修改变量path的值
特别提示:地点名称填在在Excel表的第一个表,第G列的第二行,如图所示。
需要行业精确:请注意下T变量tag的值,百度地图Tag分类详见:https://lbsyun.baidu.com/index.php?title=lbscloud/poitags
另外如果希望更精确区域:请变更变量region的值