import pandas as pd
# 定义服务器配置清单工作表数据
data_server_config = [
["序号", "资产编号", "设备种类", "品牌型号", "SN", "部门", "使用情况", "IP", "备注", "详情"],
[1, "11 - 23456", "服务器", "HP xx G9", "xxx", "xx", "在用", "xxx", "", "CPU：2*E7 内存：128G"],
[2, "11 - 56789", "服务器", "HP xx X5", "xxx", "xx", "在用", "xxx", "", "CPU：2*E7 内存：128G"],
]
# 定义新平台工作表数据
data_new_platform = [
["文件存储登录账号", "", "", "", "", "", ""],
["服务项", "内网IP", "原默认端口", "修改后端口", "账户", "密码", ""],
["", "", "", "", "", "", ""],
]
# 定义旧平台工作表数据
data_old_platform = [
["应用服务器登录账号", "", "", "", "", "", ""],
["服务项", "内网IP", "原默认端口", "修改后端口", "账户", "密码", ""],
["", "", "", "", "", "", ""],
]
# 创建 DataFrame
df_server_config = pd.DataFrame(data_server_config[1:], columns=data_server_config[0])
df_new_platform = pd.DataFrame(data_new_platform[1:], columns=data_new_platform[0])
df_old_platform = pd.DataFrame(data_old_platform[1:], columns=data_old_platform[0])
# 创建 Excel 文件并写入数据
output_path = '../../output/file/服务器清单.xlsx'
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
df_server_config.to_excel(writer, sheet_name='服务器配置清单', index=False)
df_new_platform.to_excel(writer, sheet_name='新平台', index=False)
df_old_platform.to_excel(writer, sheet_name='旧平台', index=False)
print("Excel文件已生成")
美化样式
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
# 定义服务器配置清单工作表数据
data_server_config = [
["序号", "资产编号", "设备种类", "品牌型号", "SN", "部门", "使用情况", "IP", "备注", "详情"],
[1, "11 - 23456", "服务器", "HP xx G9", "xxx", "xx", "在用", "xxx", "", "CPU：2*E7 内存：128G"],
[2, "11 - 56789", "服务器", "HP xx X5", "xxx", "xx", "在用", "xxx", "", "CPU：2*E7 内存：128G"],
]
# 定义新平台工作表数据
data_new_platform = [
["文件存储登录账号", "", "", "", "", "", ""],
["服务项", "内网IP", "原默认端口", "修改后端口", "账户", "密码", ""],
["", "", "", "", "", "", ""],
]
# 定义旧平台工作表数据
data_old_platform = [
["应用服务器登录账号", "", "", "", "", "", ""],
["服务项", "内网IP", "原默认端口", "修改后端口", "账户", "密码", ""],
["", "", "", "", "", "", ""],
]
# 创建 DataFrame
df_server_config = pd.DataFrame(data_server_config[1:], columns=data_server_config[0])
df_new_platform = pd.DataFrame(data_new_platform[1:], columns=data_new_platform[0])
df_old_platform = pd.DataFrame(data_old_platform[1:], columns=data_old_platform[0])
# 创建 Excel 文件并写入数据
output_path = '../../output/file/服务器清单.xlsx'
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
df_server_config.to_excel(writer, sheet_name='服务器配置清单', index=False)
df_new_platform.to_excel(writer, sheet_name='新平台', index=False)
df_old_platform.to_excel(writer, sheet_name='旧平台', index=False)
# 加载工作簿以应用样式
workbook = load_workbook(output_path)
# 定义样式
header_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
subtitle_font = Font(name='微软雅黑', size=11, bold=True, color='FFFFFF')
subtitle_fill = PatternFill(start_color='70AD47', end_color='70AD47', fill_type='solid')
subtitle_alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
data_font = Font(name='微软雅黑', size=10)
data_alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
# 为每个工作表应用样式
for sheet_name in workbook.sheetnames:
worksheet = workbook[sheet_name]
# 自动调整列宽
for column in worksheet.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))
except:
pass
# 设置列宽（最大不超过50个字符）
adjusted_width = min(max_length + 2, 50)
worksheet.column_dimensions[column_letter].width = adjusted_width
# 为第一行（标题行）应用特殊样式
for cell in worksheet[1]:
cell.font = header_font
cell.fill = header_fill
cell.alignment = header_alignment
cell.border = border
# 为数据行应用样式
for row in worksheet.iter_rows(min_row=2):
for cell in row:
cell.font = data_font
cell.alignment = data_alignment
cell.border = border
# 如果单元格内容为空，则设置背景色为浅灰色
if cell.value == "" or cell.value is None:
cell.fill = PatternFill(start_color='F2F2F2', end_color='F2F2F2', fill_type='solid')
# 特殊处理新平台和旧平台工作表中的标题行
if sheet_name in ['新平台', '旧平台']:
for row_idx, row_data in enumerate(data_new_platform if sheet_name == '新平台' else data_old_platform):
# 检查是否为标题行（第一个单元格包含特定文本且其他单元格为空）
if (row_data[0] and
("服务器" in row_data[0] or "登录账号" in row_data[0]) and
all(cell == "" for cell in row_data[1:])):
# 合并单元格（从A列到G列）
start_col = 'A'
end_col = get_column_letter(len(row_data))
merge_range = f"{start_col}{row_idx + 1}:{end_col}{row_idx + 1}"
worksheet.merge_cells(merge_range)
# 应用标题样式
cell = worksheet[f'A{row_idx + 1}']
cell.font = subtitle_font
cell.fill = subtitle_fill
cell.alignment = subtitle_alignment
cell.border = border
cell.value = row_data[0] # 确保值正确显示
# 保存美化后的Excel文件
workbook.save(output_path)
print("Excel文件已生成，包含样式设置")
