excel的格式如下
python代码如下,这里最难的就是合并单元格的处理
import xlrd
import json
excel_obj = xlrd.open_workbook("test.xlsx")
sheet_name = excel_obj.sheet_names()[0]
sheet_obj = excel_obj.sheet_by_index(0)
hadoop_dict = {
"services": [
"AMBARI_METRICS",
"HBASE",
"HDFS",
"HIVE",
"KAFKA",
"MAPREDUCE2",
"PIG",
"SLIDER",
"SMARTSENSE",
"SPARK2",
"STORM",
"TEZ",
"YARN",
"ZOOKEEPER"
],
"pm_group" :[],
"host_groups":[]
}
host = {}
vm = {}
# components_list = []
r_num = sheet_obj.nrows
c_num = sheet_obj.ncols
merge_cell_list = sheet_obj.merged_cells
# for i in range(r_num):
# if sheet_obj.cell_value(i,c_num-1):
# components_list.append(sheet_obj.cell_value(i,c_num-1))
# 获取最后一列的所有数据
for i in merge_cell_list:
if i[2] == 0:
host[sheet_obj.cell_value(i[0],i[2])] = [i[0],i[1],i[2],i[3]]
# 存放所有合并的单元格
for k,v in host.items():
host_dict = {}
print(sheet_obj.cell_value(v[0],1),sheet_obj.cell_value(v[0],2),sep="---->")
# 获取主机的ip地址
pm_ip = sheet_obj.cell_value(v[0],2)
print(pm_ip,"物理机地址")
print(sheet_obj.cell_value(v[0] + 1,1),sheet_obj.cell_value(v[0] + 1,2),sep="---->")
# 获取主机的主机名
pm_name = sheet_obj.cell_value(v[0] + 1,2)
host_dict["ip"] = pm_ip
host_dict["hostname"] = pm_name
host_dict["vms"] = []
for vms_cell in merge_cell_list:
vm_dict = {}
vm_components_dict = {}
if vms_cell[1] <= host[k][1] and vms_cell[2] == 2 and vms_cell[0] > host[k][0] + 1:
print(sheet_obj.cell_value(vms_cell[0],2))
# 获取虚拟机的名称
print(sheet_obj.cell_value(vms_cell[0],vms_cell[2] + 1))
# 获取虚拟机的ip的k
print(sheet_obj.cell_value(vms_cell[0],vms_cell[2] + 2))
# 获取虚拟机的ip地址
vm_ip = sheet_obj.cell_value(vms_cell[0], vms_cell[2] + 2)
print(sheet_obj.cell_value(vms_cell[0] + 1, vms_cell[2] + 1))
# 获取虚拟机的虚拟机名称的k
print(sheet_obj.cell_value(vms_cell[0] + 1, vms_cell[2] + 2))
# 获取虚拟机的名字的值
vm_name = sheet_obj.cell_value(vms_cell[0] + 1, vms_cell[2] + 2)
vm_name = sheet_obj.cell_value(vms_cell[0] + 1, vms_cell[2] + 2)
vm_dict = {
"hostname":vm_name,
}
vm_components_dict["ip"] = vm_ip
vm_components_dict["hostname"] = vm_name
vm_components_dict["components"] = []
host_dict["vms"].append(vm_dict)
vmcomponents_location_start = vms_cell[0] + 2
vmcomponents_location_end = vms_cell[1]
# print(vmcomponents_location_start,vmcomponents_location_end,"我是大傻逼")
vm_components_info_list = []
for i in range(vmcomponents_location_start,vmcomponents_location_end):
temp_components = sheet_obj.cell_value(i,c_num-1)
vm_components_info_list.append(temp_components)
for component in vm_components_info_list:
temp_dict = {}
temp_dict["name"] = component
vm_components_dict["components"].append(temp_dict)
hadoop_dict["host_groups"].append(vm_components_dict)
# 获取每个虚拟机的components信息
hadoop_dict["pm_group"].append(host_dict)
import json
file_name = "journalnode_".upper() + "test_journalnode_case_1" + "." + "json"
my_file_obj = open(file_name,"w")
json.dump(hadoop_dict,my_file_obj,indent=4)
my_file_obj.close()
最后按照要求生成制定格式的json文件
{
"services": [
"AMBARI_METRICS",
"HBASE",
"HDFS",
"HIVE",
"KAFKA",
"MAPREDUCE2",
"PIG",
"SLIDER",
"SMARTSENSE",
"SPARK2",
"STORM",
"TEZ",
"YARN",
"ZOOKEEPER"
],
"pm_group": [
{
"ip": "1.1.1.1",
"hostname": "host1",
"vms": [
{
"hostname": "vm1"
},
{
"hostname": "vm2"
},
{
"hostname": "vm3"
}
]
},
{
"ip": "1.1.1.2",
"hostname": "host2",
"vms": [
{
"hostname": "vm4"
},
{
"hostname": "vm5"
}
]
},
{
"ip": "1.1.1.3",
"hostname": "host3",
"vms": [
{
"hostname": "vm6"
}
]
}
],
"host_groups": [
{
"ip": "192.168.1.1",
"hostname": "vm1",
"components": [
{
"name": "ZOOKEEPER_SERVER"
},
{
"name": "JOURNALNODE"
},
{
"name": "NODEMANAGER"
}
]
},
{
"ip": "192.168.1.2",
"hostname": "vm2",
"components": [
{
"name": "HBASE_REGIONSERVER"
},
{
"name": "ZKFC"
}
]
},
{
"ip": "192.168.1.3",
"hostname": "vm3",
"components": [
{
"name": "HBASE_CLIENT"
}
]
},
{
"ip": "192.168.1.4",
"hostname": "vm4",
"components": [
{
"name": "ZOOKEEPER_SERVER"
},
{
"name": "JOURNALNODE"
},
{
"name": "NODEMANAGER"
}
]
},
{
"ip": "192.168.1.5",
"hostname": "vm5",
"components": [
{
"name": "HBASE_REGIONSERVER"
},
{
"name": "ZKFC"
}
]
},
{
"ip": "192.168.1.6",
"hostname": "vm6",
"components": [
{
"name": "ZOOKEEPER_SERVER"
},
{
"name": "JOURNALNODE"
},
{
"name": "NODEMANAGER"
}
]
}
]
}