在给客户做项目的过程中可能需要交付
excel文档，每次都要花费一定的时间来修改格式，感觉略烦，故而准备直接用python的openpyxl库来修改excel的格式，省心省力，瞬间世界清爽 QaQ本文相关代码和文章已同步至
github和微信公众号，各位看官按需取用。代码开源地址：
https://github.com/itlubber/openpyxl-excel-style-template博客网站推文：
https://itlubber.art/archives/openpyxl-excel-style-template
背景简介
本文旨在提供一种使用 python 的 openpyxl 库自动渲染 excel 文件的方法，直接使用或修改部分代码后使用都可，看个人需要按需取用。
项目结构
代码结构
format_bins: 特定场景下的特定需求下定制函数，可忽略
feature_bin_stats: 特定场景下的特定需求下定制函数，可忽略
plot_bin: 特定场景下的特定需求下定制函数，可忽略
cal_psi: 特定场景下的特定需求下定制函数，可忽略
itlubber_border: 边框格式设置函数
render_excel: excel 样式渲染函数
方法使用
output_excel_name = "指标有效性验证.xlsx"
output_sheet_name = "指标有效性"
merge_row_number = []
render_excel(output_excel_name, sheet_name=output_sheet_name, conditional_columns=["J", "N"], freeze="D2", merge_rows=merge_row_number, percent_columns=[5, 7, 9, 10])
render_excel("变量字典及字段解释.xlsx")
详细说明
def render_excel(excel_name, sheet_name=None, conditional_columns=[], freeze=None, merge_rows=[], percent_columns=[], theme_color="2639E9", conditional_color="9980FA", font="楷体", fontsize=10, max_column_width=50):
"""openpyxl对excel文件样式进行渲染
对excel的首行填充主题色，字体设置白色，文本居中，外边框主题色，内边框白色
对excel除首行之外的内容行，填充白色，字体设置为黑色，同时设置边框颜色
可以冻结单元格、条件格式填充等
Parameters
----------
excel_name : str
需要渲染的 excel 文件名称
sheet_name : str or list
需要渲染的 sheet 名称，可以不填，默认全部渲染
conditional_columns : list
需要填充条件格式的列
freeze: str
需要冻结的单元格
merge_rows: list
需要突出显示的单元格序号，从小到大
percent_columns: list
需要显示百分数的列
theme_color: str
主题色
conditional_color: str
条件格式填充的颜色
font: str
需要使用的字体
fontsize: int
字体大小
max_column_width: float
最大的列宽
Returns
-------
渲染好的 excel 文件
"""
workbook = load_workbook(excel_name)
if sheet_name and isinstance(sheet_name, str):
sheet_names = [sheet_name]
else:
sheet_names = workbook.get_sheet_names()
for sheet_name in sheet_names:
worksheet = workbook.get_sheet_by_name(sheet_name)
def add_conditional_formatting(column, theme_color="FDA7DF"):
worksheet.conditional_formatting.add(f'{column}2:{column}{worksheet.max_row}', DataBarRule(start_type='min', end_type='max', color=theme_color))
for conditional_column in conditional_columns:
add_conditional_formatting(f"{conditional_column}", theme_color=conditional_color)
for row_index, row in enumerate(worksheet.rows, start=1):
if row_index == 1:
for col_index, cell in enumerate(row, start=1):
cell.font = Font(size=fontsize, name=font, color="FFFFFF", bold=True)
cell.fill = PatternFill(fill_type="solid", start_color=theme_color)
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
if col_index == 1:
cell.border = itlubber_border(["medium", "thin", "medium", "medium"], [theme_color, "FFFFFF", theme_color, theme_color])
elif col_index == len(row):
cell.border = itlubber_border(["thin", "medium", "medium", "medium"], ["FFFFFF", theme_color, theme_color, theme_color])
else:
cell.border = itlubber_border(["thin", "thin", "medium", "medium"], ["FFFFFF", "FFFFFF", theme_color, theme_color])
else:
for col_index, cell in enumerate(row, start=1):
cell.font = Font(size=fontsize, name=font, color="000000")
cell.fill = PatternFill(fill_type="solid", start_color="FFFFFF")
cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
if col_index in percent_columns:
# cell.alignment = Alignment(horizontal='right', vertical='center', wrap_text=False)
cell.number_format = "0.00%"
else:
pass
# cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=False)
if row_index == worksheet.max_row:
if col_index == 1:
cell.border = itlubber_border(["medium", "thin", "medium"], [theme_color, "FFFFFF", theme_color])
elif col_index == len(row):
cell.border = itlubber_border(["thin", "medium", "medium"], ["FFFFFF", theme_color, theme_color])
else:
cell.border = itlubber_border(["thin", "thin", "medium"], ["FFFFFF", "FFFFFF", theme_color])
else:
if merge_rows in [[], None] or (row_index - 1 in merge_rows):
if col_index == 1:
cell.border = itlubber_border(["medium", "thin", "thin"], [theme_color, "FFFFFF", theme_color])
elif col_index == len(row):
cell.border = itlubber_border(["thin", "medium", "thin"], ["FFFFFF", theme_color, theme_color])
else:
cell.border = itlubber_border(["thin", "thin", "thin"], ["FFFFFF", "FFFFFF", theme_color])
else:
if col_index == 1:
cell.border = itlubber_border(["medium", "thin", "thin"], [theme_color, "FFFFFF", "FFFFFF"])
elif col_index == len(row):
cell.border = itlubber_border(["thin", "medium", "thin"], ["FFFFFF", theme_color, "FFFFFF"])
else:
cell.border = itlubber_border(["thin", "thin", "thin"], ["FFFFFF", "FFFFFF", "FFFFFF"])
feature_table = pd.read_excel(
excel_name, sheet_name=sheet_name, engine="openpyxl"
)
feature_table_len_max = feature_table.apply(lambda x: [(len(str(i).encode('utf-8')) - len(str(i))) / 2 + len(str(i)) for i in x]).max()
for i in feature_table.columns:
# 列的字母
j = list(feature_table.columns)
column_letter = [chr(j.index(i) + 65) if j.index(i) <= 25 else 'A' + chr(j.index(i) - 26 + 65) ][0]
# 列的宽度
columns_length = (len(str(i).encode('utf-8')) - len(str(i)))/2 + len(str(i))
data_max_length = feature_table_len_max[i]
column_width = [data_max_length if columns_length < data_max_length else columns_length][0]
column_width = [column_width if column_width <= max_column_width else max_column_width][0] + 3
# 更改列的宽度
worksheet.column_dimensions['{}'.format(column_letter)].width = column_width
if freeze:
worksheet.freeze_panes = freeze
workbook.save(excel_name)
workbook.close()
渲染结果
默认效果
合并单元格的渲染效果
评论区