#!/usr/bin/env python # -*- coding:utf-8 -*- #获取城市之间的驾车距离/时间并且存入到excel表格中 import requests, json, os, time, xlwt, sys flush_time = 6000 # 刷新间隔，单位秒 file_name1 = "两地之间的开车时间.xlsx" file_name2 = "两地之间的开车距离.xlsx" # 预置地点列表 address_list = [ # "沈阳市", "大连市", "鞍山市", "抚顺市", "本溪市", "丹东市", "锦州市", "营口市", "阜新市", "辽阳市", "盘锦市", "铁岭市", "朝阳市", "葫芦岛市", '哈尔滨市', '齐齐哈尔市', '鸡西市', '鹤岗市', '双鸭山市', '大庆市', '伊春市', '佳木斯市', '七台河市', '牡丹江市', '黑河市', '绥化市', '大兴安岭地区', ] workdir = os.path.dirname(os.path.realpath(sys.argv[0])) file_path1 = os.path.join(workdir, file_name1) file_path2 = os.path.join(workdir, file_name2) AK = "9AfjsK2CXGuESwSb4sF3LsGhWh999Rdd" # 百度地图API认证码 # 获取地址的经纬度 def getPosition(address, AK): url = "http://api.map.baidu.com/place/v2/search" params = { "query": address, "region": "全国", "output": "json", "ak": AK, } try: res = requests.get(url, params=params) res.raise_for_status() # 如果响应状态码不是200, 则抛出异常 json_data = res.json() if json_data["status"] == 0: lat = json_data["results"][0]["location"]["lat"] # 纬度 lng = json_data["results"][0]["location"]["lng"] # 经度 return (lat, lng), json_data["status"] else: print(json_data["message"]) return (0, 0), json_data["status"] except requests.RequestException as e: print(f"Request error: {e}") return (0, 0), -1 except ValueError as e: print(f"Response parsing error: {e}") return (0, 0), -1 # 获取两地开车的时间 def getTime(start, end, AK): url = "https://api.map.baidu.com/directionlite/v1/driving" params = { "origin": start, "destination": end, "ak": AK, } try: res = requests.get(url, params=params) res.raise_for_status() # 如果响应状态码不是200, 则抛出异常 json_data = json.loads(res.text) if json_data["status"] == 0: return int(json_data['result']['routes'][0]['duration']) # 获取时间,单位s else: print(json_data["message"]) return -1 except requests.RequestException as e: print(f"Request error: {e}") return -1 except ValueError as e: print(f"Response parsing error: {e}") return -1 def getDis(start, end, AK): url = "https://api.map.baidu.com/directionlite/v1/driving" params = { "origin": start, "destination": end, "ak": AK, } try: res = requests.get(url, params=params) res.raise_for_status() # 如果响应状态码不是200, 则抛出异常 json_data = json.loads(res.text) if json_data["status"] == 0: return int(json_data["result"]["routes"][0]["distance"]) else: print(json_data["message"]) return -1 except requests.RequestException as e: print(f"Request error: {e}") return -1 except ValueError as e: print(f"Response parsing error: {e}") return -1 def calcTime(startName, endName): start, status1 = getPosition(startName) end, status2 = getPosition(endName) if status1 == 0 and status2 == 0: return round(getTime(start, end)/60, 1) # 将时间转换为分钟 else: return -1 def calcDistance(startName, endName): start, status1 = getPosition(startName) end, status2 = getPosition(endName) if status1 == 0 and status2 == 0: return round(getDis(start, end),1) else: return -1 # 设置标题样式 def set_title_style(blod=False, underline=False): style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.name = "Calibri" # 字体类型 font.height = 20 * 11 # 20为衡量单位,11为字号 font.bold = blod # 是否加粗 font.underline = underline # 是否添加下划线 style.font = font return style # 生成Excel文件 def createExcelDis(data,file_path): workboot = xlwt.Workbook(encoding='utf-8')# 创建workbook和sheet对象 worksheet = workboot.add_sheet('计算两点间开车距离') # 设置工作表的名字 for i in range(len(address_list)+1): worksheet.col(i).width = 256 * 30 # 设置每列宽, 256为衡量单位，30表示30个字符宽度 row0 = ["两点间开车距离(单位千米)"]+address_list# 写入Excel标题 for i in range(len(row0)): worksheet.write(0, i, row0[i], set_title_style(True)) for i, line in enumerate(data): for j, drive_dis in enumerate(line): worksheet.write(i+1, j, str(drive_dis), set_title_style()) workboot.save(file_path) print("[INFO] 成功创建%s" % file_path) def createExcelTime(data,file_path): # 创建workbook和sheet对象 workboot = xlwt.Workbook(encoding='utf-8') worksheet = workboot.add_sheet('计算两点间开车时间') # 设置工作表的名字 for i in range(len(address_list)+1): worksheet.col(i).width = 256 * 30 # 设置每列宽, 256为衡量单位，30表示30个字符宽度 # 写入Excel标题 row0 = ["两点间开车时间(单位分钟)"]+address_list for i in range(len(row0)): worksheet.write(0, i, row0[i], set_title_style(True)) # 写入查询百度地图api获取的数据 for i,line in enumerate(data): for j,drive_time in enumerate(line): worksheet.write(i+1, j, str(drive_time), set_title_style()) workboot.save(file_path) print("[INFO] 成功创建%s" % file_path) # 生成开车时间矩阵信息 def generateTimeMatrix(flush_time,file_path): while True: matrix_list = [] # 以矩阵的方式来存放两地之间的开车时间 alist = [] # 存放横坐标的值 print("[INFO] 开始计算两地之间的开车时间,每隔%s秒刷新一次..." % flush_time) for start in address_list: # 起始位置作为纵坐标 alist.append(start) for end in address_list: # 终点位置作为横坐标 dt = calcTime(start, end) alist.append(dt) matrix_list.append(alist) alist = [] createExcelTime(matrix_list,file_path) time.sleep(flush_time) # 生成开车时间矩阵信息 def generateDistanceMatrix(flush_time,file_path): while True: matrix_list = [] # 以矩阵的方式来存放两地之间的开车距离 alist = [] # 存放横坐标的值 print("[INFO] 开始计算两地之间的开车距离,每隔%s秒刷新一次..." % flush_time) for start in address_list: # 起始位置作为纵坐标 alist.append(start) for end in address_list: # 终点位置作为横坐标 dt = calcDistance(start, end) alist.append(dt) matrix_list.append(alist) alist = [] createExcelDis(matrix_list,file_path) time.sleep(flush_time) if __name__ == "__main__": try: # 时间 generateTimeMatrix(flush_time, file_path1) # 距离 generateTimeMatrix(flush_time, file_path2) except Exception as e: print('ERROR:%s' % e)