使用openpyxl操作Excel

安装

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]]                  # 打开第一个sheet
ws.column_dimensions['A'].width = 40.0     # 调整列A宽
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: # 环境key值、说明列宽
    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") # key 20% blue
    ws[f'{env_col_names[0]}{i}'].fill=PatternFill(fill_type='solid',fgColor="ebf1de") # 第一个环境名称 20% green
    ws[f'{env_col_names[-1]}{i}'].fill=PatternFill(fill_type='solid',fgColor="fde9d9") # 说明 20% yellow
    ws.cell(row=i, column=max_cols).fill=PatternFill(fill_type='solid',fgColor="e4dfec") # 环境相关 20% purple
    
# 边框
#定义边框样式
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)
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值