def test4(): XLS_FILE = ur'D:\2014项目\配电网故障定位\yx_line.xls' book = xlrd.open_workbook(XLS_FILE) startrowidx = 1 toplines = set() ret = [] for sheet in book.sheets(): for row in range(startrowidx, sheet.nrows): # o = {'properties':{'webgis_type':'polyline_dn'}} # o['properties']['func_pos_code'] = sheet.cell_value(row, 1) # o['properties']['name'] = sheet.cell_value(row, 2) # o['properties']['owner'] = sheet.cell_value(row, 3) # update_geometry2d(o, False) if len(sheet.cell_value(row, 6)): toplines.add(sheet.cell_value(row, 6)) # print(toplines) for row in range(startrowidx, sheet.nrows): funcpos = sheet.cell_value(row, 1) if funcpos in toplines: o = {'properties':{'webgis_type':'polyline_dn'}} o['properties']['device_id_nx'] = str(int(sheet.cell_value(row, 0))) o['properties']['func_pos_code'] = funcpos o['properties']['name'] = sheet.cell_value(row, 2) o['properties']['owner'] = sheet.cell_value(row, 3) o['properties']['nodes'] = [] o['properties']['subnet'] = [] if u'10kV' in o['properties']['name']: o['properties']['voltage'] = '08' o = update_geometry2d(o, False) print(o['properties']['name']) ret.append(o) # print(o) mongo_action('kmgd', 'network', 'save', ret)
def test2(): book = xlrd.open_workbook(XLS_FILE) startrowidx = 1 ret = [] for sheet in book.sheets(): if sheet.name == u'负荷开关': for row in range(startrowidx, sheet.nrows): o = {} lng = sheet.cell_value(row, 8) lat = sheet.cell_value(row, 9) if isinstance(lng, str) or isinstance(lng, unicode) or isinstance(lat, str) or isinstance(lat, unicode): pass elif isinstance(lng, float) and isinstance(lat, float): if len(sheet.cell_value(row, 6)): o['type'] = 'Feature' o['properties'] = {} o['properties']['device_id_nx'] = str(int(sheet.cell_value(row, 0))) o['properties']['webgis_type'] = 'point_dn' o['properties']['function_pos_code'] = sheet.cell_value(row, 1) o['properties']['name'] = sheet.cell_value(row, 2) o['properties']['function_pos_type'] = 'PAD' o['properties']['owner'] = sheet.cell_value(row, 4) o['properties']['line_func_code'] = sheet.cell_value(row, 6) o['properties']['device_no'] = sheet.cell_value(row, 10) # o['properties']['tower'] = None if isinstance(sheet.cell_value(row, 12), float): o['properties']['voltage_rated'] = sheet.cell_value(row, 12) if isinstance(sheet.cell_value(row, 13), float): o['properties']['current_rated'] = sheet.cell_value(row, 13) o['geometry'] = {'type':'Point','coordinates':[lng, lat]} if u'10kV' in o['properties']['name']: o['properties']['voltage'] = '08' # o['properties']['name'] = o['properties']['name'].replace(u'10kV', '') o = update_geometry2d(o, True) ret.append(o) if sheet.name == u'断路器': for row in range(startrowidx, sheet.nrows): o = {} lng = sheet.cell_value(row, 8) lat = sheet.cell_value(row, 9) if isinstance(lng, str) or isinstance(lng, unicode) or isinstance(lat, str) or isinstance(lat, unicode): pass elif isinstance(lng, float) and isinstance(lat, float): if len(sheet.cell_value(row, 6)): o['type'] = 'Feature' o['properties'] = {} o['properties']['device_id_nx'] = str(int(sheet.cell_value(row, 0))) o['properties']['webgis_type'] = 'point_dn' o['properties']['function_pos_code'] = sheet.cell_value(row, 1) o['properties']['name'] = sheet.cell_value(row, 2) o['properties']['function_pos_type'] = 'PAC' o['properties']['owner'] = sheet.cell_value(row, 4) o['properties']['line_func_code'] = sheet.cell_value(row, 6) o['properties']['device_no'] = sheet.cell_value(row, 10) # o['properties']['tower'] = None if isinstance(sheet.cell_value(row, 12), float): o['properties']['voltage_rated'] = sheet.cell_value(row, 12) if isinstance(sheet.cell_value(row, 13), float): o['properties']['current_rated'] = sheet.cell_value(row, 13) o['geometry'] = {'type':'Point','coordinates':[lng, lat]} if u'10kV' in o['properties']['name']: o['properties']['voltage'] = '08' # o['properties']['name'] = o['properties']['name'].replace(u'10kV', '') o = update_geometry2d(o, True) ret.append(o) if sheet.name == u'隔离开关': for row in range(startrowidx, sheet.nrows): o = {} lng = sheet.cell_value(row, 8) lat = sheet.cell_value(row, 9) if isinstance(lng, str) or isinstance(lng, unicode) or isinstance(lat, str) or isinstance(lat, unicode): pass elif isinstance(lng, float) and isinstance(lat, float): if len(sheet.cell_value(row, 6)): o['type'] = 'Feature' o['properties'] = {} o['properties']['device_id_nx'] = str(int(sheet.cell_value(row, 0))) o['properties']['webgis_type'] = 'point_dn' o['properties']['function_pos_code'] = sheet.cell_value(row, 1) o['properties']['name'] = sheet.cell_value(row, 2) o['properties']['function_pos_type'] = 'PAE' o['properties']['owner'] = sheet.cell_value(row, 4) o['properties']['line_func_code'] = sheet.cell_value(row, 6) o['properties']['device_no'] = sheet.cell_value(row, 10) # o['properties']['tower'] = None if isinstance(sheet.cell_value(row, 12), float): o['properties']['voltage_rated'] = sheet.cell_value(row, 12) if isinstance(sheet.cell_value(row, 13), float): o['properties']['current_rated'] = sheet.cell_value(row, 13) o['geometry'] = {'type':'Point','coordinates':[lng, lat]} if u'10kV' in o['properties']['name']: o['properties']['voltage'] = '08' # o['properties']['name'] = o['properties']['name'].replace(u'10kV', '') o = update_geometry2d(o, True) ret.append(o) print(len(ret)) mongo_action('kmgd', 'features', 'save', ret)
def test12(): import re XLS_FILE = ur'G:\2014项目\配电网故障定位\普洱FTU导出数据\geodata.xls' book = xlrd.open_workbook(XLS_FILE) startrowidx = 1 recs = [] ids_map = {} for sheet in book.sheets(): if sheet.name.lower() == u'sheet1': ids_map['pzz'] = [] for row in range(startrowidx, sheet.nrows): if sheet.cell_value(row, 0) == '': continue rec = {} lat = float(sheet.cell_value(row, 6)) lng = float(sheet.cell_value(row, 7)) rec['geometry'] = { 'type':'Point', 'coordinates':[lng, lat] } rec['properties'] = { 'name': u'%s%s号杆' % (sheet.cell_value(row, 1), sheet.cell_value(row, 2)), 'voltage': '12', 'horizontal_span': float(sheet.cell_value(row, 5))*1000, 'webgis_type': 'point_tower' } rec = update_geometry2d(rec, True) recs.append(rec) if sheet.name.lower() == u'sheet2': ids_map['jfyk'] = [] for row in range(startrowidx, sheet.nrows): if sheet.cell_value(row, 0) == '': continue rec = {} lat = float(sheet.cell_value(row, 1)) lng = float(sheet.cell_value(row, 2)) rec['geometry'] = { 'type': 'Point', 'coordinates': [lng, lat] } rec['properties'] = { 'name': sheet.cell_value(row, 0), 'voltage': '12', 'webgis_type': 'point_tower' } rec = update_geometry2d(rec, True) recs.append(rec) if sheet.name.lower() == u'sheet3': for row in range(startrowidx, sheet.nrows): if sheet.cell_value(row, 0) == '': continue line_name = sheet.cell_value(row, 0).replace('10kV055', '') s = sheet.cell_value(row, 1) m = re.search(ur'配电编号:(\d+)', s, re.UNICODE) m1 = re.search(ur'\(配电编号:(\d+)\)', s, re.UNICODE) no = '' device_no = '' name = sheet.cell_value(row, 1).replace('10kV', '') if m: no = m1.group(0) name = name.replace(no, '') # print('name:%s' % name) if m1: device_no = m.group(0) device_no = device_no.replace(u'配电编号:', '') # print('device_no:%s' % device_no) rec = {} lat = float(sheet.cell_value(row, 2)) lng = float(sheet.cell_value(row, 3)) rec['geometry'] = { 'type': 'Point', 'coordinates': [lng, lat] } rec['properties'] = { 'name': '%s%s' % (line_name, name), 'voltage': '12', 'function_pos_type': 'PAB', 'device_no':device_no, 'webgis_type': 'point_dn', } rec = update_geometry2d(rec, True) recs.append(rec) # client = MongoClient('localhost', 27017) # db = client['kmgd'] # collection = db['features'] # collection.insert_many(add_mongo_id(recs)) print(json.dumps(recs, ensure_ascii=False, indent=4)) print(len(recs))
def test3(): XLS_FILE = ur'D:\2014项目\配电网故障定位\yx_line.xls' book = xlrd.open_workbook(XLS_FILE) startrowidx = 1 toplines = set() for sheet in book.sheets(): for row in range(startrowidx, sheet.nrows): # o = {'properties':{'webgis_type':'polyline_dn'}} # o['properties']['func_pos_code'] = sheet.cell_value(row, 1) # o['properties']['name'] = sheet.cell_value(row, 2) # o['properties']['owner'] = sheet.cell_value(row, 3) # update_geometry2d(o, False) if len(sheet.cell_value(row, 6)): toplines.add(sheet.cell_value(row, 6)) # print(toplines) XLS_FILE1 = ur'D:\2014项目\配电网故障定位\玉溪局台账数据导出1\玉溪杆塔.xls' book1 = xlrd.open_workbook(XLS_FILE1) startrowidx = 1 ret = [] linesmap = {} for sheet in book1.sheets(): for row in range(startrowidx, sheet.nrows): line_no = sheet.cell_value(row, 3) if line_no in toplines: # device_no = sheet.cell_value(row, 1) lng = sheet.cell_value(row, 5) lat = sheet.cell_value(row, 6) if isinstance(lng, str) or isinstance(lng, unicode) or isinstance(lat, str) or isinstance(lat, unicode): pass elif isinstance(lng, float) and isinstance(lat, float): if not line_no in linesmap.keys(): linesmap[line_no] = [] o = {} o['type'] = 'Feature' o['properties'] = {} # o['properties']['device_id_nx'] = str(int(sheet.cell_value(row, 0))) o['properties']['webgis_type'] = 'point_tower' o['properties']['function_pos_code'] = sheet.cell_value(row, 0) if not o['properties']['function_pos_code'] in linesmap[line_no]: linesmap[line_no].append(o['properties']['function_pos_code']) o['properties']['name'] = sheet.cell_value(row, 1) if len(o['properties']['name']) == 0 or o['properties']['name'] == u'杆塔': o['properties']['name'] = o['properties']['function_pos_code'] + u'杆塔' # if (isinstance(o['properties']['name'], str) or isinstance(o['properties']['name'], unicode)) and len(o['properties']['name']) == 0: # o['properties']['name'] = device_no # if isinstance(o['properties']['name'], int) or isinstance(o['properties']['name'], float): # o['properties']['name'] = str(o['properties']['name']) o['properties']['function_pos_type'] = 'LAD' o['properties']['line_func_code'] = line_no o['geometry'] = {'type':'Point','coordinates':[lng, lat]} # if u'10kV' in o['properties']['name']: o['properties']['voltage'] = '08' o = update_geometry2d(o, True) if len(ret) % 100 == 0: print(len(ret)) ret.append(o) # for k in linesmap.keys(): # s = sorted(linesmap[k]) # l = [] # for i in range(len(s)-1): # l.append([s[i], s[i+1]]) # linesmap[k] = l # print(len(ret)) # print(len(linesmap.keys())) # with codecs.open(ur'd:\linesmap.json', 'w', 'utf-8-sig') as f: # f.write(json.dumps(linesmap, ensure_ascii=False, indent=4)) mongo_action('kmgd', 'features', 'save', ret)