Python 读取Excel地点名称通过百度地图获取行政区划信息

根据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=教育培训&region=%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的值