131 lines
5.2 KiB
Python
131 lines
5.2 KiB
Python
import yaml
|
||
import pandas as pd
|
||
from datetime import datetime, timedelta
|
||
from openpyxl.styles import Border, Side, Font, PatternFill
|
||
from utils.logger import logger
|
||
from config.settings import EXCEL_OUTPUT_PATH, YAML_OUTPUT_PATH, SNAPSHOT_RETENTION_DAYS
|
||
from core.get_vm_snapshots import get_all_vms
|
||
|
||
# 输出数据到 Excel 文件
|
||
def create_excel_report(vms):
|
||
vm_data, snapshot_data, old_snapshots = [], [], []
|
||
for vm in vms:
|
||
vm_data.append({
|
||
'NodeHost': vm['NodeHost'],
|
||
'VMName': vm['name'],
|
||
'MOID': vm['moId'],
|
||
'PowerState': vm['powerState'],
|
||
'System': vm['system'],
|
||
'IPAddress': vm['ipAddress'],
|
||
'HostName': vm['hostName'],
|
||
'CurrentSnapshotID': vm.get('currentSnapshotId'),
|
||
'DiskSpace/GB': vm['diskSpaceGB'],
|
||
'createDate': vm['createDate'], # 虚拟机的创建时间
|
||
'bootTime': vm['bootTime'], # 虚拟机上次启动的时间
|
||
'Host': vm['Host'],
|
||
'VMPath': vm['vmPath']
|
||
})
|
||
|
||
if vm['snapshots']:
|
||
for snapshot in vm['snapshots']:
|
||
collect_snapshot_data(snapshot, vm, snapshot_data, old_snapshots)
|
||
|
||
vm_df = pd.DataFrame(vm_data)
|
||
snapshot_df = pd.DataFrame(snapshot_data)
|
||
logger.info(f"总共有 {len(snapshot_data)} 个快照")
|
||
with pd.ExcelWriter(EXCEL_OUTPUT_PATH, engine='openpyxl') as writer:
|
||
vm_df.to_excel(writer, sheet_name='VMs', index=False)
|
||
snapshot_df.to_excel(writer, sheet_name='Snapshots', index=False)
|
||
|
||
workbook = writer.book
|
||
style_sheet(workbook['VMs'])
|
||
# style_sheet(workbook['Snapshots'], snapshot_df['is_old'].tolist())
|
||
# 判断 DataFrame 是否有数据，没有数据则跳过，有数据则写入 excel 文件
|
||
if 'is_old' in snapshot_df.columns and snapshot_df['is_old'].tolist():
|
||
style_sheet(workbook['Snapshots'], snapshot_df['is_old'].tolist())
|
||
|
||
logger.debug(f"Excel 文件已生成: {EXCEL_OUTPUT_PATH}")
|
||
# 返回旧快照的数据，用于生成 Yaml 数据文件
|
||
return old_snapshots
|
||
|
||
|
||
def collect_snapshot_data(snapshot, vm, snapshot_data, old_snapshots):
|
||
""" 递归快照数据用于 Excel，并收集旧快照 """
|
||
create_time = datetime.strptime(snapshot['createTime'], '%Y-%m-%d %H:%M:%S')
|
||
is_old = create_time < (datetime.now() - timedelta(days=SNAPSHOT_RETENTION_DAYS))
|
||
|
||
snapshot_info = {
|
||
'NodeHost': vm['NodeHost'],
|
||
'VMName': vm['name'],
|
||
'Snapshot Name': snapshot['name'],
|
||
'Description': snapshot['description'],
|
||
'CreateTime': snapshot['createTime'],
|
||
'State': snapshot['state'],
|
||
'ID': snapshot['id'],
|
||
'MOID': snapshot['moId'],
|
||
'Quiesced': snapshot['quiesced'],
|
||
'is_old': is_old
|
||
}
|
||
|
||
# 如果是旧快照，添加到旧快照列表
|
||
if is_old:
|
||
old_snapshots.append(snapshot_info)
|
||
|
||
snapshot_data.append(snapshot_info)
|
||
|
||
for child in snapshot['children']:
|
||
collect_snapshot_data(child, vm, snapshot_data, old_snapshots)
|
||
|
||
|
||
"""设置表格样式"""
|
||
def style_sheet(sheet, is_old_data=None):
|
||
"""设置工作表样式：列宽、边框、加粗标题、冻结首行和设置背景颜色"""
|
||
# 定义边框样式
|
||
thin = Side(border_style="thin", color="000000")
|
||
border = Border(left=thin, right=thin, top=thin, bottom=thin)
|
||
|
||
# 设置列宽和边框
|
||
for column in sheet.columns:
|
||
max_length = 0
|
||
column_letter = column[0].column_letter
|
||
|
||
for cell in column:
|
||
try:
|
||
if len(str(cell.value)) > max_length:
|
||
max_length = len(str(cell.value))
|
||
cell.border = border # 为每个单元格添加边框
|
||
except Exception as e:
|
||
pass
|
||
|
||
adjusted_width = (max_length + 2)
|
||
sheet.column_dimensions[column_letter].width = adjusted_width
|
||
|
||
# 加粗标题并冻结首行
|
||
for cell in sheet[1]: # 加粗标题
|
||
cell.font = Font(bold=True)
|
||
sheet.freeze_panes = 'A2' # 冻结首行
|
||
|
||
# 设置背景颜色
|
||
if is_old_data is not None:
|
||
for row in range(2, len(is_old_data) + 2):
|
||
if is_old_data[row - 2]:
|
||
for col in range(1, sheet.max_column + 1): # 使用 sheet.max_column
|
||
cell = sheet.cell(row=row, column=col)
|
||
cell.fill = PatternFill(start_color='ADD8E6', end_color='ADD8E6', fill_type='solid')
|
||
|
||
|
||
# 输出待删除的旧快照到 YAML 文件
|
||
def export_yaml(old_snapshots):
|
||
logger.info(f"可删除的快照有 {len(old_snapshots)} 个")
|
||
# 将旧快照信息存储到 YAML 文件
|
||
with open(YAML_OUTPUT_PATH, 'w', encoding='utf-8') as yaml_file:
|
||
# allow_unicode：输出 Unicode 字符（中文等），allow_unicode：使用块样式（多行缩进），sort_keys：不按键名排序，保留原始插入顺序
|
||
yaml.dump(old_snapshots, yaml_file, allow_unicode=True, default_flow_style=False, sort_keys=False)
|
||
logger.debug(f"YAML 文件已生成: {YAML_OUTPUT_PATH}")
|
||
|
||
if __name__ == '__main__':
|
||
vms = get_all_vms() # 导出 excel 和 yaml 文件，需要先获取虚拟机信息
|
||
old_snapshots = create_excel_report(vms) # 生成 Excel 报告并获取旧快照
|
||
export_yaml(old_snapshots)
|
||
# print(old_snapshots)
|