openpyxl批量格式化Excel报表：标题蓝底加粗、边框、自动列宽与冻结首行
公司要求所有报表统一格式：标题行加粗、蓝色背景、字号12、单元格加边框。每次导出数据都是裸表，手动调整50份表格至少4小时。用Python+openpyxl只需10秒。技术原理
pandas擅长数据处理不擅长格式控制，openpyxl直接操作Excel格式属性。核心对象：Font(字体)、PatternFill(填充)、Border(边框)、Alignment(对齐)、column_dimensions(列宽)、freeze_panes(冻结行)。工作流程：加载.xlsx → 应用格式 → 保存。
环境准备
pip install openpyxl
完整代码
import os
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
def format_excel_sheet(input_file, output_file=None):
if output_file is None:
base_name = os.path.splitext(input_file)
output_file = f"{base_name}_已格式化.xlsx"
wb = load_workbook(input_file)
ws = wb.active
# 定义样式
title_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
title_fill = PatternFill(start_color='4472C4', fill_type='solid')
data_font = Font(name='微软雅黑', size=11)
thin_border = Border(
left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin')
)
center_align = Alignment(horizontal='center', vertical='center')
left_align = Alignment(horizontal='left', vertical='center')
max_row = ws.max_row
max_col = ws.max_column
if max_row < 1:
print(f"警告: {input_file} 没有数据")
return
# 格式化标题行
for col in range(1, max_col + 1):
cell = ws.cell(row=1, column=col)
cell.font = title_font
cell.fill = title_fill
cell.border = thin_border
cell.alignment = center_align
# 格式化数据行
for row in range(2, max_row + 1):
for col in range(1, max_col + 1):
cell = ws.cell(row=row, column=col)
cell.font = data_font
cell.border = thin_border
if isinstance(cell.value, (int, float)):
cell.alignment = center_align
else:
cell.alignment = left_align
# 自动列宽
for col in range(1, max_col + 1):
max_length = 0
column_letter = ws.cell(row=1, column=col).column_letter
for row in range(1, max_row + 1):
cell_value = ws.cell(row=row, column=col).value
if cell_value:
cell_length = len(str(cell_value))
if cell_length > max_length:
max_length = cell_length
adjusted_width = min(max(max_length + 2, 10), 30)
ws.column_dimensions.width = adjusted_width
# 冻结首行
ws.freeze_panes = 'A2'
wb.save(output_file)
print(f"格式化完成: {output_file}")
def batch_format_excel_files(folder_path):
count = 0
for filename in os.listdir(folder_path):
if filename.endswith('.xlsx') and '已格式化' not in filename:
file_path = os.path.join(folder_path, filename)
format_excel_sheet(file_path)
count += 1
print(f"\n批量格式化完成！共处理 {count} 个文件")
if __name__ == "__main__":
# 单个文件: format_excel_sheet("原始数据.xlsx")
batch_format_excel_files(r"D:\待格式化报表")
代码逐行解析
样式对象定义
Font参数：name（字体名，如微软雅黑、Arial）、size（字号）、bold（加粗True/False）、italic（斜体）、color（HEX颜色码，如FFFFFF白色）。
背景色填充
PatternFill(start_color='4472C4', fill_type='solid')，其中start_color为HEX色值，fill_type='solid'表示纯色。常用颜色：4472C4（Office蓝）、C00000（红）、70AD47（绿）、FFC000（橙）、E2EFDA（浅绿）。
边框设置
Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))，Side的style可选thin、medium、thick、double、dashed、dotted。
自动列宽计算
adjusted_width = min(max(max_length + 2, 10), 30)：最大值加2余量，最小10，最大30防止超长文本撑爆列。
冻结首行
ws.freeze_panes = 'A2'：滚动数据时标题行始终保持可见。
进阶技巧
条件格式：自动高亮异常值
from openpyxl.formatting.rule import CellIsRule
ws.conditional_formatting.add(
f'B2:B{max_row}',
CellIsRule(
operator='lessThan',
formula=['0'],
fill=PatternFill(start_color='FFC7CE', fill_type='solid')
)
)
高亮所有小于0的数值（红色背景）。
隔行变色（斑马纹）
light_fill = PatternFill(start_color='F2F2F2', fill_type='solid')
for row in range(2, max_row + 1):
if row % 2 == 0:
for col in range(1, max_col + 1):
ws.cell(row=row, column=col).fill = light_fill
偶数行填充浅灰色增强可读性。
从模板复制格式
import shutil
shutil.copy("公司标准模板.xlsx", output_file)
wb = load_workbook(output_file)
ws = wb.active
for row_idx, row_data in enumerate(data_rows, start=2):
for col_idx, value in enumerate(row_data, start=1):
ws.cell(row=row_idx, column=col_idx, value=value)
先复制带格式的空白模板，再填入数据。
常见问题
Q1：保存时报错ValueError: Max value is 255
原因：openpyxl对旧版.xls格式支持有限。解决：确保文件是.xlsx格式，若为.xls则用Excel另存为.xlsx。
Q2：中文显示为方框（乱码）
原因：系统缺少指定字体。改用通用字体如 'Arial Unicode MS' 或 'SimSun'（宋体）。
Q3：如何设置行高？
# 单独设置第一行
ws.row_dimensions.height = 25
# 批量设置所有行
for row in range(1, max_row + 1):
ws.row_dimensions.height = 20
总结
格式需求与openpyxl对应：字体->Font(name,size,bold,color)；背景色->PatternFill(start_color,fill_type)；边框->Border(Side(style))；对齐->Alignment(horizontal,vertical)；列宽->column_dimensions[].width；行高->row_dimensions[].height；冻结窗格->freeze_panes='A2'。
通过脚本自动化控制Excel格式，配合批量处理函数可快速完成大量报表格式化任务。
Re: openpyxl批量格式化Excel报表：标题蓝底加粗、边框、自动列宽与冻结首行
这个脚本很实用，解决了手工调格式的痛点。标题蓝底加粗、边框、自动列宽、冻结首行这些常规格式化需求都覆盖了，代码结构也清晰，直接就可以拿过来改路径用。 有个小建议：如果报表里存在合并单元格，目前代码按行遍历可能会报错或漏改，可以考虑加个异常处理或者用 `merged_cells.ranges` 检查一下。另外，是否需要支持多工作表（比如循环所有 `ws`）或者处理 `.xls` 旧格式？不过针对公司固定格式的 `.xlsx` 任务，这套逻辑已经足够高效了。 感谢分享，准备存下来下次批量出报表时试试。Re: openpyxl批量格式化Excel报表：标题蓝底加粗、边框、自动列宽与冻结首行
这个脚本挺实用的，正好解决了手动调格式的痛点。我用pandas导出数据时也常遇到格式问题，试过改样式但总不如openpyxl灵活。代码里的自动列宽和冻结首行很贴心，加边框和标题蓝底也符合大多数公司的要求。想问一下，如果有一列全是姓名，但长度不一时，自动列宽会不会导致某些单元格显示不全？另外，要是数据有合并单元格，这个脚本会不会出错？Re: openpyxl批量格式化Excel报表：标题蓝底加粗、边框、自动列宽与冻结首行
这个脚本太实用了！之前手动调格式调得崩溃，尤其50份表格调完眼睛都花了。您这个方案直接省下大量重复劳动，而且代码结构很清晰，样式定义一目了然。特别是自动列宽和冻结首行的处理，正好是我们报表常用的要求。 想问一下，如果有些列的标题不是单纯的文字（比如带换行符的），自动列宽那块的长度计算会不会有偏差？另外，如果后续扩展需求（比如给某些特定数据行加条件格式），直接在循环里加判断应该也不难吧？总之感谢分享，已经准备收藏代码改用到自己项目里了！
页:
[1]