python导入excel表格

使用pandas模块导入excel，将数据转换为json

from pandas import read_excel


class ReadExecl:
    @classmethod
    def read(cls, file_name):
        df = read_excel(file_name, sheet_name=0)
        df.fillna('', inplace=True)
        json_data = list()
        rows = [
            "idc", "floor", "rack", "position", "service_type", "onwer", "brand", "model", "hostname", "ip", "manage_ip", "sn_number", "desc", "tags"
        ] # 字段名
        for i in df.index.values:
            row_data = df.loc[i, rows].to_dict() #获取每行数据
            json_data.append(row_data)
        return json_data

    @classmethod
    def format_data(cls):
	"""改字段名称，可以不改"""
        json_data = cls.read(file)
        serializers = list()
        for row in json_data:
            format_data = dict(
                hostname=row['hostname'],
                ip=row['ip'],
                manage_ip=row['manage_ip'],
                desc=row['desc'],
                position=row['position'],
                rack=row['rack'],
                idc=row['idc'],
                sn_number=row['sn_number'],
                model=row['model'],
                brand=row['brand'],
                tags=row['tags']
            )
            serializers.append(format_data)
        return serializers


if __name__ == "__main__":
	file = 'host.xlsx'
	result = ReadExecl.read(file)
	print(reslut)

输出结果：

[{'hostname': 'H-QT-Esxi110', 'ip': '10.1.25.110', 'manage_ip': '10.1.110.10', 'desc': 'VM-Test', 'position': '22-25', 'rack': 'I14', 'idc': 'GDS-SH', 'sn_number': 'CN12345', 'model': 'DL580', 'brand': 'HP', 'tags': '测试ESXI主'},
{'hostname': 'H-QT-Esxi111', 'ip': '10.1.25.111', 'manage_ip': '10.1.110.11', 'desc': 'VM-Test', 'position': '22-25', 'rack': 'I13', 'idc': 'GDS-WGQ', 'sn_number': 'CN12346', 'model': 'DL580', 'brand': 'HP', 'tags': '测试ESXI主'},
{'hostname': 'H-QT-Esxi112', 'ip': '10.1.25.112', 'manage_ip': '10.1.110.12', 'desc': 'VM-Test', 'position': '26-29', 'rack': 'I13', 'idc': 'GDS-WGQ', 'sn_number': 'CN12347', 'model': 'DL580', 'brand': 'HP', 'tags': '测试ESXI主'},
{'hostname': 'H-QT-Esxi113', 'ip': '10.1.25.113', 'manage_ip': '10.1.110.13', 'desc': 'VM-Test', 'position': '32-35', 'rack': 'I12', 'idc': 'GDS-WGQ', 'sn_number': 'CN12348', 'model': 'DL580', 'brand': 'HP', 'tags': '测试ESXI主'},
{'hostname': 'H-QT-Esxi114', 'ip': '10.1.25.114', 'manage_ip': '10.1.110.14', 'desc': 'VM-Test', 'position': '36-39', 'rack': 'I12', 'idc': 'GDS-WGQ', 'sn_number': 'CN12349', 'model': 'DL580', 'brand': 'HP', 'tags': '测试ESXI主'},
{'hostname': 'H-QT-Esxi115', 'ip': '10.1.25.115', 'manage_ip': '10.1.110.15', 'desc': 'VM-Test', 'position': '40-43', 'rack': 'I12', 'idc': 'GDS-WGQ', 'sn_number': 'CN12350', 'model': 'DL580', 'brand': 'HP', 'tags': '测试ESXI主'}]