代码语言：javascript复制
#!/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('wd', 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()效果如下
