def data_set_from_excel(fileFullPath, sheetName='assets'): """ read and set data from excel """ fieldMap = format_field_map() try: wb = load_workbook(filename=fileFullPath) sheet = wb[sheetName] head = map(lambda x: x.value, sheet.rows[0]) dataSet = [] for row in sheet.rows[1:]: data_row = {} for i, cell in enumerate(row): data_row[fieldMap[head[i]]] = str(cell.value).strip() if cell.value else '' if data_row['asset_type'] in macList(): data_row['type_info'] = dict(mac=data_row['mac']) elif data_row['asset_type'] in wireList(): data_row['type_info'] = dict(imei=data_row['imei'], version=data_row['version']) elif data_row['asset_type'] in vpnList(): data_row['type_info'] = dict(userful_life=data_row['userful_life']) else: data_row['type_info'] = {} dataSet.append(data_row) return True, dataSet except Exception, _ex: LOG.error("error occured while read data from excel : %s" % str(_ex)) return False, []
def construct_and_add_batch_operate_info(self, params, rtx_id): operInfos = [] for param in params: asset_type = param['asset_type'] operInfo = {} before_field = {} after_field = {} excel_en_list = AssetInfoUtils().input_excel_en_list() flag = False for name in excel_en_list: if asset_type in macList(): # 记录MAC地址 if name in set(type_info_en_list()).difference(set(assetInfoMacEnlist())): continue elif asset_type in vpnList(): if name in set(type_info_en_list()).difference(set(assetInfoVpnEnlist())): continue elif asset_type in wireList(): if name in set(type_info_en_list()).difference(set(assetInfoWireEnlist())): continue else: if name in type_info_en_list(): continue before_field[name] = '' after_field[name] = param[name] operInfo['before_field'] = before_field operInfo['after_field'] = after_field operInfo['asset_id'] = param['asset_id'] operInfo['oper_type'] = '批量入库' operInfo['operator'] = rtx_id operInfo['text'] = '' operInfo['oper_time'] = get_now_date() operInfos.append(operInfo) OperationInfoDal().add_batch_oper_infos(operInfos)