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"
}
]
}
]
}