Python实现将阿里实例信息绘制成表格

#!/usr/bin/env python
#coding=utf-8

'''获取阿里云实例基础信息(实例名称、Cpu、内存、磁盘、出网带宽、运行状态), 绘制成excel表格'''

import os, re, json
from aliyunsdkcore.client import AcsClient
from aliyunsdkcore.acs_exception.exceptions import ClientException
from aliyunsdkcore.acs_exception.exceptions import ServerException
from aliyunsdkecs.request.v20140526.DescribeInstancesRequest import DescribeInstancesRequest
from aliyunsdkecs.request.v20140526.DescribeDisksRequest import DescribeDisksRequest
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment


def get_page():
    '''获取实例总数、页大小、页数'''
    request = DescribeInstancesRequest()
    request.set_accept_format('json')

    response = client.do_action_with_exception(request)
    dict_response = json.loads(response)

    totalcount = dict_response["TotalCount"]
    pagesize = dict_response["PageSize"]
    pagenums = totalcount // pagesize + 2
   
    return request, pagenums


def create_workbook():
    '''创建工作表'''
    wb = Workbook()
    sheet = wb.active
    # 设置F列宽
    sheet.column_dimensions['F'].width = 35.0

    return wb, sheet

def get_disk(instance_id):
    '''获取相关实例的磁盘信息'''
    request = DescribeDisksRequest()
    request.set_accept_format('json')
    request.set_InstanceId(instance_id)

    response = client.do_action_with_exception(request)
    dict_response = json.loads(response)

    disk_list = []
    for disk in dict_response["Disks"]["Disk"]:
        device = disk["Device"]  # 挂载设备名称
        size = disk["Size"]  # 大小
        if len(str(disk["Size"])) == 4:
            size = str(size // 1024) + "TB "
        size = str(size) + "GB "
        category = disk["Category"]  # 磁盘设备类型
        data = device + " " + size + category
        disk_list.append(data)
    
    return "\n".join(disk_list)


def get_data():
    '''循环页数获取数据'''
    request, pagenums = get_page()
    wb, sheet = create_workbook()

    for pagenum in range(1, pagenums):
        request.set_PageNumber(pagenum)
        response = client.do_action_with_exception(request)
        dict_response = json.loads(response)

        for instance in dict_response["Instances"]["Instance"]:
            instance_name = instance["InstanceName"]  # 实例名称
            instance_id = instance["InstanceId"]  # 实例id
            disk = get_disk(instance_id)
            try:
                public_ip = instance["PublicIpAddress"]["IpAddress"][0]  # 公网ip
            except IndexError:
                public_ip = instance["EipAddress"]["IpAddress"]  # 弹性ip
                if not public_ip:
                    public_ip = '无'
            private_ip = instance["VpcAttributes"]["PrivateIpAddress"]["IpAddress"][0]  # 私有ip
            cpu = str(instance["Cpu"]) + " vCPU"  # Cpu核数
            memory = str(instance["Memory"] // 1024) + " GB"  # 内存
            bandwidth = str(instance["InternetMaxBandwidthOut"]) + " Mbps"  # 出网带宽
            status = instance["Status"]  # 实例状态

            sheet.append([instance_name, public_ip, private_ip, cpu, memory, disk, bandwidth, status])

    # Excel自动换行
    rows = sheet.max_row
    for row in range(1, rows + 1):
        column = "F" + str(row)
        sheet[column].alignment = Alignment(wrapText=True)

    wb.save('inventory_tmp.xlsx')
    adjust_column_width()


def adjust_column_width():
    '''自动调整Excel列宽'''
    wb = load_workbook('inventory_tmp.xlsx')
    ws = wb.get_sheet_by_name('Sheet')

    for col in ws.columns:
        max_lenght = 0
        col_name = re.findall('\w\d', str(col[0]))
        col_name = col_name[0]
        col_name = re.findall('\w', str(col_name))[0]
        for cell in col:
            try:
                if len(str(cell.value)) > max_lenght:
                    max_lenght = len(cell.value)
            except:
                pass
        adjusted_width = (max_lenght + 2)
        if col_name != 'F':
            ws.column_dimensions[col_name].width = adjusted_width

    wb.save('inventory.xlsx')
    os.remove('inventory_tmp.xlsx')

client = AcsClient('<accessKeyId>', '<accessSecret>', 'cn-hangzhou')
get_data()