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)
