安装
pip install openpyxl
使用
alphabet = '0ABCDEFGHIJKLMNOPQRSTUVWXYZ'
max_rows, max_cols = df.shape
area = f'A1:{alphabet[max_cols]}{max_rows + 1}'
fpath = os.path.join(path, f'Apollo对比-{service_name}.xlsx')
df.to_excel(fpath, index=False)
wb = load_workbook(fpath)
ws = wb[wb.sheetnames[0]]
ws.column_dimensions['A'].width = 40.0
env_col_names = [f'{alphabet[i]}' for i in range(1, max_cols) if ws[f'{alphabet[i]}1'].value in env_names]
env_col_names += [f'{alphabet[i]}' for i in range(1, max_cols) if '说明' in (ws[f'{alphabet[i]}1'].value or '') ]
for col_name in env_col_names:
ws.column_dimensions[col_name].width = 60.0
red_fill = PatternFill(bgColor="FFC7CE")
dxf = DifferentialStyle(fill=red_fill)
compare_col_names = [f'{alphabet[i]}' for i in range(1, max_cols) if '是否一致' in (ws[f'{alphabet[i]}1'].value or '') ]
for col_name in compare_col_names:
r = Rule(type="expression", dxf=dxf, stopIfTrue=True)
r.formula = [f'${col_name}1="FALSE"']
ws.conditional_formatting.add(f"{col_name}1:{col_name}{max_rows + 10}", r)
ws.auto_filter.ref = area
title_cols = ['key'] + env_names + ['环境相关']
ws.auto_filter.add_filter_column(0, title_cols)
ws.freeze_panes = 'A2'
black_fill = PatternFill(fgColor="222222")
title_font = Font(name='Microsoft YaHei UI',
size=12,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='000000')
font = Font(name='Microsoft YaHei UI',
size=10,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='000000')
for i in range(2, max_rows + 2):
ws[f'A{i}'].fill=PatternFill(fill_type='solid',fgColor="dce6f1")
ws[f'{env_col_names[0]}{i}'].fill=PatternFill(fill_type='solid',fgColor="ebf1de")
ws[f'{env_col_names[-1]}{i}'].fill=PatternFill(fill_type='solid',fgColor="fde9d9")
ws.cell(row=i, column=max_cols).fill=PatternFill(fill_type='solid',fgColor="e4dfec")
def my_border(t_border, b_border, l_border, r_border):
border = Border(top=Side(border_style=t_border, color=colors.BLACK),
bottom=Side(border_style=b_border, color=colors.BLACK),
left=Side(border_style=l_border, color=colors.BLACK),
right=Side(border_style=r_border, color=colors.BLACK))
return border
def format_border(area):
for row in tuple(ws[area]):
for cell in row:
cell.border = my_border('thin', 'thin', 'thin', 'thin')
format_border(area)