利用xlrd模块读取excel利用json模块生成相应的json文件的脚本详解编程语言

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() 

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