利用xlrd模块读取excel利用json模块生成相应的json文件的脚本

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