openpyxl批量格式化Excel报表：标题蓝底加粗、边框、自动列宽与冻结首行

脚本专家

公司要求所有报表统一格式：标题行加粗、蓝色背景、字号12、单元格加边框。每次导出数据都是裸表，手动调整50份表格至少4小时。用Python+openpyxl只需10秒。

技术原理
pandas擅长数据处理不擅长格式控制，openpyxl直接操作Excel格式属性。核心对象：Font(字体)、PatternFill(填充)、Border(边框)、Alignment(对齐)、column_dimensions(列宽)、freeze_panes(冻结行)。工作流程：加载.xlsx → 应用格式 → 保存。

环境准备
pip install openpyxl

完整代码

2. import os
3. from openpyxl import load_workbook
4. from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
6. def format_excel_sheet(input_file, output_file=None):
7.     if output_file is None:
8.         base_name = os.path.splitext(input_file)[0]
9.         output_file = f"{base_name}_已格式化.xlsx"
10.     wb = load_workbook(input_file)
11.     ws = wb.active
12.     # 定义样式
13.     title_font = Font(name='微软雅黑', size=12, bold=True, color='FFFFFF')
14.     title_fill = PatternFill(start_color='4472C4', fill_type='solid')
15.     data_font = Font(name='微软雅黑', size=11)
16.     thin_border = Border(
17.         left=Side(style='thin'),
18.         right=Side(style='thin'),
19.         top=Side(style='thin'),
20.         bottom=Side(style='thin')
21.     )
22.     center_align = Alignment(horizontal='center', vertical='center')
23.     left_align = Alignment(horizontal='left', vertical='center')
24.     max_row = ws.max_row
25.     max_col = ws.max_column
26.     if max_row < 1:
27.         print(f"警告: {input_file} 没有数据")
28.         return
29.     # 格式化标题行
30.     for col in range(1, max_col + 1):
31.         cell = ws.cell(row=1, column=col)
32.         cell.font = title_font
33.         cell.fill = title_fill
34.         cell.border = thin_border
35.         cell.alignment = center_align
36.     # 格式化数据行
37.     for row in range(2, max_row + 1):
38.         for col in range(1, max_col + 1):
39.             cell = ws.cell(row=row, column=col)
40.             cell.font = data_font
41.             cell.border = thin_border
42.             if isinstance(cell.value, (int, float)):
43.                 cell.alignment = center_align
44.             else:
45.                 cell.alignment = left_align
46.     # 自动列宽
47.     for col in range(1, max_col + 1):
48.         max_length = 0
49.         column_letter = ws.cell(row=1, column=col).column_letter
50.         for row in range(1, max_row + 1):
51.             cell_value = ws.cell(row=row, column=col).value
52.             if cell_value:
53.                 cell_length = len(str(cell_value))
54.                 if cell_length > max_length:
55.                     max_length = cell_length
56.         adjusted_width = min(max(max_length + 2, 10), 30)
57.         ws.column_dimensions[column_letter].width = adjusted_width
58.     # 冻结首行
59.     ws.freeze_panes = 'A2'
60.     wb.save(output_file)
61.     print(f"格式化完成: {output_file}")
63. def batch_format_excel_files(folder_path):
64.     count = 0
65.     for filename in os.listdir(folder_path):
66.         if filename.endswith('.xlsx') and '已格式化' not in filename:
67.             file_path = os.path.join(folder_path, filename)
68.             format_excel_sheet(file_path)
69.             count += 1
70.     print(f"\n批量格式化完成！共处理 {count} 个文件")
72. if __name__ == "__main__":
73.     # 单个文件: format_excel_sheet("原始数据.xlsx")
74.     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'：滚动数据时标题行始终保持可见。

进阶技巧

条件格式：自动高亮异常值

2. from openpyxl.formatting.rule import CellIsRule
3. ws.conditional_formatting.add(
4.     f'B2:B{max_row}',
5.     CellIsRule(
6.         operator='lessThan',
7.         formula=['0'],
8.         fill=PatternFill(start_color='FFC7CE', fill_type='solid')
9.     )
10. )

复制代码

高亮所有小于0的数值（红色背景）。

隔行变色（斑马纹）

2. light_fill = PatternFill(start_color='F2F2F2', fill_type='solid')
3. for row in range(2, max_row + 1):
4.     if row % 2 == 0:
5.         for col in range(1, max_col + 1):
6.             ws.cell(row=row, column=col).fill = light_fill

复制代码

偶数行填充浅灰色增强可读性。

从模板复制格式

2. import shutil
3. shutil.copy("公司标准模板.xlsx", output_file)
4. wb = load_workbook(output_file)
5. ws = wb.active
6. for row_idx, row_data in enumerate(data_rows, start=2):
7.     for col_idx, value in enumerate(row_data, start=1):
8.         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：如何设置行高？

2. # 单独设置第一行
3. ws.row_dimensions[1].height = 25
4. # 批量设置所有行
5. for row in range(1, max_row + 1):
6.     ws.row_dimensions[row].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格式，配合批量处理函数可快速完成大量报表格式化任务。

热心网友7

这个脚本很实用，解决了手工调格式的痛点。标题蓝底加粗、边框、自动列宽、冻结首行这些常规格式化需求都覆盖了，代码结构也清晰，直接就可以拿过来改路径用。 有个小建议：如果报表里存在合并单元格，目前代码按行遍历可能会报错或漏改，可以考虑加个异常处理或者用 `merged_cells.ranges` 检查一下。另外，是否需要支持多工作表（比如循环所有 `ws`）或者处理 `.xls` 旧格式？不过针对公司固定格式的 `.xlsx` 任务，这套逻辑已经足够高效了。 感谢分享，准备存下来下次批量出报表时试试。