import requests
import json
import xlrd
import xlwt

work_book = xlrd.open_workbook('/Users/xjh/Desktop/物流设计大赛/中国外运杯案例附件/附件8：两日订单.xlsx')
# 获取第一个工作表
table = work_book.sheet_by_index(0)
# 获取行数
nrows = table.nrows
# 获取列数
ncols = table.ncols
# 定义excel_list
excel_list = []
for row in range(0, nrows):
    # 获取单元格数据
    cell_value1 = table.cell(row, 4).value
    # 把数据追加到excel_list中
    excel_list.append(cell_value1)

#调用高德api转化经纬度
def coords(city):
    # 输入API问号前固定不变的部分
    url = 'https://restapi.amap.com/v3/geocode/geo'
    # 将两个参数放入字典
    params = { 'key': '65ff66fb8970e85b077efe406f11be48',
               'address': city   }
    res = requests.get(url, params)
    jd =  json.loads(res.text)
    return jd['geocodes'][0]['location']

outlist = []
coordinate_lis = []
for i in range(1,len(excel_list)):
    loca = coords(excel_list[i])
    outlist.append(loca)
    tmp_lis = []
    tmp_lis.append(float(loca.split(',')[0]))
    tmp_lis.append(float(loca.split(',')[1]))
    tmp_dir = {}
    tmp_dir["coordinate"] = tmp_lis
    coordinate_lis.append(tmp_dir)
print(outlist)
print(coordinate_lis)
bJson = json.dumps(coordinate_lis, ensure_ascii=False)
f = open('/Users/xjh/Desktop/物流设计大赛/new_json.json', 'w')
f.write(bJson)

data_w=xlwt.Workbook(encoding = 'utf-8')
table_w=data_w.add_sheet('data')
savalist = []
ite = 0
print(outlist)
for item in range(len(outlist)):
    table_w.write(ite, 0, excel_list[item+1])
    table_w.write(ite,1,outlist[item])
    ite += 1
data_w.save('/Users/xjh/Desktop/物流设计大赛/coordinates.xlsx')