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