def write_to_xlsx(poilist, cityname, classfield):
# 一个Workbook对象，这就相当于创建了一个Excel文件
wb = Workbook()
ws = wb.active
# 第一行(列标题)
ws['A1'] = 'lon'
ws['B1'] = 'lat'
ws['C1'] = 'name'
ws['D1'] = 'address'
ws['E1'] = 'pname'
ws['F1'] = 'cityname'
ws['G1'] = 'business_area'
ws['H1'] = 'type'
for i, poi in enumerate(poilist):
row = i + 2
name = poi['name']
address = poi['address'] if poi['address'] else ''
pname = poi['pname']
cityname = poi['cityname']
business_area = json.dumps(
poi['business_area'],
ensure_ascii=False) if poi['business_area'] else ''
type = poi['type']
lng = poi['location'].split(",")[0]
lat = poi['location'].split(",")[1]
if coord == 2:
result = gcj02_to_wgs84(float(lng), float(lat))
lng = result[0]
lat = result[1]
elif coord == 3:
result = gcj02_to_bd09(float(lng), float(lat))
lng = result[0]
lat = result[1]
# 每一行写入
ws['A%s' % row] = lng
ws['B%s' % row] = lat
ws['C%s' % row] = name
ws['D%s' % row] = address
ws['E%s' % row] = pname
ws['F%s' % row] = cityname
ws['G%s' % row] = business_area
ws['H%s' % row] = type
# 最后，将以上操作保存到指定的Excel文件中
folder = os.path.join(os.getcwd(), 'data')
if not os.path.exists(folder):
os.mkdir(folder)
file_path = os.path.join(folder, 'poi-%s-%s.xlsx' % (cityname, classfield))
wb.save(file_path)
def write_to_excel(poilist, cityname, classfield, coord):
# 一个Workbook对象，这就相当于创建了一个Excel文件
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet(classfield, cell_overwrite_ok=True)
# 第一行(列标题)
sheet.write(0, 0, 'lon')
sheet.write(0, 1, 'lat')
sheet.write(0, 2, 'name')
sheet.write(0, 3, 'address')
sheet.write(0, 4, 'pname')
sheet.write(0, 5, 'cityname')
sheet.write(0, 6, 'business_area')
sheet.write(0, 7, 'type')
for i in range(len(poilist)):
location = poilist[i]['location']
name = poilist[i]['name']
address = poilist[i]['address']
pname = poilist[i]['pname']
cityname = poilist[i]['cityname']
business_area = poilist[i]['business_area']
type = poilist[i]['type']
lng = str(location).split(",")[0]
lat = str(location).split(",")[1]
if (coord == "2"):
result = gcj02_to_wgs84(float(lng), float(lat))
lng = result[0]
lat = result[1]
if (coord == "3"):
result = gcj02_to_bd09(float(lng), float(lat))
lng = result[0]
lat = result[1]
# 每一行写入
sheet.write(i + 1, 0, lng)
sheet.write(i + 1, 1, lat)
sheet.write(i + 1, 2, name)
sheet.write(i + 1, 3, address)
sheet.write(i + 1, 4, pname)
sheet.write(i + 1, 5, cityname)
sheet.write(i + 1, 6, business_area)
sheet.write(i + 1, 7, type)
# 最后，将以上操作保存到指定的Excel文件中
p = Pinyin()
p.get_pinyin(cityname)
path = "data/poi/" + p.get_pinyin(cityname) + "-" + p.get_pinyin(
classfield) + '.xls'
book.save(r'' + os.getcwd() + "/" + path)
return path
def write_to_csv(poilist, cityname, classfield):
data_csv = {}
lons, lats, names, addresss, pnames, citynames, business_areas, types = [], [], [], [], [], [], [], []
for i in range(len(poilist)):
print('===================')
print(poilist[i])
location = poilist[i].get('location')
name = poilist[i].get('name')
address = poilist[i].get('address')
pname = poilist[i].get('pname')
cityname = poilist[i].get('cityname')
business_area = poilist[i].get('business_area')
type = poilist[i].get('type')
lng = str(location).split(",")[0]
lat = str(location).split(",")[1]
if (coord == 2):
result = gcj02_to_wgs84(float(lng), float(lat))
lng = result[0]
lat = result[1]
if (coord == 3):
result = gcj02_to_bd09(float(lng), float(lat))
lng = result[0]
lat = result[1]
lons.append(lng)
lats.append(lat)
names.append(name)
addresss.append(address)
pnames.append(pname)
citynames.append(cityname)
if business_area == []:
business_area = ''
business_areas.append(business_area)
types.append(type)
data_csv['lon'], data_csv['lat'], data_csv['name'], data_csv['address'], data_csv['pname'], \
data_csv['cityname'], data_csv['business_area'], data_csv['type'] = \
lons, lats, names, addresss, pnames, citynames, business_areas, types
df = pd.DataFrame(data_csv)
folder_name = 'poi-' + cityname + "-" + classfield
folder_name_full = 'data' + os.sep + folder_name + os.sep
if os.path.exists(folder_name_full) is False:
os.makedirs(folder_name_full)
file_name = 'poi-' + cityname + "-" + classfield + ".csv"
file_path = folder_name_full + file_name
df.to_csv(file_path, index=False, encoding='utf_8_sig')
return folder_name_full, file_name
def write_to_excel(poilist, cityname, classfield):
# 一个Workbook对象，这就相当于创建了一个Excel文件
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet(classfield, cell_overwrite_ok=True)
# 第一行(列标题)
sheet.write(0, 0, 'lon')
sheet.write(0, 1, 'lat')
sheet.write(0, 2, 'name')
sheet.write(0, 3, 'address')
sheet.write(0, 4, 'pname')
sheet.write(0, 5, 'cityname')
sheet.write(0, 6, 'business_area')
sheet.write(0, 7, 'type')
for i in range(len(poilist)):
location = poilist[i].get('location')
name = poilist[i].get('name')
address = poilist[i].get('address')
pname = poilist[i].get('pname')
cityname = poilist[i].get('cityname')
business_area = poilist[i].get('business_area')
type = poilist[i].get('type')
lng = str(location).split(",")[0]
lat = str(location).split(",")[1]
if (coord == 2):
result = gcj02_to_wgs84(float(lng), float(lat))
lng = result[0]
lat = result[1]
if (coord == 3):
result = gcj02_to_bd09(float(lng), float(lat))
lng = result[0]
lat = result[1]
# 每一行写入
sheet.write(i + 1, 0, lng)
sheet.write(i + 1, 1, lat)
sheet.write(i + 1, 2, name)
sheet.write(i + 1, 3, address)
sheet.write(i + 1, 4, pname)
sheet.write(i + 1, 5, cityname)
sheet.write(i + 1, 6, business_area)
sheet.write(i + 1, 7, type)
# 最后，将以上操作保存到指定的Excel文件中
book.save(r'data' + os.sep + 'poi-' + cityname + "-" + classfield + ".xls")
def write_to_excel(poilist, citycode, classfield, coord):
# 一个Workbook对象，这就相当于创建了一个Excel文件
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet = book.add_sheet("sheet1", cell_overwrite_ok=True)
# 第一行(列标题)
sheet.write(0, 0, 'lon')
sheet.write(0, 1, 'lat')
sheet.write(0, 2, 'name')
sheet.write(0, 3, 'address')
sheet.write(0, 4, 'pname')
sheet.write(0, 5, 'cityname')
sheet.write(0, 6, 'adcode')
sheet.write(0, 7, 'adname')
sheet.write(0, 8, 'business_area')
sheet.write(0, 9, 'type')
sheet.write(0, 10, 'id')
index = 0
if len(poilist) == 0:
return
for i in range(len(poilist)):
location = poilist[i]['location']
name = poilist[i]['name']
address = poilist[i]['address']
pname = poilist[i]['pname']
cityname = poilist[i]['cityname']
business_area = poilist[i]['business_area']
type = poilist[i]['type']
id = poilist[i]['id']
adcode = poilist[i]['adcode']
adname = poilist[i]['adname']
#根据adcode判断当前数据是否属于当前所需要的城市 根据城市编码前四位判断
if adcode[:3] != citycode[:3]:
continue
lng = str(location).split(",")[0]
lat = str(location).split(",")[1]
if (coord == "2"):
result = gcj02_to_wgs84(float(lng), float(lat))
lng = result[0]
lat = result[1]
if (coord == "3"):
result = gcj02_to_bd09(float(lng), float(lat))
lng = result[0]
lat = result[1]
# 每一行写入
sheet.write(index + 1, 0, lng)
sheet.write(index + 1, 1, lat)
sheet.write(index + 1, 2, name)
sheet.write(index + 1, 3, address)
sheet.write(index + 1, 4, pname)
sheet.write(index + 1, 5, cityname)
sheet.write(index + 1, 6, adcode)
sheet.write(index + 1, 7, adname)
sheet.write(index + 1, 8, business_area)
sheet.write(index + 1, 9, type)
sheet.write(index + 1, 10, id)
index = index + 1
# 最后，将以上操作保存到指定的Excel文件中
p = Pinyin()
data_path = os.getcwd() + os.sep + "data" + os.sep + "poi" + os.sep
if not os.path.exists(data_path):
os.mkdir(data_path)
path = data_path + str(classfield) + '.xls'
book.save(r'' + path)
print('写入成功')
return path
def transfer(orgcoord, targetcoord, filename):
'''
坐标转换
默认第一二列为经纬度
:param filename:
:return:
'''
workbook = xlrd.open_workbook(filename)
new_workbook = copy(workbook)
new_worksheet = new_workbook.get_sheet(0)
sheet = workbook.sheets()[0]
index = 0
for i in range(1, sheet.nrows):
lon, lat = sheet.cell_value(i, 0), sheet.cell_value(i, 1)
# 坐标转换
if orgcoord == "1":
if targetcoord == "1":
pass
elif targetcoord == "2":
result = gcj02_to_wgs84(float(lon), float(lat))
lon = result[0]
lat = result[1]
elif targetcoord == "3":
result = gcj02_to_bd09(float(lon), float(lat))
lon = result[0]
lat = result[1]
elif orgcoord == "2":
if targetcoord == "1":
result = wgs84_to_gcj02(float(lon), float(lat))
lon = result[0]
lat = result[1]
elif targetcoord == "2":
pass
elif targetcoord == "3":
result = wgs84_to_bd09(float(lon), float(lat))
lon = result[0]
lat = result[1]
elif orgcoord == "3":
if targetcoord == "1":
result = bd09_to_gcj02(float(lon), float(lat))
lon = result[0]
lat = result[1]
elif targetcoord == "2":
result = bd09_to_wgs84(float(lon), float(lat))
lon = result[0]
lat = result[1]
elif targetcoord == "3":
pass
for j in range(sheet.ncols):
if index == 0:
new_worksheet.write(i - 1, sheet.ncols + 1, 'lon-new')
new_worksheet.write(i - 1, sheet.ncols + 2, 'lat-new')
else:
new_worksheet.write(i - 1, sheet.ncols + 1, lon)
new_worksheet.write(i - 1, sheet.ncols + 2, lat)
index = index + 1
new_file_name = "upload/" + str(filename).split("/")[-1].split(
".")[0] + "-new" + ".xls"
new_file_path = os.path.abspath(os.getcwd()) + "/" + new_file_name
new_workbook.save(new_file_path) # 保存工作簿
return new_file_name
def write_to_csv(poilist, provincename, classfield):
data_csv = {}
lons, lats, names, addresss, pnames, business_areas,citynames, adnames, types, typecodes\
, type_1s, type_2s, type_3s, type_4s, ids = [], [], [], [], [], [], [], [], [], [], [], [], [], [], []
for i in range(len(poilist)):
location = poilist[i]['location']
name = poilist[i]['name']
address = poilist[i]['address']
pname = poilist[i]['pname']
#provincename = poilist[i]['provincename']
business_area = poilist[i]['business_area']
cityname = poilist[i]['cityname']
adname = poilist[i]['adname']
type = poilist[i]['type']
typecode = poilist[i]['typecode']
lng = str(location).split(",")[0]
lat = str(location).split(",")[1]
id = poilist[i]['id']
type = str(type)
type_1 = ''
type_2 = ''
type_3 = ''
type_4 = ''
if str(type) != None and str(type) != '':
type_strs = type.split(';')
for i in range(len(type_strs)):
ty = type_strs[i]
if i == 0:
type_1 = ty
elif i == 1:
type_2 = ty
elif i == 2:
type_3 = ty
elif i == 3:
type_4 = ty
if (coord == 2):
result = gcj02_to_wgs84(float(lng), float(lat))
lng = result[0]
lat = result[1]
if (coord == 3):
result = gcj02_to_bd09(float(lng), float(lat))
lng = result[0]
lat = result[1]
lons.append(lng)
lats.append(lat)
names.append(name)
addresss.append(address)
citynames.append(cityname)
adnames.append(adname)
pnames.append(pname)
#provincenames.append(provincename)
if business_area == []:
business_area = ''
business_areas.append(business_area)
types.append(type)
typecodes.append(typecode)
type_1s.append(type_1)
type_2s.append(type_2)
type_3s.append(type_3)
type_4s.append(type_4)
ids.append(id)
data_csv['lon'], data_csv['lat'], data_csv['name'], data_csv['address'], data_csv['pname'], \
data_csv['business_area'], data_csv['cityname'], data_csv['adname'], data_csv['type'], data_csv['typecode'], \
data_csv['type1'], data_csv['type2'], data_csv['type3'], data_csv['type4'], data_csv['id'] = \
lons, lats, names, addresss, pnames, business_areas, citynames, adnames, types, typecodes, type_1s, type_2s, type_3s, type_4s, ids
pin = Pinyin()
provincename_pinyin = pin.get_pinyin(provincename) # 默认分割符为-
df = pd.DataFrame(data_csv)
folder_name_full = 'data' + os.sep + provincename_pinyin + os.sep #+ classfield + os.sep
if os.path.exists(folder_name_full) is False:
os.makedirs(folder_name_full)
file_name = 'poi-' + provincename_pinyin + "-" + classfield + ".csv"
file_path = folder_name_full + file_name
df.to_csv(file_path, index=False, encoding='utf_8_sig')
print('写入地址：', folder_name_full, file_name)
return folder_name_full, file_name
def write_to_csv(poilist, citycode, classfield, coord):
data_csv = {}
lons, lats, names, addresss, pnames, citynames, business_areas, types, typecodes, ids, type_1s, type_2s, type_3s, type_4s = [], [], [], [], [], [], [], [], [], [], [], [], [], []
if len(poilist) == 0:
print("处理完成，当前citycode:" + str(citycode), ", classfield为：",
str(classfield) + "，数据为空，，，结束.......")
return None, None
for i in range(len(poilist)):
location = poilist[i].get('location')
name = poilist[i].get('name')
address = poilist[i].get('address')
pname = poilist[i].get('pname')
cityname = poilist[i].get('cityname')
business_area = poilist[i].get('business_area')
type = poilist[i].get('type')
typecode = poilist[i].get('typecode')
lng = str(location).split(",")[0]
lat = str(location).split(",")[1]
id = poilist[i].get('id')
if (coord == 2):
result = gcj02_to_wgs84(float(lng), float(lat))
lng = result[0]
lat = result[1]
if (coord == 3):
result = gcj02_to_bd09(float(lng), float(lat))
lng = result[0]
lat = result[1]
type_1, type_2, type_3, type_4 = '', '', '', ''
if str(type) != None and str(type) != '':
type_strs = type.split(';')
for i in range(len(type_strs)):
ty = type_strs[i]
if i == 0:
type_1 = ty
elif i == 1:
type_2 = ty
elif i == 2:
type_3 = ty
elif i == 3:
type_4 = ty
lons.append(lng)
lats.append(lat)
names.append(name)
addresss.append(address)
pnames.append(pname)
citynames.append(cityname)
if business_area == []:
business_area = ''
business_areas.append(business_area)
types.append(type)
typecodes.append(typecode)
ids.append(id)
type_1s.append(type_1)
type_2s.append(type_2)
type_3s.append(type_3)
type_4s.append(type_4)
data_csv['lon'], data_csv['lat'], data_csv['name'], data_csv['address'], data_csv['pname'], \
data_csv['cityname'], data_csv['business_area'], data_csv['type'], data_csv['typecode'], data_csv['id'], data_csv[
'type1'], data_csv['type2'], data_csv['type3'], data_csv['type4'] = \
lons, lats, names, addresss, pnames, citynames, business_areas, types, typecodes, ids, type_1s, type_2s, type_3s, type_4s
df = pd.DataFrame(data_csv)
folder_name = 'poi-' + citycode + "-" + classfield
folder_name_full = 'data' + os.sep + folder_name + os.sep
if os.path.exists(folder_name_full) is False:
os.makedirs(folder_name_full)
file_name = 'poi-' + citycode + "-" + classfield + ".csv"
file_path = folder_name_full + file_name
df.to_csv(file_path, index=False, encoding='utf_8_sig')
print('写入成功')
return folder_name_full, file_name
